Solved

SQL Matrix Table

Posted on 2015-01-21
7
281 Views
Last Modified: 2015-01-22
I'm no SQL expert by any means but am trying to create a table with rows / columns based upon the following query.  Basically I have a table of appointments with Appt_DateTime and Refer_Dr_Abbr ...   I want to have column headings of each year and rows for each Referring Doctor.   Each cell will contain the count of appointments for that year.

The examples I've seen are a bit over my head so I need some help on the easiest way to do this.

Thanks a million for any help you can give ....

SELECT     Count(vwGenPatApptInfo.Appt_Refer_Dr_Abbr) AS Count,PM.vwRefDrList.Last_Name, OrgDesc
FROM        vwGenPatApptInfo INNER JOIN
                      PM.vwRefDrList ON vwGenPatApptInfo.Appt_Refer_Dr_ID = PM.vwRefDrList.RefDrID
WHERE     (vwGenPatApptInfo.Appt_DateTime BETWEEN CONVERT(DATETIME, '2010-01-01 00:00:01', 102) AND CONVERT(DATETIME, '2015-12-31 23:59:00', 102)) AND
                      (vwGenPatApptInfo.Appt_Status = 'A') AND OrgDesc IS NOT NULL --AND OrgDesc In ('Bend Memorial Clinic')                  
GROUP BY      OrgDesc,vwGenPatApptInfo.Appt_Refer_Dr_Abbr, PM.vwRefDrList.Last_Name
Order BY OrgDesc, Count DESC
0
Comment
Question by:jtbrown1111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40563327
Can you post the structure of the 2(3) tables related here please? I suggest u that create a small store procedure that give you that. if you post the table structure I can help you with that

 The store procedure will create each column for the dates and then you can add the related doctors and the counters
0
 

Author Comment

by:jtbrown1111
ID: 40563351
@ Jesus,    what's the best way to do this as I see several examples online .......    Thanks .
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40563392
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 500 total points
ID: 40563471
This is a rough adaptation of  what you need to do more or less on the long way. Check if you can adapt your data to this example that I create for you
DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @COLS AS INTEGER

SET @DATEFROM='1/1/2010'
SET @DATETO='12/31/2015'
SET @COLS=DATEDIFF(YY,@DATEFROM,@DATETO)
DECLARE @CC INTEGER=0

--DELETING THE EXISTING TABLE
if exists (select * from sys.tables where name = N'TMPDATA')  drop table [TMPDATA]

--CREATING THE NEW TABLE BASE ON THE COLUMNS THAT WILL NEED
DECLARE @QRYTMPTABLE AS VARCHAR(MAX)
SET @QRYTMPTABLE='CREATE TABLE TMPDATA(ID_DR INTEGER NULL,DR_NAME NVARCHAR(250) NULL,'
WHILE @CC<@COLS+1
   BEGIN
    SET @QRYTMPTABLE=@QRYTMPTABLE+'YEAR'+ RTRIM(LTRIM(STR(YEAR(DATEADD(YY,@CC,@DATEFROM)))))+' INTEGER NULL,'
	SET @CC=@CC+1
   END
SET @QRYTMPTABLE=SUBSTRING(@QRYTMPTABLE,1,LEN(@QRYTMPTABLE)-1)+')'
EXEC (@QRYTMPTABLE)

-- INSERTING EVERY DOCTOR
SET @QRYTMPTABLE='INSERT INTO TMPDATA(ID_DR,DR_NAME) SELECT DISTINCT DOCTOR_ID,DR_NAME FROM TBL_DOCTORS'
EXEC (@QRYTMPTABLE)

--SELECTING EVERY DOCTOR ON THE TABLE
DECLARE @DRID INTEGER
DECLARE @DRNAME NVARCHAR(250)
DECLARE TDR CURSOR FOR
             SELECT ID_DR,DR_NAME FROM TMPDATA

OPEN TDR
FETCH FROM TDR INTO @DRID,@DRNAME
WHILE @@FETCH_STATUS=0
BEGIN
	SET @CC=0
	WHILE @CC<@COLS+1
	   BEGIN
        SET @QRYTMPTABLE='INSERT INTO TMPDATASET(YEAR'+ RTRIM(LTRIM(STR(YEAR(DATEADD(YY,@CC,@DATEFROM)))))+') SELECT COUNT(APPTS) FROM APPTS_TABLE WHERE ID_DR_IN_APPTS_TABLE='''+ @DRID+''' AND YEAR(APPT_DATE)='''+RTRIM(LTRIM(STR(YEAR(DATEADD(YY,@CC,@DATEFROM)))))+''''
		EXEC (@QRYTMPTABLE)
		SET @CC=@CC+1
	   END
END
CLOSE TDR
DEALLOCATE TDR

Open in new window


you can pass the variables for the date on the store procedure later and then the table will be created for any range of years in the data
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40564795
I have a small correction for this. On the line that said
SET @QRYTMPTABLE='INSERT INTO TMPDATASET(YEAR'+ RTRIM(LTRIM(STR(YEAR(DATEADD(YY,@CC,@DATEFROM)))))+') SELECT COUNT(APPTS) FROM APPTS_TABLE WHERE ID_DR_IN_APPTS_TABLE='''+ @DRID+''' AND YEAR(APPT_DATE)='''+RTRIM(LTRIM(STR(YEAR(DATEADD(YY,@CC,@DATEFROM)))))+''''

Open in new window


you must replaced with
        SET @QRYTMPTABLE='UPDATE A SET A.YEAR'+ RTRIM(LTRIM(STR(YEAR(DATEADD(YY,@CC,@DATEFROM)))))+'=B.CC 
		                  FROM TMPDATASET AS A
		                  INNER JOIN (SELECT ID_DR,COUNT(APPTS) AS CC 
						              FROM APPTS_TABLE 
									  WHERE ID_DR_IN_APPTS_TABLE='''+ @DRID+''' AND YEAR(APPT_DATE)='''+RTRIM(LTRIM(STR(YEAR(DATEADD(YY,@CC,@DATEFROM)))))+'''
									  ) AS B ON B.ID_BR=A.ID_DR'

Open in new window


because will be and update for each ID_DR already existing on the database to writing the values by year
0
 

Author Comment

by:jtbrown1111
ID: 40564880
@ Jesus,

Thanks .... I've gotten pulled away from this project and will have to return to it in a few days.  I've looked at your code but haven't not tested it yet.

What I'm doing is looking at every record for a given period of time in the appointment_tbl , grabbing the dr_name and determining the year of the appointment based upon the appt_date.

Once I've done this I'll create a table that shows the total appointments for each doctor for each year in a pivot type format.  

Does that make sense ?    Is that what your code is attempting to do ?
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40564969
Yes, and do all this on an store procedure dynamically but you has to modified a little bit your code with the tables that you have.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question