How to calculate quarter depends on month in mssql2000

Posted by Raghuldrag under Sql Server on 10/13/2014 | Points: 10 | Views : 375 | Status : [Member] | Replies : 2
Hi Friends,



I ve the sample table like


create table test
(
Item varchar(500),
uom char(200),
qty float(50),
price numeric(5,2),
bill_date datetime
)



insert into test values ('pudina','kgs','5','300','2014-04-05')
insert into test values ('karpoor','kgs','15','300','2014-06-15')
insert into test values ('Garlic','kgs','25','300','2014-08-05')
insert into test values ('Oil','Ltr','05','300','2014-11-05')


like i ve the raw materials purchased on different dates



now my clients expecting the o/p to show quarter depends on month

QUARTER:
--------
1=(apr,may,june),
2=(july,aug,sep)
3=(oct,nov,dec)
4=(jan,feb,march)


Expect O/p:

item UOM QTY Quarter Month
pudina KGS 5 1 apr
karpoor KGS 15 1 june
Garlic KGS 25 2 Aug
Oil Ltr 5 3 NOv




How to make query in Mssql 2000




Responses

Posted by: Bandi on: 10/13/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT 
Item, UOm, Qty,
CASE WHEN DATENAME( Month, bill_date) IN ( 'April', 'May', 'June') THEN 1
WHEN DATENAME( Month, bill_date) IN ( 'July', 'August', 'September') THEN 2
WHEN DATENAME( Month, bill_date) IN ( 'October', 'November', 'December') THEN 3
WHEN DATENAME( Month, bill_date) IN ( 'January', 'February', 'March') THEN 4
END [Quarter],
DATENAME( MONTH,bill_date) [Month]
FROM Test


Refer this link for DATENAME() function
http://technet.microsoft.com/en-us/library/aa258263(v=sql.80).aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raghuldrag on: 10/14/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Bandi,

Thanks For Ur kind Info



Thanks & Regards

A.rama krishnan

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response