March 10, 2015 at 10:57 am
I have a data set with results similar to this:
dateid recordtype h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16
20141001 Discharges 2 1 2 1 3 5 1 10 21 51 102 168 181 235 243 272 199
20141001 Admits 38 44 36 21 20 36 90 33 44 53 65 92 112 132 146 140 150
I am trying to make a line chart where the axis along the bottom is each hour (h0, h1, etc.) and has two lines, one for each recordtype showing the value for each hour. Does that make sense? I can't figure it out.
Thanks!
March 10, 2015 at 1:33 pm
If that's the structure of your data, no wonder. Your structure looks wrong.
Got consumable data? (Something I can drop into SSRS and build a quick chart?)
March 10, 2015 at 3:34 pm
Can you use this to help?
CREATE TABLE [HourlyAdmitsandDischarges](
[dateid] [int] NULL,
[recordtype] [varchar](25) NULL,
[h0] [int] NULL,
[h1] [int] NULL,
[h2] [int] NULL,
[h3] [int] NULL,
[h4] [int] NULL,
[h5] [int] NULL,
[h6] [int] NULL,
[h7] [int] NULL,
[h8] [int] NULL,
[h9] [int] NULL,
[h10] [int] NULL,
[h11] [int] NULL,
[h12] [int] NULL,
[h13] [int] NULL,
[h14] [int] NULL,
[h15] [int] NULL,
[h16] [int] NULL,
[h17] [int] NULL,
[h18] [int] NULL,
[h19] [int] NULL,
[h20] [int] NULL,
[h21] [int] NULL,
[h22] [int] NULL,
[h23] [int] NULL
)
Insert into HourlyAdmitsandDischarges
(
[dateid]
,[recordtype]
,[h0]
,[h1]
,[h2]
,[h3]
,[h4]
,[h5]
,[h6]
,[h7]
,[h8]
,[h9]
,[h10]
,[h11]
,[h12]
,[h13]
,[h14]
,[h15]
,[h16]
,[h17]
,[h18]
,[h19]
,[h20]
,[h21]
,[h22]
,[h23]
)
Values
(
20141001,'Admits',38,44,36,21,20,36,90,33,44,53,65,92,112,132,146,140,150,145,143,95,103,102,80,55
)
,(20141001,'Discharges',2,1,2,1,3,5,1,10,21,51,102,168,181,235,243,272,199,161,132,63,37,22,9,11
)
March 10, 2015 at 8:44 pm
Robert,
Well, no wonder you're having such a horrible time of it. I wouldn't have a clue how to graph something like that. What are you starting with, something like this?:
CREATE TABLE PatientActivity(
PatientID INT,
AdmitDate DATETIME,
DischargeDate DATETIME
);
I guess you could split out the hour and get counts - that's trivial. Then you could graph no problem, right?
March 10, 2015 at 8:44 pm
duplicate post! oops!
Okay, I think I got it...
Here's the stored procedure (I'm sure Dwain will school me on doing it this way, but I was just trying to get it to work first):
ALTER PROC [dbo].[UnPivotData]
AS
SELECT dateID
, recordType AS ActivityType
, ActivityCount
, (1 + ROW_NUMBER() OVER(ORDER BY dateID))/2 AS TimeSlot
FROM
(
SELECT dateID, recordType, h0 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h1 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h2 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h3 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h4 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h5 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h6 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h7 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h8 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h9 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h10 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h11 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h12 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h13 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h14 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h15 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h16 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h17 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h18 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h19 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h20 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h21 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h22 As ActivityCount FROM HourlyAdmitsAndDischarges
UNION ALL
SELECT dateID, recordType, h23 As ActivityCount FROM HourlyAdmitsAndDischarges
) x;
I attached the graph... not really pretty, but it works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply