July 30, 2008 at 8:03 am
I have an automated procedure that runs on a daily basis. It picks up a daily file and imports it. Sometimes the file is not available and I only get it after two or three days.
What I'm trying to do is to check for gaps going back only seven days if I pick up gaps...My script does it for the whole month..
So far when I test it it works fine...I generate the missing files and build the commands to copy the files and to execute them...
1) How do I list gaps going back only 7 days only
July 30, 2008 at 12:37 pm
;with Last7 (Date) as
(select getdate() -1
union
select getdate() -2
union
select getdate() -3
... -- up to 7)
select Date
from Last7
left outer join dbo.Table
on Last7.Date = Table.LogDate
where Table.ID is null
Something like that will do what you need.
Generating the list is easier with a Numbers table, but with only 7 rows, it's no big deal to build it manually.
The idea is to build a list of the last 7 days, then do a Left Outer Join to your main table on the date, then select those dates that don't have a match in that table.
In the join, you'll have to watch out for the time part of the date. You might need to compensate for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 31, 2008 at 7:17 am
IF OBJECT_ID('tempdb..#Missing_Dates') IS NOT NULL
DROP TABLE #Missing_Dates
GO
CREATE TABLE #Missing_Dates(RowsINT,TransactionDate Datetime,Rundate Datetime)
--INSERT #Missing_Dates VALUES(244 ,'2008-07-28 00:00:00.000','2008-07-30 00:00:00.000')
--INSERT #Missing_Dates VALUES(11307,'2008-07-29 00:00:00.000','2008-07-30 00:00:00.000')
--INSERT #Missing_Dates VALUES(7408 ,'2008-07-28 00:00:00.000','2008-07-29 00:00:00.000')
--INSERT #Missing_Dates VALUES(254 ,'2008-07-27 00:00:00.000','2008-07-29 00:00:00.000')
--INSERT #Missing_Dates VALUES(8018 ,'2008-07-27 00:00:00.000','2008-07-28 00:00:00.000')
--INSERT #Missing_Dates VALUES(362 ,'2008-07-26 00:00:00.000','2008-07-28 00:00:00.000')
--INSERT #Missing_Dates VALUES(386 ,'2008-07-25 00:00:00.000','2008-07-27 00:00:00.000')
--INSERT #Missing_Dates VALUES(10560,'2008-07-26 00:00:00.000','2008-07-27 00:00:00.000')
--INSERT #Missing_Dates VALUES(290 ,'2008-07-24 00:00:00.000','2008-07-26 00:00:00.000')
--INSERT #Missing_Dates VALUES(9620 ,'2008-07-25 00:00:00.000','2008-07-26 00:00:00.000')
--INSERT #Missing_Dates VALUES(8351 ,'2008-07-24 00:00:00.000','2008-07-25 00:00:00.000')
--INSERT #Missing_Dates VALUES(252 ,'2008-07-23 00:00:00.000','2008-07-25 00:00:00.000')
--INSERT #Missing_Dates VALUES(8278 ,'2008-07-23 00:00:00.000','2008-07-24 00:00:00.000')
--INSERT #Missing_Dates VALUES(236 ,'2008-07-22 00:00:00.000','2008-07-24 00:00:00.000')
--INSERT #Missing_Dates VALUES(9763 ,'2008-07-22 00:00:00.000','2008-07-23 00:00:00.000')
INSERT #Missing_Dates VALUES(226 ,'2008-07-21 00:00:00.000','2008-07-23 00:00:00.000')
INSERT #Missing_Dates VALUES(6737 ,'2008-07-21 00:00:00.000','2008-07-22 00:00:00.000')
INSERT #Missing_Dates VALUES(273 ,'2008-07-20 00:00:00.000','2008-07-22 00:00:00.000')
INSERT #Missing_Dates VALUES(345 ,'2008-07-19 00:00:00.000','2008-07-21 00:00:00.000')
INSERT #Missing_Dates VALUES(8160 ,'2008-07-20 00:00:00.000','2008-07-21 00:00:00.000')
INSERT #Missing_Dates VALUES(10552,'2008-07-19 00:00:00.000','2008-07-20 00:00:00.000')
INSERT #Missing_Dates VALUES(426 ,'2008-07-18 00:00:00.000','2008-07-20 00:00:00.000')
INSERT #Missing_Dates VALUES(9608 ,'2008-07-18 00:00:00.000','2008-07-19 00:00:00.000')
INSERT #Missing_Dates VALUES(262 ,'2008-07-17 00:00:00.000','2008-07-19 00:00:00.000')
INSERT #Missing_Dates VALUES(223 ,'2008-07-16 00:00:00.000','2008-07-18 00:00:00.000')
INSERT #Missing_Dates VALUES(8673 ,'2008-07-17 00:00:00.000','2008-07-18 00:00:00.000')
INSERT #Missing_Dates VALUES(8070 ,'2008-07-16 00:00:00.000','2008-07-17 00:00:00.000')
INSERT #Missing_Dates VALUES(267 ,'2008-07-15 00:00:00.000','2008-07-17 00:00:00.000')
INSERT #Missing_Dates VALUES(9943 ,'2008-07-15 00:00:00.000','2008-07-16 00:00:00.000')
INSERT #Missing_Dates VALUES(218 ,'2008-07-14 00:00:00.000','2008-07-16 00:00:00.000')
--INSERT #Missing_Dates VALUES(263 ,'2008-07-13 00:00:00.000','2008-07-15 00:00:00.000')
GO
IF OBJECT_ID('tempdb..#Missing_Files') IS NOT NULL
DROP TABLE #Missing_Files
GO
CREATE TABLE #Missing_Files(RowIDINT identity(1,1),MissingDate DATETIME,strTableName VARCHAR(100))
INSERT #Missing_Files(MissingDate )
;WITH Last7 (MissingDates) AS
(
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-1,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-2,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-3,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-4,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-5,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-6,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-7,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-8,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-9,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-10,120) AS DATETIME)
UNION
SELECT CAST(CONVERT(VARCHAR(10),GETDATE()-11,120) AS DATETIME)
)
SELECT MissingDates FROM Last7 LEFT OUTER JOIN #Missing_Dates x
ON Last7.MissingDates = CAST(CONVERT(VARCHAR(10),x.RunDate ,120) AS DATETIME)
WHERE x.RunDate IS NULL
When I run the statement it does not give me all the missing dates
1)
First error
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
2)
When I add the ;
I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ';'.
3) How will I build the filename now
August 1, 2008 at 1:13 am
The problem is with your CTE syntax when used with INSERT.
Without rewriting your SQL, it should be basically in the following order (pseudo code.)
;WITH MissingDatesCTE (MissingDateField) AS (SELECT......)
INSERT INTO #Missing_Files(MissingDate)
SELECT MissingDateField FROM MissingDatesCTE LEFT JOIN...etc...
Hope you can work through this....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply