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
Go to Top of Page
   

- Advertisement -