Zero record

[Guest]

Dear,
Please see my sql statement below:
---Start
SELECT top 100 percent count(1) * 8 MWH ,YearCol,
case MonthCol when 1 then 'January' when 2 then 'February' when 3 then 'March'
when 4 then 'April' when 5 then 'May' when 6 then 'June'
when 7 then 'July' when 8 then 'August' when 9 then 'September'
when 10 then 'October' when 11 then 'November' when 12 then
'December' end as MName, Plancode
FROM dcss_calendar WHERE daycode=2 and plancode=2 and yearcol=2005 group
by yearcol,monthcol,plancode order by yearcol,monthcol,plancode
--End

And now, the result is the following:

MWH Yearcol MName PlanCode
16 2005 January 2
16 2005 February 2
16 2005 March 2
24 2005 April 2
16 2005 May 2
16 2005 June 2
16 2005 July 2
16 2005 August 2
24 2005 September 2
16 2005 October 2
16 2005 December 2

As you could see, since November 2005 does not have any record, the whole
month is skipped by the statment. How do I change my sql into something that
will return
everthing like the way it was, plus the missing month November with a 0
count instead of whole month line is missing?

Thank you!
Martin

 

[Andrew Backer]

Thats just the way group-by queries work. No real way around it, that
I can recall right now, except...

Make a table (temporary or otherwise) that contains all the dates (or
date ranges, month numbers, etc) that you want to group by. You would
then join the two tables and group by the dates table, so there is
always a row, and sum on the calendar table.

A very appropriate place to post this might be in the SQL Server
groups. Though this is happening inside an ADP, it's really a question
about how to query. That said, if you would like a little more help on
this, let me know.

In a more 'warehousy' world you would have a table like such :

create table DateKeys (
dateKeyId identity
, yearNum int
, monthNum int
, quarterNum int
, otherCalc int
, dateStart datetime
, dateEnd datetime
)

other table (
FK : dateKeyId
PlanName NVARCHAR(200)
...
)

Your date keys are stored separatly, and then your other table (any
table in your db) can reference them. You just have to take some care
making the date key table so that you have all the calculated fields
you need. Then you have the best 'o both worlds.

- Andrew Backker

 

[Andrew Backer]

Thats just the way group-by queries work. No real way around it, that
I can recall right now, except...

Make a table (temporary or otherwise) that contains all the dates (or
date ranges, month numbers, etc) that you want to group by. You would
then join the two tables and group by the dates table, so there is
always a row, and sum on the calendar table.

A very appropriate place to post this might be in the SQL Server
groups. Though this is happening inside an ADP, it's really a question
about how to query. That said, if you would like a little more help on
this, let me know.

In a more 'warehousy' world you would have a table like such :

create table DateKeys (
dateKeyId identity
, yearNum int
, monthNum int
, quarterNum int
, otherCalc int
, dateStart datetime
, dateEnd datetime
)

other table (
FK : dateKeyId
PlanName NVARCHAR(200)
...
)

Your date keys are stored separatly, and then your other table (any
table in your db) can reference them. You just have to take some care
making the date key table so that you have all the calculated fields
you need. Then you have the best 'o both worlds.

HTH,
Andrew Backer
gmail://abacker