Solved

SQL Matrix Table

Posted on 2015-01-21
7
253 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now