For my report, I want 3 charts. One crosstab and 2 horizontal bar graphs.
I currently have the crosstab chart set-up with all months (12 in total) across the top horizontal axis and my codes (10 total) along my vertical axis. This report is working as I need. What I need help with is my two horizonal bar graphs. I want one to pull the following codes (99201 - 99205) for the current month only and the other chart to pull codes (99211 - 99215) for the current month.
I really hope I explained myself well. If not, please tell me and I'll be happy to explain better. Essentially, I just need the two bar graphs and one to pull one set of codes and the other to pull the other range of codes for the End date used in my SQL.
I attached the SQL to this report just in case its needed. Please note, the SQL is handled dynamically and alot in the Where clause may look odd to you.
My SQL:
__________________________________________________________
SET NOCOUNT ON
DECLARE
@db varchar(50),
@startdate datetime,
@enddate datetime,
@counterdate datetime,
@datevar datetime,
@counter int,
@i datetime
SET @datevar = ISNULL('12/01/2008','1/1/1900')
SET @counter =1
SET @DB = DB_Name()
SET @enddate = DATEADD(month , 1 , @datevar)
SET @startdate = DATEADD(month , -11 , @datevar)
DECLARE @Temp TABLE
(
TheMonth DATETIME,
ProcedureCode CHAR(5)
)
SELECT @i = @Startdate
WHILE @i <= @datevar
BEGIN
INSERT INTO @Temp SELECT @i, '99201'
INSERT INTO @Temp SELECT @i, '99202'
INSERT INTO @Temp SELECT @i, '99203'
INSERT INTO @Temp SELECT @i, '99204'
INSERT INTO @Temp SELECT @i, '99205'
INSERT INTO @Temp SELECT @i, '99211'
INSERT INTO @Temp SELECT @i, '99212'
INSERT INTO @Temp SELECT @i, '99213'
INSERT INTO @Temp SELECT @i, '99214'
INSERT INTO @Temp SELECT @i, '99215'
SELECT @i = DATEADD(month, 1, @i)
END
SELECT
a.TheMonth,
a.ProcedureCode,
ISNULL(b.Referrals, 0) AS Referrals
FROM @Temp a
LEFT OUTER JOIN
(
SELECT
*
FROM
(
SELECT
CONVERT(DATETIME , CONVERT(VARCHAR , MONTH(pvp.dateofentry)) + '/1/' + CONVERT(VARCHAR , YEAR(pvp.dateofentry))) AS Month ,
pvp.cptcode AS 'Type' ,
SUM(CASE WHEN pvp.cptcode IN ( '99201' , '99202' , '99203' , '99204' , '99205' , '99211' , '99212' , '99213' , '99214' , '99215' ) THEN 1
ELSE 0
END) AS 'Referrals'
FROM PatientVisit pv
JOIN doctorfacility dr ON pv.doctorid = dr.doctorfacilityid
JOIN patientvisitprocs pvp ON pv.patientvisitid = pvp.patientvisitid
WHERE
pvp.dateofentry >= @startdate
AND pvp.dateofentry < @enddate
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY
CONVERT(DATETIME , CONVERT(VARCHAR , MONTH(pvp.dateofentry)) + '/1/' + CONVERT(VARCHAR , YEAR(pvp.dateofentry))) ,
pvp.cptcode
) AS T
WHERE
Type IN ( '99201' , '99202' , '99203' , '99204' , '99205' , '99211' , '99212' , '99213' , '99214' , '99215')) b
ON a.TheMonth = b.[Month] AND a.ProcedureCode = b.[Type]