assistance rqrd to display records..

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

SQL Server 2005 Forums

Transact-SQL (2005)

assistance rqrd to display records..

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-02-20 : 00:04:08

Hi All,

i have a table like this:

Location eqpType agencyvolume socvolume carriageid carriageterm Month activity
-------------------------------------------------------------------------------
INMAD DV20 1 0 NULL NULL 01 load
INMAD DV20 7 0 NULL NULL 01 load
INMAD DV40 1 0 NULL NULL 01 load
INMAD HC40 12 0 NULL NULL 01 load
INMAD OT40 1 0 NULL NULL 01 load
INMAD RH40 1 0 NULL NULL 01 load
INMAD DV20 5 0 NULL NULL 01 disch
INMAD DV20 63 31 30 fifo 01 disch
INMAD DV40 4 0 NULL NULL 01 disch
INMAD HC40 39 4 30 fifo 01 disch
INMAD RH40 3 0 NULL NULL 01 disch

the 'FIFO' record should show (consider) only SOC and dont show the agencyvolume in the result as:
Expexted Result:
Month Location carriageid carrterm load20 load20Soc load40 load40Soc disch20 disch20soc disch40 disch40soc
----------------------------------------------------------------------------------------------------------------------------
1 INMAD NULL NULL 8 0 15 0 68 0 46 0
1 INMAD 30 FIFO 0 0 0 0 0 31 0 4

BUT, am getting the result as below:
Result am getting is:
Month Location carriageid carrterm load20 load20Soc load40 load40Soc disch20 disch20soc disch40 disch40soc
----------------------------------------------------------------------------------------------------------------------------
1 INMAD NULL NULL 8 0 15 0 5 0 7 0
1 INMAD 30 FIFO 0 0 0 0 63 31 39 4

query is:

select Month,location,carriageid,carriageterm,
sum(LOAD_20_Agncy) as load20,
sum(LOAD_20_SOC) as load20SOC,
sum(LOAD_40_Agncy) as load40,
sum(LOAD_40_SOC) as load40SOC,

sum(DISCH_20_Agncy) as disch20,
sum(DISCH_20_SOC) as disch20SOC,
sum(DISCH_40_Agncy) as disch40,
sum(DISCH_40_SOC) as disch40SOC

from volume (nolock)
group by month,location,carriageid,carriageterm order by location

anyone kindly help to solve this.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 02:17:30

[code]
try this
declare @tab table(Location varchar(32),eqpType varchar(32),agencyvolume int,socvolume int,carriageid int,carriageterm varchar(32),Month int,activity varchar(32))
insert into @tab select
'INMAD','DV20', 1, 0, NULL, NULL, 01, 'load' union all select
'INMAD','DV20' ,7, 0, NULL, NULL, 01 ,'load' union all select
'INMAD', 'DV40', 1, 0, NULL, NULL, 01, 'load' union all select
'INMAD', 'HC40', 12, 0, NULL, NULL, 01, 'load' union all select
'INMAD', 'OT40', 1, 0, NULL, NULL, 01, 'load' union all select
'INMAD', 'RH40', 1, 0, NULL, NULL, 01, 'load' union all select
'INMAD', 'DV20', 5, 0, NULL, NULL, 01, 'disch' union all select
'INMAD', 'DV20', 63, 31, 30 ,'fifo', 01,'disch' union all select
'INMAD', 'DV40' ,4, 0 ,NULL, NULL, 01, 'disch' union all select
'INMAD', 'HC40', 39, 4 ,30, 'fifo', 01, 'disch' union all select
'INMAD', 'RH40', 3, 0, NULL, NULL, 01, 'disch'

select distinct month,location, carriageterm,carriageid,
sum(case when activity ='load' and right(eqptype,2)='20' then agencyvolume else 0 end)over(partition by location,carriageterm) as load20,
sum(case when activity ='load' and right(eqptype,2)='20' then socvolume else 0 end)over(partition by location,carriageterm) as load20soc,
sum(case when activity ='load' and right(eqptype,2)='40' then agencyvolume else 0 end)over(partition by location,carriageterm) as load40,
sum(case when activity ='load' and right(eqptype,2)='40' then socvolume else 0 end)over(partition by location,carriageterm) as load40soc,
case when isnull(carriageid,'') = '' then sum(case when activity ='disch' and right(eqptype,2)='20' then agencyvolume else 0 end)over(partition by location)else 0 end as disch20,
sum(case when activity ='disch' and right(eqptype,2)='20' then socvolume else 0 end)over(partition by location,carriageterm) as disch20soc,
case when isnull(carriageid,'') = '' then sum(case when activity ='disch' and right(eqptype,2)='40' then agencyvolume else 0 end)over(partition by location) else 0 end as disch40,
sum(case when activity ='disch' and right(eqptype,2)='40' then socvolume else 0 end)over(partition by location,carriageterm) as disch40soc
from @tab
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources