Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Matrix Table

Posted on 2015-01-21
7
Medium Priority
?
302 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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