How to print a record against a value that doesn't exist?

Question

I am trying an INNER JOIN on two columns of date type.

I need to compare the two columns and find out if one of the column doesn't have a particular date. 

In case, there is a mismatch - I want to print the date that is missing and print 0 in the corresponding row element.

for example:-

original set

2013-07-17 00:00:00.000 76505
2013-07-16 00:00:00.000 38617
2013-07-15 00:00:00.000 37117
2013-07-14 00:00:00.000 309320
2013-07-12 00:00:00.000 387583

intended result set

2013-07-17 00:00:00.000 76505
2013-07-16 00:00:00.000 38617
2013-07-15 00:00:00.000 37117
2013-07-14 00:00:00.000 309320
2013-07-13 00:00:00.000       0             -- insert this record
2013-07-12 00:00:00.000 387583

How best can the following query be modified?

SELECT DISTINCT ex, 

                countnum 
FROM   (SELECT Dateadd(dd, 0, Datediff(dd, 0, extract_date)) AS ex, 
               Count(*)                                      AS countnum 
        FROM   ods..o_cmhistory_idx(nolock) 
        WHERE  extract_date >= Dateadd(dd, -20, Getdate()) 
        GROUP  BY extract_date) AS tr1 
       INNER JOIN (SELECT TOP 20 Dateadd(dd, 0, Datediff(dd, 0, date)) AS 
                                 display_date 
                   FROM   warehouse..dim_date 
                   WHERE  date <= Cast(Getdate() AS DATE) 
                   ORDER  BY date DESC) AS tr2 
               ON tr1.ex = tr2.display_date 
ORDER  BY ex DESC 

Is there a way, the above query can be simplified to get the desired result set?

Answer 1

You need to construct the date set and use the UNION operator.

Date sequence generation:

http://www.sqlusa.com/bestpractices2005/sequence/

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

Have tried modifying the query but I am not able to extract unique values.

(SELECT DATEADD(dd, 0, DATEDIFF(dd, 0,extract_date)) AS ex 

        FROM   ods..o_cmhistory_idx(nolock) 
        WHERE  extract_date >= Dateadd(dd, -8, Getdate()) 
        GROUP  BY extract_date) UNION ALL (select top 7 DATEADD(dd, 0, DATEDIFF(dd, 0,date)) as display_date from warehouse..dim_date
where date <= cast(getdate() as Date))

             

Answer 3

Issue resolved using the following query:

SELECT tr2.display_date, 
       Isnull(tr1.countnum, 0) 
FROM   (SELECT Dateadd(dd, 0, Datediff(dd, 0, extract_date)) AS ex, 
               Count(*)                                      AS countnum 
        FROM   ods..o_cmhistory_idx(nolock) 
        WHERE  extract_date >= Dateadd(dd, -20, Getdate()) 
        GROUP  BY extract_date) AS tr1 
       RIGHT OUTER JOIN (SELECT TOP 20 Dateadd(dd, 0, Datediff(dd, 0, date)) AS 
                                       display_date 
                         FROM   warehouse..dim_date 
                         WHERE  date <= Cast(Getdate() AS DATE) 
                         ORDER  BY date DESC) AS tr2 
                     ON tr1.ex = tr2.display_date 
ORDER  BY tr2.display_date DESC 

Answer 4

Why can't we use FULL Join between two tables. Refer below example,

------------------------
declare @original table(odate datetime,id int)
insert into @original values('2013-07-17 00:00:00.000', 76505)
insert into @original values('2013-07-16 00:00:00.000', 38617)
insert into @original values('2013-07-15 00:00:00.000', 37117)
insert into @original values('2013-07-14 00:00:00.000',309320)
insert into @original values('2013-07-12 00:00:00.000', 387583 )
----------------------
declare @intended table(idate datetime,id int)
insert into @intended values('2013-07-17 00:00:00.000', 76505)
insert into @intended values('2013-07-16 00:00:00.000', 38617)
insert into @intended values('2013-07-15 00:00:00.000' ,37117)
insert into @intended values('2013-07-14 00:00:00.000', 309320)
insert into @intended values('2013-07-13 00:00:00.000' ,22    )       -- insert this record
insert into @intended values('2013-07-12 00:00:00.000',387583)
------------------------------
SELECT ODATE,OID,IDATE,IID, CASE
WHEN DDIFF = 0 THEN 'MATCHED'
ELSE 'MISMATCHED' END AS FLAG 
FROM (
	SELECT A.ODATE,A.ID AS OID,B.IDATE,B.ID AS IID,DATEDIFF(D,A.ODATE,B.IDATE) AS DDIFF FROM @original A 
	FULL JOIN @intended B 
	ON A.ID=B.ID
) X

---

Regards, RSingh

Answer 5

In this type of requirements where an auxiliary Number table is most needed...

If you have a Number table then your requirement can be done using a simple LEFT OUTER JOIN like below:

DECLARE @Foo TABLE
(
ADate DATETIME,
SomeValue INT
)

DECLARE @Number TABLE
(
Num INT
);

DECLARE 
	@MinADate DATETIME,
	@MaxADate DATETIME,
	@TotalDays INT = 0

INSERT INTO @Number
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM sys.columns

INSERT INTO @Foo VALUES
	('2013-07-17 00:00:00.000', 76505),
	('2013-07-16 00:00:00.000', 38617),
	('2013-07-15 00:00:00.000', 37117),
	('2013-07-14 00:00:00.000', 309320),
	('2013-07-12 00:00:00.000', 387583)

SELECT @MinADate = MIN(ADate), @MaxADate = MAX(ADate) FROM @Foo;
SET @TotalDays = DATEDIFF(DAY, @MinADate, @MaxADate) + 1;

WITH CTE_Days
AS
(
SELECT DATEADD(DAY, Num - 1, @MinADate) AS ADate 
FROM @Number WHERE Num <= @TotalDays
)
SELECT D.ADate, ISNULL(F.SomeValue, 0) AS SomeValue
FROM CTE_Days D
LEFT OUTER JOIN
	@Foo F
ON
	F.ADate = D.ADate
ORDER BY
	D.ADate DESC;

/* Result
ADate|SomeValue
2013-07-17 00:00:00.000|76505
2013-07-16 00:00:00.000|38617
2013-07-15 00:00:00.000|37117
2013-07-14 00:00:00.000|309320
2013-07-13 00:00:00.000|0
2013-07-12 00:00:00.000|387583
*
/

---

Krishnakumar S