Group by date question

# Thread: Group by date question

1. Junior Member
Join Date
Dec 1969
Posts
28

## Group by date question

I have a stored procedure which groups records according to week for a given fiscal year. There is a field in the table called &#039;invoice date&#039;. It is possible to have no invoices for a given week. When i run my page i show last fiscal years sales numbers and next to it i show this years sales numbers (by changing the dates in the sp). My question is how can i make sure that each week has a row even if no sales were recorded for that week? In other words, I would like to output a 0 for sales and units and the date of the week even if no sales for that week. Below is my code:<BR><BR>SELECT Week = DateAdd(day, 1 - DATEPART(dw, InvoiceDate), CONVERT(datetime, CONVERT(varchar,InvoiceDate,112) ) ) ,<BR> SUM(Units), SUM(Amount) <BR>FROM AccountTable<BR>WHERE InvoiceDate BETWEEN &#039;1/1/2002&#039; AND &#039;12/31/2002&#039;<BR>AND Code = &#039;A123&#039;<BR>GROUP BY DateAdd(day, 1 - DATEPART(dw, InvoiceDate), CONVERT(datetime, CONVERT(varchar,InvoiceDate,112) ) )<BR>ORDER BY Week <BR><BR>I was thinking of left joining a table with the weeks of the year and check for nulls and return 0. Is there another way? A better way?

2. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## RE: Group by date question

No, my first guess is that you&#039;ll need to have a table of weeks.<BR><BR>This can simply be a single field table w/ 52 rows.<BR><BR>Then, you&#039;ll actually need to just left join the table. But, SUM() should handle the nulls for you. If it doesn&#039;t a simple ISNULL(Units, 0) should work.

3. Senior Member
Join Date
Dec 1969
Posts
3,195

## I'm not sure but can't you

do something like<BR><BR>SUM(CASE WHEN Units IS NULL THEN 0<BR> ELSE units END)<BR><BR>right in your column list?

4. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Won't help. The group by..

.. won&#039;t include records that don&#039;t exist...

5. Senior Member
Join Date
Dec 1969
Posts
3,195

## Now I see

that ; )<BR><BR>Then he should listen to you and join to something that has all the dates. <BR><BR><BR><BR><BR>

6. Junior Member
Join Date
Dec 1969
Posts
28

## RE: Group by date question

Yeah, I thought it would take joining in a week table. I was hoping to find a way to do it without one. Thanks for the help.

7. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## If SQL Server 2000, you could..

.. mimick it - have a function that returns a populated table.<BR><BR>That way, you don&#039;t have to have a junk table in the DB...

8. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: If SQL Server 2000, you could..

It&#039;s a good idea to have a table Numbers with one column N in the database which is populated from 1 to 10000<BR><BR>You could use that table in lots of circumstances

9. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Actually have to agree..

.. with you there.<BR><BR>I&#039;ve had numerous situations like Sinner&#039;s.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•