Post reply

I ruined my query

  • September 11, 2006 at 9:59 am

    #153178

    I have a query that worked beautifully thanks to help from this forum.

    Recently, I needed to add another condition to the CASE statement. The added line that ruined the query is in bold:

    SELECT DISTINCT(WRid) AS JobNum, WRenteredDate, WRcurrentStatus, WRclaimNumber, WRstatusUpdated, BMcopynetInvoiceNumber, WRmodifiedDate,

    CASE WHEN Payday IS NOT NULL THEN DEaltKeyValue ELSE '0' END AS totPaid

    ,CASE WHEN WRcurrentStatus = 110 THEN DEaltKeyValue ELSE '0' END AS totCancel

    ,CASE WHEN Payday IS NOT NULL OR WRcurrentStatus = 110 THEN '0' ELSE DEaltKeyValue END AS totOpen

    ,CASE WHEN WRcurrentStatus = 40 THEN DEaltKeyValue ELSE '0' END AS totVoid

    ,DEaltKeyValue AS totBillAmount

    FROM workrequest

    LEFT JOIN(SELECT DEkeyValue AS JobNumber, DEentryTypeID, DEenteredDate AS PayDay

    FROM diaryentry inner join workrequest on DEkeyValue=WRid

    AND DEentryTypeID='ENRPAY')AS cnt ON WRid=JobNumber

    LEFT OUTER JOIN billmaster ON WRid = BMworkRequestId

    LEFT OUTER JOIN diaryentry ON WRid = DEkeyValue

    WHERE 1=1

    AND WRmodifiedDate BETWEEN '" & strStartDate & "' AND '" & strEndDate & "'

    AND diaryentry.DEentryTypeID='ENRINV' OR diaryentry.DEentryTypeID = 'ENRIVD'

    AND DEdeletedDate IS NULL

    AND BMdeletedDate IS NULL

    AND BMvoidDate IS NULL

    AND WRjobTypeID <> 'TRJTINT'

    GROUP BY WRid, WRenteredDate, WRcurrentStatus, WRclaimNumber, WRmodifiedDate,

    DEaltKeyValue, cnt.Payday, BMcopynetInvoiceNumber, WRstatusUpdated

    ORDER BY WRid, BMcopynetInvoiceNumber, WRmodifiedDate

    So now I have a new condition and a new column in the results called totVoid.

    There should be data in one of the colums(totPaid, totCancel, TotOpen, or TotVoid) and the rest should have zeroes. However, after adding the TotVoid column, data is now duplicated in both the totOpen AND totVoid. So for every entry in totVoid, it is duplicated in totOpen.

    This should not be the case. But I can't figure out why it would be doing this.

    Can anyone see an issue with the query?

    Thanks!

  • September 11, 2006 at 10:55 am

    #659717

    There must be data in both totVoid and totOpen. This query isn't adding or duplicating data into your tables - it's only a SELECT statement. Did you check your tables first?

    -SQLBill

  • September 11, 2006 at 12:30 pm

    #659742

    Data will be in the TotOpen column if PayDay is Null or WRcurrentStatus is not 110.

    (NOTE: PayDay is the result of a subquery)

    Data will be in TotVoid column if WRcurrentStatus = 40

    Maybe I am wording the CASE statement wrong?

  • September 11, 2006 at 1:52 pm

    #659756

    Replace the totOpen column def in Query with this, hope it will work

    CASE WHEN Payday IS NOT NULL OR WRcurrentStatus = 110 OR WRcurrentStatus = 40 THEN '0' ELSE DEaltKeyValue END AS totOpen

  • September 11, 2006 at 2:01 pm

    #659758

    Hey Gopi! That worked! What did you do? I don't understand your logic behind the change.

    But thanks!

  • September 11, 2006 at 5:52 pm

    #659779

    Its very simple logic,

    In All the four columns One Column only contains the value and all other's contains 0, Then dont allow any value to be true for two conditions, that's it

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply