Get date not exist
-
I am using sql server 2005. I have a column with date values (say column1). I need to fetch the date which does not exist in the past 15 days. Means : user insert records daily. But on May 1st due to leave no records got inserted. So I need to analyse past 15 days (from current date) and fetch only May 1st. I tried below query.But its return the past 15 days (excluding weekends). But i need days which not exist in past 15 days.
select distinct top 15 inserteddate from table1 where
((DATEPART(dw, inserteddate) + @@DATEFIRST) % 7) NOT IN (0, 1) and ( inserteddate >= DATEADD(DAY,-15,inserteddate) and inserteddate <= getdate())
order by inserteddate descHelp me pls.
-
I am using sql server 2005. I have a column with date values (say column1). I need to fetch the date which does not exist in the past 15 days. Means : user insert records daily. But on May 1st due to leave no records got inserted. So I need to analyse past 15 days (from current date) and fetch only May 1st. I tried below query.But its return the past 15 days (excluding weekends). But i need days which not exist in past 15 days.
select distinct top 15 inserteddate from table1 where
((DATEPART(dw, inserteddate) + @@DATEFIRST) % 7) NOT IN (0, 1) and ( inserteddate >= DATEADD(DAY,-15,inserteddate) and inserteddate <= getdate())
order by inserteddate descHelp me pls.
I would suggest you create a temporary table with the dates for the last 15 days and then select the dates from the temporary table not in the table1 you referenced. The temporary table can be populated using a while loop.
-
I am using sql server 2005. I have a column with date values (say column1). I need to fetch the date which does not exist in the past 15 days. Means : user insert records daily. But on May 1st due to leave no records got inserted. So I need to analyse past 15 days (from current date) and fetch only May 1st. I tried below query.But its return the past 15 days (excluding weekends). But i need days which not exist in past 15 days.
select distinct top 15 inserteddate from table1 where
((DATEPART(dw, inserteddate) + @@DATEFIRST) % 7) NOT IN (0, 1) and ( inserteddate >= DATEADD(DAY,-15,inserteddate) and inserteddate <= getdate())
order by inserteddate descHelp me pls.
-
I am using sql server 2005. I have a column with date values (say column1). I need to fetch the date which does not exist in the past 15 days. Means : user insert records daily. But on May 1st due to leave no records got inserted. So I need to analyse past 15 days (from current date) and fetch only May 1st. I tried below query.But its return the past 15 days (excluding weekends). But i need days which not exist in past 15 days.
select distinct top 15 inserteddate from table1 where
((DATEPART(dw, inserteddate) + @@DATEFIRST) % 7) NOT IN (0, 1) and ( inserteddate >= DATEADD(DAY,-15,inserteddate) and inserteddate <= getdate())
order by inserteddate descHelp me pls.
Something like this should work:
WITH cteTally (N) As
(
SELECT TOP 21 -- 15 weekdays + 6 weekend days
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM
sys.objects
),
cteDays (TheDate) As
(
SELECT
DATEADD(day, -N, GetUtcDate())
FROM
cteTally
)
SELECT
TheDate
FROM
cteDays As D
WHERE
((DATEPART(dw, TheDate) + @@DATEFIRST) % 7) Not In (0, 1)
And
Not Exists
(
SELECT 1
FROM table1 As T
WHERE T.inserteddate = D.TheDate
)
ORDER BY
TheDate DESC
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Something like this should work:
WITH cteTally (N) As
(
SELECT TOP 21 -- 15 weekdays + 6 weekend days
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM
sys.objects
),
cteDays (TheDate) As
(
SELECT
DATEADD(day, -N, GetUtcDate())
FROM
cteTally
)
SELECT
TheDate
FROM
cteDays As D
WHERE
((DATEPART(dw, TheDate) + @@DATEFIRST) % 7) Not In (0, 1)
And
Not Exists
(
SELECT 1
FROM table1 As T
WHERE T.inserteddate = D.TheDate
)
ORDER BY
TheDate DESC
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Is there any benefit to the CTE over a temp table. I consider the temp table to be much more readable but if there was a significant performance benefit I will need to reconsider!
Never underestimate the power of human stupidity RAH
-
Is there any benefit to the CTE over a temp table. I consider the temp table to be much more readable but if there was a significant performance benefit I will need to reconsider!
Never underestimate the power of human stupidity RAH
For 21 rows, there probably won't be much difference in performance between a CTE and a temp table. I prefer the CTE solution because it's a single statement. With a temp table, you need to check if the table exists, create it, populate it, run your query, and drop it again. Of course, if you've already got a tally table in your DB, then you could use that instead. It might even be worth creating a table of dates, so that you can mark holidays and other closures as well as weekends.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
For 21 rows, there probably won't be much difference in performance between a CTE and a temp table. I prefer the CTE solution because it's a single statement. With a temp table, you need to check if the table exists, create it, populate it, run your query, and drop it again. Of course, if you've already got a tally table in your DB, then you could use that instead. It might even be worth creating a table of dates, so that you can mark holidays and other closures as well as weekends.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
For simple dates I have a view that goes from start of previous year to + 10 years but for some apps I have a table
Holidaywith all the public holidays of various countries we deal with.Never underestimate the power of human stupidity RAH
