February 21, 2016 at 12:43 pm
I wrote the query below to get the number of episodes of restraint each hour. I wanted the hour to show up in AM/PM format. The time in restraint, time is an integer in military time. I think the query works but I'm wondering if there is an easier way to do this. I'm a T/SQL newbie so any suggestions or feedback would be welcome.
Regards,
- Tom
Here is the query I used:
/****** This T/SQL reads in the restraint data and converts the time in to standard time from military time ******/
WITH RSDATA AS /*CTE to get the restraint data along with the hour of restraint from 1 to 24*/
(
SELECT [patno],
[in_date],
[timein],
cast(replace(left(cast((timein/100) as varchar(5)),2),'.','') as int) as hour_ini,
[out_date],
[timeout]
FROM [Pro_SSRS_2008R2].[dbo].[Sheet1$]
WHERE in_date is not null
)
/*Use above CTE to get hour of restraint in AM/PM format*/
select patno, in_date, timein, hour_ini,
CASE
WHEN hour_ini between 1 and 11 then cast(hour_ini as varchar(2)) + N' AM'
WHEN hour_ini = 12 then '12 PM'
WHEN hour_ini between 13 and 23 then CAST((hour_ini-12) as varchar(2)) + N' PM'
WHEN hour_ini = 24 then '12 AM'
WHEN hour_ini = 0 then '12 AM'
ELSE 'WEIRD'
END as hour_in_ampm
into #temp_rsdata
from RSDATA
;
/*from table #temp_rsdata, get the number of episodes each hour*/
select hour_ini, hour_in_ampm, count(*) as total_episodes
from #temp_rsdata
GROUP BY hour_ini, hour_in_ampm
ORDER BY hour_ini, hour_in_ampm
;
DROP TABLE #temp_rsdata;
;
February 21, 2016 at 1:50 pm
thomaswellington (2/21/2016)
I wrote the query below to get the number of episodes of restraint each hour. I wanted the hour to show up in AM/PM format. The time in restraint, time is an integer in military time. I think the query works but I'm wondering if there is an easier way to do this. I'm a T/SQL newbie so any suggestions or feedback would be welcome.Regards,
- Tom
Here is the query I used:
/****** This T/SQL reads in the restraint data and converts the time in to standard time from military time ******/
WITH RSDATA AS /*CTE to get the restraint data along with the hour of restraint from 1 to 24*/
(
SELECT [patno],
[in_date],
[timein],
cast(replace(left(cast((timein/100) as varchar(5)),2),'.','') as int) as hour_ini,
[out_date],
[timeout]
FROM [Pro_SSRS_2008R2].[dbo].[Sheet1$]
WHERE in_date is not null
)
/*Use above CTE to get hour of restraint in AM/PM format*/
select patno, in_date, timein, hour_ini,
CASE
WHEN hour_ini between 1 and 11 then cast(hour_ini as varchar(2)) + N' AM'
WHEN hour_ini = 12 then '12 PM'
WHEN hour_ini between 13 and 23 then CAST((hour_ini-12) as varchar(2)) + N' PM'
WHEN hour_ini = 24 then '12 AM'
WHEN hour_ini = 0 then '12 AM'
ELSE 'WEIRD'
END as hour_in_ampm
into #temp_rsdata
from RSDATA
;
/*from table #temp_rsdata, get the number of episodes each hour*/
select hour_ini, hour_in_ampm, count(*) as total_episodes
from #temp_rsdata
GROUP BY hour_ini, hour_in_ampm
ORDER BY hour_ini, hour_in_ampm
;
DROP TABLE #temp_rsdata;
;
Convert the integer to a real date/time and everything will get easier.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2016 at 6:56 pm
cast(replace(left(cast((timein/100) as varchar(5)),2),'.','') as int) as hour_ini
If you are doing timein/100 , isnt the timein column already in integer format? It seems you are converting from integer to string and then back to integer again.
----------------------------------------------------
February 22, 2016 at 7:06 pm
Here is what I came up with
if object_id('#temp2') is not null
drop table #temp2;
create table #temp2 (rowNum tinyint, timein int)
insert into #temp2
select 1, '0800' union all
select 2, '1200' union all
select 3, '0000' union all
select 4, '2000' union all
select 5, '2300'
SELECT
rowNum,
timein,
cast(replace(left(cast((timein/100) as varchar(5)),2),'.','') as int) as hour_ini,
timein/100 as hour_ini2, /* same as hour_ini*/
right(convert(varchar,dateadd(hh, (timein/100) , 0),100) ,7)as hourInAmPm2
From #temp2
----------------------------------------------------
February 22, 2016 at 7:23 pm
Thanks for all the suggestions, everyone. The query below uses a case expression to get the time in am/pm format and then converts that to datetime. This is good enough for me since I can bring the query into SSRS and format the datetime variable hour_in_restraint in "1:30 PM" format. I think I am all set for now and, again, I appreciate everyone's feedback.
Best,
- Tom
Final version of the query:
/****** This T/SQL reads in the restraint data and converts the time in to standard time from military time ******/
WITH RSDATA AS /*CTE to get the restraint data along with the hour of restraint from 1 to 24 and the hour in military time*/
(
SELECT [patno],
[in_date],
[timein],
cast(replace(left(cast((timein/100) as varchar(5)),2),'.','') as int) as hour_ini,
CASE
WHEN timein between 1 and 59 or timein = 2400 or timein = 0 then '12 AM'
WHEN timein between 100 and 159 then '1 AM '
WHEN timein between 200 and 259 then '2 AM '
WHEN timein between 300 and 359 then '3 AM '
WHEN timein between 400 and 459 then '4 AM '
WHEN timein between 500 and 559 then '5 AM '
WHEN timein between 600 and 659 then '6 AM '
WHEN timein between 700 and 759 then '7 AM '
WHEN timein between 800 and 859 then '8 AM '
WHEN timein between 900 and 959 then '9 AM '
WHEN timein between 1000 and 1059 then '10 AM'
WHEN timein between 1100 and 1159 then '11 AM'
WHEN timein between 1200 and 1259 then '12 PM'
WHEN timein between 1300 and 1359 then '1 PM '
WHEN timein between 1400 and 1459 then '2 PM '
WHEN timein between 1500 and 1559 then '3 PM '
WHEN timein between 1600 and 1659 then '4 PM '
WHEN timein between 1700 and 1759 then '5 PM '
WHEN timein between 1800 and 1859 then '6 PM '
WHEN timein between 1900 and 1959 then '7 PM '
WHEN timein between 2000 and 2059 then '8 PM '
WHEN timein between 2100 and 2159 then '9 PM '
WHEN timein between 2200 and 2259 then '10 PM'
WHEN timein between 2300 and 2359 then '11 PM'
ELSE 'WEIRD'
END as hour_in_ampm,
[out_date],
[timeout]
FROM [Pro_SSRS_2008R2].[dbo].[Sheet1$]
)
/*use the above CTE to group by the hour formatted as a datetime and count total episodes of restraint*/
select cast(hour_in_ampm as datetime) as hour_in_restraint, count(*) as total_episodes_of_restraint
from RSDATA
where timein is not null
group by cast(hour_in_ampm as datetime)
order by cast(hour_in_ampm as datetime)
;
February 22, 2016 at 9:28 pm
thomaswellington (2/22/2016)
Thanks for all the suggestions, everyone. The query below uses a case expression to get the time in am/pm format and then converts that to datetime. This is good enough for me since I can bring the query into SSRS and format the datetime variable hour_in_restraint in "1:30 PM" format. I think I am all set for now and, again, I appreciate everyone's feedback.Best,
- Tom
Final version of the query:
/****** This T/SQL reads in the restraint data and converts the time in to standard time from military time ******/
WITH RSDATA AS /*CTE to get the restraint data along with the hour of restraint from 1 to 24 and the hour in military time*/
(
SELECT [patno],
[in_date],
[timein],
cast(replace(left(cast((timein/100) as varchar(5)),2),'.','') as int) as hour_ini,
CASE
WHEN timein between 1 and 59 or timein = 2400 or timein = 0 then '12 AM'
WHEN timein between 100 and 159 then '1 AM '
WHEN timein between 200 and 259 then '2 AM '
WHEN timein between 300 and 359 then '3 AM '
WHEN timein between 400 and 459 then '4 AM '
WHEN timein between 500 and 559 then '5 AM '
WHEN timein between 600 and 659 then '6 AM '
WHEN timein between 700 and 759 then '7 AM '
WHEN timein between 800 and 859 then '8 AM '
WHEN timein between 900 and 959 then '9 AM '
WHEN timein between 1000 and 1059 then '10 AM'
WHEN timein between 1100 and 1159 then '11 AM'
WHEN timein between 1200 and 1259 then '12 PM'
WHEN timein between 1300 and 1359 then '1 PM '
WHEN timein between 1400 and 1459 then '2 PM '
WHEN timein between 1500 and 1559 then '3 PM '
WHEN timein between 1600 and 1659 then '4 PM '
WHEN timein between 1700 and 1759 then '5 PM '
WHEN timein between 1800 and 1859 then '6 PM '
WHEN timein between 1900 and 1959 then '7 PM '
WHEN timein between 2000 and 2059 then '8 PM '
WHEN timein between 2100 and 2159 then '9 PM '
WHEN timein between 2200 and 2259 then '10 PM'
WHEN timein between 2300 and 2359 then '11 PM'
ELSE 'WEIRD'
END as hour_in_ampm,
[out_date],
[timeout]
FROM [Pro_SSRS_2008R2].[dbo].[Sheet1$]
)
/*use the above CTE to group by the hour formatted as a datetime and count total episodes of restraint*/
select cast(hour_in_ampm as datetime) as hour_in_restraint, count(*) as total_episodes_of_restraint
from RSDATA
where timein is not null
group by cast(hour_in_ampm as datetime)
order by cast(hour_in_ampm as datetime)
;
See that huge CASE statement you've got? Replace the whole thing with this simple query and get rid of the CASTs in the outer SELECT, GROUP BY, and ORDER BY.
hour_in_ampm = DATEADD(hh,timein/100,0)
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2016 at 9:59 pm
In fact and judging from the columns you have in the CTE of your final query, I believe the following code can be used to replace the whole shebang (keeping in mind that I don't have your data to test with). It also give sub-totals by PatNo, InDate, and Hour_In_Restraint as well as a Grand Total (look for the NULLs in the output).
WITH cteConvert AS
(
SELECT PatNo
,InDate = CAST(InDate AS DATE)
,Hour_In_Restraint = DATEADD(hh,TimeIn/100,0)
FROM [Pro_SSRS_2008R2].[dbo].[Sheet1$]
)
SELECT PatNo
,InDate
,Hour_In_Restraint = STUFF(RIGHT(CONVERT(CHAR(19),Hour_In_Restraint,100),7),3,3,' ')
,Total_Episodes_Of_Restraint = COUNT(*)
FROM cteConvert
GROUP BY PatNo, InDate, Hour_In_Restraint WITH ROLLUP
ORDER BY GROUPING(PatNo),PatNo
,GROUPING(InDate),InDate
,GROUPING(Hour_In_Restraint),CAST(Hour_In_Restraint AS TIME)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2016 at 11:28 pm
So others can follow along, here's some test data (10,000 rows) I made for a full month of entries across 10 different PatNo's.
SELECT TOP 10000
PatNo = ABS(CHECKSUM(NEWID()))%10+1 --1 to 10
,InDate = CAST(DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'Feb 2016','Mar 2016'),'Feb 2016') AS DATE)
,TimeIn = CAST(REPLACE(CAST(CAST(DATEADD(mi,ABS(CHECKSUM(NEWID()))%1440,0) AS TIME) AS CHAR(5)),':','') AS INT)
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
And, we can go whole hog on the reporting before we even get to SSRS. All sorts of totals and sub-totals as well. Just imagine if I'd used WITH CUBE. 😀
WITH cteConvert AS
(
SELECT PatNo
,InDate = CAST(InDate AS DATE)
,Hour_In_Restraint = TimeIn/100
FROM #TestTable
)
, ctePreagg AS
(
SELECT PatNo
,InDate
,Hour_In_Restraint
,Total_Episodes_Of_Restraint = COUNT(*)
FROM cteConvert
GROUP BY PatNo, InDate, Hour_In_Restraint
)
SELECT InDate
,PatNo
,[12 AM] = SUM(CASE WHEN Hour_In_Restraint = 0 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [1 AM] = SUM(CASE WHEN Hour_In_Restraint = 1 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [2 AM] = SUM(CASE WHEN Hour_In_Restraint = 2 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [3 AM] = SUM(CASE WHEN Hour_In_Restraint = 3 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [4 AM] = SUM(CASE WHEN Hour_In_Restraint = 4 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [5 AM] = SUM(CASE WHEN Hour_In_Restraint = 5 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [6 AM] = SUM(CASE WHEN Hour_In_Restraint = 6 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [7 AM] = SUM(CASE WHEN Hour_In_Restraint = 7 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [8 AM] = SUM(CASE WHEN Hour_In_Restraint = 8 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [9 AM] = SUM(CASE WHEN Hour_In_Restraint = 9 THEN Total_Episodes_Of_Restraint ELSE 0 END)
,[10 AM] = SUM(CASE WHEN Hour_In_Restraint = 10 THEN Total_Episodes_Of_Restraint ELSE 0 END)
,[11 AM] = SUM(CASE WHEN Hour_In_Restraint = 11 THEN Total_Episodes_Of_Restraint ELSE 0 END)
,[12 PM] = SUM(CASE WHEN Hour_In_Restraint = 12 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [1 PM] = SUM(CASE WHEN Hour_In_Restraint = 13 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [2 PM] = SUM(CASE WHEN Hour_In_Restraint = 14 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [3 PM] = SUM(CASE WHEN Hour_In_Restraint = 15 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [4 PM] = SUM(CASE WHEN Hour_In_Restraint = 16 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [5 PM] = SUM(CASE WHEN Hour_In_Restraint = 17 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [6 PM] = SUM(CASE WHEN Hour_In_Restraint = 18 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [7 PM] = SUM(CASE WHEN Hour_In_Restraint = 19 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [8 PM] = SUM(CASE WHEN Hour_In_Restraint = 20 THEN Total_Episodes_Of_Restraint ELSE 0 END)
, [9 PM] = SUM(CASE WHEN Hour_In_Restraint = 21 THEN Total_Episodes_Of_Restraint ELSE 0 END)
,[10 PM] = SUM(CASE WHEN Hour_In_Restraint = 22 THEN Total_Episodes_Of_Restraint ELSE 0 END)
,[11 PM] = SUM(CASE WHEN Hour_In_Restraint = 23 THEN Total_Episodes_Of_Restraint ELSE 0 END)
,Total = SUM(Total_Episodes_Of_Restraint)
FROM ctePreagg
GROUP BY InDate,PatNo WITH ROLLUP
ORDER BY GROUPING(InDate),InDate,GROUPING(PatNo),PatNo
;
That gives a pretty nice CROSSTAB if you'd like to try it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2016 at 7:08 pm
Thanks a lot for all your posts, everybody! I learned a lot by reading them. I especially liked Jeff's suggestion about how to get rid of my huge case statement with hour_in_ampm = DATEADD(hh,timein/100,0). Below is my query now.
I really appreciate the help.
- Tom
/****** This T/SQL reads in the restraint data and converts the time in to standard time from military time ******/
WITH RSDATA AS /*CTE to get the restraint data along with the hour of restraint from 1 to 24 and the hour in military time*/
( /*note that dateadd will advance year 0 , (i.e. 1/1/1900) by the number of hours in the integer part of timein/100*/
SELECT [patno],
[in_date],
[timein],
DATEADD(hh,timein/100,0) as hour_in_ampm,
[out_date],
[timeout]
FROM [Pro_SSRS_2008R2].[dbo].[Sheet1$]
)
/*use the above CTE to group by the hour formatted as a datetime and count total episodes of restraint*/
select hour_in_ampm, count(*) as total_episodes_of_restraint
from RSDATA
where timein is not null
group by hour_in_ampm
order by hour_in_ampm
;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply