Solved

SQL Matrix Table

Posted on 2015-01-21
7
265 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

770 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