February 22, 2010 at 1:16 pm
Hi, Can someone help me out in figuring out the MS SQL equivalent of the following for mysql:
CREATE EVENT
e
ON SCHEDULE
EVERY 5 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 SECOND
DO
UPDATE names SET names.count=names.count+1;
I'm basically trying to increment the values of all the rows under a particular column every 5 seconds. I could do it easily in mysql but when I'm trying to do it for SQL, I don't know what to do.
I came up with an incomplete function that I'd maybe use, but I do not know how to trigger it every 5 seconds(the names of the variables do change). I wasted more than 3 days on this. I need some help desperately!!!
CREATE FUNCTION lastday ( @joindate DATETIME)
RETURNS INT
BEGIN
DECLARE @referdate DATETIME
DECLARE @count INT
DECLARE @i DATETIME
SET @referdate = CAST(YEAR(@joindate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@joindate) AS VARCHAR(2)) + '/01'
SET @referdate=DATEADD(DD,-1,DATEADD(M, 1, @referdate))
IF(day(@referdate)-day(@joindate)>=15)
SET @count = 1
ELSE
SET @count = 0
WHILE (@i!=0)
BEGIN
SET @referdate=DATEADD(SECOND,5,@referdate)
SET @count=@count +1
RETURN @count
END
February 22, 2010 at 7:10 pm
Why would you want to update the time (or increment a counter) in a number of rows every 5 seconds? Are other columns being updated too? Does names.count get periodilcally reset to zero or one or some other value?
With a bit more explanation of the business need behind this, I 'd think folks may be able to help you with an efficient, and more easily maintained solution.
February 22, 2010 at 8:10 pm
i agree with john.arnott; if we know the reason, we can help;
for example, why not use a view?
you can easily create a view or function that returns a number, based on x seconds from the starting time;
that way you do not need to run something every five seconds, but instead get an accurate value on demand.
Lowell
February 22, 2010 at 8:18 pm
To say exactly, 5 seconds was just a way to get it as a confirmation of what I need-a test.
This is what I want- I'm building an employee management system using aspnetdb. As you clearly know its a MS SQL. I need to come up with a column where in I can record the number of holidays an employee can have every month. According to my requirements, it's a day specifically incremented on the 15th of every month. In the function I provided, I tried to round up the hire date to the nearest last day of the month so that from there, I can use something similar to "DATEADD" and increment it every month. But "DATEADD" doesn;t support incrementing multiple number of times unless I use some sort of a loop, which isn't possible as the loop must use a DATETIME variable as a counter. That's why I'm specifically looking to increment this value using some "event" for MS SQL which can run every month on the 15th and increment the holiday value.
Thanks!
February 23, 2010 at 6:46 am
Yep I'm still sure you can calculate the accrued holidays (vacationtime?) with a view instead.
dunno your math, you will need to be very specific: give us the create table , expected calculation, etc. for a complete answer.
i might be missing something, but why will a calculation like this not work?:
note i added an extra table to show which days were used up, to calculate the net days available.
create table example(exampleid int identity(1,1) primary key,
SSCName varchar(30),
DateofHire datetime )
insert into example SELECT 'Lowell','2009-06-05 14:23:45.000'
Create table UsedHolidays(
SSCName varchar(30),
DateofHolidayUsed datetime )
insert into UsedHolidays
SELECT 'Lowell','2009-12-24 14:23:45.000' UNION ALL
SELECT 'Lowell','2009-12-25 14:23:45.000'
-- math assumption: i'm assuming you can accrue 12 vacation days per year, one per month, with a maximum of 21 days of vacation
create view AccruedHolidays AS
select
example.exampleid,
example.SSCName,
DATEDIFF(mm,DateofHire,getdate()) AS RAWAccrual,
--diff of midnite(hiredate vs midnite(today)
DATEDIFF(mm,DATEADD(dd, DATEDIFF(dd,0,DateofHire), 0),DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As MidniteAccrual,
DATEDIFF(mm,DATEADD(dd, DATEDIFF(dd,0,DateofHire), 0),DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) - DaysUsed As NetDays
from example
LEFT OUTER JOIN (SELECT SSCName,COUNT(*) AS DaysUsed FROM UsedHolidays GROUP BY SSCName ) X
On example.SSCName = X.SSCName
results:
exampleid SSCName RAWAccrual MidniteAccrual NetDays
----------- ------------------------------ ----------- -------------- -----------
1 Lowell 8 8 6
the only relevant difference i see is that you want to calculate based ont he 15th of the month, instead of today;
where i was using midnight of getdate(), you'd simply substitute the calculation of the 15th of this month:
--First Day of this Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--15th of this Month
select DATEADD(dd,14,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
Lowell
February 23, 2010 at 8:52 am
Thanks a ton for the help.
But the table I'm using is a bit different. It looks like this:
ProfileId HireDate HolidayCount DaysUsed(in the form of dates)
1 2009/2/2 13 0
2 2009/2/20 12 0
3 2010/1/1 1 1
The main problem is this: As you can see above, if a person is hired on or before the 15th of a month, he gets a leave accrued for that month.But if he is hired after 15 th, he gets nothing!
Another thing is that the 'days used' column needs to take dates as input from the user and calculate the difference in days. This wouldn't be much of a problem, I was able to do it using DATEDIFF.
Now how do I calculate the holiday count using views?
Also finally, ho do I use a view to post back the data to the database- I mean, how can I update the table with the new value so that I can use it directly in my web form?
Hope it's not much but this problem has halted me from finishing the project since the last 4 days. I really appreciate ur help!!!
February 23, 2010 at 10:53 am
replace your static column "Holidays with the calculation included in my example,a dn you get the results you wanted, without the whole update of my db every five seconds:
Create Table Whatever(
ProfileId int,
HireDate datetime,
HolidayCount As DATEDIFF(mm,DATEADD(dd, DATEDIFF(dd,0,HireDate), 0),DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))
+ CASE WHEN day(HireDate) < 15 THEN 1 ELSE 0 END ,
DaysUsed int)
INSERT INTO Whatever(ProfileId,HireDate,DaysUsed)
SELECT 1 ,'2009/2/2', 0 UNION ALL
SELECT 2 ,'2009/2/20', 0 UNION ALL
SELECT 3 ,'2010/1/1', 1
select * from Whatever
--Lowells Results
ProfileId HireDate HolidayCount DaysUsed
----------- ----------------------- ------------ -----------
1 2009-02-02 00:00:00.000 13 0
2 2009-02-20 00:00:00.000 12 0
3 2010-01-01 00:00:00.000 2 1
I've given you two complete examples with CREATE TABLE/SELECT INTO/expected results. if this is not what you want, you MUST post everything the same way....help us help you
Lowell
March 1, 2010 at 6:31 am
harshalnag did this help you or did you do something different?
Lowell
March 1, 2010 at 7:55 am
Hey,
Sorry couldn't post back earlier- was busy with my project submissions- I did not exactly use the views provided by you. Instead, I just added 2 columns for the 'date of hire' and the 'days used' to the aspnetdb.mdf. Instead I just used your function to display the data onto a webpage directly from the database.
It looks like this:
SELECT MemFName, DaysUsed, DATEDIFF(mm, DateOfHire, GETDATE()) AS RAWAccrual, DATEDIFF(mm, DATEADD(dd, DATEDIFF(dd, 0, DateOfHire), 0), DATEADD(dd, 14, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) AS MidniteAccrual, DATEDIFF(mm, DATEADD(dd, DATEDIFF(dd, 0, DateOfHire), 0), DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) - DaysUsed AS NetDays, DateOfHire, ProfileId FROM User_Profile.
Since it's always comparing with the presebt day, the users are bound to get the exact number of days.
Thanks a lot for the help!!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply