Former Member

### Query Problem

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??

10|10000 characters needed characters exceeded

Former Member
Jun 18, 2010 at 02:23 PM

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

10|10000 characters needed characters exceeded
• Jun 18, 2010 at 01:21 PM

Hello David...

Maybe try to change your format like this???

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

Regards - Zal

10|10000 characters needed characters exceeded
• Jun 18, 2010 at 01:29 PM

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

10|10000 characters needed characters exceeded
• Former Member
Jun 18, 2010 at 01:56 PM

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?

10|10000 characters needed characters exceeded
• 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