Query Help!!! - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help!!!

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-27 : 09:44:34

Hi Guys

I really hope you can help me with this. I am using the following query:

SELECT
DATENAME(dw,r.DateEntered) AS 'DayOfWeek'
,COUNT(ReceiptId) AS 'Orders'

FROM Receipt AS r WITH (NOLOCK)

WHERE DATEDIFF(wk,r.DateEntered,GETDATE()) = 0
AND DATEDIFF(yy,r.DateEntered,GETDATE()) = 0

GROUP BY DATENAME(dw,r.DateEntered)

ORDER BY
DATENAME(dw,r.DateEntered) ASC

This query is meant to produce the number of products sold this week.
I get the following results:
Monday 14932
Sunday 13511
Thursday 8548
Tuesday 20860
Wednesday 21199

The first thing is that the first day of the week for here (UK) is Monday, so there shouldn’t be no Sunday figures as of yet because today is Thursday.
Secondly how come the day names are not being shown in order?

Thanking you in advance!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 09:47:56

[code]SELECT DATENAME(dw, r.DateEntered) AS [DayOfWeek],
COUNT(ReceiptId) AS Orders
FROM Receipt AS r WITH (NOLOCK)
WHERE DATEDIFF(wk, r.DateEntered, GETDATE()) = 0
GROUP BY DATENAME(dw, r.DateEntered),
DATEPART(dw, r.DateEntered)
ORDER BY DATEPART(dw, r.DateEntered)[/code]

E 12°55'05.63"
N 56°04'39.26"