December 19, 2013 at 12:40 pm
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
Kindly let me know how to display last item only in group and multiply all values and display in main group
December 19, 2013 at 1:13 pm
Please don't cross post. Direct all replies here. http://qa.sqlservercentral.com/Forums/Topic1524759-391-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2013 at 9:19 am
Something like this?
Select A.[Product Id],
(
select exp(sum(log(quantity))) from product_table B
where isprimary = 0
and CHARINDEX(Ltrim(Rtrim(A.[Product Id]))+'.',B.[Product Id]) > 0
) as Value
from product_table A
where
A.isPrimary = 1
Union
select A.[Product Id],0
from product_table A
where A.isPrimary = 0
and not Exists
(Select *
from product_table B
Where
substring(Ltrim(Rtrim(B.[Product Id])), CHARINDEX('.',Ltrim(Rtrim(B.[Product Id]))) + 1, LEN(Ltrim(Rtrim(B.[Product Id])))) >
substring(Ltrim(Rtrim(A.[Product Id])), CHARINDEX('.',Ltrim(Rtrim(A.[Product Id]))) + 1, LEN(Ltrim(Rtrim(A.[Product Id]))))
and B.isPrimary = 0
and substring(Ltrim(Rtrim(A.[Product Id])), 1, CHARINDEX('.',Ltrim(Rtrim(A.[Product Id]))) - 1) =
substring(Ltrim(Rtrim(B.[Product Id])), 1, CHARINDEX('.',Ltrim(Rtrim(B.[Product Id]))) - 1)
)
Let me Know if it works
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply