April 4, 2005 at 4:03 pm
I'm trying to outer join two tables, but there are two values from the right side that I have to have, and I can't figure out an easy to do this.
DECLARE @aging table (dt smalldatetime, cd int, bkt tinyint, cnt int, amt money)
INSERT INTO @aging VALUES ('4/4/2005', 1, 1, 1, 10)
INSERT INTO @aging VALUES ('4/4/2005', 1, 3, 3, 30)
INSERT INTO @aging VALUES ('4/4/2005', 2, 2, 2, 20)
INSERT INTO @aging VALUES ('4/5/2005', 2, 3, 3, 30)
DECLARE @bkt table (bkt tinyint)
INSERT INTO @bkt VALUES (1)
INSERT INTO @bkt VALUES (2)
INSERT INTO @bkt VALUES (3)
INSERT INTO @bkt VALUES (4)
I want every bucket for every code for a given date. So, given the above data, I want this result set for 4/4/2005:
4/4/2005,1,1,1,10
4/4/2005,1,2,0,0
4/4/2005,1,3,3,30
4/4/2005,1,4,0,0
4/4/2005,2,1,0,0
4/4/2005,2,2,2,20
4/4/2005,2,3,0,0
4/4/2005,2,4,0,0
My ugly solution involves creating another temp table.
DECLARE @cd_bkt table (dt smalldatetime, cd int, bkt tinyint)
INSERT INTO @cd_bkt
SELECT c.dt, c.cd, b.bkt
FROM (SELECT DISTINCT dt, cd FROM @aging WHERE dt = '4/4/2005') c
CROSS JOIN @bkt b
SELECT a.dt, a.cd, a.bkt, ISNULL(SUM(d.cnt),0), ISNULL(SUM(d.amt),0)
FROM @aging d RIGHT JOIN @cd_bkt a ON d.dt = a.dt and d.cd = a.cd AND d.bkt = a.bkt
WHERE a.dt = '4/4/2005'
GROUP BY a.dt, a.cd, a.bkt
ORDER BY 2, 3
I can't just outer join on bucket, because if the bucket doesn't exist for a given code for the queried date, I get a NULL in the code. But the above seems terribly inefficient (the aging table has 100K rows in it, of which 5-10K are for a given date, and the @cd_bkt table will have 12K+ rows in for a given date). Is there an easier way I'm missing?
Thanks!
Vince
April 4, 2005 at 6:21 pm
How about left-joining @aging to a derived table that contains all the required cd/bkt combinations:
Select a.dt, dt.cd, dt.bkt, IsNull(a.cnt, 0) as cnt, IsNull(a.amt, 0) As amt
From
(
Select Distinct a.cd, b.bkt
From @aging As a
Cross Join @bkt As b
Where a.dt = '4/4/2005'
) dt
Left Join @aging As a
On (a.cd = dt.cd And
a.bkt = dt.bkt And
a.dt = '4/4/2005')
Order By 2, 3
April 5, 2005 at 7:07 am
Try this
SELECT
A.dt,
A.cd,
B.bkt,
Max(case when B.bkt = A.bkt then A.cnt else 0 end) cnt,
Max(case when B.bkt = A.bkt then A.amt else 0 end) amt
FROM
@aging A
CROSS JOIN
@bkt B
WHERE
A.dt = '20050404'
GROUP BY
A.dt,
A.cd,
B.bkt
ORDER BY
A.dt,
A.cd,
B.bkt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply