November 30, 2004 at 7:10 am
here is a tricky request for SQL programmers.
You have a @STARTDATE and an @ENDDATE in a stored proc. You want to check, if there are values for all dates between @STARTDATE and @ENDDATE in a table.
I.e. some SQL code should generate TRUE, if there ARE values for each day, and FALSE if there are holes (a day or some days are missing).
I must admit I have not found an elegant way to do it. Does anybody have some proper SQL to do it?
Kay
November 30, 2004 at 7:26 am
OK, stupid me, here's a first shot. Does anybody have anything better ? shorter? faster ?
declare @STARTDATE datetime
declare @ENDDATE datetime
select @STARTDATE ='1.1.2004' , @ENDDATE='31.1.2004'
if (select count(distinct my_date)
from my_table
where my_date >= @STARTDATE
and my_date <= @ENDDATE
) = datediff(dd, @STARTDATE, @ENDDATE) +1
print 'got something for each day'
else
print 'something is missing'
November 30, 2004 at 10:01 am
How bout?
DECLARE @Date DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Loop INTEGER
DECLARE @Cnt INTEGER
SET @Loop = 1
SET @Cnt = 1
SET @StartDate = '2004-11-20'
SET @EndDate = '2004-11-30'
SET @Loop = DATEDIFF(DAY, @StartDate, @EndDate)
SET @Date = @StartDate
WHILE @Cnt <= @Loop
BEGIN
IF (SELECT COUNT(*) FROM dbo.Table1 WHERE CreateDtTm = @Date) <> 0
BEGIN
PRINT 'Found record'
END
ELSE
BEGIN
PRINT 'Nothing Found'
END
SET @StartDate = DATEADD(DAY, 1, @StartDate)
SET @Cnt = @Cnt + 1
END
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 30, 2004 at 2:08 pm
What about having a separate date table and joining against this one?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 1, 2004 at 2:35 pm
i might be missing the question here,
do you mean if start date = 11/28 and end_date = 12/01
and you have data for 11/28, 11/29, 11/30 and 12/31, then TRUE
December 1, 2004 at 5:18 pm
I would add a little something to Kay's suggestion.
Its not pretty, but I can find no better way of stripping the time off a datetime value, which could produce erroneous results
declare @STARTDATE datetime
declare @ENDDATE datetime
select @STARTDATE ='1.1.2004' , @ENDDATE='31.1.2004'
if (select count(distinct CONVERT(DATETIME, CAST(YEAR(my_date) AS NVARCHAR) + CASE LEN(MONTH(my_date)) WHEN 1 THEN '0' ELSE '' END + CAST(MONTH(my_date) AS NVARCHAR) + CASE LEN(DAY(my_date)) WHEN 1 THEN '0' ELSE '' END + CAST(DAY(my_date) AS NVARCHAR), 112)))
from my_table
where my_date >= @STARTDATE
and my_date <= @ENDDATE
) = datediff(dd, @STARTDATE, @ENDDATE) +1
print 'got something for each day'
else
print 'something is missing'
December 1, 2004 at 5:38 pm
Nick,
Look at the CONVERT function. I prefer the following to strip TIME: CONVERT(VARCHAR(10), GETDATE(), 101)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 1, 2004 at 5:52 pm
This shouldn't be hard. Just create a DateValues Table with a date column with every possible date in the date range you are working with.
Example
dt
-----------
01/01/2004
02/01/2004
03/01/2004
04/01/2004
05/01/2004
06/01/2004
07/01/2004
08/01/2004
09/01/2004
10/01/2004
11/01/2004
12/01/2004
13/01/2004
14/01/2004
15/01/2004
Then apply the below query:
SELECT DISTINCT DateValues.Dt, CASE ISNULL(my_table.my_date, '01/01/01')
WHEN '01/01/01' THEN 'False'
ELSE 'True'
END AS output
FROM DateValues
LEFT OUTER JOIN my_table
ON DateValues.dt = my_table.my_date
WHERE DateValues.Dt >= CONVERT(DATETIME, '01/01/2004', 103)
AND DateValues.Dt <= CONVERT(DATETIME, '10/01/2004', 103)
Rohini
December 2, 2004 at 12:52 am
Its not pretty, but I can find no better way of stripping the time off a datetime value, which could produce erroneous results
Yuk, there are certainly better methods for this. Have a play with these:
SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)
SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME)
SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(8)),1,4) AS INT) AS DATETIME)
SELECT CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))
SELECT CONVERT(CHAR(8),GETDATE(),112)
SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
I like to think the first three offer the best performance.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 3, 2004 at 3:26 am
Thank you everybody for your support in this matter.
@aj Ahrens: Your Loop is nice, but has performance disadvantages. In addition, I would have to add some logic to return "true" if there is a date-hole somewhere or "false" if there is no hole at all.
@leepozdol: @STARTDATE and @ENDDATE may be any datetime values with @STARTDATE <= @ENDDATE.
@nick-2 M.: Yes, you're right, I did not consider time portions in a datetime value, but stripping off the time portion from a datetime value is a different issue [and a good solution is CAST(FLOOR(CAST(@DT AS float))AS datetime ].
@frank, Rohini and Jeff: Phua, you would really go the extra mile and create a table with all possible date velues? But that would mean a maintanance job to make sure all possible values are available. Is there any way to avoid maintaining a regular table and join a table-valued function instead, which produces the values 'on the fly' ??
Kay
December 3, 2004 at 3:29 am
@frank, Rohini and Jeff: Phua, you would really go the extra mile and create a table with all possible date velues?
Yes!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 4, 2004 at 12:41 pm
We use a Calendar table as well. It has all sorts of uses for reporting and logging IF Holiday, EOQ, EOW, EOY, weekday/weekend, etc...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply