sort by day of week 2

[childrenfirst]

Hi,

This is probably too easy for experts in this forum, but I just can't figure it out by myself...

I have a query like this:

SELCTE DATENAME(dw,[Date]),COUNT(*)
FROM Dates, TimeOff
WHERE DATENAME(dw,[DATE]) BETWEEN StartDate AND EndDate
GROUP BY DATENAME(dw,[Date])

It returns results like this:

Friday 1
Saturday 1
Tuesday 3
Wednesday 2

But I would like to sort the results by day of week instead of alphabetically.

I tried to add ORDER BY DATENAME(dw,[DATE]) at the end of the query but it did not work.

Any advice will be greatly appreciated!

 

[gmmastros]

DatePart returns an integer.

SELCTE DATENAME(dw,[Date]),COUNT(*)
FROM Dates, TimeOff
WHERE DATENAME(dw,[DATE]) BETWEEN StartDate AND EndDate
GROUP BY DATENAME(dw,[Date])
[!]Order By DatePart(weekday, [Date])[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

lookup datepart(dw) in bol that will give you the day of the week

select datepart(dw,getdate())

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[childrenfirst]

Hi,

Thank you for your advice. So, I modified my query to:

SELCTE DATEPART(dw,[Date]),COUNT(*)
FROM Dates, TimeOff
WHERE DATEPART(dw,[DATE]) BETWEEN StartDate AND EndDate
GROUP BY DATEPART(dw,[Date])
ORDER BY DATEPART(dw,[Date])

and got the results like:

3 3
4 2
6 1
7 1

Maybe I confused you when I said that I wanted the result to be in the order of day the of week... I would like results to show:

Tuesday 3
Wednesday 2
Friday 1
Saturday 1

Please advise. Thank you!

 

[gmmastros]

In my example, only the order by had the datepart. The rest of the code was unchanged (so that it used datename).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I understand that it's the group by that is causing you problems.

You can modify your query like this...

SELECT DATENAME(dw,[Date])[!], DatePart(dw, [Date])[/!],COUNT(*)
FROM Dates, TimeOff
WHERE DATENAME(dw,[DATE]) BETWEEN StartDate AND EndDate
GROUP BY DATENAME(dw,[Date])[!], DatePart(dw, [Date])[/!]
[!]Order By DatePart(dw, [Date])[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[childrenfirst]

Hello George,

I got an error message after modifying the code to your last posting:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Any idea?

 

[SQLDenis]

I believe this is the line that causes the error

WHERE DATENAME(dw,[DATE]) BETWEEN StartDate AND EndDate

change to

WHERE [DATE] BETWEEN StartDate AND EndDate




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[childrenfirst]

Hello George and Denis,

Thank you both! I modified the query based on both of your last postings, and it worked like charm!

Have a great day Here are some stars for you!!