SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 display last row in subgroup and calcuate dynamic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/19/2013 :  16:15:11  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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

Edited by - shanmugaraj on 12/19/2013 16:16:34

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/19/2013 :  16:35:04  Show Profile  Reply with Quote
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

Edited by - TG on 12/19/2013 16:37:58
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000