Display data based on month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Display data based on month

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-04-26 : 04:25:41

declare @Details table
(
order_id int identity(1,1),
product_name varchar(5),
amount decimal(12,2),
order_date datetime
)

insert into @details(product_name,amount,order_date)
select 'A',7345.50,'2012-05-01 00:00:00' union all
select 'B',134.10,'2012-06-01 00:00:00' union all
select 'C',56.00,'2012-07-01 00:00:00' union all
select 'D',6345.45,'2012-09-01 00:00:00' union all
select 'E',4345.45,'2012-10-01 00:00:00'

select * from @Details

--if the month for order_date is next month, display '1 '
--if the month for order_date is between 1 and 2 months, display ' 2 '
--if the month for order_date is between 2 and 3 months, display '3 '
--if the month for order_date is 6 months, display ' 6 '

--if the month for order_date is between 4 and 6 months, ignore

--Expected Result
--ProductName Amount Status
--A 7345.50 1
--B 134.10 2
--C 56.00 3
--E 4345.45 6

I tried like this,didn't succeed.
select
product_name
,case ( DATEDIFF(M,getdate() ,order_date))
when 1 then '1 month' else 0
when 2 then '2 month' else 0
when 3 then '3 month' else 0
when 6 then '6 month' else 0

end as status
from @Details
Please advice.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-26 : 05:32:04

Notice the difference between the two case statements:

select 
	*, 
	DATEDIFF(M,getdate() ,order_date),
	status = case 
		when DATEDIFF(M,getdate() ,order_date) = 1 then '1 month'
		when DATEDIFF(M,getdate() ,order_date) = 2 then '2 month'
		when DATEDIFF(M,getdate() ,order_date) = 3 then '3 month'
		when DATEDIFF(M,getdate() ,order_date) between 4 and 6 then '6 month'
		else 'unknown'
		end,
	status = case DATEDIFF(M,getdate() ,order_date)
		when 1 then '1 month'
		when 2 then '2 month'
		when 3 then '3 month'
		when 6 then '6 month'
		else 'unknown'
		end
from @Details

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources