-
Aug 16th, 2018, 04:27 AM
#1
Thread Starter
Member
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
-
Aug 16th, 2018, 04:29 AM
#2
Re: SQL Server Query to show days of months for selected months in date range
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
-
Aug 16th, 2018, 04:34 AM
#3
Thread Starter
Member
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
-
Aug 16th, 2018, 04:37 AM
#4
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
-
Aug 16th, 2018, 04:54 AM
#5
Thread Starter
Member
Re: SQL Server Query to show days of months for selected months in date range
Originally Posted by Zvoni
And have you seen my last post in the other thread?
yes i tried that dose not solve it
-
Aug 16th, 2018, 04:55 AM
#6
Thread Starter
Member
Re: SQL Server Query to show days of months for selected months in date range
Originally Posted by Zvoni
And have you seen my last post in the other thread?
yes i tried that dose not solve it
-
Aug 16th, 2018, 08:27 AM
#7
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?
-
Aug 16th, 2018, 08:55 AM
#8
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
-
Aug 16th, 2018, 08:57 AM
#9
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.
-
Aug 16th, 2018, 12:13 PM
#10
Re: SQL Server Query to show days of months for selected months in date range
Originally Posted by yousufkhan73
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.
-
Aug 16th, 2018, 12:21 PM
#11
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
-
Aug 16th, 2018, 12:25 PM
#12
Re: SQL Server Query to show days of months for selected months in date range
Originally Posted by techgnome
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|