Query Problem

[Former Member]

SELECT T0.[DocNum] AS 'Document Number', T1.[RefDate] AS 'Posting Date', T2.[Account] AS 'Account Code', T2.[Credit] AS 'Credit Amount', T2.[Debit] AS 'Debit Amount', T2.[LineMemo] AS 'Row Details', T1.[TransId] AS 'Transaction Number' FROM [dbo].[OWOR] T0 INNER JOIN [dbo].[OJDT] T1 ON T1.[TransId] = T0.[TransId] INNER JOIN [dbo].[JDT1] T2 ON T2.[TransId] = T1.[TransId] WHERE T0.[PostDate] Between GETDATE() -180 and GETDATE() and T2.[Account] = (N'11010100' ) AND T2.[Debit] > (0.1 ) oR T2.[Credit] > (0.1 ) ORDER BY T0.[Type],T1.[TransId]

Hi have written the above query to pull through posting into the wip variance account above 0.01 in the last month. The query actually shows all the postings rather than just the ones in the last month. I know it has something to do with the T0.[PostDate] Between GETDATE() -180 and GETDATE(). But I cant figure it out any advice??

[Former Member · Accepted solution]

David,

Try this way:

SELECT T0.DocNum AS 'Document Number', T1.RefDate AS 'Posting Date', T2.Account AS 'Account Code', T2.Credit AS 'Credit Amount', T2.Debit AS 'Debit Amount', T2.LineMemo AS 'Row Details', T1.TransId AS 'Transaction Number'

FROM dbo.OWOR T0 INNER JOIN dbo.OJDT T1 ON T1.TransId = T0.TransId

INNER JOIN dbo.JDT1 T2 ON T2.TransId = T1.TransId

WHERE DateDiff(DD,T0.PostDate,GETDATE()) < 181 and T2.Account = (N'11010100' ) AND (T2.Debit > (0.1 ) oR T2.Credit > (0.1 ))

ORDER BY T0.Type,T1.TransId

Thanks,

Gordon

[Former Member]

SELECT T0.[DocNum] AS 'Document Number', T1.[RefDate] AS 'Posting Date', T2.[Account] AS 'Account Code', T2.[Credit] AS 'Credit Amount', T2.[Debit] AS 'Debit Amount', T2.[LineMemo] AS 'Row Details', T1.[TransId] AS 'Transaction Number' FROM [dbo].[OWOR] T0 INNER JOIN [dbo].[OJDT] T1 ON T1.[TransId] = T0.[TransId] INNER JOIN [dbo].[JDT1] T2 ON T2.[TransId] = T1.[TransId] WHERE DATEDIFF (day, T0.PostDate, GETDATE()) > 59 AND T2.[Account] = (N'11010100' ) AND T2.[Debit] > (0.1 ) OR T2.[Credit] > (0.1 ) ORDER BY T0.[Type],T1.[TransId]

Tried this but no difference. Its almost like its ignroing the date rule on the query. I dont know whether its because i have "and's" and "or's" in my rule?

[zal_parchem2]

Hello David - first off, the T1.RefDate makes it a bit confusing so I substitued T0.Post Date. Next I changed the T2.Account in the WHERE clause...

Try this and let's go from there...Zal

SELECT T0.DocNum AS 'Document Number', 

T0.PostDate AS 'Posting Date', 

T2.Account AS 'Account Code', 
T2.Credit AS 'Credit Amount', 
T2.Debit AS 'Debit Amount', 
T2.LineMemo AS 'Row Details', 
T1.TransId AS 'Transaction Number' 

FROM dbo.OWOR T0 
INNER JOIN dbo.OJDT T1 
ON T1.TransId = T0.TransId 
INNER JOIN dbo.JDT1 T2 
ON T2.TransId = T1.TransId 

WHERE T0.PostDate >= '[%0]' AND T0.PostDate <= '[%1]' 

and (T2.Account = (N'11010100' ) AND T2.Debit > (0.1 ) oR T2.Credit > (0.1 ))

ORDER BY T0.Type,T1.TransId

Edited by: Zal Parchem on Jun 18, 2010 10:12 AM

[zal_parchem2]

Hello again David - your WHERE statement with T2.Debit and T2.Credit might need a change with another parenthesis??? Maybe not, since I do not know what the actual reqs are...

AND (T2.Debit > (0.1 ) OR T2.Credit > (0.1 ))

Regards - Zal

[zal_parchem2]

Hello David...

Maybe try to change your format like this???

DATEDIFF(day, T0.PostDate, GETDATE()) > 59

Regards - Zal