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

-
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 387583intended 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 DESCIs there a way, the above query can be simplified to get the desired result set?
- Edited by Titash RC Thursday, July 25, 2013 9:22 PM
Question
Answers
-
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- Marked as answer by Allen Li - MSFTModerator Monday, July 29, 2013 6:56 AM
All replies
-
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- Proposed as answer by Sergio S Arias Friday, July 26, 2013 12:48 AM
-
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))
-
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- Marked as answer by Allen Li - MSFTModerator Monday, July 29, 2013 6:56 AM
-
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
-
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