Different count for order_date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Different count for order_date

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-04-21 : 13:55:14

Hi All,

I have the below query:

select (convert(varchar(2), month(o.order_received_date)) +'/1/' +
convert(varchar(4), year (o.order_received_date))) Order_date,count(*)
into #Temp_Orders
from Orders o with (nolock)
inner join Call_Log cl with (nolock) on o.order_id=cl.order_id
WHERE
(o.order_received_date >='3/1/2009' and o.order_received_date <='4/1/2009')
group by (convert(varchar(2), month(o.order_received_date)) +'/1/' +
convert(varchar(4), year (o.order_received_date)))

Using this query I get the following results:
ORDER_DATE cOUNT
3/1/2009 878

BUT.. when I use just order_received_date instead of month(o.order_received_date)) +'/1/' +
convert(varchar(4), year (o.order_received_date)))

I get the following results:
ORDER_DATE cOUNT
3/1/2009 23
.. ..
.. ..
3/31/2009 40
Total 1250

I do not understand why there could be a difference in the count..
What I want is to display the count for each month with the order_date as the first of the month

Thanks,
Petronas

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-21 : 14:30:58

One, You might want to change your query to this::

select  
	DATEADD(mm, DATEDIFF(mm,0,o.order_received_date), 0)  Order_date,
	count(*)
into 
	#Temp_Orders
from 
	Orders o with (nolock) 
	inner join Call_Log cl with (nolock) on o.order_id=cl.order_id
WHERE
	(o.order_received_date >='3/1/2009' and o.order_received_date <='4/1/2009')
group by  
	DATEADD(mm, DATEDIFF(mm,0,o.order_received_date), 0)

What count mismatch are you concerned with, 878 and 1250??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources