Need Query for summarizing data
-
Hi there,
I need query for the attached output
create table #Wholeseller ( wsid varchar(100),[Productid From] int,[Productid To] int,units int)
create table #Retailer ( retid varchar(100), [Productid From] int,[Productid To] int,units int)
create table #subretailer ( subretid varchar(100), [Productid From] int,[Productid To] int,units int)
insert into #Wholeseller values ('XXX','1','100','100')
insert into #Wholeseller values ('YYY','101','200','100')
insert into #Retailer values ('Ret1','1','50','50')
insert into #Retailer values ('Ret2','51','100','50')
insert into #Retailer values ('Ret1','101','175','75')
insert into #subretailer values ('Subret1', '1','10','10')
insert into #subretailer values ('Subret2', '11','40','30')
insert into #subretailer values ('Subret3', '41','50','10')
insert into #subretailer values ('Subret4', '101','140','40')
note : product id is unique
Thanks in advance
-
Anybody help ??
-
I have had a quick look at this and need to ask:
Your desired output
YYY Ret1 35
YYY 25
Where and how in your inputs available do these values come from?
-
higgim (4/9/2014)
I have had a quick look at this and need to ask:Your desired output
YYY Ret1 35
YYY 25
Where and how in your inputs available do these values come from?
-
By no means elegant and I have not tested this with any other data than what you have provided but it does give you the expected results based on what you have provided.
Would be interested to see if there is a better solution out there for this
DECLARE @Wholeseller TABLE
(
wsid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @Retailer TABLE
(
retid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @subretailer TABLE
(
subretid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
INSERT INTO @Wholeseller
VALUES ( 'XXX', '1', '100', '100' )
INSERT INTO @Wholeseller
VALUES ( 'YYY', '101', '200', '100' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '1', '50', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret2', '51', '100', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '101', '175', '75' )
INSERT INTO @subretailer
VALUES ( 'Subret1', '1', '10', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret2', '11', '40', '30' )
INSERT INTO @subretailer
VALUES ( 'Subret3', '41', '50', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret4', '101', '140', '40' )
SELECT w.wsid AS WholeSeller ,
NULL AS Retailer ,
NULL AS SubRetailer ,
w.units - SUM(ISNULL(r.units, 0)) AS WhatsLeft
FROM @Wholeseller w
LEFT OUTER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
GROUP BY w.wsid ,
w.units
HAVING w.units - SUM(ISNULL(r.units, 0)) > 0
UNION ALL
SELECT w.wsid AS WholeSeller ,
r.retid AS Retailer ,
NULL AS SubRetailer ,
r.units - SUM(ISNULL(sr.units, 0)) AS WhatsLeft
FROM @Wholeseller w
INNER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
LEFT OUTER JOIN @subretailer sr ON sr.[Productid From] >= r.[Productid From]
AND sr.[Productid To] <= r.[Productid To]
GROUP BY w.wsid ,
r.retid ,
r.units
HAVING r.units - SUM(ISNULL(sr.units, 0)) > 0
UNION ALL
SELECT w.wsid AS WholeSeller ,
r.retid AS Retailer ,
sr.subretid AS SubRetailer ,
sr.units AS WhatsLeft
FROM @Wholeseller w
INNER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
INNER JOIN @subretailer sr ON sr.[Productid From] >= r.[Productid From]
AND sr.[Productid To] <= r.[Productid To]
ORDER BY 1 ,
2 ,
3
-
higgim (4/9/2014)
By no means elegant and I have not tested this with any other data than what you have provided but it does give you the expected results based on what you have provided.Would be interested to see if there is a better solution out there for this
DECLARE @Wholeseller TABLE
(
wsid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @Retailer TABLE
(
retid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @subretailer TABLE
(
subretid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
INSERT INTO @Wholeseller
VALUES ( 'XXX', '1', '100', '100' )
INSERT INTO @Wholeseller
VALUES ( 'YYY', '101', '200', '100' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '1', '50', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret2', '51', '100', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '101', '175', '75' )
INSERT INTO @subretailer
VALUES ( 'Subret1', '1', '10', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret2', '11', '40', '30' )
INSERT INTO @subretailer
VALUES ( 'Subret3', '41', '50', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret4', '101', '140', '40' )
SELECT w.wsid AS WholeSeller ,
NULL AS Retailer ,
NULL AS SubRetailer ,
w.units - SUM(ISNULL(r.units, 0)) AS WhatsLeft
FROM @Wholeseller w
LEFT OUTER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
GROUP BY w.wsid ,
w.units
HAVING w.units - SUM(ISNULL(r.units, 0)) > 0
UNION ALL
SELECT w.wsid AS WholeSeller ,
r.retid AS Retailer ,
NULL AS SubRetailer ,
r.units - SUM(ISNULL(sr.units, 0)) AS WhatsLeft
FROM @Wholeseller w
INNER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
LEFT OUTER JOIN @subretailer sr ON sr.[Productid From] >= r.[Productid From]
AND sr.[Productid To] <= r.[Productid To]
GROUP BY w.wsid ,
r.retid ,
r.units
HAVING r.units - SUM(ISNULL(sr.units, 0)) > 0
UNION ALL
SELECT w.wsid AS WholeSeller ,
r.retid AS Retailer ,
sr.subretid AS SubRetailer ,
sr.units AS WhatsLeft
FROM @Wholeseller w
INNER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
INNER JOIN @subretailer sr ON sr.[Productid From] >= r.[Productid From]
AND sr.[Productid To] <= r.[Productid To]
ORDER BY 1 ,
2 ,
3
thanks friend... but it took long time to complete ...
any optimized way ???
-
You could try adding some primary keys etc onto the tables. An actual execution plan would be a good start
-
higgim (4/9/2014)
By no means elegant and I have not tested this with any other data than what you have provided but it does give you the expected results based on what you have provided.Would be interested to see if there is a better solution out there for this
DECLARE @Wholeseller TABLE
(
wsid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @Retailer TABLE
(
retid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @subretailer TABLE
(
subretid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
INSERT INTO @Wholeseller
VALUES ( 'XXX', '1', '100', '100' )
INSERT INTO @Wholeseller
VALUES ( 'YYY', '101', '200', '100' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '1', '50', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret2', '51', '100', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '101', '175', '75' )
INSERT INTO @subretailer
VALUES ( 'Subret1', '1', '10', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret2', '11', '40', '30' )
INSERT INTO @subretailer
VALUES ( 'Subret3', '41', '50', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret4', '101', '140', '40' )
SELECT w.wsid AS WholeSeller ,
NULL AS Retailer ,
NULL AS SubRetailer ,
w.units - SUM(ISNULL(r.units, 0)) AS WhatsLeft
FROM @Wholeseller w
LEFT OUTER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
GROUP BY w.wsid ,
w.units
HAVING w.units - SUM(ISNULL(r.units, 0)) > 0
UNION ALL
SELECT w.wsid AS WholeSeller ,
r.retid AS Retailer ,
NULL AS SubRetailer ,
r.units - SUM(ISNULL(sr.units, 0)) AS WhatsLeft
FROM @Wholeseller w
INNER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
LEFT OUTER JOIN @subretailer sr ON sr.[Productid From] >= r.[Productid From]
AND sr.[Productid To] <= r.[Productid To]
GROUP BY w.wsid ,
r.retid ,
r.units
HAVING r.units - SUM(ISNULL(sr.units, 0)) > 0
UNION ALL
SELECT w.wsid AS WholeSeller ,
r.retid AS Retailer ,
sr.subretid AS SubRetailer ,
sr.units AS WhatsLeft
FROM @Wholeseller w
INNER JOIN @Retailer r ON r.[Productid From] >= w.[Productid From]
AND r.[Productid To] <= w.[Productid To]
INNER JOIN @subretailer sr ON sr.[Productid From] >= r.[Productid From]
AND sr.[Productid To] <= r.[Productid To]
ORDER BY 1 ,
2 ,
3
There are certainly a couple of issues with this. First you should NEVER order by ordinal position. You should use column names.
The much bigger issue is this is full of triangular joins which are very bad for performance. Take a look at this article. http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]
I am kind of swamped at the moment but will try to put together an example of how we can do this without the triangular joins.
_______________________________________________________________
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/ -
Sean - Would be interested in seeing a better approach to this purely from a learning point of view.
-
More for Sean this one. Since I saw your comment it's been bugging me all afternoon (hate writing iffy code but still learning) so had another attempt which may not be much better and uses recursive CTE's to break down the product ID's for each of the 3 tables.
DECLARE @Wholeseller TABLE(
wsid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @Retailer TABLE
(
retid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
DECLARE @subretailer TABLE
(
subretid VARCHAR(100) ,
[Productid From] INT ,
[Productid To] INT ,
units INT
)
INSERT INTO @Wholeseller
VALUES ( 'XXX', '1', '100', '100' )
INSERT INTO @Wholeseller
VALUES ( 'YYY', '101', '200', '100' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '1', '50', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret2', '51', '100', '50' )
INSERT INTO @Retailer
VALUES ( 'Ret1', '101', '175', '75' )
INSERT INTO @subretailer
VALUES ( 'Subret1', '1', '10', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret2', '11', '40', '30' )
INSERT INTO @subretailer
VALUES ( 'Subret3', '41', '50', '10' )
INSERT INTO @subretailer
VALUES ( 'Subret4', '101', '140', '40' );
WITH w AS ( SELECT wsid ,
[Productid From] AS ProductID ,
[Productid To]
FROM @Wholeseller
UNION ALL
SELECT wsid ,
ProductID + 1 ,
[Productid To]
FROM w
WHERE ProductID < [Productid To]
),
r AS ( SELECT retid ,
[Productid From] AS ProductID ,
[Productid To]
FROM @Retailer
UNION ALL
SELECT retid ,
ProductID + 1 ,
[Productid To]
FROM r
WHERE ProductID < [Productid To]
),
s AS ( SELECT subretid ,
[Productid From] AS ProductID ,
[Productid To]
FROM @subretailer
UNION ALL
SELECT subretid ,
ProductID + 1 ,
[Productid To]
FROM s
WHERE ProductID < [Productid To]
)
SELECT w.wsid ,
r.retid ,
s.subretid ,
COUNT(w.ProductID) AS Units
FROM w
LEFT OUTER JOIN r ON w.ProductID = r.ProductID
LEFT OUTER JOIN s ON r.ProductID = s.ProductID
GROUP BY w.wsid ,
r.retid ,
s.subretid
ORDER BY w.wsid ,
r.retid ,
s.subretid
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply