• # How to calculate quarter depends on month in mssql2000

Are you looking for a way to calculate quarter depends on month in sql ? then read this thread to know more about it

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
• Hi,

Try below SQL query. In sql we have quarter property to get the quarter name form date.

`SELECT item, UOM, QTY, DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), bill_date) AS DATETIME)) as [quarter], DATENAME(MONTH, bill_date) from test`

Let me know if this is OK or you are looking for any other query.

Regards,
Asheej T K

• Refer the following script,

SELECT item,
uom,
qty,
CASE
WHEN ( Datename(quarter, Cast(CONVERT(VARCHAR(8), bill_date) AS
DATETIME)) - 1
) = 0 THEN 4
ELSE Datename(quarter, Cast(CONVERT(VARCHAR(8), bill_date) AS DATETIME)
) - 1
END AS [quarter],
Datename(month, bill_date)
FROM test