1. Registered User
Join Date
Oct 2008
Posts
24

Hi, I need help in a stored procedure that

1. Counts the number of Saturdays in a month
2. Returns 12 rows, each row containing number of Saturdays for each month of the year
3. Single parameter for the specification of the year

Thanks

2. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
I'm not going to do this for you, but I will give you some hints.

1) use a list of 12 static values, from, say, master..spt_values.
2) using DATEPART on the first day of each month, you should be able to determine the date of the first Saturday of that each month.
3) knowing the number of days in each month (and the date of the first Saturday in each month from step 2) you now know the number of days in the month from the first Saturday.
4) divide the number resulting from step 3 by 7 using the FLOOR function.

Should be able to do this in a single SELECT statement.

3. Registered User
Join Date
Apr 2012
Posts
213
Hi,

Try something like that:

Code:
```declare @ParYear int;
declare @DateProc date;
declare @DateNext date;
declare @QtyDays int;
declare @WeekDay1 int;
declare @QtySat int;
declare @T table (DateMonth date, QtySat int);

set @ParYear = 2012;
set @DateProc = CAST(CAST(@ParYear as CHAR(4)) + '/01/01' as Date);

while YEAR(@DateProc) = @ParYear
begin
set @DateNext = DATEADD(MONTH, 1, @DateProc);
set @QtyDays = DATEDIFF(DAY, @DateProc, @DateNext);
set @WeekDay1 = DATEPART(WEEKDAY, @DateProc);
set @QtySat = (@QtyDays + @WeekDay1 - 1) / 7;
insert into @T (DateMonth, QtySat) values (@DateProc, @QtySat);
set @DateProc = @DateNext;
end;

select * from @T;```
Hope this helps.

4. Registered User
Join Date
Aug 2012
Posts
30
Create a calendar table and then you would be good to go with it without any loops or tally table usage

http://visakhm.*************/2010/02...dar-table.html

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
try this url instead -- VM Blogs: Generating Calendar Table

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Originally Posted by visakh16
Create a calendar table and then you would be good to go with it without any loops or tally table usage
Create a table with a record for every date???

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```SELECT a, CASE
WHEN DateAdd(wk, 4, DateAdd(d, 7 - DatePart(dw, a), a)) > b THEN 4
ELSE 5
END AS d
FROM (SELECT
,     DateAdd(month, number, '2000-01-31') AS b
FROM master.dbo.spt_values
WHERE  'P' = type) AS c```
-PatP

8. Registered User
Join Date
Aug 2004
Location
Dallas, Texas
Posts
831
Originally Posted by blindman
Create a table with a record for every date???
In access they would use columns and not rows.

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Originally Posted by corncrowe
Originally Posted by blindman
Create a table with a record for every date???
In access they would use columns and not rows.
In .net they'd dump the whole thing into an XML file.

10. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by blindman
In .net they'd dump the whole thing into an XML file.
Then parse it with PowerShell, export to Excel, and then import it back into MS-Access!

Sorry, no offense to MS-Access folks but I've been unraveling some things done in Access that would set Rube Goldberg agog this week!

-PatP

11. Registered User
Join Date
Aug 2004
Location
Dallas, Texas
Posts
831
Originally Posted by Pat Phelan
Then parse it with PowerShell, export to Excel, and then import it back into MS-Access!

Sorry, no offense to MS-Access folks but I've been unraveling some things done in Access that would set Rube Goldberg agog this week!

-PatP
Yep, MS Access re-writes are a boon industry. I just wrote an import to sql from access. They kept the front-end and it is just a mess. I could have written a complete solution in VB.NET and SQL Server for 1/10 the cost of this project and still had a better income. I recently came across another industry leader attempting the same solution and I burped.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•