September 11, 2006 at 9:59 am
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
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
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
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
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
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