CODEWITHSUNDEEP
sql-servert-sql
mbrewer
mbrewer

Reputation: 11

Using a range with GETDATE & BETWEEN

I'm fairly new to T-SQL and am still learning so please bear with me on this one...I've tried several ways to do this with no luck. I have my 'current' and 90+ days but it's the 30 and 60 days that I'm struggling with. Why can't I use the 'between' clause? 

   ,[CURRENT]=(SELECT sum (cle.[Amount]) 
     FROM [NISNAV].[dbo].[NIS$Cust_ Ledger Entry] cl3 left outer join 
     [NISNAV].[dbo].[NIS$Detailed Cust_ Ledg_ Entry] cle on cl3.[entry no_]=cle.[cust_ ledger entry no_]
     where C.[NO_]=CL3.[CUSTOMER NO_] AND cl3.[open]='1' AND cl3.[Due Date]>getdate()-30) 



   ,[30 DAYS]=(SELECT sum (cle.[Amount])
     FROM [NISNAV].[dbo].[NIS$Cust_ Ledger Entry] cl3 left outer join 
     [NISNAV].[dbo].[NIS$Detailed Cust_ Ledg_ Entry] cle on cl3.[entry no_]=cle.[cust_ ledger entry no_]
     where C.[NO_]=CL3.[CUSTOMER NO_] AND cl3.[open]='1' AND cl3.[Due Date]BETWEEN GETDATE()-31 AND getdate()-59) 

   ,[60 DAYS]=(SELECT sum (cle.[Amount]) 
     FROM [NISNAV].[dbo].[NIS$Cust_ Ledger Entry] cl3 left outer join 
     [NISNAV].[dbo].[NIS$Detailed Cust_ Ledg_ Entry] cle on cl3.[entry no_]=cle.[cust_ ledger entry no_]
     where C.[NO_]=CL3.[CUSTOMER NO_] AND cl3.[open]='1' AND cl3.[Due Date] BETWEEN GETDATE()-60 AND getdate()-89) 

      ,[90 + DAYS]=((SELECT sum (cle.[Amount]) 
     FROM [NISNAV].[dbo].[NIS$Cust_ Ledger Entry] cl3 left outer join 
     [NISNAV].[dbo].[NIS$Detailed Cust_ Ledg_ Entry] cle on cl3.[entry no_]=cle.[cust_ ledger entry no_]
     where C.[NO_]=CL3.[CUSTOMER NO_] AND cl3.[open]='1' AND cl3.[Due Date]<=getdate()-90 ))

I know there are entries for that particular customer in both of those buckets.

suggestions?

Upvotes: 1

Views: 1184

Answers (2)

Anon
Anon

Reputation: 10908

Instead of four separate subqueries, you can use one query with CASE statements

SELECT
  [CURRENT] = SUM(CASE WHEN cl3.[Due Date] >= DATEADD(day,-30,GETDATE())
                       THEN cle.[Amount]
                  END)
 ,[30 DAYS] = SUM(CASE WHEN cl3.[Due Date] <  DATEADD(day,-30,GETDATE())
                        AND cl3.[Due Date] >= DATEADD(day,-60,GETDATE())
                       THEN cle.[Amount]
                  END)
 ,[60 DAYS] = SUM(CASE WHEN cl3.[Due Date] <  DATEADD(day,-60,GETDATE())
                        AND cl3.[Due Date] >= DATEADD(day,-90,GETDATE())
                       THEN cle.[Amount]
                  END)
 ,[90 DAYS] = SUM(CASE WHEN cl3.[Due Date] <  DATEADD(day,-90,GETDATE())
                       THEN cle.[Amount]
                  END)
FROM [NISNAV].[dbo].[NIS$Cust_ Ledger Entry] cl3
LEFT OUTER JOIN [NISNAV].[dbo].[NIS$Detailed Cust_ Ledg_ Entry] cle
  ON( cl3.[entry no_]=cle.[cust_ ledger entry no_])
WHERE C.[NO_]=CL3.[CUSTOMER NO_]
  AND cl3.[open]='1'

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

When filtering a range of dates, BETWEEN expects the earlier date to come before the later date.

You need to reverse the sides of the BETWEEN so that the older date (with highest -value) comes first:

   ,[30 DAYS]=(SELECT sum (cle.[Amount])
     FROM [NISNAV].[dbo].[NIS$Cust_ Ledger Entry] cl3 left outer join 
     [NISNAV].[dbo].[NIS$Detailed Cust_ Ledg_ Entry] cle on cl3.[entry no_]=cle.[cust_ ledger entry no_]
     where C.[NO_]=CL3.[CUSTOMER NO_] AND cl3.[open]='1' AND cl3.[Due Date]BETWEEN GETDATE()-59 AND getdate()-31) 

   ,[60 DAYS]=(SELECT sum (cle.[Amount]) 
     FROM [NISNAV].[dbo].[NIS$Cust_ Ledger Entry] cl3 left outer join 
     [NISNAV].[dbo].[NIS$Detailed Cust_ Ledg_ Entry] cle on cl3.[entry no_]=cle.[cust_ ledger entry no_]
     where C.[NO_]=CL3.[CUSTOMER NO_] AND cl3.[open]='1' AND cl3.[Due Date] BETWEEN GETDATE()-89 AND getdate()-60)

Upvotes: 1

Related Questions