May 29, 2019 at 4:54 pm
I have start_date, end_date and attended_day. I need to convert the attended_day e.g. "Wednesday" to a data e.g. 2019-05-29 using start_date and end_date as my date range.
May 29, 2019 at 5:24 pm
If start date is 1/1/2019 and end_date is 5/5/2019 and attended_day is Monday, which date should be used?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2019 at 6:22 pm
DECLARE @day varchar(9)
DECLARE @end_date date
DECLARE @start_date date
SET @start_date = '20190525'
SET @end_date = '20190531'
SET @day = 'Wednesday'
SELECT DATEADD(DAY, -DATEDIFF(DAY, day_number, @end_date) % 7, @end_date) AS date_you_want
FROM (
SELECT CASE @day WHEN 'Monday' THEN 0 WHEN 'Tuesday' THEN 1
WHEN 'Wednesday' THEN 2 WHEN 'Thursday' THEN 3 WHEN 'Friday' THEN 4
WHEN 'Saturday' THEN 5 WHEN 'Sunday' THEN 6 END AS day_number
) AS date_calc_1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 30, 2019 at 1:54 am
Hi Phil
Thank you very much for your time. The date range is always within a week, could be 3 days or 5 days but does not go beyond 7 days.
May 30, 2019 at 1:56 am
Hi ScottPletcher
Thank you very much for your time. Will test solution and get back to you.
May 30, 2019 at 3:43 am
SUPER!!!! Just tested the solution aligning it to my environment and it WORKED! Thank you very much ScottPletcher. Just a bit of background, we had a poorly designed table where attended_day was created as a multi-select based on days instead of data type date. Now we will be able to transform.
May 31, 2019 at 9:17 am
Using an auxilary date table can solve a huge number of date related questions.
Questions like:
What is the number of days between two days?
What is the number of working days between two days?
What is the number of working hours between two days?
What date is it 25 working days from now?
How many working days does each year between 2000 and 2020 have.
And plenty more date related questions.
What is the number of days between two days.
Your questions could be:
select * from tempdb.dbo.SP_Calendar WHERE dt >= '20190505' and dt < '20190515' and dayname = 'Wednesday'
If gives alle Wednesdays within the given period. (Zero if none, 1 if there is only one etc.).
An example of the table could be :
USE [tempdb]
/****** Object: Table [dbo].[SP_Calendar] Script Date: 05/31/2019 11:05:16 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Calendar]') AND type in (N'U'))
DROP TABLE [dbo].[SP_Calendar]
GO
CREATE TABLE [dbo].[SP_Calendar](
[dt] [datetime] NOT NULL,
[isWeekday] [bit] NULL,
[isHoliday] [bit] NULL,
[Holidayname] [varchar](200) NULL,
[Y] [smallint] NULL,
[FY] [smallint] NULL,
[Quarter] [tinyint] NULL,
[M] [tinyint] NULL,
[D] [tinyint] NULL,
[DW] [tinyint] NULL,
[monthname] [varchar](9) NULL,
[dayname] [varchar](9) NULL,
[W] [tinyint] NULL,
[spare1] [varchar](30) NULL,
[spare2] [varchar](30) NULL,
[spare3] [varchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Offcourse it takes some code to fill the table, but once filled, it can serve very many purposes. One row for each day should be created (suggestion from 1860 to 2150 would be adequate for most of the purposes.)
Most columns can be filled fairly automatic using SQL-server only.
For holidays like Christmas it is easy. For example for easter etc. 'tables' with the dates for a large number off years can be used.
I use the isholiday to denote that this is not a working day, I use the holidayname for the name of the day, some named holidays are still working days where I live.
For myself I can calculate the number of hours every employee works, for each year taking in account that some employees work parttime, for example not working on a wednesday or a friday.
All the calculations etc. can also be done with a script, but once the Auxilary table is available, most of the questions become far simpler with this table.
There might be additional columns usefull to the Auxilary table. So anybody with suggestions ?
Greetings,
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply