March 11, 2012 at 9:51 pm
I am getting the below result set from a query.
Itemid RegionId Quantity
4003 5 10
4134 5 20
4003 55 70
4134 55 40
4174 55 90
4175 55 100
4134 56 50
4174 56 60
4175 56 15
RegionId 55 has total 4 items among all the RegionIds in the result set.
RegionId 5 has 2 and RegionId 56 has 3.
I want the final result set in such a way that ItemIds 4134 and 4137 are available for RegionId 55,but are not available for RegionId 5
so I want to add these 2 Items for Regiond 5 with Quantity=0 and like wise ItemId 4003 for Regionid 56.
I need to modify the result set with a query and not the query that bring the result set.
so the final result set would look like:
Itemid RegionId Quantity
4003 5 10
4134 5 20
4174 5 0
4175 5 0
4003 55 70
4134 55 40
4174 55 90
4175 55 100
4003 56 0
4134 56 50
4174 56 60
4175 56 15
Can I do it with a Query or use Cursors or Loops?
Thanks.
March 11, 2012 at 10:23 pm
How about this?
; WITH CTE (ItemID, RegionID, Quantity) AS
(
SELECT 4003, 5 ,10
UNION ALL SELECT 4134, 5 ,20
UNION ALL SELECT 4003, 55, 70
UNION ALL SELECT 4134, 55 ,40
UNION ALL SELECT 4174, 55 ,90
UNION ALL SELECT 4175, 55 ,100
UNION ALL SELECT 4134, 56 ,50
UNION ALL SELECT 4174, 56 ,60
UNION ALL SELECT 4175, 56 ,15
)
, DistinctItemID AS
(
SELECT DISTINCT T1.ItemID
FROM CTE T1
)
, DistinctRegionID AS
(
SELECT DISTINCT T1.RegionID
FROM CTE T1
)
, FullList AS
(
SELECT It.ItemID , Rg.RegionID
FROM DistinctItemID It
CROSS JOIN DistinctRegionID Rg
)
SELECT Base.ItemID , Base.RegionID
, Quantity = ISNULL ( CTE.Quantity , 0)
FROM FullList Base
LEFT JOIN CTE
ON Base.ItemID = CTE.ItemID
AND Base.RegionID = CTE.RegionID ;
If my guess is right, this wont be scaling so well as we are touching the base table thrice to arrive at the results. There may be better options, but this all i can think of right now.. Let me think through and get back to u or let us wait for other good folks to jump on this!
And please look at how i set up the sample data for other to work on; in future follow the same method so that your queries are answered fast!
For the benefit of others who are try to give this a shot:
IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp;
CREATE TABLE #Temp
(
ItemID INT
, RegionID INT
, Quantity INT
)
INSERT INTO #Temp (ItemID, RegionID, Quantity)
SELECT 4003, 5 ,10
UNION ALL SELECT 4134, 5 ,20
UNION ALL SELECT 4003, 55, 70
UNION ALL SELECT 4134, 55 ,40
UNION ALL SELECT 4174, 55 ,90
UNION ALL SELECT 4175, 55 ,100
UNION ALL SELECT 4134, 56 ,50
UNION ALL SELECT 4174, 56 ,60
UNION ALL SELECT 4175, 56 ,15
IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp;
March 12, 2012 at 12:44 am
Normally I'm a big fan of using CTEs to improve readability but in this case, I think I'd just write it like this:
DECLARE @Temp TABLE
(
ItemID INT
, RegionID INT
, Quantity INT
)
INSERT INTO @Temp (ItemID, RegionID, Quantity)
SELECT 4003, 5 ,10
UNION ALL SELECT 4134, 5 ,20
UNION ALL SELECT 4003, 55, 70
UNION ALL SELECT 4134, 55 ,40
UNION ALL SELECT 4174, 55 ,90
UNION ALL SELECT 4175, 55 ,100
UNION ALL SELECT 4134, 56 ,50
UNION ALL SELECT 4174, 56 ,60
UNION ALL SELECT 4175, 56 ,15
SELECT x.ItemID, y.RegionID, ISNULL(t.Quantity,0) As Quantity
FROM(SELECT ItemID FROM @Temp GROUP BY ItemID) x
CROSS JOIN (SELECT RegionID FROM @Temp GROUP BY RegionID) y
LEFT OUTER JOIN @Temp t ON t.ItemID = x.ItemID and t.RegionID = y.RegionID
I think the idea to create a Cartesian product of unique ItemID and RegionID is the right approach, I just did it a little different.
Note that this solution is no more scalable than the first (execution plans looked identical on first glance).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2012 at 1:00 am
There is a way to get a better query plan:
INSERT INTO @Temp (ItemID, RegionID, Quantity)
SELECT 4003, 5 ,10
UNION ALL SELECT 4134, 5 ,20
UNION ALL SELECT 4003, 55, 70
UNION ALL SELECT 4134, 55 ,40
UNION ALL SELECT 4174, 55 ,90
UNION ALL SELECT 4175, 55 ,100
UNION ALL SELECT 4134, 56 ,50
UNION ALL SELECT 4174, 56 ,60
UNION ALL SELECT 4175, 56 ,15
SELECT x.ItemID, y.RegionID, MAX(ISNULL(t.Quantity,0)) As Quantity
FROM(SELECT ItemID FROM @Temp) x
CROSS APPLY (SELECT RegionID FROM @Temp) y
LEFT OUTER JOIN @Temp t ON t.ItemID = x.ItemID and t.RegionID = y.RegionID
GROUP BY x.ItemID, y.RegionID
Still no guarantees that it will perform better in practice. Best to run both with STATISTICS TIME and IO ON for a larger record set to be sure.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2012 at 10:15 am
dwain.c
Thanks for all the help!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply