Results 1 to 12 of 12

Thread: SQL Server Query to show days of months for selected months in date range

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    SQL Server Query to show days of months for selected months in date range

    I have 2 tables in sql server db I wanted generate a report which will display
    the following result

    TotalNoofBeds, NoofDaysInMonth, Month, Year, TotalNoOfAdmissions

    Table 1,
    BedMaster
    BedID int
    BedCategoryID int
    BedDescription varchar

    Table 2
    Admissions
    PtnID int
    BedID int
    CrtDt date
    AdmSts char

    User can select any date range for 2 months or 5 months from dateTimePicker given on the screen
    if user selects 01/01/18 to 31/05/18 means for 5 months report
    we need to display 5 records(rows) as follows

    sample data
    TotalNoofBeds, NoofDaysInMonth, Month, Year, TotalNoOfAdmissions
    50, 31, Jan, 2018, 35
    50, 28, Feb, 2018, 45
    50, 31, Mar, 2018, 40
    50, 30, Apr, 2018, 40
    50, 31, May, 2018, 50



    TotalNoofBeds is count of total bedid in BedMaster table
    NoofDaysInMonth is no of days in the perticular month selected for each month
    Month is month name for each month selected
    Year is year for each month selected
    TotalNoOfAdmissions is count of ptnid from admissions table where Month(CrtDt) = selected month for each month selected


    Hope i explained well to understand the matter

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query to show days of months for selected months in date range

    and the difference to this thread is what?
    http://www.vbforums.com/showthread.p...ultiple-months
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: SQL Server Query to show days of months for selected months in date range

    i could not explained the issue in my previous thread so i tried to explain in this

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query to show days of months for selected months in date range

    And have you seen my last post in the other thread?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: SQL Server Query to show days of months for selected months in date range

    Quote Originally Posted by Zvoni View Post
    And have you seen my last post in the other thread?
    yes i tried that dose not solve it

  6. #6

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: SQL Server Query to show days of months for selected months in date range

    Quote Originally Posted by Zvoni View Post
    And have you seen my last post in the other thread?
    yes i tried that dose not solve it

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Server Query to show days of months for selected months in date range

    Will you ALWAYS have ADMISSIONS in EACH month? Or do we need to handle months with ZERO activity?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query to show days of months for selected months in date range

    Nevermind, that he ignores, that the TotalNoOfBeds can change troughout the year.
    In Jan 2018 they had 50 beds, but in Sep 2018 they've build ten more rooms with two beds each, resulting in having 70 beds from then on, but executing the query with what we have, it would give a result, that there were 70 beds in Jan 2018
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Server Query to show days of months for selected months in date range

    I'm not going to focus on the bad table designs that you all might have discovered. I'm going to simply assist with query syntax and when the results are examined hopefully holes in design like this will come to light.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: SQL Server Query to show days of months for selected months in date range

    Quote Originally Posted by yousufkhan73 View Post

    sample data
    TotalNoofBeds, NoofDaysInMonth, Month, Year, TotalNoOfAdmissions
    50, 31, Jan, 2018, 35
    50, 28, Feb, 2018, 45
    50, 31, Mar, 2018, 40
    50, 30, Apr, 2018, 40
    50, 31, May, 2018, 50
    your Data makes no sense

    should it not be per Room, you can rent a room per Day
    like all Hotels there are Room numbers, the amount of Beds in the Room is secondary.

    mabye you should think about you Table-Design

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: SQL Server Query to show days of months for selected months in date range

    IF it's a hotel, then I'd agree. If it's a hostel, then no, it's by bed. Same for a hospital too... Hopefully he knows more about his situation and domain than the rest of us do.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: SQL Server Query to show days of months for selected months in date range

    Quote Originally Posted by techgnome View Post
    IF it's a hotel, then I'd agree. If it's a hostel, then no, it's by bed. Same for a hospital too... Hopefully he knows more about his situation and domain than the rest of us do.

    -tg
    good point, your right

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width