January 31, 2017 at 12:22 pm
Hi all;
I can get the info an individual day like this;
INSERT INTO #TEMP
SELECT '01/01/2017 09:00:00'
UNION ALL
SELECT '02/01/2017 09:00:00'
UNION ALL
SELECT '03/01/2017 09:00:00'
UNION ALL
SELECT '04/01/2017 09:00:00'
UNION ALL
SELECT '05/01/2017 09:00:00'
UNION ALL
SELECT '06/01/2017 09:00:00'
UNION ALL
SELECT '06/01/2017 13:00:00'
UNION ALL
SELECT '07/01/2017 09:00:00'
UNION ALL
SELECT '07/01/2017 20:00:00'
select
SUM(CASE WHEN DATEPART(HH, MYDATECOL) BETWEEN 1 AND 12 THEN 1 ELSE 0 END) AS MONDAYAM,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) BETWEEN 12 AND 19 THEN 1 ELSE 0 END) AS MONDAYAFT,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) > 19 THEN 1 ELSE 0 END) AS MONDAYPM
FROM #TEMP
WHERE DATEName(DW, MYDATECOL) = 'Monday'
DROP TABLE #TEMP
Its not very efficient to do that seven times but I couldn't think of a better way to do it so I'm asking the experts!
For some context its checking if a volunteer opportuinty is available on certain days and times, the opp could go over several days and even weeks.
Thanks
January 31, 2017 at 12:46 pm
kangarolf - Tuesday, January 31, 2017 12:22 PMHi all;I can get the info an individual day like this;
INSERT INTO #TEMP
SELECT '01/01/2017 09:00:00'
UNION ALL
SELECT '02/01/2017 09:00:00'
UNION ALL
SELECT '03/01/2017 09:00:00'
UNION ALL
SELECT '04/01/2017 09:00:00'
UNION ALL
SELECT '05/01/2017 09:00:00'
UNION ALL
SELECT '06/01/2017 09:00:00'
UNION ALL
SELECT '06/01/2017 13:00:00'
UNION ALL
SELECT '07/01/2017 09:00:00'
UNION ALL
SELECT '07/01/2017 20:00:00'select
SUM(CASE WHEN DATEPART(HH, MYDATECOL) BETWEEN 1 AND 12 THEN 1 ELSE 0 END) AS MONDAYAM,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) BETWEEN 12 AND 19 THEN 1 ELSE 0 END) AS MONDAYAFT,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) > 19 THEN 1 ELSE 0 END) AS MONDAYPM
FROM #TEMP
WHERE DATEName(DW, MYDATECOL) = 'Monday'DROP TABLE #TEMP
Its not very efficient to do that seven times but I couldn't think of a better way to do it so I'm asking the experts!
For some context its checking if a volunteer opportuinty is available on certain days and times, the opp could go over several days and even weeks.
Thanks
try:
select DATEPART(DW, MYDATECOL) WD,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) BETWEEN 1 AND 12 THEN 1 ELSE 0 END) AS AM,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) BETWEEN 12 AND 19 THEN 1 ELSE 0 END) AS AFT,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) > 19 THEN 1 ELSE 0 END) AS PM
FROM #TEMP
group by DATEPART(DW, MYDATECOL)
This won't have your Day of Week as column headers, but will remove the need to do it for each day of the week.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 31, 2017 at 1:39 pm
Of course what an idiot..groupby..I started off with trying to get a boolean value (which wouldnt work with groupby) then switched to just getting a total but forgot I could then use groupby!
Thanks (hand slaps head)
January 31, 2017 at 2:14 pm
A couple of things here:
1) The hours ranges given exclude times and allow times to overlap, which is almost certainly not what you really want.
2) Let's go ahead and show the actual day name in the final result, but still independent of language settings, i.e., doesn't have to be English. I use an outer query for this because it's more efficient to do the char translation only one time for each day, rather than for every row.
SELECT DATENAME(WEEKDAY, DATEADD(DAY, DayOfWeek, 0)) AS WeekDay, Morning, Afternoon, Evening
FROM (
SELECT
DATEDIFF(DAY, 0, MYDATECOL) % 7 AS DayOfWeek,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) >= 0 AND DATEPART(HH, MYDATECOL) < 12 THEN 1 ELSE 0 END) AS Morning,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) >= 12 AND DATEPART(HH, MYDATECOL) < 19 THEN 1 ELSE 0 END) AS Afternoon,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) >= 19 THEN 1 ELSE 0 END) AS Evening
FROM #TEMP
GROUP BY DATEDIFF(DAY, 0, MYDATECOL) % 7
) AS derived
/*ORDER BY DayOfWeek*/
Edit: Added ORDER BY clause, just in case it would be needed.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 31, 2017 at 2:38 pm
ScottPletcher - Tuesday, January 31, 2017 2:14 PMA couple of things here:1) The hours ranges given exclude times and allow times to overlap, which is almost certainly not what you really want.
2) Let's go ahead and show the actual day name in the final result, but still independent of language settings, i.e., doesn't have to be English. I use an outer query for this because it's more efficient to do the char translation only one time for each day, rather than for every row.
SELECT DATENAME(WEEKDAY, DATEADD(DAY, DayOfWeek, 0)) AS WeekDay, Morning, Afternoon, Evening
FROM (
SELECT
DATEDIFF(DAY, 0, MYDATECOL) % 7 AS DayOfWeek,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) >= 0 AND DATEPART(HH, MYDATECOL) < 12 THEN 1 ELSE 0 END) AS Morning,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) >= 12 AND DATEPART(HH, MYDATECOL) < 19 THEN 1 ELSE 0 END) AS Afternoon,
SUM(CASE WHEN DATEPART(HH, MYDATECOL) >= 19 THEN 1 ELSE 0 END) AS Evening
FROM #TEMP
GROUP BY DATEDIFF(DAY, 0, MYDATECOL) % 7
) AS derived
/*ORDER BY DayOfWeek*/Edit: Added ORDER BY clause, just in case it would be needed.
DATEPART(HH, MYDATECOL) >= 0 is ALWAYS true, because it will only ever produce a value in the range 0 to 23. There is absolutely no reason to include that test and removing it will likely increase performance (although not significantly).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply