none
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?


    • Edited by Titash RC Thursday, July 25, 2013 9:22 PM
    Thursday, July 25, 2013 9:18 PM

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 

    Friday, July 26, 2013 5:07 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


    Thursday, July 25, 2013 9:23 PM
    Moderator
  • 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))

                 
    Thursday, July 25, 2013 10:57 PM
  • 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 

    Friday, July 26, 2013 5:07 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

    Friday, July 26, 2013 6:03 AM
  • 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

    Friday, July 26, 2013 6:04 AM