Results 1 to 14 of 14

Thread: days count

  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Unanswered: days count

    i want to count how many mondays are in a month. Example 1st of march and 31st of march are the inputs. or we can just take a month. Now, all i want is no of mondays which fall in this month.

    Thanks

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Check BOL for days of week in DATENAME function.

  3. #3
    Join Date
    Sep 2010
    Posts
    153
    i know datename function but it is for fetchign data for a provided particular data. how do i count for the entire month? Kindlt provide em the solution by writing a query.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Try using the datepart function in conjuction with datename. :-)

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831

  6. #6
    Join Date
    Sep 2010
    Posts
    153
    I know datepart thing also. I am saying, i am not able to implement properly. Can you write a query for me?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunny_007 View Post
    ... it is for fetchign data for a provided particular data.
    kindly show table layout for provided particular data

    also, you will need to input not only "1st of march" and "31st of march" but also the year
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2010
    Posts
    153

    I don't have the table

    I want to know how many mondays for the month march, 2011.
    Yes, i tried by providing the year also but not working out for me .

    ny1 write a query, plz

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I provided the samples and tools for you create this query. All you need is to implement. And don't forget to use count of Monday's in each month. You can't learn if people do the work.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunny_007 View Post
    ny1 write a query, plz
    kindly show table layout for provided particular data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Breaking down your problem into steps . . .

    1) Let's assume that you are creating a stored procedure to accomplish this need.
    2) Let's assume that your stored procedure will take as input, a date, which you will use to determine the month and year for which you are interested in determining the number of Mondays.
    3) Using whatever method you like within the stored procedure, I would first determine the number of days in the month in question.
    4) Then, I would determine the weekday of the first day of the month and, from that, determine the day of the first Monday of the month.
    5) I would subtract the day of that first Monday of the month from the total number of days in the month.
    6) I would then divide the value determined in 5) by seven and take the FLOOR of the result--FLOOR is the integer portion of that division.

    That's it.

    Any of us could do this all in a single SELECT statement.

    Instead of a stored procedure, you could make this a scalar function, or you could incorporate this directly into a production SELECT statement.

    I have not done this and I can't guarantee 100% that all of these steps are exactly needed to solve the problem, but this is how I would approach the problem in my head.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sunny still hasn't divulged any meaningful information, so i don't see why he can't just look at the friggin calendar on the wall and count the mondays there

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by PracticalProgram View Post
    Breaking down your problem into steps . . .

    1) Let's assume that you are creating a stored procedure to accomplish this need.
    2) Let's assume that your stored procedure will take as input, a date, which you will use to determine the month and year for which you are interested in determining the number of Mondays.
    3) Using whatever method you like within the stored procedure, I would first determine the number of days in the month in question.
    4) Then, I would determine the weekday of the first day of the month and, from that, determine the day of the first Monday of the month.
    5) I would subtract the day of that first Monday of the month from the total number of days in the month.
    6) I would then divide the value determined in 5) by seven and take the FLOOR of the result--FLOOR is the integer portion of that division.

    That's it.

    Any of us could do this all in a single SELECT statement.

    Instead of a stored procedure, you could make this a scalar function, or you could incorporate this directly into a production SELECT statement.

    I have not done this and I can't guarantee 100% that all of these steps are exactly needed to solve the problem, but this is how I would approach the problem in my head.
    That seems far more complex than it needs to be.

    I'd use a recursive CTE to pull out all the days and test for which ones are the day of the week required.

    Eg assuming @DateFrom, @DateTo and @DayOfWeek are parameters defined for a sp/function

    Code:
    with dateCTE as
    	(
    		select @DateFrom As CTEDate
    		union all
    		select DATEADD(day,1,CTEDate}
    		From dateCTE
    		Where CTEDate < @DateTo
    	)
    select count(*) from dateCTE where DATEPART(dow,CTEDate) = @DayOfWeek
    (Deliberate syntax errors put in so you can't just copy and paste )

  14. #14
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by EngadaSQL View Post
    That seems far more complex than it needs to be.
    Really ???

    Here is my actual code, which exactly replicates my verbal representation . . .

    Code:
    set datefirst 2
    print ceiling((datepart(day,@DateTo)-(7-datepart(dw,@DateFrom)))/7.)
    And here is your code . . .

    Code:
    with dateCTE as
    	(
    		select @DateFrom As CTEDate
    		union all
    		select DATEADD(day,1,CTEDate}
    		From dateCTE
    		Where CTEDate < @DateTo
    	)
    select count(*) from dateCTE where DATEPART(dow,CTEDate) = @DayOfWeek
    Which methodology requires more resources and is more complex???
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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