display last row in subgroup and calcuate dynamic

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

Development Tools

Reporting Services Development

display last row in subgroup and calcuate dynamic

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-19 : 16:15:11

Query need be displaying Product , last SubProduct in the list based on IsPrimary

Display quantity for each Product values multiplied. I need to have an query for below result

CREATE TABLE [dbo].[Product_Table](
[Product Id] [char](10) NULL,
[IsPrimary] [int] NULL,
[Quantity] [numeric](18, 0) NULL
) ON [PRIMARY]

INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001', 1, 0)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.1', 0, 2)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.2', 0, 4)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.3', 0, 5)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002', 1, 0)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.1', 0,6)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.2', 0,7)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.3', 0,9)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.4', 0,10)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.5', 0,11)

Need the result to be as
Product Id | SubProduct |Quantity
P001 | | 40
P001 | P001.3 |0
P002 | | 41580
P002 | P002.5| 0

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 16:35:04

cross post - although this one at least has DDL/DML ;)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190419

EDIT:
I'm not a reporting services guy the two rows per group requirement may be better to implement in the reporting interface. And the PRODUCT function is just not pretty anywhere. CLR would be the cleanest solution but as I said in the other post it won't take many rows to end up with an arithmetic overflow.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources