SQL aggregates including missing dates
-
I have an invoice table which I would like to get the counts for the number of invoices per subsidiary per invoice date. But I also want to include missing dates and have a count of zero for these.
My query looks like this:
select subsidiary, invoice_date, count(*)
from invoices
where invoice_date between '9/1/2003' and '9/30/2003'
group by subsidiary, invoice_date
order by subsidiary, invoice_date
This of course, only returns counts for the the dates actually recieved invoices. How can I include the zero-invoice dates?
THANKS
-
Hello Jay,
as far as I can remember, there were several such questions not long ago - if you search the forums, you'll probably find the answer quickly. Just in a few words - what I would do is to create a table that contains every date of a year (or every date that should be in the report, maybe it's only workdays), start the select from that table with left join ON subsidiary.invoice_date. In case the date is stored in the datetime format, you'll have to convert it so that only the date remains.
-
You could write a UDF that returns a table with one row for each date in the period, and then left join your invoices table to the UDF
e.g. the function would look something like this
create function dbo.GetDates(@st datetime,@end datetime)
RETURNS @Dates TABLE (dt datetime)
as
begin
declare @dt datetime
set @dt = @st
while @dt <=@end
begin
insert @dates values(@dt)
set @dt = dateadd(day,1,@dt)
end
return
end
go
and your query would look something like this:-
select count(inv.dt),d.dt from dbo.getdates('12 sep 2003','25 sep 2003') d
left join invoices inv on inv.dt = d.dt
group by d.dt
order by d.dt
-
Vladan - thanks for the info. I'll try a search. In the meantime, I've tried your method with a temp table loaded with all dates but still can't get the zero dates to appear...what is wrong with this query?
select i.subsidiary, tt.invoice_date, count(i.invoice_date)
from temp_date_table tt LEFT OUTER JOIN invoices i on tt.invoice_date = i.invoice_date
where i.invoice_date between '9/1/2003' and '9/30/2003'
group by i.subsidiary, tt.invoice_date
order by i.subsidiary, tt.invoice_date
invoice_date
---- ------------------------------------------------------ -----------
005 2003-09-01 00:00:00.000 355
005 2003-09-02 00:00:00.000 354
005 2003-09-03 00:00:00.000 279
005 2003-09-04 00:00:00.000 389
005 2003-09-05 00:00:00.000 278
005 2003-09-08 00:00:00.000 296
005 2003-09-09 00:00:00.000 339
005 2003-09-10 00:00:00.000 394
005 2003-09-11 00:00:00.000 441
005 2003-09-12 00:00:00.000 283
005 2003-09-15 00:00:00.000 240
005 2003-09-16 00:00:00.000 308
005 2003-09-17 00:00:00.000 280
005 2003-09-18 00:00:00.000 251
005 2003-09-19 00:00:00.000 281
005 2003-09-22 00:00:00.000 296
005 2003-09-23 00:00:00.000 381
005 2003-09-24 00:00:00.000 286
005 2003-09-25 00:00:00.000 390
005 2003-09-26 00:00:00.000 1080
005 2003-09-29 00:00:00.000 1118
005 2003-09-30 00:00:00.000 600
-
count(i.invoice_date) eliminates all NULL values in the field i.invoice_date, that is, all days without invoices. Try count(*) instead, that should help.
BTW, one of the reasons why I proposed a date table is that our managers sometimes have special (and rather strange) wishes as to what should and what should not be displayed in the report - and such approach is variable, because it allows you to skip certain dates that they don't want to see for some reason.
-
Beleive it or not, I'm still coming up missing the dates using count(*). I know it's something stupid with the nulls. I may try using a temp table as an interim fix.
The temp table for dates is more flexible for us as well. I want to eventually adapt it for holidays.
-
Two things,
Firstly, the where clause is restricting your selection based on invoices when it should be on temp_date_table, eg
where tt.invoice_date between '9/1/2003' and '9/30/2003'
Secondly, your join will result in NULLs for subsidiary where a date is not present in invoices which will cause problems in aggregation. What you need to do is have table of subsidiaries and add it to the join.
create table temp_sub (subsidiary char(3))
insert into temp_sub select distinct subsidiary from invoices
selectts.subsidiary, tt.invoice_date , count(i.invoice_date)
fromtemp_sub ts
cross join temp_date_table tt
LEFT OUTER JOIN invoices i
on i.subsidiary = ts.subsidiary
and i.invoice_date = tt.invoice_date
where tt.invoice_date between '9/1/2003' and '9/30/2003'
group by ts.subsidiary, tt.invoice_date
order by ts.subsidiary, tt.invoice_date
Far away is close at hand in the images of elsewhere.
Anon. -
If the periods in question will always have fewer than 256 days, then you could use the existing numbers table:
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = '20030901', @EndDate = '20030930'
SELECT i.Subsidiary, v.IDate, COUNT(i.PK)
FROM Invoices i RIGHT JOIN
(SELECT @StartDate + Number IDate
FROM master..spt_values
WHERE Type = 'P' AND Number <= @EndDate - @StartDate) v
ON i.Invoice_Date = v.IDate
GROUP BY Subsidiary, v.IDate
ORDER BY Subsidiary, v.IDate
This assumes that your dates have no time components.
Of course the spt_values table is undocumented and therefore is not guaranteed to exist in future releases of SQL Server.
--Jonathan
--Jonathan -
quote:
count(i.invoice_date) eliminates all NULL values in the field i.invoice_date, that is, all days without invoices. Try count(*) instead, that should help.I think you've got that backwards. The COUNT(ColName) aggregate function evaluates to zero for a null set, which is what is needed here. COUNT(*) will include all rows, so groupings with no rows in the Invoices table will be counted as having had one invoice. I suggested using the primary key in my solution as that may be less confusing to the reader than using any other non-nullable column (which would also work as long as you don't also use DISTINCT).
--Jonathan
--Jonathan -
Oops..
I shouldn't reply when I don't have enough time to think about what I'm writing. Thanks for correction, Jonathan+David, of course you're right - and I hope I'll be more careful next time. -
quote:
I shouldn't reply when I don't have enough time to think about what I'm writingShould'nt worry Vladan, I seem to be doing this a lot lately and even worse, opening my mouth, putting foot in it, before engaging brain
. Wish I was brave enough to post my first thoughts, always afraid someone will say "hey look a dork".Far away is close at hand in the images of elsewhere.
Anon.
I shouldn't reply when I don't have enough time to think about what I'm writing. Thanks for correction, Jonathan+David, of course you're right - and I hope I'll be more careful next time.
. Wish I was brave enough to post my first thoughts, always afraid someone will say "hey look a dork".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply