[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

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?
0
jimwarrenus
Asked:
jimwarrenus
2 Solutions
 
sdstuberCommented:
Datepart(mm,[MAILDATE]) <> Datepart(mm,getdate())  


you're explicitly skipping data that is for the current month
0
 
Ashish PatelCommented:
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

0
 
jimwarrenusAuthor Commented:
Got it.....thanks!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now