quarters - 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

quarters

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-25 : 02:05:57

Hi agian. Another probs. I need help again....

DECLARE @quarter varchar(10), @year int
SET @quarter = 'First' --First, Second, Third, Fourth
SET @year = 2007

table1 - hrEmpPaySumm

empno period1 sss_ee sss_er
1 1/1/2007 500 100
1 3/1/2007 600 200
1 3/16/2007 700 300
2 4/1/2007 800 400
3 4/1/2007 900 500

expected result...
if @quarter = 'First' AND @year = 2007 - returns all data of jan,feb,mar of 2007
empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3
1 January 500 100 Feb 0 0 March 1100 500
2 January 0 0 Feb 0 0 March 0 0
3 January 0 0 Feb 0 0 March 0 0

if @quarter = 'Second' AND @year = 2007 - returns all data of apr,may,jun of 2007
empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3
1 April 0 0 May 0 0 June 0 0
2 April 800 400 May 0 0 June 0 0
3 April 900 500 May 0 0 June 0 0

and so on....

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-25 : 02:48:46

Try this...
declare @t table (empno int, period1 datetime, sss_ee int, sss_er int)
insert @t
Select 1, '1/1/2007', 500, 100 union all
Select 1, '3/1/2007', 600, 200 union all
Select 1, '3/16/2007', 700, 300 union all
Select 2, '4/1/2007' ,800, 400 union all
Select 3, '4/1/2007' ,900, 500

DECLARE @quarter varchar(10), @year int
SET @quarter = 'First'--'First' --First, Second, Third, Fourth
SET @year = 2007

Select Empno,
DateName(mm,case @quarter when 'First' then '01/01/1900'
when 'Second' then '04/01/1900'
when 'Third' then '07/01/1900'
when 'Fourth' then '10/01/1900'
End) as Month1,
Sum(Case when month(period1) = (case @quarter when 'First' then 1
when 'Second' then 4
when 'Third' then 7
when 'Fourth' then 10
End) Then sss_ee Else 0 End) as SSS_EE1,
Sum(Case when month(period1) = (case @quarter when 'First' then 1
when 'Second' then 4
when 'Third' then 7
when 'Fourth' then 10
End) Then sss_er Else 0 End) as SSS_ER1,
DateName(mm,case @quarter when 'First' then '02/01/1900'
when 'Second' then '05/01/1900'
when 'Third' then '08/01/1900'
when 'Fourth' then '11/01/1900'
End) as Month2,
Sum(Case when month(period1) = (case @quarter when 'First' then 2
when 'Second' then 5
when 'Third' then 8
when 'Fourth' then 11
End) Then sss_ee Else 0 End) as SSS_EE2,
Sum(Case when month(period1) = (case @quarter when 'First' then 2
when 'Second' then 5
when 'Third' then 8
when 'Fourth' then 11
End) Then sss_er Else 0 End) as SSS_ER2,
DateName(mm,case @quarter when 'First' then '03/01/1900'
when 'Second' then '06/01/1900'
when 'Third' then '09/01/1900'
when 'Fourth' then '12/01/1900'
End) as Month3,
Sum(Case when month(period1) = (case @quarter when 'First' then 3
when 'Second' then 6
when 'Third' then 9
when 'Fourth' then 12
End) Then sss_ee Else 0 End) as SSS_EE3,
Sum(Case when month(period1) = (case @quarter when 'First' then 3
when 'Second' then 6
when 'Third' then 9
when 'Fourth' then 12
End) Then sss_er Else 0 End) as SSS_ER3

from @t
where /*(month(period1) between (Case when @quarter = 'First' then 0 end) and
(Case when @quarter = 'First' then 3 end)
or month(period1) between (Case when @quarter = 'Second' then 4 end) and
(Case when @quarter = 'Second' then 6 end)
or month(period1) between (Case when @quarter = 'Third' then 7 end) and
(Case when @quarter = 'Third' then 9 end)
or month(period1) between (Case when @quarter = 'Fourth' then 10 end) and
(Case when @quarter = 'Fourth' then 12 end)) and */
year(period1) = @year
group by empno

--------------------------------------------------
S.Ahamed

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-25 : 03:02:51

it works well in my sample data. Not yet tried in my actual tables.
pbguy thank you so much :)

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-25 : 03:33:08

DECLARE @hrEmpPaySumm TABLE (empno int, period1 datetime, sss_ee int, sss_er int)

INSERT INTO @hrEmpPaySumm SELECT 1, '2007/01/01', 500, 100
UNION SELECT 1, '2007/03/01', 600, 200
UNION SELECT 1, '2007/03/16', 700, 300
UNION SELECT 2, '2007/04/01', 800, 400
UNION SELECT 3, '2007/04/01', 900, 500

DECLARE @quarter varchar(10), @year int
SET @quarter = 'Second' --First, Second, Third, Fourth
SET @year = 2007

DECLARE @Q int

SELECT @Q = CASE @quarter WHEN 'First' THEN 1 WHEN 'Second' THEN 2 WHEN 'Third' THEN 3 WHEN 'Fourth' THEN 4 END

SELECT DISTINCT A.empno,
month1 = CASE @Q WHEN 1 THEN 'January' WHEN 2 THEN 'April' WHEN 3 THEN 'July' ELSE 'October' END,
sss_ee1 = (SELECT IsNull(SUM(Z.sss_ee), 0) FROM @hrEmpPaySumm Z WHERE Z.empno = A.empno AND YEAR(Z.period1) = @year AND MONTH(Z.period1) = (@Q - 1) * 3 + 1),
sss_er1 = (SELECT IsNull(SUM(Z.sss_er), 0) FROM @hrEmpPaySumm Z WHERE Z.empno = A.empno AND YEAR(Z.period1) = @year AND MONTH(Z.period1) = (@Q - 1) * 3 + 1),
month2 = CASE @Q WHEN 1 THEN 'February' WHEN 2 THEN 'May' WHEN 3 THEN 'August' ELSE 'November' END,
sss_ee2 = (SELECT IsNull(SUM(Z.sss_ee), 0) FROM @hrEmpPaySumm Z WHERE Z.empno = A.empno AND YEAR(Z.period1) = @year AND MONTH(Z.period1) = (@Q - 1) * 3 + 2),
sss_er2 = (SELECT IsNull(SUM(Z.sss_er), 0) FROM @hrEmpPaySumm Z WHERE Z.empno = A.empno AND YEAR(Z.period1) = @year AND MONTH(Z.period1) = (@Q - 1) * 3 + 2),
month3 = CASE @Q WHEN 1 THEN 'March' WHEN 2 THEN 'July' WHEN 3 THEN 'September' ELSE 'December' END,
sss_ee3 = (SELECT IsNull(SUM(Z.sss_ee), 0) FROM @hrEmpPaySumm Z WHERE Z.empno = A.empno AND YEAR(Z.period1) = @year AND MONTH(Z.period1) = (@Q - 1) * 3 + 3),
sss_er3 = (SELECT IsNull(SUM(Z.sss_er), 0) FROM @hrEmpPaySumm Z WHERE Z.empno = A.empno AND YEAR(Z.period1) = @year AND MONTH(Z.period1) = (@Q - 1) * 3 + 3)
FROM @hrEmpPaySumm A
ORDER BY A.empno

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-25 : 21:12:51

thanks koji. works perfectly too.

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources