We help IT Professionals succeed at work.

month summary query (with datepart) ignoring first month in each year?

i am using this query to summarize order data by month...
select  datepart(mm,[MAILDATE]) as monthnum,
         min([MAILDATE]) as [Month Commencing]
         , count(*) as #Orders
from ORDERHEADER
where
     FULLORDERNO not like 'W%' and STATUS in ( 'PC','PR') and ENTRYOPER <> 'WEBMACS' and COMPANY = '01' and
     Datepart(mm,[MAILDATE]) <> Datepart(mm,getdate())
group by DatePart(yy,[MAILDATE]),Datepart(mm,[MAILDATE])
order by [Month Commencing]


a snippet of the output looks like this:
monthnum      Month Commencing      #Orders
10      20061001                          18595
11      20061101                          14604
12      20061201                          13621
2      20070201                          14054
3      20070301                           20595

it skipped month 1 of 2007  --- ie, month commencing 20070101

what have i missed in the query to get january to show up?
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Datepart(mm,[MAILDATE]) <> Datepart(mm,getdate())  


you're explicitly skipping data that is for the current month
Why you are adding the condition to neglect the current month by using this in where clause
and     Datepart(mm,[MAILDATE]) <> Datepart(mm,getdate())

Rahter just use this.
select  datepart(mm,[MAILDATE]) as monthnum,
         min([MAILDATE]) as [Month Commencing]
         , count(*) as #Orders
from ORDERHEADER
where
     FULLORDERNO not like 'W%' and STATUS in ( 'PC','PR') and ENTRYOPER <> 'WEBMACS' and COMPANY = '01' 
group by DatePart(yy,[MAILDATE]),Datepart(mm,[MAILDATE])
order by [Month Commencing]

Open in new window

Author

Commented:
Got it.....thanks!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.