SQL Query Problem

[ljwilson]

I am trying to modify the following query so that it will not pull results where we have more records for a given account in #tempBarTransactions than we do in the view_finalizedVisitsProcedureDetail. In other words, currently the following query will return results for an account that has several records in the #tempBarTransactions table and only one record in the view_finalizedVisitsProcedureDetail. Since bt.charge matches fv.visitAmount for that one that is in both sources, we get a record returned for that account.

Here is the query:

select * from [achri-sql].clinicaltrials.dbo.Scheduled_Visits	
        WHERE   ([is_reconciled] = 0 OR [is_reconciled] IS NULL)
                AND id IN (
                SELECT  fv.visit_fk
				--SELECT  fv.AccountNumber, fv.ProcedureNumber, fv.VisitQuantity, ISNULL(fv.VisitAmount,0) AS VisitAmount, bt.[PatientAccount], bt.[ProcedureCode], bt.[Quantity], bt.[Charge]
                FROM    [achri-sql].clinicaltrials.dbo.view_finalizedVisitsProcedureDetail fv
						LEFT JOIN dbo.BAR_procedureDictionary p ON p.mnemonic = fv.ProcedureNumber
                        LEFT JOIN #tempBarTransactionTotals bt ON fv.AccountNumber = bt.[PatientAccount]
                                                                  AND fv.ProcedureNumber = bt.[ProcedureCode]
                                                                  AND (fv.VisitQuantity = bt.[Quantity] OR p.department = '014250') 
                                                                  AND fv.visitAmount = bt.charge
																  AND cast(convert(char(10),fv.[visit_date],120) as datetime) = cast(convert(char(10),bt.ServiceDate,120) as datetime)
                WHERE   (bt.charge = fv.visitAmount
                        OR fv.visitAmount = 0)
                        


                GROUP BY fv.visit_fk )

I think the problem lies here:

AND fv.ProcedureNumber = bt.[ProcedureCode]

Since that will only try to match on records that exist in both sources. I need it to match on every record in both sources.

Thanks,

LJ

 

[gmmastros]

try changing your second where clause to AND.

select * from [achri-sql].clinicaltrials.dbo.Scheduled_Visits    
        WHERE   ([is_reconciled] = 0 OR [is_reconciled] IS NULL)
                AND id IN (
                SELECT  fv.visit_fk
                --SELECT  fv.AccountNumber, fv.ProcedureNumber, fv.VisitQuantity, ISNULL(fv.VisitAmount,0) AS VisitAmount, bt.[PatientAccount], bt.[ProcedureCode], bt.[Quantity], bt.[Charge]
                FROM    [achri-sql].clinicaltrials.dbo.view_finalizedVisitsProcedureDetail fv
                        LEFT JOIN dbo.BAR_procedureDictionary p ON p.mnemonic = fv.ProcedureNumber
                        LEFT JOIN #tempBarTransactionTotals bt ON fv.AccountNumber = bt.[PatientAccount]
                                                                  AND fv.ProcedureNumber = bt.[ProcedureCode]
                                                                  AND (fv.VisitQuantity = bt.[Quantity] OR p.department = '014250')
                                                                  AND fv.visitAmount = bt.charge
                                                                  AND cast(convert(char(10),fv.[visit_date],120) as datetime) = cast(convert(char(10),bt.ServiceDate,120) as datetime)
                [!]And[/!]   (bt.charge = fv.visitAmount
                        OR fv.visitAmount = 0)
                        


                GROUP BY fv.visit_fk )

If this works and you want me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ljwilson]

That did not work. It is examining only the one record from both sources that match. I need it to return only when both sources match on

fv.ProcedureNumber = bt.[ProcedureCode]

but also only return when the number of fv.ProcedureNumber records is the same as the number of bt.[ProcedureCode]
records.

Does that make sense?

 

[ljwilson]

Does anyone have any ideas on this. I have tried a couple of different things, buy can't seem to get a grasp on this one.

 

[SQLSister]

I think your problem is that you have referenced one of the tables that you are left joining to in the where clause thus changing it to an inner join (you also have a completely unneeded group by clause). I would not do this in a subquery at any rate because subqueries are to be avoided as much as possible for performance reasons. I would use a derived table approach. Try this:

select sv.* from [achri-sql].clinicaltrials.dbo.Scheduled_Visits   sv
Join
 (SELECT  fv.visit_fk, BT.Charge
 --SELECT  fv.AccountNumber, fv.ProcedureNumber, fv.VisitQuantity, ISNULL(fv.VisitAmount,0) AS VisitAmount, bt.[PatientAccount], bt.[ProcedureCode], bt.[Quantity], bt.[Charge]
 FROM    [achri-sql].clinicaltrials.dbo.view_finalizedVisitsProcedureDetail fv
         LEFT JOIN dbo.BAR_procedureDictionary p ON p.mnemonic = fv.ProcedureNumber
         LEFT JOIN #tempBarTransactionTotals bt ON fv.AccountNumber = bt.[PatientAccount]
      AND fv.ProcedureNumber = bt.[ProcedureCode]
      AND (fv.VisitQuantity = bt.[Quantity] OR p.department = '014250') 
      AND fv.visitAmount = bt.charge
      AND cast(convert(char(10),fv.[visit_date],120) as datetime) = cast(convert(char(10),bt.ServiceDate,120) as datetime)
 WHERE   (bt.charge = fv.visitAmount
         OR fv.visitAmount = 0)) a on a.visit_fk  = sv.id
WHERE   ([is_reconciled] = 0 OR [is_reconciled] IS NULL) and (a.Charge is not null or fv.visitAmount = 0)

It is also a bad practice to use select * in code that will be on a production server. You should get in the habit of always specifying only the columns you are actually going to use.

"NOTHING is more important in a database than integrity." ESquared

 

[ljwilson]

SQLSister,

Thanks for the help. I think I see what you are doing, but when I run the code, I get the following error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "fv.visitAmount" could not be bound.