How to calculate quarter depends on month in mssql2000

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	Hi Bandi, Thanks For Ur kind Info Thanks & Regards A.rama krishnan Raghuldrag, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts