Returning Rows Where Some Are Null

Question

Post reply

Returning Rows Where Some Are Null

dec_obrien

Default port

Points: 1486
More actions
December 11, 2007 at 2:51 am

#179863

Hi,
I have two tables, one to track Members and one to track Member Usage. The Member table contains a Monthly Allocation to limit use and the Usage table contains each transaction for the Members. I want to write an sp to return a list of Members who didn't use their full allocation by month for the year. I can return the list of Members who have transactions in the month and are under allocation but not those Members who had no usage for the month i.e. the TransMonth and TransYear are NULL.
Here's what I've tried:
SELECT MemDetail.Allocation AS Alloc,
SUM(ISNULL(vwTransactions.ActQuantity, 0)) AS UsedUp,
vwTransactions.MemberID,
MONTH(vwTransactions.TransDate) AS TransMonth,
YEAR(vwTransactions.TransDate) AS TransYear,
MemDetail.FirstName,
MemDetail.Surname
FROM vwTransactions RIGHT OUTER JOIN
MemDetail ON vwTransactions.MemberID = MemDetail.MemberID
GROUP BY vwTransactions.MemberID,
MONTH(vwTransactions.TransDate),
YEAR(vwTransactions.TransDate),
MemDetail.Allocation,
MemDetail.FirstName,
MemDetail.Surname
HAVING SUM(ISNULL(vwTransactions.ActQuantity, 0)) < MemDetail.Allocation
Any help appreciated. Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Try reversing the direction of your JOIN.

SELECT

MemDetail.Allocation AS Alloc,

SUM(ISNULL(vwTransactions.ActQuantity, 0)) AS UsedUp,

vwTransactions.MemberID,

MONTH(vwTransactions.TransDate) AS TransMonth,

YEAR(vwTransactions.TransDate) AS TransYear,

MemDetail.FirstName,

MemDetail.Surname

FROM

-- vwTransactions

-- RIGHT OUTER JOIN MemDetail

-- ON vwTransactions.MemberID = MemDetail.MemberID

MemDetail

LEFT JOIN vwTransactions

ON vwTransactions.MemberID = MemDetail.MemberID

GROUP BY

vwTransactions.MemberID,

MONTH(vwTransactions.TransDate),

YEAR(vwTransactions.TransDate),

MemDetail.Allocation,

MemDetail.FirstName,

MemDetail.Surname

HAVING

SUM(ISNULL(vwTransactions.ActQuantity, 0)) < MemDetail.Allocation

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

dec_obrien Default port Points: 1486 More actions · Answer 2

Hi,

I tried that and it didn't work. What I want to end up with is the following:

So the results would contain an entry for each member for each month in which they didn't use their full quota. All I can get is the members that have used part of the quota for the month: if they haven't used any of their quota, they will only list for one month i.e. not every month that they haven't used it because TransMonth and TransYear are NULL and grouped together for all months.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I think you just need to change the LEFT JOIN to be an INNER JOIN?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

dec_obrien Default port Points: 1486 More actions · Answer 4

Hi,

If I change it to an inner join, I won't get any of the members who had no usage for a particular month which I also need.

I am thinking I need to maybe make a temporary table and populate it with the 12 months and then do a left join to that to first get all of the months included. It seems a bit long-winded though. Any other "slick" ideas??

Thanks.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 5

select

mem.FirstName,

mem.Surname,

mem.Allocation as Allocated,

isnull(usage.[Quantity],0) as UsedUp,

max( 0, mem.Allocation - isnull(usage.[Quantity],0)) as LeftOver

from dbo.MemDetail mem

cross join (

select 2007 as [year], 1 as [month]

union all

select 2007 as [year], 2 as [month]

union all

select 2007 as [year], 3 as [month]

union all

select 2007 as [year], 4 as [month]

union all

select 2007 as [year], 5 as [month]

union all

select 2007 as [year], 6 as [month]

union all

select 2007 as [year], 7 as [month]

union all

select 2007 as [year], 8 as [month]

union all

select 2007 as [year], 9 as [month]

union all

select 2007 as [year], 10 as [month]

union all

select 2007 as [year], 11 as [month]

union all

select 2007 as [year], 12 as [month]

) per

left outer join (

select

trn.MemberID,

YEAR(trn.TransDate) AS [Year],

MONTH(trn.TransDate) as [Month],

SUM(trn.ActQuantity) as [Quantity]

from dbo.vwTransactions trn

group by

trn.MemberID,

YEAR(trn.TransDate),

MONTH(trn.TransDate)

) usage on (usage.MemberID = mem.MemberID and usage.[Year] = per.[Year] and usage.[Month] = per.[Month])

where mem.Allocation > isnull(usage.[Quantity],0)

I would have added a 3rd table (or view as your prefix suggests) -vwAllocations- listing for each period plus member the allocated quantity in that period. Using this 3rd table you could then replace the above cross join with an inner join to get just those member - periods that allocations exist for.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

dec_obrien Default port Points: 1486 More actions · Answer 6

Hi,

Yes, I think that's what I'm looking for: I'll give it a go later on this evening.

Thanks.