May 11, 2017 at 2:27 pm
I have daily dates for a month with number of appointments made per day.
Table: DailyNumOfAppointments
DailyDate date
NumberOfAppointments int
I want a weekly report for a given month as below, as an example for Jan 2017
01/01/2017 to 01/07/2017 || 50 Appointments
01/08/2017 to 01/14/2017 || 10 Appointments
01/15/2017 to 01/21/2017 || 20 Appointments
01/22/2017 to 01/28/2017 || 30 Appointments
01/29/2017 to 01/31/2017 || 55 Appointments
is it possible to write in a MS SQL or use any reporting tool ?
May 11, 2017 at 2:48 pm
vizagboy - Thursday, May 11, 2017 2:27 PMI have daily dates for a month with number of appointments made per day.
Table: DailyNumOfAppointmentsDailyDate date
NumberOfAppointments intI want a weekly report for a given month as below, as an example for Jan 2017
01/01/2017 to 01/07/2017 || 50 Appointments
01/08/2017 to 01/14/2017 || 10 Appointments
01/15/2017 to 01/21/2017 || 20 Appointments
01/22/2017 to 01/28/2017 || 30 Appointments
01/29/2017 to 01/31/2017 || 55 Appointmentsis it possible to write in a MS SQL or use any reporting tool ?
Use DATEPART to get the week number, then group on that and do a count,
May 11, 2017 at 2:54 pm
I got the weekly count, Thank you.
but how to get the week start date & week end date for a month ?
May 11, 2017 at 3:00 pm
A few points need to be made.
First, if you could post DDL for your table (the CREATE TABLE statement), sample data (in the form of INSERT statements), and expected results that would be fantastic.
http://www.sqlservercentral.com/blogs/spaghettidba/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ gives a nice guide to that.
I think I have a good idea what you're trying to do, but that makes it easier for people here to see exactly what you want and test their solutions before posting them.
Second, if you want every month to be handled like your example, where days 1-7 are the first week, days 8-14 are the second week, and so on, then the DATEPART(WEEK,...) will not do what you want.
With that particular way of counting weeks, the first 7 days of the month can (and even usually will) be split between different "weeks".
Third, is your table always guaranteed to have a row for every day of the month, even if there are no appointments, or could there be gaps in the data, where a day with no appointments simply isn't entered in to the table?
If you always have a row for every day, then the solution is very simple. It's a little less simple if you have gaps, but it's still not too bad.
Cheers!
May 11, 2017 at 7:53 pm
vizagboy - Thursday, May 11, 2017 2:54 PMhow to get the week start date & week end date for a month ?
please look up the ISO 8601 standards. You will find that there is a week date format that looks like"yyyyW[0-5][[0-9]-[1-7]" which which begins with the year uses the W as a spacing token, followed by the week with in the year (52 or 53), followed by the day with in each week (1= Monday). This date format is popular in the Nordic countries probably has something to do with those insanely long nights. If you put this in your calendar table, you can quickly write a simple query to find the calendar date and the weekly dates; just look at substrings in the display format.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 12, 2017 at 2:14 am
Join to a query or to a table:
SELECT
res.DateFrom,
res.DateTo
FROM (SELECT StartOfYear = DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)) d
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) m (n)
CROSS APPLY (SELECT StartOfMonth = DATEADD(MONTH,m.n,StartOfYear)) som
CROSS APPLY (VALUES (0),(7),(14),(21),(28)) wk (w)
CROSS APPLY (
SELECT
DateFrom = DATEADD(DAY,wk.w,som.StartOfMonth),
DateTo = CASE
WHEN wk.w = 28 THEN DATEADD(DAY,-1,DATEADD(MONTH,1,som.StartOfMonth))
ELSE DATEADD(DAY,wk.w+6,som.StartOfMonth) END
) res
WHERE MONTH(res.DateFrom) = MONTH(res.DateTo) -- fixes leap year
ORDER BY 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 12, 2017 at 9:29 am
Thanks you all for your replies. Below is the scenario & DDL (work in progress..)
To answer Jacob Wilkins questions,
I'm posting my table scripts & and i want like this only (days 1-7 are the first week, days 8-14 are the second week, and so on).
and lastly, all centers will not define timeslots for all the days.
1. I need to display weekly available slots, appointments scheduled per week for a Center for a given past month.
2. Centers will define the available slots as below.
a) Center Day of week -- each slot frequency can be (15 mins, 30 mins, 45 mins, 60 mins, 90mins)
b) Center Date Setting - On a specific date, they will define center availability, frequency as above, so if a specific date is defined it will override Day of week setting.
c) center Date Exclusion -- On a specic date, Center is not ready to take any appointments, so if any specific date is defined, above 2 conditions will be overridden.
Table :
CREATE TABLE [dbo].[Center](
[CenterID] [tinyint] NOT NULL,
[CenterGUID] [uniqueidentifier] NOT NULL
)
Table : DayOfWeek
CREATE TABLE [dbo].[DayOfWeek](
[DowID] [tinyint] NOT NULL,
[ShortDescription] [nvarchar](3) NOT NULL,
[LongDescription] [nvarchar](20) NULL,
[CreateDate] [smalldatetime] NOT NULL,
[ModifyDate] [smalldatetime] NULL,
CONSTRAINT [PK_DayOfWeek] PRIMARY KEY CLUSTERED
(
[DowID] ASC
)
Table : CenterDOWSetting
CREATE TABLE [dbo].[CenterDowSetting](
[CenterDowSettingID] [int] IDENTITY(1,1) NOT NULL,
[CenterGUID] [uniqueidentifier] NOT NULL,
[DowID] [tinyint] NOT NULL,
[CreateBy] [int] NOT NULL,
[CreateDate] [smalldatetime] NOT NULL,
[ModifyBy] [int] NULL,
[ModifyDate] [smalldatetime] NULL,
CONSTRAINT [PK_CenterDowSetting] PRIMARY KEY CLUSTERED
(
[CenterDowSettingID] ASC
)
Table : CenterDOWSettingDetail
CREATE TABLE [dbo].[CenterDowSettingDetail](
[CenterDowSettingDetailID] [int] IDENTITY(1,1) NOT NULL,
[CenterDowSettingID] [int] NOT NULL,
[StartTime] [time](0) NOT NULL,
[EndTime] [time](0) NOT NULL,
[TimeSlotDuration] [smallint] NOT NULL,
[TimeSlotMaxEvents] [int] NOT NULL,
[AppointmentTypeId] [smallint] NULL,
[CreateBy] [int] NOT NULL,
[CreateDate] [smalldatetime] NOT NULL,
[ModifyBy] [int] NULL,
[ModifyDate] [smalldatetime] NULL,
CONSTRAINT [PK_CenterDowSettingDetail] PRIMARY KEY CLUSTERED
(
[CenterDowSettingDetailID] ASC
)
Table : CenterDateSetting
CREATE TABLE [dbo].[CenterDateSetting](
[CenterDateSettingID] [int] IDENTITY(1,1) NOT NULL,
[CenterGUID] [uniqueidentifier] NOT NULL,
[CalendarDate] [date] NOT NULL,
[CreateBy] [int] NOT NULL,
[CreateDate] [smalldatetime] NOT NULL,
[ModifyBy] [int] NULL,
[ModifyDate] [smalldatetime] NULL,
CONSTRAINT [PK_CenterDateSetting] PRIMARY KEY CLUSTERED
(
[CenterDateSettingID] ASC
)
Table : CenterDateSettingDetail
CREATE TABLE [dbo].[CenterDateSettingDetail](
[CenterDateSettingDetailID] [int] IDENTITY(1,1) NOT NULL,
[CenterDateSettingID] [int] NOT NULL,
[StartTime] [time](0) NOT NULL,
[EndTime] [time](0) NOT NULL,
[TimeSlotDuration] [smallint] NOT NULL,
[TimeSlotMaxEvents] [int] NOT NULL,
[AppointmentTypeID] [smallint] NULL,
[CreateBy] [int] NOT NULL,
[CreateDate] [smalldatetime] NOT NULL,
[ModifyBy] [int] NULL,
[ModifyDate] [smalldatetime] NULL,
CONSTRAINT [PK_CenterDateSettingDetail] PRIMARY KEY CLUSTERED
(
[CenterDateSettingDetailID] ASC
)
Table : CenterDateExclusion
CREATE TABLE [dbo].[CenterDateExclusionSetting](
[CenterDateExclusionSettingID] [int] IDENTITY(1,1) NOT NULL,
[CenterGUID] [uniqueidentifier] NOT NULL,
[CalendarDate] [date] NOT NULL,
[GroupId] [int] NULL,
[CreateBy] [int] NOT NULL,
[CreateDate] [smalldatetime] NOT NULL,
[ModifyBy] [int] NULL,
[ModifyDate] [smalldatetime] NULL,
CONSTRAINT [PK_CenterDateExclusionSetting] PRIMARY KEY CLUSTERED
(
[CenterDateExclusionSettingID] ASC
)
On a day there can be more than on setting in CenterDOWSetting & CenterDOWSettingDetail, like
Dowid 1, 10:00 to 12:00 TimeSlotDuration= 30 mins, TimeSlotMaxEvents = 2
Dowid 1, 14:00 to 17:00 TimeSlotDuration= 60 mins, TimeSlotMaxEvents = 5 etc
Below is DDL which is under work in progress...
declare @date datetime
set @date = '20170101';
with DaysInMonth as (
select @date as DailyDate
union all
select dateadd(dd,1,DailyDate) as DailyDate
from DaysInMonth
where month(DailyDate) = month(@Date)),
--,
-- select * from DaysInMonth
cte1 AS
(
SELECT
DM.DailyDate, DATEPART ( dw , DM.DailyDate) as DOW,
CDW.DOWID as DOW_DOWID,
--CDWL.StartTime,CDWL.EndTime,
--CDWL.TimeSlotDuration,CDWL.TimeslotMaxEvents,
--DATEDIFF(minute, CDWL.StartTime,CDWL.EndTime) as Hours_Differences,
CASE WHEN CDE.CalendarDate IS NOT NULL
THEN
'0'
WHEN CDS.CalendarDate IS NOT NULL
THEN
SUM((DATEDIFF(minute, CDSD.StartTime,CDSD.EndTime)/CDSD.TimeSlotDuration))
ELSE
SUM((DATEDIFF(minute, CDWL.StartTime,CDWL.EndTime)/CDWL.TimeSlotDuration))
END as Number_of_Slots_PerDay, -- Total slots per day
CASE WHEN CDE.CalendarDate IS NOT NULL
THEN
'0'
WHEN CDS.CalendarDate IS NOT NULL
THEN
SUM(((DATEDIFF(minute, CDSD.StartTime,CDSD.EndTime)/CDSD.TimeSlotDuration)*CDSD.TimeslotMaxEvents))
ELSE
-- SUM(((DATEDIFF(minute, CDWL.StartTime,CDWL.EndTime)/CDWL.TimeSlotDuration)*CDWL.TimeslotMaxEvents))
CDR.TotalSlotsPerDowID -- always take DOW settings from CenterDowReport
END as Total_Number_of_Slots_PerDay -- Total slots per day including TimeslotMaxEvents
--COUNT (A.AppointmentDate) Number_of_Appointments
--SUM((DATEDIFF(minute, CDWL.StartTime,CDWL.EndTime)/CDWL.TimeSlotDuration)) as Number_of_Slots,
--SUM(((DATEDIFF(minute, CDWL.StartTime,CDWL.EndTime)/CDWL.TimeSlotDuration)*CDWL.TimeslotMaxEvents)) as Total_Number_of_Slots,
--CDE.CalendarDate as CenterCloseDate,
--CDS.CalendarDate as CenterDateSettingByDate, CDR.DowID,CDR.StartDate,CDR.EndDate, CDR.TotalSlotsPerDowID
FROM
DaysInMonth DM
LEFT join CenterDowSetting CDW on CDW.DOWID = DATEPART ( dw , DM.DailyDate) and CDW.CenterGUID = 'CF715374-B6B0-421F-B184-026AEBBC0BA1'
LEFT join CenterDowSettingDetail CDWL ON CDWL.CenterDOWSettingID = CDW.CenterDOWSettingID
LEFT join CenterDateExclusionSetting CDE ON CDE.CenterGUID = CDW.CenterGUID AND CDE.CalendarDate = DM.DailyDate
LEFT join CenterDateSetting CDS ON CDS.CenterGUID = CDW.CenterGUID AND CDS.CalendarDate = DM.DailyDate
LEFT join CenterDateSettingDetail CDSD ON CDSD.CenterDateSettingID = CDS.CenterDateSettingID
LEFT join CenterDowReport CDR ON CDR.DowID = CDW.DOWID AND CDR.CenterGUID = CDW.CenterGUID
and (DM.DailyDate between CDR.StartDate and ISNULL(CDR.EndDate,Getdate()))
--LEFT JOIN Appointment A ON A.CenterGUID = 'CF715374-B6B0-421F-B184-026AEBBC0BA1' AND cast(A.AppointmentDate as date) = cast(DM.DailyDate as date)
--AND ( (DM.DailyDate >= CDR.StartDate OR CDR.StartDate IS NULL)
-- AND (DM.DailyDate < DATEADD(day,1,CDR.EndDate) OR CDR.EndDate IS NULL)
-- )
--AND (
-- (DM.DailyDate >= CDR.StartDate and DM.DailyDate < DATEADD(day,1,CDR.EndDate))
-- OR (CDR.StartDate IS NULL AND CDR.EndDate IS NULL)
-- OR (CDR.StartDate IS NULL AND DM.DailyDate < DATEADD(day,1,CDR.EndDate))
-- OR (CDR.EndDate IS NULL AND DM.DailyDate >= CDR.StartDate)
-- )
--LEFT join CenterDowReport CDR ON CDR.DowID = CDW.DOWID AND CDR.CenterGUID = CDW.CenterGUID and (DM.DailyDate between dateadd(day,datediff(day,1,CDR.StartDate),0) and ISNULL(dateadd(day,datediff(day,1,CDR.EndDate),0),dateadd(day,datediff(day,1,GETDATE()),0)))
WHERE month(DM.DailyDate) = month(@Date) --and AppointmentDate = '01/27/2017'
Group by DM.DailyDate, CDW.DowID,CDE.CalendarDate, CDS.CalendarDate,CDR.DowID,CDR.StartDate,CDR.EndDate, CDR.TotalSlotsPerDowID
--order by DailyDate
)
SELECT
----DOW_DOWID,Number_of_Slots_PerDay,TOtal_Number_of_Slots_PerDay,Number_of_Slots,Total_Number_of_Slots,CenterCloseDate,CenterDateSettingByDate,DowID,StartDate,EndDate,
DATEPART(wk, DailyDate) WeekNumber,
--cte1.DailyDate, DOW, DOW_DOWID,
count(Number_of_Slots_PerDay),count(Total_Number_of_Slots_PerDay), Count (A.AppointmentDate) Number_of_Appointments
--sum(TotalSlotsPerDowID )
FROM cte1
LEFT JOIN Appointment A ON cast(A.AppointmentDate as date) = cast(DailyDate as date) --AND A.CenterGUID = 'CF715374-B6B0-421F-B184-026AEBBC0BA1'
--Group by cte1.DailyDate, DOW, DOW_DOWID,Number_of_Slots_PerDay,Total_Number_of_Slots_PerDay,
GROUP BY DATEPART(wk, DailyDate)
I just introduced CenterDOWReport table in my design as Center can keep on changing their DOW settings.
Table : CenterDOWReport, This table is used to store old date settings when a change is made on CenterDOWSetting & CenterDOWSettingDetail. Initially on 01/01/2017, if Monday is defined as 10:00 to 13:00, TimeSlotDuration= 30 mins, TimeSlotMaxEvents = 2, this table will have any entry of startdate = 01/01/2017, EndDate Null, TimeSlotsperDOWID = 12. On 01/15/2017, Monday setting is updated to 13:00 to 14:00, TimeSlotDuration= 60 mins, TimeSlotMaxEvents = 2 above entry will have end date = 01/14/2017 and new row will be added as below
startdate = 01/15/2017, EndDate Null, TimeSlotsperDOWID = 2
CREATE TABLE [dbo].[CenterDowReport](
[CenterDowReportID] [int] IDENTITY(1,1) NOT NULL,
[DowID] [int] NOT NULL,
[CenterGUID] [uniqueidentifier] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NULL,
[TotalSlotsPerDowID] [int] NOT NULL,
[CreateDate] [smalldatetime] NOT NULL,
[CreateBy] [int] NOT NULL,
[ModifyDate] [smalldatetime] NULL,
[ModifyBy] [int] NULL,
CONSTRAINT [PK_CenterDowReport] PRIMARY KEY CLUSTERED
(
[CenterDowReportID] ASC
)
May 12, 2017 at 9:50 am
I can't help thinking you're going to miss an opportunity to make this exercise vastly simpler:ALTER VIEW dbo.vw_WeekMonths ASSELECT
DateMonth = m.n+1,
WeekNumber = ROW_NUMBER() OVER(PARTITION BY m.n ORDER BY DateFrom),
res.DateFrom,
res.DateTo
FROM (SELECT StartOfYear = DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)) soy
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) m (n)
CROSS APPLY (SELECT StartOfMonth = DATEADD(MONTH,m.n,soy.StartOfYear)) som
CROSS APPLY (VALUES (0),(7),(14),(21),(28)) wk (w)
CROSS APPLY (
SELECT
DateFrom = DATEADD(DAY,wk.w,som.StartOfMonth),
DateTo = CASE
WHEN wk.w = 28 THEN DATEADD(DAY,-1,DATEADD(MONTH,1,som.StartOfMonth))
ELSE DATEADD(DAY,wk.w+6,som.StartOfMonth) END
) res
WHERE MONTH(res.DateFrom) = MONTH(res.DateTo) -- fixes leap year
GO
SELECT DateMonth, WeekNumber, DateFrom, DateTo
FROM dbo.vw_WeekMonths
ORDER BY DateMonth, WeekNumber
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 12, 2017 at 3:07 pm
I'm posting my table scripts & and i want like this only (days 1-7 are the first week, days 8-14 are the second week, and so on).
and lastly, all centers will not define timeslots for all the days.
1. I need to display weekly available slots, appointments scheduled per week for a Center for a given past month.
2. Centers will define the available slots as below.
a) Center Day of week -- each slot frequency can be (15 mins, 30 mins, 45 mins, 60 mins, 90mins)
b) Center Date Setting - On a specific date, they will define center availability, frequency as above, so if a specific date is defined it will override Day of week setting.
c) center Date Exclusion -- On a specic date, Center is not ready to take any appointments, so if any specific date is defined, above 2 conditions will be overridden.
Can you throw out this mess? None of your DDL makes any sense whatsoever. It was clearly done by some idiot with no data modeling experience. Let’s just start at the top:
You name to table with a singular name; you only have one center? No, probably not. You then made its identifier a numeric (TINYINT), but you can’t do math on it. If you had a basic data modeling course, you would have used a string. And that string would have some constraints in the form of a regular expression.
A GUID Is used to reference things outside of the schema. The “G†stands for global (or if you use UUID, The “U†stands for “universalâ€). But you want to use them for pointer chains within your own schema. We don’t do that in RDBMS.
The old Sybase IDENTITY Is a count of physical record insertion attempts, based on the old original UNIX tape file system. It is totally nonrelational and has no place in a valid schema.
Things like “foobar_type_id†not only violate ISO 11179 naming rules, but make no sense and themselves. This is attribute a type? Or an identifier? What is your blood type identifier? See how silly it is?
The day of the week is not an entity and doesn’t belong in a table at all. It’s a unit of temporal measure. That means is the value of an attribute! Variable length descriptions of the unit of measure make no sense. What with the description of a meter be? Or a liter? Or Candela?
But even worse you put in the creation and update timestamps in the table that’s being audited. This will send you to jail. What happens when a row is deleted? All of this audit information disappears with the row. This is why it is not legal, and you get an expert like me testifying against you in court if you write code like this.
You have start and end times (which are not part of a timestamp!), Then you have the duration of the time slot. The main reason we build databases back in the olden days was to remove redundancy from our data. But you want to increase it by explicitly putting materialized computed columns in a table. Perhaps even worse. I see start and end times being stored as tiny integers in the language that actually has a TIME data type. Also, if you taken that basic data modeling course, you would know there’s no such thing as a generic start and end time; it has to belong to a particular entity or event.
Your DDL also had no references to the other tables. That’s why we call it RDBMS; the “R†stands for referential or references.
Your making a very common beginner’s mistake with SQL. You’re trying to format data for display in the database and not in the presentation layer. For example, that output you gave:
Dowid 1, 10:00 to 12:00 TimeSlotDuration= 30 mins, TimeSlotMaxEvents = 2
Should have been a row coming back from a query that looks like this:
‘2017-01-01 10:00:00’, ‘2017-01-01 12:00:00’, 30, 2
That crap about a day of the week identifier would have been replaced by an exact time of the event. Your timeslots would’ve been guaranteed not go down to seconds by a check constraint in the table that only allows durations according to your business rules.
The presentation layer Would have added the extra text, etc. and would never, never be part of the database layer
I think you should start over, and find someone who knows how to do a proper data model to help you.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply