Help with a line chart

• robert.wiglesworth

  SSCommitted

  Points: 1790

  More actions

  March 10, 2015 at 10:57 am

  #295122

  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!

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  March 10, 2015 at 1:33 pm

  #1783070

  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?)

• robert.wiglesworth

  SSCommitted

  Points: 1790

  More actions

  March 10, 2015 at 3:34 pm

  #1783083

  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

  )

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  March 10, 2015 at 8:44 pm

  #1783120

  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?

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  March 10, 2015 at 8:44 pm

  #1783121

  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.