Search
Search

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

##### Former Memberreplied

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

0 View this answer in context
View more on this topic or