Find the Missing date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find the Missing date

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2010-11-08 : 17:10:50

Create table dbo.Sales
( sales_id int ,
sku varchar(10) ,
item varchar(10) ,
creation_date datetime ,
update_date datetime)

Insert into dbo.Sales
select 1, 'ABC123', 'IceCream', GETDATE()-5, GETDATE()-5 union
select 2, 'ABC123', 'Chocolate', GETDATE()-5, GETDATE()-5 union
select 3, 'ABC123', 'Chocolate', GETDATE()-4, GETDATE()-5 union
select 4, 'ABC123', 'Candy', GETDATE()-4, GETDATE()-4 union
select 5, 'ABC123', 'Candy', GETDATE()-4, GETDATE()-4 union
select 6, 'ABC123', 'Candy', GETDATE()-2, GETDATE()-2 union
select 7, 'ABC123', 'IceCream', GETDATE()-1, GETDATE()-1 union
select 7, 'ABC123', 'Chocolate', GETDATE()-1, GETDATE()-1 union
select 7, 'ABC123', 'Candy', GETDATE()-1, GETDATE()-1

Need to go back dbo.sales for last 5 days and fine the Creation_date day when we didnt recive any entry into the table.

In the above example. we didnt have any entry on getdate()-3 .
Can you please advise.

Chandragupta Mourya

Takhyashila

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-08 : 17:54:06

1. define missing day, a business day, weekend included etc
2. define your week (Mon-fri? mon-sun? etc) business days
3. define your first of business week (Sunday or Monday)
4. you might need a calendar table (http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html)

If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-08 : 18:46:00

As Yosiaaz suggests you probably want to use some sort of Date table. But, here is one way using an in-line tally table that might get you going:

;WITH 
Tens (N)     AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                 SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9), 
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), 
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), 
Tally (N)    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT 
    *
FROM
    (
        SELECT 
            DATEADD(DAY, N * -1, CAST(CURRENT_TIMESTAMP AS DATE)) AS StartDate
            ,DATEADD(DAY, (N * -1) + 1, CAST(CURRENT_TIMESTAMP AS DATE)) AS EndDate
        FROM Tally
        WHERE N BETWEEN 1 AND 5
    ) AS T
LEFT OUTER JOIN
    dbo.Sales
    ON Sales.creation_date >= StartDate
    AND Sales.creation_date < EndDate
WHERE
    Sales.sales_id IS NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 04:03:08

We have had this two days ago like this:

declare @Sales table ( 
						sales_id int , 
						sku varchar(10) , 
						item varchar(10) , 
						creation_date datetime , 
						update_date datetime) 

Insert into @Sales 
select 1, 'ABC123', 'IceCream', GETDATE()-5, GETDATE()-5 union
select 2, 'ABC123', 'Chocolate', GETDATE()-5, GETDATE()-5 union
select 3, 'ABC123', 'Chocolate', GETDATE()-4, GETDATE()-5 union
select 4, 'ABC123', 'Candy', GETDATE()-4, GETDATE()-4 union
select 5, 'ABC123', 'Candy', GETDATE()-4, GETDATE()-4 union
select 6, 'ABC123', 'Candy', GETDATE()-2, GETDATE()-2 union
select 7, 'ABC123', 'IceCream', GETDATE()-1, GETDATE()-1 union
select 7, 'ABC123', 'Chocolate', GETDATE()-1, GETDATE()-1 union
select 7, 'ABC123', 'Candy', GETDATE()-1, GETDATE()-1 

--select * from @Sales

Declare @startdate datetime
Declare @enddate datetime

Set @startdate=dateadd(day,datediff(day,0,getdate()-5),0) -- getdate()-5 with time part 00:00:00.000
Set @enddate=dateadd(day,datediff(day,0,getdate()),0) -- getdate() with time part 00:00:00.000

select 
	dateadd(day,number,@startdate) as missingDate 
from master.dbo.spt_values 
where master.dbo.spt_values.type='p' 
and   dateadd(day,number,@startdate)<=@enddate
and   dateadd(day,number,@startdate) not in (select dateadd(day,datediff(day,0,creation_date),0) 
											 from @Sales)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources