need help with subtotal grouping query

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)

need help with subtotal grouping query

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-05-04 : 07:46:55

[code]parent Child grp output_needed
part1 Comp01 a 10 - output increments by 10 and does not increment where grp is the same.
part1 Comp02 a 10
part1 Comp03 NULL 20
part1 Comp04 NULL 30
part1 Comp05 b 40
part1 Comp06 b 40
part1 Comp07 NULL 50
part1 Comp08 NULL 60
part2 Comp09 NULL 10 - output resets when part2 changes.
part2 Comp10 c 20
part2 Comp11 c 20
part2 Comp12 c 20
part2 Comp13 NULL 30
part2 Comp14 NULL 40
part2 Comp15 d 50
part2 Comp16 d 50
part2 Comp17 NULL 60
part2 Comp18 NULL 70[/code]

i would like the output to be genereated by sql. thank you.

DDL is
select 'part1' as parent,
'Comp01' as child, 'a' as grp, 10 as output
union
select 'part1', 'Comp02', 'a', 10
union
select 'part1', 'Comp03', NULL, 20
union
select 'part1', 'Comp04', NULL, 30
union
select 'part1', 'Comp05', 'b', 40
union
select 'part1', 'Comp06', 'b', 40
union
select 'part1', 'Comp07', NULL, 50
union
select 'part1', 'Comp08', NULL, 60
union
select 'part2', 'Comp09', NULL, 10
union
select 'part2', 'Comp10', 'c', 20
union
select 'part2', 'Comp11', 'c', 20
union
select 'part2', 'Comp12', 'c', 20
union
select 'part2', 'Comp13', NULL, 30
union
select 'part2', 'Comp14', NULL, 40
union
select 'part2', 'Comp15', 'd', 50
union
select 'part2', 'Comp16', 'd', 50
union
select 'part2', 'Comp17', NULL, 60
union
select 'part2', 'Comp18', NULL, 70

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:38:26

whats the sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-05-04 : 13:39:51

hi visak16

its a BOM ( bill of material)
for one parent with many children , if the children are alternates ( denoted by the grp being the same) then the bom line remanins the same else the BOM line will be incremented .... also the BOM lines reset when the parent changes.

i know how to do a simple increment or subtotal but i donot know how to make it the same for the alternates.

thanks.

quote:
Originally posted by visakh16

whats the sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-05 : 03:12:16

Hi visakh,

I think what is the sample data OP has given.
the output column he wants to generate by SQL Query
that is not the original one.

quote:
Originally posted by visakh16

whats the sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-05-05 : 03:37:49

wahh this thread hard

Hope can help...but advise to wait pros with confirmation...

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-07-27 : 15:39:52

anyone figured this out - i really need a bit of help here. thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources