December 15, 2005 at 8:55 am
I have the following query which is returning me the information I need. What I'd like to do is have it return the actual day of the week (Monday, Tuesday, etc.) instead of the actual number of the day of the week. Any suggestions? The query is actually pulling from a view that we created. I've included the code for the view first.
CREATE VIEW invw_incidentcounts AS
SELECT Convert(Char, in_q.queuein, 101) AS [OccurDate],
DATEPART(hh,in_q.queuein) AS [Hour_Day],
DATEPART(wk,in_q.queuein) AS [Week],
DATEPART(dw,in_q.queuein) AS [Day_Week],
COUNT(*) AS [Incidents],
'Q' AS [Type]
FROM in_queueinfo in_q
GROUP BY Convert(Char, in_q.queuein, 101),
DATEPART(hh,in_q.queuein),
DATEPART(wk,in_q.queuein),
DATEPART(dw,in_q.queuein)
UNION
SELECT Convert(Char, in_i.close_date, 101) AS [OccurDate],
DATEPART(hh,in_i.close_date) AS [Hour_Day],
DATEPART(wk,in_i.close_date) AS [Week],
DATEPART(dw,in_i.close_date) AS [Day_Week],
COUNT(*) AS [Incidents],
'C' AS [Type]
FROM in_incident in_i
GROUP BY
Convert(Char, in_i.close_date, 101),
DATEPART(hh,in_i.close_date),
DATEPART(wk,in_i.close_date),
DATEPART(dw,in_i.close_date)
SELECT invw_i.OccurDate,
Week,
Day_week,
Hour_Day,
SUM(CASE invw_i.type WHEN 'Q' THEN invw_i.Incidents END) AS [Queued Cases],
SUM(CASE invw_i.type WHEN 'C' THEN invw_i.Incidents END) AS [Closed Cases]
FROM invw_incidentcounts invw_i
GROUP BY OccurDate,
Week,
Day_week,
Hour_Day
ORDER BY DATEPART(yy,invw_i.occurdate),
DATEPART(mm,invw_i.occurdate),
DATEPART(dd,invw_i.occurdate),
Hour_Day
December 15, 2005 at 9:03 am
To get the actual name of the day instead of the number, you use the DATENAME function:
SELECT DATENAME(DW, GETDATE())
Hope this helps.
SQL Server Helper - FAQ - About Dates
http://www.sql-server-helper.com/faq/dates-p01.aspx
December 15, 2005 at 9:32 am
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply