group by months - 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

group by months

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-24 : 23:40:41

Hi all.
I have probs on grouping by...

Sample data...
table - hrEmpPayrollSummary

empno period1 period2 pagibig_share
1 1/1/2007 1/15/2007 2000
1 1/16/2007 1/30/2007 5000
1 2/1/2007 2/16/2007 6000
2 1/1/2007 1/15/2007 8000
3 1/16/2007 1/28/2007 9000
3 ~~12/1/2007 1/15/2007 10000~~
3 12/1/2007 12/15/2007 10000

expected result...
result for the year 2007

empno january february march april may june july august september october november december

1 7000 6000 0 0 0 0 0 0 0 0 0 0
2 8000 0 0 0 0 0 0 0 0 0 0 0
3 9000 0 0 0 0 0 0 0 0 0 0 10000

-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."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-24 : 23:44:29

what happen when you have cross over month ?
like

4 1/1/2007 3/15/2007 10000

does the 10000 appear under Jan, Feb & March ?

KH
[spoiler]Time is always against us[/spoiler]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-24 : 23:45:57

that will not happen. my front-end controls the passing of dates programatically.

-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."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-24 : 23:55:55

quote:
Originally posted by cwtriguns2002

that will not happen. my front-end controls the passing of dates programatically.

-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."

How about this ?
3 12/1/2007 1/15/2007 10000

KH
[spoiler]Time is always against us[/spoiler]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-24 : 23:58:40

still will not happen.

-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."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 00:05:22

can you explain what is period1 and period2 for ?
For this line of sample data you posted, period1 is greater than period2.

3 12/1/2007 1/15/2007 10000

The summary by month is it going to take period2 into consideration ?

KH
[spoiler]Time is always against us[/spoiler]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-25 : 00:16:20

oppps very sorry. it should be 3 12/1/2007 12/15/2007 10000

period1 and period2 is a payroll period. They must fall in the same month/year as my default.

-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."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 00:19:15

[code]SELECT p.empno,
jan = SUM(CASE WHEN MONTH(period1) = 1 THEN pagibig_share ELSE 0 END),
feb = SUM(CASE WHEN MONTH(period1) = 2 THEN pagibig_share ELSE 0 END),
mar = SUM(CASE WHEN MONTH(period1) = 3 THEN pagibig_share ELSE 0 END),
. . .
FROM hrEmpPayrollSummary p
GROUP BY p.empno
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-25 : 00:20:00

I donno what is the difference b/w period 1 and period 2
i considered only period 1

declare @t table (empno int, period1 datetime, period2 datetime, pagibig_share int)
Insert @t
Select 1, '1/1/2007', '1/15/2007', 2000 union all
Select 1, '1/16/2007', '1/30/2007', 5000 union all
Select 1, '2/1/2007', '2/16/2007', 6000 union all
Select 2, '1/1/2007', '1/15/2007', 8000 union all
Select 3, '1/16/2007', '1/28/2007', 9000 union all
Select 3, '12/1/2007', '1/15/2007', 10000

Select Empno,
Isnull(Sum(Case when month(period1) = 1 Then pagibig_share End),0) as 'January',
Isnull(Sum(Case when month(period1) = 2 Then pagibig_share End),0) as 'February',
Isnull(Sum(Case when month(period1) = 3 Then pagibig_share End),0) as 'March',
Isnull(Sum(Case when month(period1) = 4 Then pagibig_share End),0) as 'April',
Isnull(Sum(Case when month(period1) = 5 Then pagibig_share End),0) as 'May',
Isnull(Sum(Case when month(period1) = 6 Then pagibig_share End),0) as 'June',
Isnull(Sum(Case when month(period1) = 7 Then pagibig_share End),0) as 'July',
Isnull(Sum(Case when month(period1) = 8 Then pagibig_share End),0) as 'August',
Isnull(Sum(Case when month(period1) = 9 Then pagibig_share End),0) as 'September',
Isnull(Sum(Case when month(period1) = 10 Then pagibig_share End),0) as 'October',
Isnull(Sum(Case when month(period1) = 11 Then pagibig_share End),0) as 'November',
Isnull(Sum(Case when month(period1) = 12 Then pagibig_share End),0) as 'December'
From @t
Group by empno

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

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-25 : 01:42:41

thanks sirs

-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."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 03:19:25

Also if you use reports to show data, you can make use of its Cross tab feature

Madhivanan

Failing to plan is Planning to fail

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-25 : 03:25:15

yes i am showing the data in report... whats this cross tabs?

-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."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 04:44:09

Which report are you using?

Madhivanan

Failing to plan is Planning to fail

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-25 : 21:11:36

reporting services...

-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