SQL 2005 - Setting a value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 - Setting a value

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-12-06 : 23:15:01

I have 10 procedure codes I need to calculate a Count on for each month. These codes are as follows:

99201, 99202, 99203, 99204, 99205, 99211, 99212, 99213, 99214 & 99215

count(pvp.patientvisitprocsid) as 'Referrals'

My issue now is I want it to give me a '0' if that procedure code was not used in a particular month.

For example ... if 99201 was not used for December 2008, I want it to report back a '0' in my data set. I am not getting NULL values so I cant use a ISNULL(field,'0') ... trying to think of another angle to take. Any help is appreciated.

My Code
[CODE]
DECLARE @Month INT
DECLARE @Year INT
DECLARE @Start DATETIME
DECLARE @End DATETIME

SELECT
@End = '12/01/2008'

SELECT
@Month = MONTH(@End)
SELECT
@Year = YEAR(DATEADD(yy , -1 , @End))
SELECT
@Start = CONVERT(DATETIME , CONVERT(VARCHAR , @Month) + '/1/' + CONVERT(VARCHAR , @Year))
SELECT
@End = DATEADD(m , 1 , @End)

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 >= @Start
AND pvp.dateofentry < @end
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' )
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 00:56:57

[code]
DECLARE @Month INT
DECLARE @Year INT
DECLARE @Start DATETIME
DECLARE @End DATETIME

SELECT
@End = '12/01/2008'

SELECT
@Month = MONTH(@End)
SELECT
@Year = YEAR(DATEADD(yy , -1 , @End))
SELECT
@Start = DATEADD(mm,@Month-1,DATEADD(yy,@Year-1900,0))
SELECT
@End = DATEADD(m , 1 , @End)

SELECT
DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) 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 >= @Start
AND pvp.dateofentry < @end
GROUP BY
DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) ,
pvp.cptcode
[/code]

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-12-07 : 01:03:35

visakh16 -

When I use your syntax, I get additional values in the 'Type' that I did not want or need. I specifically only need the values contained within my IN ('99201', '99202' ......etc)

Data Set:

Month Type Referrals
------------------------------------------------------ ---------- -----------
2008-11-01 00:00:00.000 20550 0
2008-07-01 00:00:00.000 36415 0
2008-07-01 00:00:00.000 38794 0
2008-11-01 00:00:00.000 63042 0
2008-06-01 00:00:00.000 63047 0
2008-02-01 00:00:00.000 70210 0
2008-07-01 00:00:00.000 70210 0
2008-01-01 00:00:00.000 71020 0
2008-05-01 00:00:00.000 71020 0
2008-01-01 00:00:00.000 72050 0
2008-01-01 00:00:00.000 72052 0
2008-07-01 00:00:00.000 72052 0
2008-07-01 00:00:00.000 72069 0
2008-02-01 00:00:00.000 81000 0
2008-07-01 00:00:00.000 81000 0
2008-07-01 00:00:00.000 93000 0
2008-02-01 00:00:00.000 99000 0
2008-07-01 00:00:00.000 99000 0
2008-10-01 00:00:00.000 99203 1
2008-10-01 00:00:00.000 99204 1
2008-02-01 00:00:00.000 99212 1
2008-04-01 00:00:00.000 99212 2
2008-05-01 00:00:00.000 99212 1
2008-11-01 00:00:00.000 99212 1
2008-07-01 00:00:00.000 99213 1
2008-10-01 00:00:00.000 99213 1
2008-11-01 00:00:00.000 99213 1
2008-12-01 00:00:00.000 99213 3
2008-11-01 00:00:00.000 99214 1
2008-05-01 00:00:00.000 99215 1
2008-06-01 00:00:00.000 99215 1
2008-04-01 00:00:00.000 J1055 0

(32 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 01:14:21

ok..in that case what you can do is this


SELECT mnth.Date,mnth.Type,COALESCE(m.Referrals,0)
FROM
(
SELECT pvp1.Type,DATEADD(mm,DATEDIFF(mm,0,@Start)+number,0) AS Date
FROM patientvisitprocs pvp1
CROSS JOIN master..spt_values
WHERE type='p'
AND DATEADD(mm,number,@Start)<=@End
)mnth
LEFT JOIN 
(
SELECT
        DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) 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 >= @Start
        AND pvp.dateofentry < @end
      GROUP BY
        DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) ,
        pvp.cptcode
)m
ON m.Month =mnth.Date

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-12-07 : 01:48:09

I am getting the following:

Server: Msg 207, Level 16, State 3, Line 18
Invalid column name 'Type'.

From this query:

DECLARE @Month INT
DECLARE @Year INT
DECLARE @Start DATETIME
DECLARE @End DATETIME

SELECT
@End = '12/01/2008'

SELECT
@Month = MONTH(@End)
SELECT
@Year = YEAR(DATEADD(yy , -1 , @End))
SELECT
@Start = CONVERT(DATETIME , CONVERT(VARCHAR , @Month) + '/1/' + CONVERT(VARCHAR , @Year))
SELECT
@End = DATEADD(m , 1 , @End)

SELECT mnth.Date,mnth.Type,COALESCE(m.Referrals,0)
FROM
(
SELECT pvp1.Type,DATEADD(mm,DATEDIFF(mm,0,@Start)+number,0) AS Date
FROM patientvisitprocs pvp1
CROSS JOIN master..spt_values
WHERE type='p'
AND DATEADD(mm,number,@Start)<=@End
)mnth
LEFT JOIN
(
SELECT
DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) 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 >= @Start
AND pvp.dateofentry < @end
GROUP BY
DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) ,
pvp.cptcode
)m
ON m.Month =mnth.Date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 02:04:01

[code]
SELECT mnth.Date,mnth.Type,COALESCE(m.Referrals,0)
FROM
(
SELECT pvp1.cptcode AS Type,DATEADD(mm,DATEDIFF(mm,0,@Start)+number,0) AS Date
FROM patientvisitprocs pvp1
CROSS JOIN master..spt_values
WHERE type='p'
AND DATEADD(mm,number,@Start)<=@End
)mnth
LEFT JOIN
(
SELECT
DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) 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 >= @Start
AND pvp.dateofentry < @end
AND pvp.cptcode IN ( '99201' , '99202' , '99203' , '99204' , '99205' , '99211' , '99212' , '99213' , '99214' , '99215' )

GROUP BY
DATEADD(mm,DATEDIFF(mm,0,pvp.dateofentry),0) ,
pvp.cptcode
)m
ON m.Month =mnth.Date
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources