Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Query Help Needed 1

Status
Not open for further replies.
JBourne77

JBourne77

IS-IT--Management
Jan 21, 2008
153
US
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:
Code: 
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
 
Sort by date Sort by votes
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(CASE WHEN ( bs.Code IN ( 2 , 4 , 5 , 16 ) 
 AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 3 ) OR 
            ( bs.Code IN ( 3 , 8 , 17 )  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 10) OR 
            ( bs.Code = 9  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 30) OR
            ( bs.Code = 7  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 45) THEN pva.InsBalance else 0 end)) , 0) AS InsBalance,
   ISNULL(SUM(CASE WHEN ( bs.Code IN ( 2 , 4 , 5 , 16 ) 
 AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 3 ) OR 
            ( bs.Code IN ( 3 , 8 , 17 )  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 10) OR 
            ( bs.Code = 9  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 30) OR
            ( bs.Code = 7  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 45) then pva.PatBalance else 0 end)) , 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

Try with my changes - not tested.
 
Upvote 0 Downvote
markros -

I am getting the following SQL Error back:

Msg 174, Level 15, State 1, Line 22
The isnull function requires 2 arguments.
 
Upvote 0 Downvote
Looks like there was extra )

Code: 
-- Test query
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(CASE WHEN ( bs.Code IN ( 2 , 4 , 5 , 16 )
 AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 3 ) OR
            ( bs.Code IN ( 3 , 8 , 17 )  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 10) OR
            ( bs.Code = 9  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 30) OR
            ( bs.Code = 7  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 45) THEN pva.InsBalance else 0 end),0)  AS InsBalance,
   ISNULL(SUM(CASE WHEN ( bs.Code IN ( 2 , 4 , 5 , 16 )
 AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 3 ) OR
            ( bs.Code IN ( 3 , 8 , 17 )  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 10) OR
            ( bs.Code = 9  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 30) OR
            ( bs.Code = 7  AND
                 DATEDIFF(day , pv.lastmodified , GETDATE()) > 45) then pva.PatBalance else 0 end) , 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
 
Upvote 0 Downvote
Markos -

That seems to fix my issue in preliminary testing. Thanks a million!

Cheers,

Jeff
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
377
pjw001
pjw001
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
225
f0rg3tfu1
f0rg3tfu1
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
385
Volvere
V
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
562
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
297
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top