CODEWITHSUNDEEP
sqlsql-server
jishan siddique
jishan siddique

Reputation: 1893

CASE GETDATE() - GETDATE() in sql server

can anyone explain the below example?

Query

SELECT (CASE WHEN (GETDATE() - GETDATE()) < 31 THEN  1 ELSE 0 END) [Result],
(CASE WHEN (GETDATE() - '2020-08-30') < 31 THEN  1 ELSE 0 END) [Result1],
(CASE WHEN (GETDATE() - '2020-07-30') < 31 THEN  1 ELSE 0 END) [Result2],
(CASE WHEN (GETDATE() - '2020-07-30') < 31 THEN  1 ELSE 0 END) [Result3]

Output

Result  Result1 Result2 Result3
1        1      0       0

I want to know just way Result and Result1 has 1 and else 0?

Actual Query

SELECT  ((CASE WHEN (GETDate()-[InvBillDate])<31 then 1 
else 
(case when (GETDate()-[InvBillDate])<61 And (GETDate()-[InvBillDate])>30 then 2 
else 
(case when (GETDate()-[InvBillDate])<91 And (GETDate()-[InvBillDate])>60 then 3 
else 4 end) 
end) end)) AS [ColNo] 
FROM [dbo].[Invoice] (NOLOCK)

Upvotes: 0

Views: 383

Answers (2)

LucyP
LucyP

Reputation: 31

If you don't mind, I suggest this way to shorten a bit the query.

SELECT (
       (CASE WHEN (GETDate()-[InvBillDate])<31 then 1 
       else 
       (case when (GETDate()-[InvBillDate])<61 then 2 
       else 
       (case when (GETDate()-[InvBillDate])<91 then 3 
       else 4 
       end) end) end)
       ) AS [ColNo] 
FROM [dbo].[Invoice] (NOLOCK)

Upvotes: 1

LucyP
LucyP

Reputation: 31

With limited context, I will try to interpret the query.

It is trying to calculate the date difference between today and the billing date, then based on that categorize the date differences into 4 groups:

  • Within 31 days
  • 31 - 60 days
  • 61 - 90 days
  • More than 90 days

Upvotes: 0

Related Questions