Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    24

    Unanswered: Number of Saturdays

    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. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    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. #4
    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. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this url instead -- VM Blogs: Generating Calendar Table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by visakh16 View Post
    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???
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Adjust as needed:
    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-01') AS a
    ,     DateAdd(month, number, '2000-01-31') AS b
          FROM master.dbo.spt_values
    	  WHERE  'P' = type) AS c
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by corncrowe View Post
    Quote Originally Posted by blindman View Post
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Pat Phelan View Post
    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
  •