• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

SQL Matrix Table

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
jtbrown1111
Asked:
jtbrown1111
  • 5
  • 2
1 Solution
 
Jesus RodriguezIT ManagerCommented:
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
 
jtbrown1111Author Commented:
@ Jesus,    what's the best way to do this as I see several examples online .......    Thanks .
0
 
Jesus RodriguezIT ManagerCommented:
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jesus RodriguezIT ManagerCommented:
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
 
Jesus RodriguezIT ManagerCommented:
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
 
jtbrown1111Author Commented:
@ 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
 
Jesus RodriguezIT ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now