January 18, 2017 at 3:00 pm
I need to calculate two date fields in the same recordset with DATEDIFF function (could be DATEPART) Whereas
select EventID, EventName, EventScheduled - EventAdded = DaysBetween . EventScheduled and EventAdded are two datetime data types, EventScheduled will always happen AFTER EventAdded. But it the amount of days have any Saturdays or Sundays in them, then we need to not count them. So if EventScheduled is on a Monday, and EventAdded is on a Friday, then it would be just 1 day for DaysBetween, not 3. Having problems coordinating " BETWEEN DATEPART(WEEKDAY, EventAdded) NOT IN (6, 7) and
DATEPART(WEEKDAY, EventScheduled) NOT IN (6, 7) " into the equation.
January 18, 2017 at 3:18 pm
Zososql - Wednesday, January 18, 2017 3:00 PMI need to calculate two date fields in the same recordset with DATEDIFF function (could be DATEPART) Whereas
select EventID, EventName, EventScheduled - EventAdded = DaysBetween . EventScheduled and EventAdded are two datetime data types, EventScheduled will always happen AFTER EventAdded. But it the amount of days have any Saturdays or Sundays in them, then we need to not count them. So if EventScheduled is on a Monday, and EventAdded is on a Friday, then it would be just 1 day for DaysBetween, not 3. Having problems coordinating " BETWEEN DATEPART(WEEKDAY, EventAdded) NOT IN (6, 7) and
DATEPART(WEEKDAY, EventScheduled) NOT IN (6, 7) " into the equation.
There are a number of ways to go about this. Do you want the number of days inclusive of both the added and scheduled dates? Your example suggests that you don't and want to exclude only one of the dates. Also, have you thought about what you want returned if something is added on Saturday and scheduled for Sunday? According to the logic, you'd return 0 days.
We're going to need an environment to play in, so please provide DDL, sample data and expected output. See the link in my signature for details.
January 18, 2017 at 3:28 pm
A common approach is to reference a calendar table, especially if you need to exclude holidays as well as weekends. You just count the number of days that qualify.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2017 at 3:58 pm
TABLE dbo.events
(
EventID smallint NOT NULL,
Eventname nvarchar(100) not null,
Eventadded datetime NOT NULL,
EventScheduled datetime NULL,
EventDate datetime NULL,
Department varchar(50) NULL,
CONSTRAINT PK_EventID PRIMARY KEY CLUSTERED (EventID ASC)
);
select eventname, eventadded, eventscheduled - eventadded as DaysBetween from events is something I'm trying to work with
Since EventDate will always be on a weekday (school curriculum) , then we need to eliminate weekends in the eventscheduled - eventadded as DaysBetween calculation, so the difference between a monday and a tuesday would render 1 day. Eventadded could fall on a weekend date, so yes, we need to not count that weekend day.
HTH, thanks
Zo
January 19, 2017 at 5:29 am
Zososql - Wednesday, January 18, 2017 3:58 PMSince EventDate will always be on a weekday (school curriculum) , then we need to eliminate weekends in the eventscheduled - eventadded as DaysBetween calculation, so the difference between a monday and a tuesday would render 1 day. Eventadded could fall on a weekend date, so yes, we need to not count that weekend day.
HTH, thanks
Zo
If this is based on a school schedule, then you should really implement a calendar table like Drew suggested. A simple count of days between two dates isn't going to be sufficient because of all the days off during the week. If you aren't being told you're going to need it yet, you will be eventually. My advice is to plan for it now.
January 19, 2017 at 8:00 am
Ed Wagner - Thursday, January 19, 2017 5:29 AMZososql - Wednesday, January 18, 2017 3:58 PMSince EventDate will always be on a weekday (school curriculum) , then we need to eliminate weekends in the eventscheduled - eventadded as DaysBetween calculation, so the difference between a monday and a tuesday would render 1 day. Eventadded could fall on a weekend date, so yes, we need to not count that weekend day.
HTH, thanks
ZoIf this is based on a school schedule, then you should really implement a calendar table like Drew suggested. A simple count of days between two dates isn't going to be sufficient because of all the days off during the week. If you aren't being told you're going to need it yet, you will be eventually. My advice is to plan for it now.
>calendar table
Absolutely, thanks! It will also take into account holidays, which I know we will want to include in the equation.
January 19, 2017 at 8:28 am
Is this a 3rd party School Information System or home grown. I ask as having worked for a school district our SIS had a calendar table that indicated schools day (when students were in attendance) work days for staff (no students at school), holidays, snow days, weekends.<
January 19, 2017 at 8:38 am
Below is an example of how this might work. The calendar data that I entered reflects a typical US school calendar for January 2017, with holidays on January 2 and January 16.
I'm sure there are more efficient ways to populate a calendar table than typing all the dates into an insert, but this is just to show how the "school day difference" can be calculated using the calendar table.
CREATE TABLE dbo.events
(
EventID smallint NOT NULL,
Eventname nvarchar(100) not null,
Eventadded datetime NOT NULL,
EventScheduled datetime NULL,
EventDate datetime NULL,
Department varchar(50) NULL,
CONSTRAINT PK_EventID PRIMARY KEY CLUSTERED (EventID ASC)
);
GO
CREATE TABLE dbo.calendar (
CalendarDate datetime NOT NULL PRIMARY KEY,
IsSchoolDay BIT NOT NULL
);
GO
INSERT dbo.calendar VALUES
('2017-01-01', 0),
('2017-01-02', 0),
('2017-01-03', 1),
('2017-01-04', 1),
('2017-01-05', 1),
('2017-01-06', 1),
('2017-01-07', 0),
('2017-01-08', 0),
('2017-01-09', 1),
('2017-01-10', 1),
('2017-01-11', 1),
('2017-01-12', 1),
('2017-01-13', 1),
('2017-01-14', 0),
('2017-01-15', 0),
('2017-01-16', 0),
('2017-01-17', 1);
GO
INSERT dbo.events
VALUES
(2, 'test', '2017-01-16', NULL, '2017-01-17', NULL),
(3, 'test', '2017-01-15', NULL, '2017-01-17', NULL),
(4, 'test', '2017-01-14', NULL, '2017-01-17', NULL),
(5, 'test', '2017-01-13', NULL, '2017-01-17', NULL),
(6, 'test', '2017-01-12', NULL, '2017-01-17', NULL),
(7, 'test', '2017-01-11', NULL, '2017-01-17', NULL),
(8, 'test', '2017-01-10', NULL, '2017-01-17', NULL),
(9, 'test', '2017-01-09', NULL, '2017-01-17', NULL);
SELECT e.Eventadded,
DATENAME(w, e.Eventadded) as AddedDay,
(SELECT c1.IsSchoolDay FROM dbo.calendar c1 WHERE c1.CalendarDate = e.Eventadded) AS AddedOnSchoolDay,
e.EventDate,
DATENAME(w, e.EventDate) as EventDay,
(SELECT COUNT(*) FROM
dbo.calendar c
WHERE c.IsSchoolDay = 1
AND c.CalendarDate < e.EventDate
AND c.CalendarDate >= e.Eventadded) AS Diff
FROM dbo.events e;
January 20, 2017 at 10:18 am
Zososql - Wednesday, January 18, 2017 3:00 PMI need to calculate two date fields in the same recordset with DATEDIFF function (could be DATEPART) Whereas
select EventID, EventName, EventScheduled - EventAdded = DaysBetween . EventScheduled and EventAdded are two datetime data types, EventScheduled will always happen AFTER EventAdded. But it the amount of days have any Saturdays or Sundays in them, then we need to not count them. So if EventScheduled is on a Monday, and EventAdded is on a Friday, then it would be just 1 day for DaysBetween, not 3. Having problems coordinating " BETWEEN DATEPART(WEEKDAY, EventAdded) NOT IN (6, 7) and
DATEPART(WEEKDAY, EventScheduled) NOT IN (6, 7) " into the equation.
It would help if you post actual DDL instead of a narrative. It is also nice to have sample data that we can use to test the code were writing for you. Let us begin with that. Here is my guess
CREATE TABLE Events
(event_id CHAR(5) NOT NULL PRIMARY KEY,
event_name VARCHAR(20) NOT NULL,
event_start_date DATE NOT NULL,
event_creation_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
CHECK(event_creation_date <= event_creation_date),
..);
See how your narrative can be done with a check constraint? Also, look at the use of ISO 11179 data element naming rules.
SELECT event_id, event_name,
(event_start_date – event_creation_date) AS event_duration
FROM Events ;
>> event_start_date and event_creation_date are two DATE types, event_start_date will always happen AFTER event_creation_date. <<
>> But it the amount of days have any Saturdays or Sundays in them, then we need to not count them. <<
Read how to do a calendar table.
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.
The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL,
...);
INSERT INTO Calendar
VALUES
('2007-04-05', 42),
('2007-04-06', 43), -- Good Friday
('2007-04-07', 43),
('2007-04-08', 43), -- Easter Sunday
('2007-04-09', 44);
INSERT INTO Calendar VALUES ('2007-04-10', 45); -- Tuesday, back to work
To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:
SELECT (C2.julian_business_nbr - C1.julian_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';
This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company.
Having taught SQL for a few decades, I think your real problem is that you still think in terms of a procedural model you want to compute things, just like you did in COBOL or BASIC or FORTRAN. But SQL is a database language and works much better with tables and data. Why compute and recompute over and over and over things that you can put in a table and look up?
SELECT (C2.julian_business_nbr - C1.julian_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05' AND C2.cal_date = '2007-04-10';
This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company. Having taught SQL for a few decades, I think your real problem is that you still think in terms of a procedural model you want to compute things, just like you did in COBOL or BASIC or FORTRAN. But SQL is a database language and works much better with tables and data. Why compute and recompute over and over and over things that you can put in a table and look up?
Please post DDL and follow ANSI/ISO standards when asking for help.
January 20, 2017 at 4:07 pm
jcelko212 32090 - Friday, January 20, 2017 10:18 AMZososql - Wednesday, January 18, 2017 3:00 PMI need to calculate two date fields in the same recordset with DATEDIFF function (could be DATEPART) Whereas
select EventID, EventName, EventScheduled - EventAdded = DaysBetween . EventScheduled and EventAdded are two datetime data types, EventScheduled will always happen AFTER EventAdded. But it the amount of days have any Saturdays or Sundays in them, then we need to not count them. So if EventScheduled is on a Monday, and EventAdded is on a Friday, then it would be just 1 day for DaysBetween, not 3. Having problems coordinating " BETWEEN DATEPART(WEEKDAY, EventAdded) NOT IN (6, 7) and
DATEPART(WEEKDAY, EventScheduled) NOT IN (6, 7) " into the equation.It would help if you post actual DDL instead of a narrative. It is also nice to have sample data that we can use to test the code were writing for you. Let us begin with that. Here is my guess
CREATE TABLE Events
(event_id CHAR(5) NOT NULL PRIMARY KEY,
event_name VARCHAR(20) NOT NULL,
event_start_date DATE NOT NULL,
event_creation_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
CHECK(event_creation_date <= event_creation_date),
..);See how your narrative can be done with a check constraint? Also, look at the use of ISO 11179 data element naming rules.
SELECT event_id, event_name,
(event_start_date – event_creation_date) AS event_duration
FROM Events ;>> event_start_date and event_creation_date are two DATE types, event_start_date will always happen AFTER event_creation_date. <<
>> But it the amount of days have any Saturdays or Sundays in them, then we need to not count them. <<
Read how to do a calendar table.
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.
The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL,
...);INSERT INTO Calendar
VALUES
('2007-04-05', 42),
('2007-04-06', 43), -- Good Friday
('2007-04-07', 43),
('2007-04-08', 43), -- Easter Sunday
('2007-04-09', 44);
INSERT INTO Calendar VALUES ('2007-04-10', 45); -- Tuesday, back to workTo compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:
SELECT (C2.julian_business_nbr - C1.julian_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company.
Having taught SQL for a few decades, I think your real problem is that you still think in terms of a procedural model you want to compute things, just like you did in COBOL or BASIC or FORTRAN. But SQL is a database language and works much better with tables and data. Why compute and recompute over and over and over things that you can put in a table and look up?
SELECT (C2.julian_business_nbr - C1.julian_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05' AND C2.cal_date = '2007-04-10';This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company. Having taught SQL for a few decades, I think your real problem is that you still think in terms of a procedural model you want to compute things, just like you did in COBOL or BASIC or FORTRAN. But SQL is a database language and works much better with tables and data. Why compute and recompute over and over and over things that you can put in a table and look up?
Thanks, this is very helpful in another set of equations I will be working with.
Zo
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply