Another group by question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Another group by question

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-06-08 : 17:05:56

Hi,

I have two tables:

declare	@a table (id char(4), v_on decimal(7,2), v_of decimal(7,2), evt_cde int)
insert	@a
select '4838',  0,     8,   2120001 union all
select '4838',  1,    22,   2122001 union all
select '4838',  6,     3,   2122002 union all
select '4838',  0,     6,   2122003 union all
select '4838', 47,     7,   2122121 union all
select '4838', 22,     0,   2122122 union all
select '4838',  0,     2,   2122125 union all
select '4838', 76,    42,   NULL union all
select '4838', 76,    42,   NULL union all
select '4118',  0,     7.5, 2120001 union all
select '4118',  0,    46,   2122001 union all
select '4118',  0,    54,   2122002 union all
select '4118',  0,    13,   2122003 union all
select '4118', 20,    21,   2122121 union all
select '4118',  8,    28,   2122122 union all
select '4118', 34,    37,   2122123 union all
select '4118',105,    23,   2122125 union all
select '4118',167,   216.5, NULL union all
select '4118',167,   216.5, NULL

declare	@b table (cde int, dsc varchar(40))
insert @b
select 2120001, 'grav'   union all
select 2122001, 'pre-pl' union all
select 2122002, 're pre' union all
select 2122003, 're re'  union all
select 2122121, 'comp'   union all
select 2122122, 'fala'   union all
select 2122123, 'co-re'  union all
select 2122124, 'outr'   union all
select 2122125, 'ojco'

I need to extract records from @a, grouped by id - here I'll have two records ('4838' and '4118').
In addition to id, I'll also need four more fields, which are SUMed v_of and v_on.

To know which v_on / v_of is included in which SUM, I join @b on @a.evt_cde = @b.cde (@b is in effect a lookup table, I only need records that have values in @a).

Now - if (@b.dsc NOT like 'gr%' and @b.dsc NOT like 're re%') - I use v_on and v_of for fields VA and VB.
If (@b.dsc like 're re%') - I use v_on and v_of for fields VC and VD.

So here my intermediate table (both joined) for '4838' would look like this:

id   v_on      v_of      evt_cde     dsc          AB   CD
---- --------- --------- ----------- ----------- ---- ----
4838   .00      8.00     2120001     'grav'           
4838  1.00     22.00     2122001     'pre-pl'     Y    
4838  6.00      3.00     2122002     're pre'     Y    
4838   .00      6.00     2122003     're re'           Y
4838 47.00      7.00     2122121     'comp'       Y    
4838 22.00       .00     2122122     'fala'       Y    
4838   .00      2.00     2122125     'ojco'       Y    
4838 76.00     42.00     NULL        NULL         Y    
4838 76.00     42.00     NULL        NULL         Y

Column AB indicates if v_on and v_off are used for VA / VB, CD if they are used for VC / VD.
VA is sum of v_on where AB=Y
VB is sum of v_of where AB=Y
VC is sum of v_on where CD=Y
VD is sum of v_of where CD=Y

One more thing: table @b is on another server, and I use it via OPENDATASOURCE; it's a very small table (unlike the @a).

How do I write such query? The NULLs in the @a are getting me in the way...

TIA

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-09 : 02:46:05

Hi,

check the follwing
Select a.*, b.dsc,
case when (b.dsc NOT like 'gr%' and b.dsc NOT like 're re%') or b.dsc is null
then 'y' else '' end as AB,
case when b.dsc like 're re%'then 'y' else '' end as CD
from @a a
left join @b b on a.evt_cde = b.cde
where a.id = '4838'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources