CODEWITHSUNDEEP
sqlselectdatediff
hoonu
hoonu

Reputation: 23

Add New Column in Nested SELECT with DATEDIFF

Admitted SQL newbie here but I'm working on a query where the request just got more complicated. Long short of it is that I have a table with a few columns. One of which is a date column.

I'm writing a query to return a set of columns along with adding a new column based on the date range (<=90 Days, 91-180, 180+). This is what I have so far. It works for the first 90 but I'm having a hard timing nesting the other select commands to get the other data sets.

Select Key, Name, 'C90' as Code
From LD_List
WHERE DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) <= 90

I appreciate the help.

Upvotes: 1

Views: 1237

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

If you are using TSQL, SQL Server or MYSQL you may use, CASE WHEN : Key is a resreved keyword. So use squre brackets. In my sample I have used key as id.

Query: SQL Server version: 

Select id, Name, 
case when DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) <= 90 
Then 'C90' 
when (DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) >= 91 
and DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) <= 180) 
then 'C180' 
else 'C181' 
end AS Code
From LD_List

Results:

ID  NAME    CODE
1   john    C181
2   tim     C181
3   jack    C181
4   kate    C181
5   jim     C181
6   ron     C180
7   henry   C180
8   liza    C90

EXTRA :)

Now if you need a pivot like the following: Check this * SQLFIDDLE Demonstration out

Results:

CODE    PURCHASERS
C180    ron,henry
C181    john,tim,jack,kate,jim
C90     liza

Upvotes: 2

Melanie
Melanie

Reputation: 3111

SELECT Key, Name,
   CASE WHEN DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) <= 90 THEN 'C90'
        WHEN DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) >= 91 AND DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) <= 180 THEN 'C180'
        WHEN DATEDIFF(DAY,[LAST_PURCHASE_DATE],GETDATE()) >= 181 THEN 'C181'
   END AS Code

Upvotes: 1

Related Questions