Show records if SUM does not return any records

thjens

Hi everybody.
I'm trying to write a view that will also show 0 in a value even if the SUM function does not return any records.
I'll try to explain by an example.

SELECT TOP (100) PERCENT dbo.[BAXT AS$TS Shops].EntryNo_, dbo.[BAXT AS$TS Shops].ShopName, dbo.[BAXT AS$Tradesolution].ItemNo,
SUM(dbo.[BAXT AS$Tradesolution].NavAntFaktFpack) AS NavAntFaktFpack
FROM dbo.[BAXT AS$Tradesolution] INNER JOIN
dbo.[BAXT AS$TS Shops] ON dbo.[BAXT AS$Tradesolution].[TS EntryNo] = dbo.[BAXT AS$TS Shops].EntryNo_
WHERE (dbo.[BAXT AS$Tradesolution].Fakturadato BETWEEN CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME,
'2010-01-31 00:00:00', 102)) AND (dbo.[BAXT AS$Tradesolution].AvdelingF = '40') AND (dbo.[BAXT AS$Tradesolution].Bisca = 0) AND
(dbo.[BAXT AS$Tradesolution].ItemNo = 100200 OR
dbo.[BAXT AS$Tradesolution].ItemNo = 100201 OR
dbo.[BAXT AS$Tradesolution].ItemNo = 100203 OR
dbo.[BAXT AS$Tradesolution].ItemNo = 106041)
GROUP BY dbo.[BAXT AS$TS Shops].EntryNo_, dbo.[BAXT AS$TS Shops].ShopName, dbo.[BAXT AS$Tradesolution].ItemNo
HAVING (dbo.[BAXT AS$TS Shops].EntryNo_ = 1107)
ORDER BY EntryNo_

This view will result in three records, since there is data for the three first Items. There is no sales data for itemNo 106041 for Shop 1107.
What I want is (in this case) is four records. The value in colum NavAntFaktFpack should be 0.

Now, if the filter on Shops (there are thousands of shops) is set to 1107,1108,1109 on the same items, and the sales (in the period) for shop 1108 is nothing, and shop 1109 has sale on item 100200 I would want this as the result:
ShopNo ItemNo No
1107 100200 16,00
1107 100201 16,00
1107 100203 16,00
1107 106041 0
1108 100200 0
1108 100201 0
1108 100203 0
1108 106041 0
1109 100200 32,00
1109 100201 0
1109 100203 0
1109 106041 0

I have tried with a UNION ALL, and the with the HAVING clause like this:
HAVING (SUM([BAXT AS$Tradesolution].NavAntFaktFpack) IS NULL) AND ([BAXT AS$TS Shops].EntryNo_ = 1107) --or the rest of the shops filter
Nothing changes. Tried again a number of different things (isnull, case, coalsce....), nothing.....

I'm at a loss on how to "generate" the missing data. The only solution I can think of is to generate at table that contiains all the records (from the active filter) with 0 as value, and then insert the result from the above view into this table. But, I dont know how to do that either )-:

This is a common question/challenge for companies in sales (in this case food), when you want to see if a shop in a given month suddenly stops buying a particular item alltogheter.
I would think there is a smooth way to solve this? What I mean is, I can't (or rather my customer) be the first to come across this? I would not be supprised though ((-:

Any help would be greatly appreciated
Regards,
Thomas Jensen
Norway

dkone

Hi

Maybe you could start your query from your Item Table, and do a subquery inside it.

e.g. : Select ItemName,(Select SUM() [...] FROM Tradesolution where ItemNo=i1.ItemNo) from Item i1

you will have all your items, and corresponding sums.

Regards,

dkone.

stryk

What about this:

HAVING (dbo.[BAXT AS$TS Shops].EntryNo_ = 1107) AND
           (SUM(dbo.[BAXT AS$Tradesolution].NavAntFaktFpack) <> 0)