SQL Query Help Needed. - 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 Query Help Needed.

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-10-27 : 09:16:24

It was not until I took this query off a demo db and plugged it into a live db that I found this issue. In fact, I didn't even think about it until I tried the query on a live db.

Here is my issue, The [NumberOfVisits] respects visits within specific dateranges, however my Sum on the InsBalance and PatBalance are not ( a complete oversight).

I need help on these two fields:


ISNULL(SUM(pva.InsBalance) , 0) AS InsBalance,
ISNULL(SUM(pva.PatBalance) , 0) AS PatBalance,

If you look at my full query below, in the [NumberofVisits] for bs.Code = '2' I want the query to only count the visit if it goes over 3 days. Well I need that same logic applied to the InsBalance and PatBalance fields. I only need the sum of money for the visits over "X" number of days. I need it to follow the same logic as my [NumberofVisits].

My full query:

[CODE]
-- In Progress, Approve Failed, Approved, Batched, Hold, Filed Rejected, Sent, Filed Succeeded & Filed Statuses

SELECT
SUM(CASE WHEN ( bs.Code IN ( 2 , 4 , 5 , 16 ) ) AND
DATEDIFF(day , pv.lastmodified , GETDATE()) > 3 THEN 1
WHEN ( bs.Code IN ( 3 , 8 , 17 ) ) AND
DATEDIFF(day , pv.lastmodified , GETDATE()) > 10 THEN 1
WHEN ( bs.Code = 9 ) AND
DATEDIFF(day , pv.lastmodified , GETDATE()) > 30 THEN 1
WHEN ( bs.Code = 7 ) AND
DATEDIFF(day , pv.lastmodified , GETDATE()) > 45 THEN 1
ELSE 0
END) AS NumberOfVisits,
ISNULL(SUM(pva.InsBalance) , 0) AS InsBalance,
ISNULL(SUM(pva.PatBalance) , 0) AS PatBalance,
bs.Code,
bs.Description
FROM
dbo.MedLists bs
LEFT JOIN dbo.PatientVisit pv ON pv.BillStatus = bs.JoinId
LEFT JOIN dbo.PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
WHERE
bs.Code NOT IN ( 1 , 6 , 10 , 11 , 12 , 13 , 14 , 15 ) AND
bs.TableName = 'BillStatus' -- Limit to BillStatus only
GROUP BY
bs.code,
bs.Description
[/CODE]

Sachin.Nand

2937 Posts

Posted - 2009-10-27 : 09:25:10

[code]
SELECT
Code,
Description,
NumberOfVisits,
ISNULL(SUM(CASE WHEN NumberOfVisits>"X" THEN pva.InsBalance ELSE 0 END) , 0) AS InsBalance,
ISNULL(SUM(CASE WHEN NumberOfVisits>"X" THEN pva.PatBalance ELSE 0 END) , 0) AS PatBalance
FROM
(
SELECT
SUM(CASE WHEN ( bs.Code IN ( 2 , 4 , 5 , 16 ) ) AND
DATEDIFF(DAY , pv.lastmodified , GETDATE()) > 3 THEN 1
WHEN ( bs.Code IN ( 3 , 8 , 17 ) ) AND
DATEDIFF(DAY , pv.lastmodified , GETDATE()) > 10 THEN 1
WHEN ( bs.Code = 9 ) AND
DATEDIFF(DAY , pv.lastmodified , GETDATE()) > 30 THEN 1
WHEN ( bs.Code = 7 ) AND
DATEDIFF(DAY , pv.lastmodified , GETDATE()) > 45 THEN 1
ELSE 0
END) AS NumberOfVisits,

bs.Code,
bs.Description
FROM
dbo.MedLists bs
LEFT JOIN dbo.PatientVisit pv ON pv.BillStatus = bs.JoinId
LEFT JOIN dbo.PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
WHERE
bs.Code NOT IN ( 1 , 6 , 10 , 11 , 12 , 13 , 14 , 15 ) AND
bs.TableName = 'BillStatus' -- Limit to BillStatus only
GROUP BY
bs.code,
bs.Description
)T
GROUP BY
Code,
Description,
NumberOfVisits

[/code]

PBUH

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-10-27 : 09:30:12

Idera -

I get back

Msg 107, Level 16, State 2, Line 1
The column prefix 'pva' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 1
The column prefix 'pva' does not match with a table name or alias name used in the query.

Sachin.Nand

2937 Posts

Posted - 2009-10-27 : 09:32:00

Sorry.Just replace it with InsBalance & PatBalance in the outer query.

PBUH

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-10-27 : 09:40:19

I am not sure how this is going to work?

ISNULL(SUM(CASE WHEN NumberOfVisits > "X" THEN InsBalance ELSE 0 END) , 0) AS InsBalance,
ISNULL(SUM(CASE WHEN NumberOfVisits > "X" THEN PatBalance ELSE 0 END) , 0) AS PatBalance

If you look at my NumberOfVisits column, I have it counting 4 sets of distinct billing status codes with different days greater than ... 3 days, 10 days, 30 days and 45 days. I don't think this is considering that or at least not sure what I am entering to replace the "X". Could be wrong though, not sure.

Sachin.Nand

2937 Posts

Posted - 2009-10-27 : 10:23:18

I have prepared some sample data.Not sure the the output is exactly what u want.


DECLARE @tbl AS TABLE(cd INT,VALUE INT,dt DATETIME)
INSERT INTO @tbl
SELECT 2,5,GETDATE()-4 UNION
SELECT 3,4,GETDATE()-12 UNION
SELECT 9,10,GETDATE()-30 UNION
SELECT 7,15 ,GETDATE()-45

SELECT * FROM @tbl

SELECT * FROM 
(
SELECT cd,
   SUM(CASE WHEN (cd IN ( 2 , 4 , 5 , 16 ) ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 3 THEN 1
            WHEN ( cd IN ( 3 , 8 , 17 ) ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 10 THEN 1
            WHEN ( cd = 9 ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 30 THEN 1
            WHEN ( cd = 7 ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 45 THEN 1
            ELSE 0
       END) AS NumberOfVisits,
       SUM(VALUE)OVER(PARTITION BY CASE WHEN (cd IN ( 2 , 4 , 5 , 16 ) ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 3 THEN 1
            WHEN ( cd IN ( 3 , 8 , 17 ) ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 10 THEN 1
            WHEN ( cd = 9 ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 30 THEN 1
            WHEN ( cd = 7 ) AND
                 DATEDIFF(DAY , dt , GETDATE()) > 45 THEN 1
            ELSE 0
       END)AS sumvalue  FROM @tbl
       GROUP BY cd,dt,VALUE
      )t WHERE NumberOfVisits<>0

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources