Skip to Content
avatar image
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??

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    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

    Add comment
    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

    Add comment
    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    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?

    Add comment
    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