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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy