|
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
|
|
|
|
|
|
Can't I use some kind of loop in the SQL 
|
|
|
|
|
|
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()-3
You can then use the @days variable 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..  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
|
|
|
|
|
1. How to look witch tables are in the database.
2. Is possible to create database with my application. And How?
|
|
|
|
|
I work on several programs that require ordinary users to be able to change settings system wide (specific to the programs). However, Windows security is evolving toward forbiding any of the obvious aproaches to sharing data in this way (such as HKEY_LOCAL_MACHINE, and files in the same directory that the program is installed in) The simplest solution that I've been able to find is to use an MSDE database to store shared settings. However, a database server intended to be used over a network seems to be overkill to me. Does anyone have some ideas for my problem?
Nathan Holt
|
|
|
|
|
MSDE is actually limited in how many network connections it can take, however, it sounds like Access would do what you want. I have deployed MSDE for use on single machines, but I was building a database that Access just wasn't good enough for.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Christian Graus wrote:
MSDE is actually limited in how many network connections it can take, however, it sounds like Access would do what you want. I have deployed MSDE for use on single machines, but I was building a database that Access just wasn't good enough for.
Thanks. I finally found info yesterday that one is allowed to have shared files in the All Users/Application Data provided that one makes a subdirectory and sets its permisions so that all users can write to them. That makes most of the code I have easily fixable.
Nathan Holt
|
|
|
|
|
Are the effects of DDL events like droping or altering an object stored anywhere in SQL Server 2000? For example, if a stored procedure is altered is there some way of getting this information. The SQL server logs only seem to contain info on database level events (start, stop, backup, dbcc check, etc). Is there something similar that would allow me to find the date modified of a table or when a stored procedure was dropped?
|
|
|
|
|
Hello,
I'm having a little problem with the COUNT function. I have a table with payments for customers (say 42 or 54 each), and each payment is marked as paid whenever the customer comes and pays it.
I want to make a query that gives me all the customers with the number of paid payments (including those customers new for this week). The total number of customers is 328, but if I add the COUNT(*) the resulting table only contains 291, all with 1 or more payments (meaning 37 customers have 0 payments.)
Looking in the web I found that COUNT(column) returns the number of rows in which that column is not NULL. In this case, I'm using COUNT(*) which should return the count of all rows. I'm also using a WHERE clause to limit the counted payments to only those that have been paid. I tried a LEFT JOIN, so that at least the resultset would include the rows, even if the count column is NULL, but it didn't work.
Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
|
Not exactly the answer I needed, but that'll help too.
I think my problem was that I was counting all the records from a resultset matching a criteria instead of counting some records matching a criteria from resultset from all records. I split the query into embedded subqueries and the results came as expected, except I was getting NULL instead of zeros. Your suggestion fixed it.
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
do you group by the right columns ?
how is your select written please ?
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
|
Hi,
Is there a reason why sometimes some of the link options in Crystal Reports are disabled?
eg the FULL OUTER JOIN.
I can't think of a reason why a full outer join wouldn't work?
Hope somebody can help me.
tnx!
No hurries, no worries.
|
|
|
|
|
Hi,
I found that there are two practices for writing queries for adapter.
1. one adapter queries for one table in the database
e.g. select * from customer
2. one adapter queries for joinned table in the database
e.g. select * from customer c1, country c2 where c1.country = c2.country
Are the update/insert/delete command of an adapter work only for one table only?
Which practice is encouraged?
If the two tables are filled into the dataset, are there any SQL queries method to do the "join" operation to the two tables? anymore operations can be done?
Thanks
|
|
|
|
|
scchan1984 wrote:
Are the update/insert/delete command of an adapter work only for one table only?
Personally, I would never rely on the auto generated code for the INSERT/UPDATE/DELETE commands in an Adapter - because it cannot assume anything about the database it creates some awful SQL to ensure that it works.
scchan1984 wrote:
Which practice is encouraged?
I would encourage you not to use any Wizard generated adapters. Also, never do a SELECT * if the underlying table changes then the results back to your application may be different and your application may not expect that.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Is there a way to package and deploy only the engine and any runtime support for a SQL Server database?
If not and I have to tell the user that SQL Server is required, what is the easiest way to install my application's schema and data into an existing SQL Server database? In previous situations, I've exported the data and used the bcp command-line app to import the data. Is that the best way or is there an alternative?
Cheers,
Tom Archer - Archer Consulting Group
"So look up ahead at times to come, despair is not for us. We have a world and more to see, while this remains behind." - James N. Rowe
|
|
|
|
|
|
I have a DataTable (a table in ASP.NET) in memory which I need to pass into an SQL stored procedure.
How can I achieve this? Is there any to pass the contents of the table into my stored procedure given that the size (number of rows) of the table is unknown?
|
|
|
|
|
Convert it to an XML document and use OpenXML in your stored procedure to turn it into a table.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Thanks for the response.
At the moment, I've just done it as a string with delimiters (commas and full-stops). Is there any disadvantages of doing it this way? Are there any advantages of using an XML document?
|
|
|
|
|
The advantages are just that the documents form is in a sensible format, that is to say, the XML parser is able to tell what's data and what's metadata. If you pass in a comma or full stop in your data, you could have problems.
Christian Graus - Microsoft MVP - C++
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
