on 06-18-2010 2:04 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 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??
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello David...
Maybe try to change your format like this???
DATEDIFF(day, T0.PostDate, GETDATE()) > 59
Regards - Zal
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.