CODEWITHSUNDEEP
sqlsql-serverjoin
Mansfield
Mansfield

Reputation: 15140

Joining on row if not exists

I have a table containing data by year and week. I'd like to select everything from that table that is in a subset of years and months, and rows with default values wherever that week/year combo isn't found.

Here's a test script:

--create test table and insert data
CREATE TABLE [dbo].[TestPD](
    [id] [int] NOT NULL,
    [year] [int] NOT NULL,
    [week] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [item] [int] NOT NULL,
) ON [PRIMARY]

GO
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (1, 2013, 15, N'bob', 5)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (1, 2014, 15, N'bob', 4)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (2, 2013, 15, N'joe', 3)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (3, 2014, 15, N'rick', 2)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (1, 2012, 15, N'rick', 1)
GO

--LookupDates contains the year/week combos to extract from the table
WITH LookupDates AS (
    SELECT DATEPART(YEAR, GETDATE()) AS [LookupYear], DATEPART(WEEK, GETDATE()) as [LookupWeek]
    UNION 
    SELECT DATEPART(YEAR, GETDATE()) - 1, DATEPART(WEEK, GETDATE())
)
--what I tried (doesn't return "blank" rows)
SELECT t1.[LookupYear], t1.[LookupWeek], ISNULL(id, 0), ISNULL(Name, 'not found'), ISNULL(item, 0)
FROM LookupDates as t1 LEFT JOIN TESTPD as t2 on t1.LookupYear = t2.[year] and t1.LookupWeek = t2.[Week]

What I'd like is data that looks like this (a row for every id/year/month) combo regardless of whether or not it exists in the TestPD table: (rows with 0 values for item do not actually exist, they've just been added in with the default value).

id          year        week        name      item
----------- ----------- ----------- --------- -----------
1           2013        1           bob       5
1           2014        1           bob       4
2           2013        1           joe       3
2           2014        1           joe       0
3           2013        1           rick      0
3           2014        1           rick      2

How can I achieve this? I don't have to use a CTE, I just decided it was the best way to hold the years/weeks I need data for.

Upvotes: 1

Views: 69

Answers (2)

JBrooks
JBrooks

Reputation: 10013

WITH LookupDates AS (
    SELECT DATEPART(YEAR, GETDATE()) AS [LookupYear], DATEPART(WEEK, GETDATE()) as [LookupWeek]
    UNION 
    SELECT DATEPART(YEAR, GETDATE()) - 1, DATEPART(WEEK, GETDATE())
)

--this will return "blank" rows
SELECT t1.[LookupYear], t1.[LookupWeek], id, Name, item
FROM LookupDates as t1 
JOIN TESTPD as t2 on t1.LookupYear = t2.[year] and t1.LookupWeek = t2.[Week]

UNION ALL

SELECT t1.[LookupYear], t1.[LookupWeek], id, 'Not Found', item
FROM LookupDates AS t1 
CROSS JOIN TESTPD AS t2
WHERE NOT EXISTS (SELECT 1
FROM TESTPD t3
WHERE t3.ID = T2.ID
AND t3.item = T2.item
AND t3.[year] = t1.[LookupYear] 
and t3.[Week] = t1.[LookupWeek])
ORDER BY 1, 2, 3, 4

Upvotes: 0

Karl Kieninger
Karl Kieninger

Reputation: 9129

Edited following comments.

You need to cross join with the ids to get what you want. Here's one way to do, but that's they key.

--create test table and insert data
CREATE TABLE [dbo].[TestPD](
    [id] [int] NOT NULL,
    [year] [int] NOT NULL,
    [week] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [item] [int] NOT NULL,
) ON [PRIMARY]

GO
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (1, 2013, 15, N'bob', 5)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (1, 2014, 15, N'bob', 4)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (2, 2013, 15, N'joe', 3)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (3, 2014, 15, N'rick', 2)
INSERT [dbo].[TestPD] ([id], [year], [week], [name], [item]) VALUES (1, 2012, 15, N'rick', 1)
GO

--LookupDates contains the year/week combos to extract from the table
;WITH LookupDates AS (
    SELECT DATEPART(YEAR, GETDATE()) AS [LookupYear], DATEPART(WEEK, GETDATE()) as [LookupWeek]
    UNION 
    SELECT DATEPART(YEAR, GETDATE()) - 1, DATEPART(WEEK, GETDATE())
)
,UserDates AS (
    SELECT  *
      FROM LookupDates
           CROSS JOIN
           (SELECT id FROM TESTPD GROUP BY id) a
)
SELECT t1.LookupYear
     , t1.LookupWeek
     , ISNULL(t2.id, 0) id
     , ISNULL(Name, 'not found') Name
     , ISNULL(item, 0) Item
FROM UserDates as t1 
     LEFT JOIN 
     TESTPD as t2 on t1.LookupYear = t2.[year] 
                 and t1.LookupWeek = t2.[Week]
                 and t1.id = t2.id



LookupYear  LookupWeek  id          Name                                               Item
----------- ----------- ----------- -------------------------------------------------- -----------
2013        15          1           bob                                                5
2013        15          2           joe                                                3
2013        15          0           not found                                          0
2014        15          1           bob                                                4
2014        15          0           not found                                          0
2014        15          3           rick                                               2

Upvotes: 1

Related Questions