SQL 2005 Query Help Needed

[JBourne77]

Please Note, the SQL is handled dynamically by the SQL server, therefore some items in my WHERE clauses will look alittle odd to you, please disregard them, as they are not an issue.

What I want to accomplish is adding in YTD (year to date) figures for my Payments and Adjustments. What I hoped to do begins on line 463. Is it possible to alter the temp table, add in the two fields and then add in two subselect querys to get my YTD figures? I need help on my subselects WHERE clause if so to pull the date and determine the beginning of year to get YTD total then.

SET NOCOUNT ON
 
DECLARE
    @startdate DATETIME,
    @enddate DATETIME
 
IF 2 = 1 
    BEGIN 	-- Date mode
        SET @startdate = ISNULL(NULL , '1/1/1900') 
        SET @enddate = DATEADD(DAY , 1 , ISNULL(NULL , '1/1/3000'))
    END
ELSE 
    BEGIN  	--month mode
        SET @startdate = ISNULL('6/01/2009' , '1/1/1900') 
        SET @enddate = DATEADD(month , 1 , ISNULL('6/01/2009' , '1/1/3000'))
    END
 
--Gather the charges
 
SELECT
    pv.PatientVisitId,
    pp.PatientId,
    dr.Listname AS Doctor, 
--    	cast (' ' as VARCHAR(50)) AS Resource, 
    dfr.ListName AS Resource,
    pvp.TotalFee AS Charges,
    CAST('0.00' AS MONEY) AS Payments,
    CAST('0.00' AS MONEY) AS Adjustments,
    NULL AS NewPatients,
    0 AS TypeMLC,
    CAST('0.00' AS MONEY) AS Amount,
    b.Entry AS Entered,
    0 AS TotalVisits,
    DATEPART(month , b.entry) AS [Month],
    DATEPART(year , b.entry) AS [Year]
INTO
    #temp
FROM
    PatientVisit pv 
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId 
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId 
INNER JOIN PatientProfile pp ON pva.PatientProfileId = pp.PatientProfileId 
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
INNER JOIN Procedures p ON pvp.proceduresID = p.proceduresID 
INNER JOIN Batch b ON pvp.BatchId = b.BatchId 
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID 
LEFT JOIN PatientVisitResource pvr ON pvr.PatientVisitId = pv.PatientVisitId 
LEFT JOIN DoctorFacility dfr ON pvr.ResourceId = dfr.DoctorFacilityId
WHERE
    pvp.TotalFee <> 0 AND
    b.Entry >= @StartDate AND
    b.Entry < @endDate AND
    --Filter on Carrier
	(
        (
          NULL IS NOT NULL AND
          pv.primaryinsurancecarriersID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on company
	(
        (
          NULL IS NOT NULL AND
          pv.CompanyID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on facility
	(
        (
          NULL IS NOT NULL AND
          pv.FacilityID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on Financial Class
	(
        (
          NULL IS NOT NULL AND
          pv.FinancialClassMID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on Ins Group
	(
        (
          NULL IS NOT NULL AND
          ic.insuranceGroupID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on Provider
	(
        (
          NULL IS NOT NULL AND
          pv.DoctorID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      )
 
-- Insert Payments
 
INSERT INTO
    #temp
    (
      Entered,
      PatientVisitId,
      PatientId,
      Doctor,
      Charges,
      Payments,
      TypeMLC,
      TotalVisits,
      [Month],
      [Year] 
	
    )
    SELECT
        b.Entry,
        pv.PatientVisitId,
        pp.PatientId,
        dr.ListName AS Doctor,
        NULL AS Charges,
        vt.Payments,
        0 AS TypeMLC,
        0 AS TotalVisits,
        DATEPART(month , b.entry) AS [Month],
        DATEPART(year , b.entry) AS [Year]
    FROM
        PaymentMethod pm 
    INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId 
    INNER JOIN Batch b ON pm.BatchId = b.BatchId 
    INNER JOIN PatientVisit pv ON pv.PatientVisitid = vt.PatientVisitId 
    INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId 
    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
    LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
    WHERE
        b.Entry >= @StartDate AND
        b.Entry < @endDate AND
        vt.Payments <> 0 AND
        --Filter on Carrier
	(
        (
          NULL IS NOT NULL AND
          pv.primaryinsurancecarriersID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on company
	(
        (
          NULL IS NOT NULL AND
          pv.CompanyID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on facility
	(
        (
          NULL IS NOT NULL AND
          pv.FacilityID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Financial Class
	(
        (
          NULL IS NOT NULL AND
          pv.FinancialClassMID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Ins Group
	(
        (
          NULL IS NOT NULL AND
          ic.insuranceGroupID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Provider
	(
        (
          NULL IS NOT NULL AND
          pv.DoctorID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      )
 
--Insert adjustmetns
 
INSERT INTO
    #temp
    (
      Entered,
      PatientVisitId,
      PatientId,
      Doctor,
      Charges,
      Adjustments,
      TypeMLC,
      TotalVisits,
      [Month],
      [Year] 
	
    )
    SELECT
        b.Entry,
        pv.PatientVisitId,
        pp.PatientId,
        dr.ListName AS Doctor,
        NULL AS Charges,
        vt.Adjustments,
        0 AS TypeMLC,
        0 AS TotalVisits,
        DATEPART(month , b.entry) AS [Month],
        DATEPART(year , b.entry) AS [Year]
    FROM
        PaymentMethod pm 
    INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId 
    INNER JOIN Batch b ON pm.BatchId = b.BatchId 
    INNER JOIN PatientVisit pv ON vt.PatientVisitid = pv.PatientVisitId 
    INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId 
    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
    LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
    WHERE
        b.Entry >= @StartDate AND
        b.Entry < @endDate AND
        vt.Adjustments <> 0 AND
        --Filter on Carrier
	(
        (
          NULL IS NOT NULL AND
          pv.primaryinsurancecarriersID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on company
	(
        (
          NULL IS NOT NULL AND
          pv.CompanyID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on facility
	(
        (
          NULL IS NOT NULL AND
          pv.FacilityID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Financial Class
	(
        (
          NULL IS NOT NULL AND
          pv.FinancialClassMID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Ins Group
	(
        (
          NULL IS NOT NULL AND
          ic.insuranceGroupID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Provider
	(
        (
          NULL IS NOT NULL AND
          pv.DoctorID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      )
 
--New Patients
 
INSERT INTO
    #temp
    (
      PatientVisitId,
      PatientId,
      Doctor,
      NewPatients,
      TypeMLC,
      TotalVisits,
      Entered,
      [Month],
      [Year] 
	
    )
    SELECT
        pv.PatientVisitId,
        pp.PatientId,
        dr.ListName AS Doctor,
        pvp.CPTCode AS NewPatients,
        0 AS TypeMLC,
        0 AS TotalVisits,
        b.Entry,
        DATEPART(month , b.entry) AS [Month],
        DATEPART(year , b.entry) AS [Year]
    FROM
        PatientVisit pv 
    INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId 
    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
    INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
    INNER JOIN Batch b ON pvp.BatchId = b.BatchId 
    LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
    WHERE
        b.Entry >= @StartDate AND
        b.Entry < @endDate AND
        (
          (
            pvp.CPTCode >= '99201' AND
            pvp.CPTCode <= '99205'
          ) OR
          (
            pvp.CPTCode >= '99381' AND
            pvp.CPTCode <= '99387'
          ) OR
          (
            pvp.CPTCode >= '99241' AND
            pvp.CPTCode <= '99245'
          ) OR
          ( pvp.CPTCode = 'NEWOB' ) OR
          ( pvp.CPTCode = '92002' )   
          OR
          ( pvp.CPTCode = '92004' ) OR
          ( pvp.CPTCode = '92012' ) OR
          ( pvp.CPTCode = '92014' )
        ) AND
        --Filter on Carrier
	(
        (
          NULL IS NOT NULL AND
          pv.primaryinsurancecarriersID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on company
	(
        (
          NULL IS NOT NULL AND
          pv.CompanyID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on facility
	(
        (
          NULL IS NOT NULL AND
          pv.FacilityID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Financial Class
	(
        (
          NULL IS NOT NULL AND
          pv.FinancialClassMID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Ins Group
	(
        (
          NULL IS NOT NULL AND
          ic.insuranceGroupID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
        --Filter on Provider
	(
        (
          NULL IS NOT NULL AND
          pv.DoctorID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      )
    ORDER BY
        pvp.CPTCode
 
--Total Visits
 
INSERT INTO
    #temp
    (
      PatientVisitId,
      Doctor,
      [Month],
      [Year],
      TypeMLC,
      TotalVisits,
      Entered 
	
    )
    SELECT
        t.PatientVisitId,
        t.Doctor,
        t.Month,
        t.Year,
        0 AS TypeMLC,
        1 AS TotalVisits,
        '1/1/1900' AS Entered
    FROM
        #temp t
    WHERE
        t.Charges IS NOT NULL
    GROUP BY
        t.PatientVisitId,
        t.Doctor,
        t.Month,
        t.Year
 
--Update resources
 
UPDATE
    #temp
SET 
    Resource = res.Listname
FROM
    PatientVisitResource pvr 
INNER JOIN #temp t ON pvr.PatientVisitId = t.PatientVisitId 
INNER JOIN DoctorFacility res ON pvr.ResourceId = res.DoctorFacilityId 
 
UPDATE
    #temp
SET 
    Resource = 'No Resource'
WHERE
    Resource IS NULL
 
--IF '0' = '1'
--BEGIN
--    UPDATE #temp
--    SET Doctor = doctor + ' / ' + resource
--    WHERE Resource Is Not Null 
--        and resource <> ''
--END
 
UPDATE
    #temp
SET charges = 0
WHERE
    charges IS NULL
 
UPDATE
    #temp
SET payments = 0
WHERE
    payments IS NULL
 
UPDATE
    #temp
SET adjustments = 0
WHERE
    adjustments IS NULL
    
ALTER TABLE #temp ADD YTDPayments MONEY, YTDAdjustments MONEY    
 
UPDATE #temp
SET YTDPayments = (SELECT SUM(Payments)FROM #temp WHERE ....)
    
SELECT
    *
FROM
    #temp
 
DROP TABLE #temp

 

[Emtucifor]

is not an issue

Have you actually checked execution plans or done any empirical cost testing (with profiler) to make sure that the horrid dynamic SQL your application is building doesn't harm performance?

 

[LNBruno]

Pardon me, but have you considered giving up app-generated code and actually learning T-SQL?

That "NULL IS NOT NULL" stuff is pure bovine excrement.

< M!ke >
[small]Electile dysfunction: the inability to become aroused over the choices put forth by either party for an election year.[/small]

 

[JBourne77]

LNBruno -

I have to use app-generated code and yes I agree it is that, but thats what i have to deal with.

 

[Emtucifor]

You have to use THIS app-generated code? You can't rewrite it?

Did you know that it is a relatively simple thing to add a parameter conditionally to a SQL String?

Look at this:

FilterList.Add(ColumnName := "pv.DoctorID", SqlValue := "23")
FilterList.Add(ColumnName := "ab.StringValue", SqlValue := SqlEscapeString("string value"))

For Each FilterItem In FilterList
   WhereClause = WhereClause _
      & Iif(WhereClause <> "", vbCrlf & "AND ", "") _
      & FilterItem.ColumnName _
      & Iif(IsNull(FilterList.SqlValue), " IS NULL", " = " & FilterList.SqlValue)
Next

Function SqlEscapeString(Value As String) As String
   SqlEscapeString = "'" & Replace(SqlEscapeString, "'", "''") & "'"
End Function

This has its own problems such as escaping strings itself instead of using Commands with parameters (so the connection library does the escaping instead of you) but would result in far superior SQL code...