How to combine record values? - 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

How to combine record values?

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2008-09-18 : 18:00:55

Here is my code:

select ActBillingPeriod, ActEmpCCGrp2, ActivityCode, Sum(ActTimevalue)
as TotalHours
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and actbillingperiod >= '2007/01/01' and
actbillingperiod <= '2007/03/01'
group by ActBillingPeriod, ActEmpCCGrp2, ActivityCode
order by ActBillingPeriod, ActEmpCCGrp2, ActivityCode

The Code above produces this result:

2007-01-12 00:00:00.000 Administration Division Comp. 4.0000
2007-01-12 00:00:00.000 Administration Division Regular 883.5000
2007-01-12 00:00:00.000 Advisory Services Comp. 29.0000
2007-01-12 00:00:00.000 Advisory Services Regular 946.5000
2007-01-12 00:00:00.000 Compliance Audit Comp. 13.5000
2007-01-12 00:00:00.000 Compliance Audit Regular 1363.0000

I don't want a break-out between Comp and Regular in the report above. Notice that the first 2 lines should have been combined, since the first 3 data items are common. Same is true for second and third lines, and the fourth and fifth lines. How can I put the Comp and Regular values on the same line? Notice that it is the same field, but holds a different value in each record. But I do not want a new record to print, I want both values on one line. What must I do? Please help!

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2008-09-18 : 18:09:01

Let me clarify a bit better. What I am trying to get is the Comp value and the Regulat value on the same line, not combine the values into one value. Any ideas?

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-18 : 18:19:44

Can you post your expected result that how you want?

hey

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-09-18 : 18:31:51

How are the rows related? Will you always have a Comp & Regular for each period & group?

As far as I can see you need something like

select x.*,y.SumComp, z.SumReg from
(
select distinct ActBillingPeriod, ActEmpCCGrp2 from ACTIVITIES
) x
outer join
( ActBillingPeriod, ActEmpCCGrp2, Sum(ActTimevalue) sumComp from dbo.ACTIVITIES group by ActBillingPeriod, ActEmpCCGrp2
where ActivityCode='Comp'
) y on x.ActBillingPeriod=y.ActBillingPeriod and x.ActEmpCCGrp2=y.ActEmpCCGrp2
outer join
( ActBillingPeriod, ActEmpCCGrp2, Sum(ActTimevalue) sumReg from dbo.ACTIVITIES group by ActBillingPeriod, ActEmpCCGrp2
where ActivityCode='Comp'
) z on x.ActBillingPeriod=z.ActBillingPeriod and x.ActEmpCCGrp2=z.ActEmpCCGrp2

But without sample data & stuff I'm not going to put that much effort into it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 00:18:36

quote:
Originally posted by pgmr1998

Here is my code:

select ActBillingPeriod, ActEmpCCGrp2, ~~ActivityCode,~~ Sum(ActTimevalue)
as TotalHours
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and actbillingperiod >= '2007/01/01' and
actbillingperiod <= '2007/03/01'
group by ActBillingPeriod, ActEmpCCGrp2~~, ActivityCode~~
order by ActBillingPeriod, ActEmpCCGrp2~~, ActivityCode~~
The Code above produces this result:

2007-01-12 00:00:00.000 Administration Division Comp. 4.0000
2007-01-12 00:00:00.000 Administration Division Regular 883.5000
2007-01-12 00:00:00.000 Advisory Services Comp. 29.0000
2007-01-12 00:00:00.000 Advisory Services Regular 946.5000
2007-01-12 00:00:00.000 Compliance Audit Comp. 13.5000
2007-01-12 00:00:00.000 Compliance Audit Regular 1363.0000

I don't want a break-out between Comp and Regular in the report above. Notice that the first 2 lines should have been combined, since the first 3 data items are common. Same is true for second and third lines, and the fourth and fifth lines. How can I put the Comp and Regular values on the same line? Notice that it is the same field, but holds a different value in each record. But I do not want a new record to print, I want both values on one line. What must I do? Please help!

seeme like what you want is to modify code like above

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources