CASE Statement with Date
-
Need help on writing case statement
We were assigned to modify the existing query. So, we need to write a Case Statement for two fields.
------------
1. We have a parameter called @StartDate (dateTime)
2. When the user type in a date, it will generate the days back for seven (7) days, EXCLUDING today's date. For example, if the user types in the date - for example ( Saturday - October 10, 2015), the query will generate seven days back, starting October 9 (Friday) and back to October 4, 2015 (Sunday).
3. Each row will contain the time (30 minutes interval), dayofWeek and the date (see photo below)
----------------------------
What we need to do: -- Look at photo attached.
1. Fill the OriginalDate field with the date that match the DayofWeek. For example, DayofWeek = 2, then, row # 1 for this field should be "2015-10-05 00:00:00.000". The parameter @Startdate will guide it.
DayofWeek = 3 (Tuesday); 4 = Wednesday, 5 = Thursday and 6 = Friday
2. Fill the Row_date with the OriginalDate + StartTime (converted). So, Row # 1 should display:
"2015-10-05 06:30:00.000" and row # 5 in Row_date column should display: "2015-10-05 08:30:00.000"
(I think I can figure out of how to do this part. It is a tricky one on converting substring. But, the main part is to get the main date first.)
--------------
Is it possible to fill this information using the CASE statement?
Thanks.
-
You don't need a CASE statement for this. Post the code you used to generate the dates/times and we'll show you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you very much for your reply and I do apologize for the delays in my response.
The query itself combined three subqueries.
We would like to compare the number of last week (last 7 days), three months and six months based on particular day. For example, Wednesday of last week will be comparing with the average Wednesday for three and for six months.
I also create a screen shot for your review.
----------------------------
1. The user will enter a parameter date (any date)
2. The query will go back for seven days. For example, 10/10/2015, which was Saturday was entered. The query will go back seven days, Friday 10/9/2015 and back to 10/03/2015. Day of week will be displayed of what day it was.
3. Each day, we will have 30 minutes interval, see the "starttime" field.
--------------------------------
When running this query, I receive an empty (NULL) value in the date field.
For example,
Row 1, should be displayed as: '2015-10-05 06:30:00.000' based on the date + starttime of 0630
Row 5, should be displayed as: '2015-10-05 08:30:00.000' based on the date + starttime of 0830
-----------------------------------
I think, I can manipulate to find the way to display the time. However, I have the problem of how to put the date that correspond to the 'Day of Week' into the query without using the CASE statement.
The query is as follow:
--Declare @StartDate as Datetime
--Set @StartDate = '2015-10-10'
--(The @startdate can be any date but it will go back seven days.)
Select c.Row_date, e.DayofWeek, e.STARTTIME,
CASE WHEN c.TotalCallsOffered IS NULL THEN 0 ELSE c.TotalCallsOffered END as TotalCallsOffered
d.TotalCallsOfferedThreeMonths, e.TOTALCALLSOFFEREDSIXMONTHS
from
--Six Months Number
(
SELECT b.DAYOFWEEK, SUBSTRING(b.STARTTIME,1,2) + Substring(b.StartTime,4,2) as STARTTIME, SUM(b.CALLSOFFERED) OVER (PARTITION BY b.DAYOFWEEK, b.STARTTIME) AS TOTALCALLSOFFEREDSIXMONTHS
FROM (SELECT a.DayofWeek, a.StartTime, SUM(a.AnsweredCalls + a.AbandonCalls) AS CallsOffered
FROM (SELECT DATEADD(DAY, DATEDIFF(Day, 0, cqe.opened_at), 0) AS Row_Date, DATEPART(dw, cqe.opened_at) AS DayofWeek, CONVERT(char(5),
DATEADD(minute, CASE WHEN (datepart(minute, cqe.opened_at)) BETWEEN 0 AND 29 THEN 0 WHEN (datepart(minute, opened_at))
BETWEEN 30 AND 59 THEN 30 END, DATEADD(minute, - DATEPART(minute, cqe.opened_at), cqe.opened_at)), 108) AS StartTime,
AnsweredCalls,
AbandonCalls
FROM chat_queue_entry cqe INNER JOIN
chat_message cm ON cqe.chat_channel = cm.chat_channel
WHERE (cqe.opened_at >= @StartDate-181) AND (cqe.opened_at < DATEADD(Day, 1, @StartDate))
GROUP BY cqe.number, cqe.dv_action, cqe.opened_at) a
GROUP BY a.DayofWeek, a.StartTime) b
GROUP BY b.DayOfWeek, b.StartTime, b.CallsOffered
)e
LEFT JOIN
--Three Months Number
(
SELECT b.DAYOFWEEK, SUBSTRING(b.STARTTIME,1,2) + Substring(b.StartTime,4,2) as STARTTIME, SUM(b.CALLSOFFERED) OVER (PARTITION BY b.DAYOFWEEK, b.STARTTIME) AS TOTALCALLSOFFEREDTHREEMONTHS
FROM (SELECT a.DayofWeek, a.StartTime, SUM(a.AnsweredCalls + a.AbandonCalls) AS CallsOffered
FROM (SELECT DATEADD(DAY, DATEDIFF(Day, 0, cqe.opened_at), 0) AS Row_Date, DATEPART(dw, cqe.opened_at) AS DayofWeek, CONVERT(char(5),
DATEADD(minute, CASE WHEN (datepart(minute, cqe.opened_at)) BETWEEN 0 AND 29 THEN 0 WHEN (datepart(minute, opened_at))
BETWEEN 30 AND 59 THEN 30 END, DATEADD(minute, - DATEPART(minute, cqe.opened_at), cqe.opened_at)), 108) AS StartTime,
AnsweredCalls,
AbandonCalls
FROM chat_queue_entry cqe INNER JOIN
chat_message cm ON cqe.chat_channel = cm.chat_channel
WHERE (cqe.opened_at >= @StartDate-90) AND (cqe.opened_at < DATEADD(Day, 1, @StartDate))
GROUP BY cqe.dv_action, cqe.opened_at) a
GROUP BY a.DayofWeek, a.StartTime) b
GROUP BY b.DayOfWeek, b.StartTime, b.CallsOffered
)d
ON e.DayofWeek = d.DayofWeek and e.StartTime = d.STARTTIME
LEFT JOIN
--Last Week Number
(
SELECT b.ROW_DATE, b.OriginalDate, b.DAYOFWEEK, SUBSTRING(b.STARTTIME,1,2) + Substring(b.StartTime,4,2) as STARTTIME, SUM(b.CALLSOFFERED) OVER (PARTITION BY b.DAYOFWEEK, b.STARTTIME) AS TotalCallsOffered
FROM (SELECT a.Row_Date + a.StartTime AS Row_date, a.Row_Date as OriginalDate, a.DayofWeek, a.StartTime, SUM(a.AnsweredCalls + a.AbandonCalls)
AS CallsOffered
FROM (SELECT DATEADD(DAY, DATEDIFF(Day, 0, cqe.opened_at), 0) AS Row_Date, DATEPART(dw, cqe.opened_at) AS DayofWeek, CONVERT(char(5),
DATEADD(minute, CASE WHEN (datepart(minute, cqe.opened_at)) BETWEEN 0 AND 29 THEN 0 WHEN (datepart(minute, opened_at))
BETWEEN 30 AND 59 THEN 30 END, DATEADD(minute, - DATEPART(minute, cqe.opened_at), cqe.opened_at)), 108) AS StartTime,
AnsweredCalls,
AbandonCalls
FROM chat_queue_entry cqe INNER JOIN
chat_message cm ON cqe.chat_channel = cm.chat_channel
WHERE (cqe.opened_at >= @StartDate-6) AND (cqe.opened_at < DATEADD(Day, 1, @StartDate))
GROUP BY cqe.dv_action, cqe.opened_at) a
GROUP BY a.Row_Date + a.StartTime, a.Row_Date, a.DayofWeek, a.StartTime) b
GROUP BY b.row_date, b.OriginalDate, b.DayOfWeek, b.StartTime, b.CallsOffered
)c
ON c.DayofWeek = e.DayofWeek and c.StartTime = e.STARTTIME
----------------------
In order to test it, I declared the value:
Declare @StartDate as Datetime
Set @StartDate = '2015-10-10'
(The startdate can be any date but it will go back seven days.)
----------------------
You stated that the CASE statement might not be necessary at all. Is it possible to do it in order to replace the NULL value?
Thanks.
-
First, create a "standard" tally table (a table of sequential numbers). I can't provide code because the filter thinks it's "sql injection". After creating the tally table, then you can do this to gen the dates and times:
Declare @StartDate as datetime
Set @StartDate = '20151010'
Select Dateadd(Minute, minutes.tally * 30, Dateadd(Day, -days.tally, @StartDate))
From (
Select Dateadd(Day, Datediff(Day, 0, @StartDate), 0) AS StartTimeMidnight
) As force_start_time_to_midnight
Inner Join dbo.tally days ON days.tally Between 0 And 6
Inner Join dbo.tally minutes ON minutes.tally >= 0 And minutes.tally < (24 * 60) / 30
Order By 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thank you very much for suggesting the tally table. This prompted me to think of using the 'with cte' statement.
I did not use the tally. But decide to use the 'with cte' and I got the result that I needed to join the subqueries.
The codes are the following:
Declare @StartDate as Datetime
Set @StartDate = '2015-10-10'
;WITH cte(DateAndHour) AS
(
SELECT @StartDate - 6
UNION ALL
SELECT DATEADD(MINUTE,30, DateAndHour)
FROM cte
WHERE DATEADD(MINUTE,30, DateAndHour) < @StartDate + 1
)
SELECT * from cte
OPTION (MAXRECURSION 0);
-
You need to be aware that recursion will be much, much slower, and much, much more CPU and memory overhead, than a tally table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Tamrak (10/23/2015)
Thank you very much for suggesting the tally table. This prompted me to think of using the 'with cte' statement.I did not use the tally. But decide to use the 'with cte' and I got the result that I needed to join the subqueries.
The codes are the following:
Declare @StartDate as Datetime
Set @StartDate = '2015-10-10'
;WITH cte(DateAndHour) AS
(
SELECT @StartDate - 6
UNION ALL
SELECT DATEADD(MINUTE,30, DateAndHour)
FROM cte
WHERE DATEADD(MINUTE,30, DateAndHour) < @StartDate + 1
)
SELECT * from cte
OPTION (MAXRECURSION 0);
To drive the point home that Scott just made, please read the following article on why you should NOT use rCTEs that count/increment.
http://www.sqlservercentral.com/articles/T-SQL/74118/
Shoot... even a properly formed While Loop will beat an rCTE.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply