Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jonny1409
Posting Yak Master
133 Posts |
 Posted - 2007-10-22 : 09:34:59
|
| Hello,What would the syntax be in a Stored Procedure for passing in multiple dates and looping through ?For example, I'd like to pass in a list of the bank holidays this year.These are already stored in a table (tbl_BankHolidays) which contains 9 rows, one for each of the bank holidays this year.I'd then like to loop through each of these 9 dates in the stored procedure.How would I do this ?Thanks in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-22 : 09:41:11
|
I think you better tell us what you REALLY need to do.Otherwise you will come back in a few minutes with next problem related to same data.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-22 : 09:46:47
|
| Are you trying to pass each date as parameter to a procedure?MadhivananFailing to plan is Planning to fail |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-22 : 10:00:44
|
| Ok. That is what I really need to do, although there is a little more to it.The overall thing I want to do is :1) Get Bank Holidays for the year2) Check if an employee works on any of those days (using a function already created which we pass in an employee and a date, and this is compared against their shift pattern)3) If they do, add an entry into the holiday table for that date4) If they don't, do nothing. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-22 : 10:43:33
|
You can probably use a WHERE EXISTS (SELECT * FROM tblHolidays WHERE ... )Here is an excerpt from my article about calculating daysquote:
How do I use the function?Let’s look at the question ”How many workdays are there in August 2006?”. With my function this is now quite simple.SELECT COUNT(*) Workdays FROM dbo.fnSeqDates('8/31/2006', '8/1/2006') WHERE DATEPART(dw, SeqDate) BETWEEN 2 AND 6The answer seems to be 23 workdays. Note that my function does not take into account holidays. For this you must use a separate table. But it will be very easy to join my function to your holiday table tblHolidays and date hDate!SELECT COUNT(*) Workdays FROM dbo.fnSeqDates('8/1/2006', '8/31/2006') WHERE SeqDate NOT IN (SELECT hDate FROM tblHolidays) AND DATEPART(dw, SeqDate) BETWEEN 2 AND 6”How many workdays are there every month in 2006?”. Why restrict to one month? SELECT MONTH(SeqDate) Month, COUNT(*) Workdays FROM dbo.fnSeqDates('1/1/2006', '12/31/2006')WHERE SeqDate NOT IN (SELECT hDate FROM tblHolidays) AND DATEPART(dw, SeqDate) BETWEEN 2 AND 6 GROUP BY MONTH(SeqDate) ORDER BY MONTH(SeqDate)”Which are the Fridays for the first quarter 2008?”.SELECT SeqDate Fridays FROM dbo.fnSeqDates('3/31/2008', '1/1/2008') WHERE DATEPART(dw, SeqDate) = 6Even when 2008 is a leap year, the function is working.”Which is the last thursday in April 2007?”.SELECT MAX(dt.SeqDate) LastThursday FROM dbo.fnSeqDates('4/1/2007', '4/30/2007') dt WHERE DATEPART(dw, dt.SeqDate) = 5”Which is the second tuesday in September 2012?”.SELECT MIN(dt.SeqDate) SecondTuesday FROM dbo.fnSeqDates('9/1/2012', '9/30/2012') dt WHERE DATEPART(dw, dt.SeqDate) = 3 AND dt.SeqDate > '9/7/2012'Here I use dt.SeqDate > '9/7/2012' because the first Tuesday will occur within the first seven days in month.”Which is the last Wednesday of every month in 2006?”.SELECT MAX(SeqDate) LastWednesday FROM dbo.fnSeqDates('1/1/2006', '12/31/2006') WHERE DATEPART(dw, SeqDate) = 4 GROUP BY MONTH(SeqDate) ORDER BY MONTH(SeqDate)”Which are first and last day of every month in 2008?”.SELECT MIN(SeqDate) FirstDay, MAX(SeqDate) LastDay FROM dbo.fnSeqDates('1/1/2008', '12/31/2008') GROUP BY MONTH(SeqDate) ORDER BY MONTH(SeqDate)”Which are the paydays of every month in 2008 and 2009?”. Assuming monthly pay-day is 27th of every month and if 27th is weekend or holiday, the first weekday before that.SELECT MAX(SeqDate) Paydays FROM dbo.fnSeqDates('1/1/2008', '12/31/2009')WHERE SeqDate NOT IN (SELECT hDate FROM tblHolidays) AND DAY(SeqDate) <= 27 AND DATEPART(dw, SeqDate) BETWEEN 2 AND 6GROUP BY YEAR(SeqDate), MONTH(SeqDate) ORDER BY YEAR(SeqDate), MONTH(SeqDate)And for all of you who have read all the way down here, ”How many more Mondays until I retire?”.SELECT COUNT(SeqDate) Mondays FROM dbo.fnSeqDates('5/10/2006', '9/6/2034') WHERE DATEPART(dw, SeqDate) = 2The answer is 1,478 mondays.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-22 : 10:46:45
|
| Wait. I used to work at a bank and I think the bigger question is that you have a Bank Holiday table with only 9 records in it. There should be at least 10 or 12 more records in it...not to mention 3-4 weeks of vacation a year plus operating hours of like 10am - 2pm. hehe jk |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-23 : 04:18:59
|
| I've found out that it's a cursor I need to use to bring multiple records into a SP.Thanks. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-23 : 05:13:55
|
Or a WHILE loop.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
Loop In Stored Procedure