Sql Query Required!
-
I have a column with type datetime in an employee's table. I want to delete the records that older than 3 working days. The problem is I can't use getdate()-3 in where clause because I don't know about the holidays and all.. Can anyone help me out ? Regards, Zishan
-
I have a column with type datetime in an employee's table. I want to delete the records that older than 3 working days. The problem is I can't use getdate()-3 in where clause because I don't know about the holidays and all.. Can anyone help me out ? Regards, Zishan
Zishan Haider wrote: The problem is I can't use getdate()-3 in where clause because I don't know about the holidays and all.. Can anyone help me out ? You will need information on the holidays if you are to achieve this.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Zishan Haider wrote: The problem is I can't use getdate()-3 in where clause because I don't know about the holidays and all.. Can anyone help me out ? You will need information on the holidays if you are to achieve this.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Can't I use some kind of loop in the SQL :confused:
-
Can't I use some kind of loop in the SQL :confused:
You can use loops in SQL but they are very inefficient. However, you've already said that you don't know the holiday information so I don't see how a loop is going to be useful.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Can't I use some kind of loop in the SQL :confused:
Assuming you have a table called Holidays with the a column containing the date of each holiday you could write a query like this:
DECLARE @days int;
SELECT @days = COUNT(*)+3 FROM Holidays
WHERE HolidayDate <= getdate() AND HolidayDate >= getdate()-3You can then use the
@daysvariable to count back the number of days. For example:DELETE FROM MyTable WHERE somedate < getdate-@days
Does this help?
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Assuming you have a table called Holidays with the a column containing the date of each holiday you could write a query like this:
DECLARE @days int;
SELECT @days = COUNT(*)+3 FROM Holidays
WHERE HolidayDate <= getdate() AND HolidayDate >= getdate()-3You can then use the
@daysvariable to count back the number of days. For example:DELETE FROM MyTable WHERE somedate < getdate-@days
Does this help?
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Well.. you're right couldn't do it in SQL without a holidays table. However I did it in my C# program. I selected distinct dates from table ordered by Date Desc. Then I deleted every record with the date from my select query ignoring the first three.. :-O Yes I know its poor performance, but this chunk has to run once every morning so I don't need any major performance here. And I had to do it without A holidays table. Thanks for your help :) Regards Zishan