April 6, 2018 at 8:35 am
Good morning/afternoon/evening every one 🙂
I have been asked to provide a column on a (UK specific) calendar table that marks the equivalent week/last week/quarter last year so we can report on how we are doing this year as opposed to last year.
We want this as in the UK we use ISO week numbering, with Monday being the start of the week, but PowerBI uses the US convention of starting weeks on a Sunday.
Here is the code that creates the base table in a database called calendar WARNING: This script drops any database called Calendar!
SET NOCOUNT ON
GO
USE master
GO
--pinched from https://www.experts-exchange.com/articles/12267/SQL-Server-Calendar-Table.html and changed for the UK
--Functions Developed by Paul Cresham © 2004 Orchard Information Systems Limited
IF EXISTS (SELECT name FROM sys.databases WHERE name='calendar')
DROP DATABASE calendar
GO
CREATE DATABASE calendar
GO
USE calendar
GO
-- 1=Sunday to 7=Saturday
SET DATEFIRST 1
SET LANGUAGE British;
SET DATEFORMAT DMY
GO
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Easter]')
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION dbo.Easter
GO
CREATE function dbo.Easter (@Y smallint)
returns datetime
as
begin
/*
EASTER SUNDAY FUNCTION
Calculates date of Easter Sunday based on the Carter algorithm.
This one works for 1900-2099. More complex algorithms exist,
but are deemed unnecessary for this application (for efficiency).
Sourced from National Maritime Museum - http://www.nmm.ac.uk/
Algorithm instructions from the Museum are included as comments.
*/
declare @D tinyint, @E tinyint, @Q tinyint
declare @ret datetime
-- Calculate D = 225 - 11(Y MOD 19)
select @D = 225 - (11 * (@Y % 19))
-- If D is greater than 50 then subtract multiples of 30 until the
-- resulting new value of D is less than 51
while @D > 50
select @D = @D - 30
-- If D is greater than 48 subtract 1 from it
if @D > 48
select @D = @D - 1
-- Calculate E = (Y + (Y/4) + D + 1) MOD 7. (NB Integer part of (Y/4))
select @E = (@Y + floor(@Y/4) + @D + 1) % 7
-- Calculate Q = D + 7 - E
select @Q = @D + 7 - @E
-- If Q is less than 32 then Easter is in March. If Q is greater than 31,
-- then Q-31 is its date in April.
if @Q < 32
select @ret = convert(datetime,'03/'+convert(varchar,@Q)+'/'+convert(varchar,@Y))
else
select @ret = convert(datetime,'04/'+convert(varchar,(@Q-31))+'/'+convert(varchar,@Y))
return(@ret)
end
GO
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[BankHolidays2]')
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION dbo.BankHolidays2
GO
CREATE function dbo.BankHolidays2 (@YearIn smallint)
returns @ret table (BHDate DATETIME, HolidayName VARCHAR(50))
as
begin
/*
ENGLAND AND WALES BANK HOLIDAY DATES 1978 - 2099
AUTOMATIC CALCULATION
Restricted by Easter Sunday algorithm to 1900 - 2099
Can be set to work for any dates between 1900 - 2099 but:
- Current Bank Holidays were introduced in 1971 by the
Banking and Financial Dealings Act 1971, so date before
then will be inaccurate
- New Year's Day was introduced in 1974*
- Early May Bank Holiday was introduced in 1978*
- The script therefore does not account for bank holiday dates
introduced by the Bank Holiday Act of 1871 which ran until 1970.
*not included in Act so declared by Royal Proclamation each year
Developed by Paul Cresham
© 2004 Orchard Information Systems Limited
SELECT * FROM [dbo].[BankHolidays2]('2017')
*/
declare @Holiday datetime
declare @Saturday tinyint, @Sunday tinyint, @monday tinyint
select @Sunday = 8 - @@datefirst
select @Saturday = ( (@Sunday+5) % 7) + 1
select @monday = ( (@Sunday) % 7) + 1
-- No 1: New Year's Day
-- 1 January or next weekday if it falls on a Saturday or Sunday
select @Holiday = convert(datetime,'01/01/'+convert(varchar,@YearIn))
while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)
select @Holiday = dateadd(day, 1, @Holiday)
insert @ret values (@Holiday, 'New Year''s Day')
-- No 2: Good Friday
-- 2 days before Easter Sunday
select @Holiday = dbo.Easter(@YearIn)
select @Holiday = dateadd(day,-2,@Holiday)
insert @ret values (@Holiday,'Good Friday' )
-- No 3: Easter Monday
-- 1 day after Easter Sunday (or 3 days after date currently held for Good Friday)
select @Holiday = dateadd(day,3,@Holiday)
insert @ret values (@Holiday, 'Easter Monday')
-- No 4: May Day
-- 1st Monday in May
select @Holiday = convert(datetime,'05/01/'+convert(varchar,@YearIn))
while datepart(dw,@Holiday) != @monday
select @Holiday = dateadd(day,1,@Holiday)
insert @ret values (@Holiday, 'May Day')
-- No 5: Spring Bank Holiday
-- Last Monday in May
select @Holiday = convert(datetime,'05/31/'+convert(varchar,@YearIn))
while datepart(dw,@Holiday) != @monday
select @Holiday = dateadd(day,-1,@Holiday)
insert @ret values (@Holiday, 'Spring Bank Holiday')
-- No 6: Late Summer Bank Holiday
-- Last Monday in August
select @Holiday = convert(datetime,'08/31/'+convert(varchar,@YearIn))
while datepart(dw,@Holiday) != @monday
select @Holiday = dateadd(day,-1,@Holiday)
insert @ret values (@Holiday, 'Late Summer Bank Holiday')
-- No 7: Christmas Day
-- 25 December, or next Monday in lieu if falls on Saturday or Sunday
select @Holiday = convert(datetime,'12/25/'+convert(varchar,@YearIn))
while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)
select @Holiday = dateadd(day,1,@Holiday)
insert @ret values (@Holiday, 'Christmas Day')
-- No 8: Boxing Day
-- 26 December, or next Monday in lieu if this falls on a Saturday or
-- Tuesday if it falls on a Sunday
select @Holiday = dateadd(day,1,@Holiday)
while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)
select @Holiday = dateadd(day,1,@Holiday)
insert @ret values (@Holiday, 'Boxing Day')
return
end
GO
IF EXISTS(SELECT * FROM sys.tables WHERE name='calendar')
DROP TABLE calendar
GO
CREATE TABLE calendar (
PKDate date NOT NULL PRIMARY KEY CLUSTERED,
-- Years
year smallint,
-- Quarters
quarter tinyint,
quarter_desc varchar(10),
-- Months
month tinyint,
month_name_long varchar(30),
month_name_short varchar(10),
-- Weeks
week_in_year tinyint,
week_in_month tinyint,
-- Days
day_in_year smallint,
day_in_week tinyint, -- The first of the month
day_in_month tinyint,
dmy_name_long varchar(30),
dmy_name_long_with_suffix varchar(30),
day_name_long varchar(10),
day_name_short varchar(10),
-- Continuous Y/M/D, starts with the first day = 1 and keeps going. Used for various dateadd functions.
--continuous_year tinyint,
--continuous_quarter smallint,
--continuous_month smallint,
--continuous_week smallint,
--continuous_day int,
-- Custom
description varchar(100),
is_weekend tinyint, -- Tinyint and not bit so you can add the 1's.
is_holiday tinyint, -- Tinyint and not bit so you can add the 1's.
is_workday TINYINT) -- Tinyint and not bit so you can add the 1's.
-- is_event tinyint) -- Used to indicate any special event days.
GO
-- Create the table, with dates ranging from 2010 to 2020. Change to suit your needs.
Declare @dt_start date = '2010-01-01', @dt_end date = '2065-05-22', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end)
WHILE @i <= @total_days
begin
INSERT INTO calendar (PKDate)
SELECT CAST(DATEADD(d, @i, @dt_start) as DATE)
SET @i = @i + 1
end
-- These values can be generated with single SQL Server functions
UPDATE calendar
SET
year = YEAR(PKDate),
quarter = DATEPART(q, PKDate),
month = DATEPART(m, PKDate),
week_in_year = DATEPART(isowk, PKDate),
day_in_year = DATEPART(dy, PKDate),
day_in_week = DATEPART(Weekday, PKDate),
day_in_month = DATEPART(d, PKDate),
day_name_long = datename(weekday, PKDate)
-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE calendar
SET
is_weekend = CASE DATEPART(weekday, PKDate) WHEN 6 THEN 1 WHEN 7 THEN 1 ELSE 0 END,
quarter_desc = 'Q' + CAST(quarter as char(1)) + ' ' + CAST(year as char(4)),
month_name_long = DATENAME(m, PKDate),
dmy_name_long = CAST(day_in_month as varchar(2)) + ' ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4)),
day_name_short = LEFT(datename(weekday, PKDate),3)
UPDATE calendar
SET week_in_month =
CASE
WHEN day_in_month BETWEEN 1 AND 7 THEN 1
WHEN day_in_month BETWEEN 8 AND 14 THEN 2
WHEN day_in_month BETWEEN 15 AND 21 THEN 3
WHEN day_in_month BETWEEN 22 AND 28 THEN 4
ELSE 5
END
-- Month name: The first three letters of the month.
UPDATE calendar
SET month_name_short = LEFT(DATENAME(month, PKDate),3)
-- Fancy schmancy full date column, which adds the suffix st, nd, rd, or th to the day.
UPDATE calendar
SET dmy_name_long_with_suffix = CASE RIGHT(CAST(day_in_month as varchar(2)), 2)
WHEN '1' THEN CAST(day_in_month as varchar(2)) + 'st ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
WHEN '21' THEN CAST(day_in_month as varchar(2)) + 'st ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
WHEN '31' THEN CAST(day_in_month as varchar(2)) + 'st ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
WHEN '2' THEN CAST(day_in_month as varchar(2)) + 'nd ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
WHEN '22' THEN CAST(day_in_month as varchar(2)) + 'nd ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
WHEN '3' THEN CAST(day_in_month as varchar(2)) + 'rd ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
WHEN '13' THEN CAST(day_in_month as varchar(2)) + 'th ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
WHEN '23' THEN CAST(day_in_month as varchar(2)) + 'rd ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4))
ELSE CAST(day_in_month as varchar(2)) + 'th ' + DATENAME(m, PKDate) + ', ' + CAST(year as CHAR(4)) END
-- CONTINUOUS YEARS
--UPDATE calendar
--SET
-- continuous_year = DATEDIFF(year, @dt_start, PKDate) + 1,
-- continuous_quarter = DATEDIFF(quarter, @dt_start, PKDate) + 1,
-- continuous_month = DATEDIFF(month, @dt_start, PKDate) + 1,
-- continuous_week = DATEDIFF(week, @dt_start, PKDATE) + 1,
-- continuous_day = DATEDIFF(day, @dt_start, PKDATE) + 1
-- HOLIDAYS
-- HOLIDAYS
-- HOLIDAYS
-- Fixed holidays which are always on the same date of every year, Monday through Friday.
-- HOLIDAYS WHERE THE DAY IS ALWAYS THE SAME
-- Fixed Holidays, Mondays through Fridays.
-- (see http://www.cute-calendar.com/category/federal-holidays-in-the-united-states.html)
-- Client-defined special events, which I'm defining as not a workday or a holiday,
-- but a day of interest that the client may want to track for data analysis.
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp (PKDate date, description varchar(100))
--INSERT INTO #tmp (PKDate, description)
--VALUES
-- ('2015-01-02', 'Frank Caliendo'),
-- ('2015-01-05', 'Rock of the 80''s'),
-- ('2015-01-15', 'Smokey Robinson Pre-Show Wine & Smoked Cheese Tasting'),
-- ('2015-01-16', 'Smokey Robinson'),
-- ('2015-01-22', 'Rewind Fest 2015: Psychedelic Furs and More'),
-- ('2015-02-04', 'ZZ Top with Special Guest Blackberry Smoke'),
-- ('2015-02-07', 'Block Party With Kool & The Gang And More'),
-- ('2015-02-13', 'Thunder Vibes Reggae Festival'),
-- ('2015-02-14', 'Michael McDonald and Boz Scaggs'),
-- ('2015-02-16', 'Marco Antonio Solis Y Camilia - La Experiencia Tour'),
-- ('2015-02-26', 'Gladiator Challenge: Collision Course'),
-- ('2015-02-27', 'Rick Springfield'),
-- ('2015-03-04', 'The Thunder Down Under Australian Nudie Revue'),
-- ('2015-03-05', 'The Cosplay Five Sings The Hits'),
-- ('2015-03-10', 'Strawberry Music Fest - Amy''s Orchid Late Nite Lounge'),
-- ('2015-03-27', 'Kenny G: The Rock Opera'),
-- ('2015-03-28', 'Purple Ones - Tribute to the Music of Prince'),
-- ('2015-03-31', 'Steel Slinky - Party Band'),
-- ('2015-04-05', 'Chains Required - Horn driven R&B'),
-- ('2015-04-17', 'Cheezy Poofs = A Dash of alt-rock with soul and funk'),
-- ('2015-04-23', 'Rewind Fest 2015: Psychedelic Furs and More'),
-- ('2015-04-24', 'Shaq''s All-Star Comedy Jam'),
-- ('2015-05-15', 'Peppermint Patty and Mustafa''s All Star Jamacian Steel Drum Band'),
-- ('2015-05-21', 'IFC Caged Combat'),
-- ('2015-06-15', 'The Fabulous Jewish Magician Signumd J. Goldstein'),
-- ('2015-06-30', 'Pearl City Marathon'),
-- ('2015-07-20', 'Tough Mudder'),
-- ('2015-09-05', 'Huey Lewis and The News' ),
-- ('2015-09-18', 'Sarah Colonna and feature act Jeff Bodart'),
-- ('2015-09-19', 'Sarah Colonna and feature act Jeff Bodart'),
-- ('2015-09-12', 'Duc Huy: 50 Years of Love and Music'),
-- ('2015-09-25', 'ABBACADABRA–The Ultimate ABBA Tribute'),
-- ('2015-09-26', 'Last Comic Standing Live Tour'),
-- ('2015-09-09', 'America''s Got Talent Live: The All-Stars Tour!'),
-- ('2015-10-29', 'Flashdance – The Musical'),
-- ('2015-10-10', 'Kenny Rogers' ),
-- ('2015-10-31', 'Sinners & Saints Halloween Party'),
-- ('2015-11-20', 'Donny & Marie Celebrating the Holidays'),
-- ('2015-11-21', 'Donny & Marie Celebrating the Holidays')
---- Set the days with events
--UPDATE d
--SET d.is_event = 1, d.Description = t.description
--FROM #tmp t
-- JOIN days d ON t.PKDate = d.PKDate
-- Set the days without events
--UPDATE days SET is_event = 0 WHERE is_event IS NULL
DECLARE @years TABLE (PKYear INT NOT NULL)
INSERT @years (PKYear)
SELECT DISTINCT
c.year
FROM dbo.calendar c
DECLARE @year INT
DECLARE @hols table(PKDate DATE NOT NULL PRIMARY KEY, DESCRIPTION varchar(50) NOT NULL)
WHILE EXISTS (SELECT 1 FROM @years)
BEGIN
SELECT TOP(1) @year = PKYear FROM @years y
ORDER BY y.PKYear
SET LANGUAGE us_english;
INSERT @hols
SELECT * FROM dbo.BankHolidays2(@year) bh
DELETE @years WHERE PKYear = @year
END
UPDATE d
SET d.description = h.DESCRIPTION,
d.is_holiday = 1
FROM dbo.calendar d
INNER JOIN @hols h
ON d.PKDate = h.PKDate
UPDATE calendar SET is_holiday = 0 WHERE is_holiday IS NULL
UPDATE calendar SET is_workday = CASE WHEN is_weekend = 0 AND is_holiday = 0 THEN 1 ELSE 0 END
Here is the query that I am trying to mark the relevant week, last week, quarter and corrosponding quater. The case statements will become computed columns if I can get them right.
The problem is when we cross year boundries, particularly when the previous year had 53 ISO weeks. The rule in that case is to compare week 53 with week 52 of last year, but my CASE statement-fu skills are not up to the task.
DECLARE @d DATETIME = '2017-12-29';
SELECT
d.PKDate,
CASE
WHEN DATEPART(iso_week, d.PKDate) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, @d) THEN 1
ELSE 0 END currentWeek,
CASE
WHEN DATEPART(iso_week, DATEADD(wk, 1, d.PKDate)) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, DATEADD(wk, 1, d.PKDate)) = DATEPART(YEAR, @d) THEN 1
ELSE 0 END LastWeek,
CASE
WHEN d.PKDate BETWEEN DATEADD(dd, -7, @d) AND @d THEN 1
ELSE 0 END LastSevenDays,
CASE
WHEN d.PKDate BETWEEN DATEADD(dd, -14, @d) AND DATEADD(dd, -7, @d) THEN 1
ELSE 0 END PreviousSevenDays,
CASE
WHEN d.week_in_year = 53 THEN
CASE
WHEN DATEPART(iso_week, d.PKDate) = 52
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END
ELSE CASE
WHEN DATEPART(iso_week, d.PKDate) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END END currentWeekLastYear,
CASE
WHEN d.week_in_year = 53 THEN
CASE
WHEN DATEPART(iso_week, DATEADD(wk, 1, d.PKDate)) = 52
AND DATEPART(YEAR, DATEADD(wk, 1, d.PKDate)) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END
ELSE CASE
WHEN DATEPART(iso_week, DATEADD(wk, 1, d.PKDate)) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, DATEADD(wk, 1, d.PKDate)) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END END LastWeekLastYear,
CASE
WHEN DATEPART(qq, d.PKDate) = DATEPART(qq, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, @d) THEN 1
ELSE 0 END currentQuarter,
CASE
WHEN DATEPART(qq, d.PKDate) = DATEPART(qq, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END currentQuarterLastYear,
d.PKDate,
d.year,
d.quarter,
d.quarter_desc,
d.month,
d.month_name_long,
d.month_name_short,
d.week_in_year,
d.week_in_month,
d.day_in_year,
d.day_in_week,
d.day_in_month,
d.dmy_name_long,
d.dmy_name_long_with_suffix,
d.day_name_long,
d.day_name_short,
d.description,
d.is_weekend,
d.is_holiday,
d.is_workday
FROM dbo.calendar d
WHERE
CASE
WHEN DATEPART(iso_week, d.PKDate) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, @d) THEN 1
ELSE 0 END = 1 OR
CASE
WHEN DATEPART(iso_week, DATEADD(wk, 1, d.PKDate)) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, DATEADD(wk, 1, d.PKDate)) = DATEPART(YEAR, @d) THEN 1
ELSE 0 END = 1 OR
CASE
WHEN d.PKDate BETWEEN DATEADD(dd, -7, @d) AND @d THEN 1
ELSE 0 END = 1 OR
CASE
WHEN d.PKDate BETWEEN DATEADD(dd, -14, @d) AND DATEADD(dd, -7, @d) THEN 1
ELSE 0 END = 1 OR
CASE
WHEN d.week_in_year = 53 THEN
CASE
WHEN DATEPART(iso_week, d.PKDate) = 52
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END
ELSE CASE
WHEN DATEPART(iso_week, d.PKDate) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END END = 1 OR
CASE
WHEN d.week_in_year = 53 THEN
CASE
WHEN DATEPART(iso_week, DATEADD(wk, 1, d.PKDate)) = 52
AND DATEPART(YEAR, DATEADD(wk, 1, d.PKDate)) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END
ELSE CASE
WHEN DATEPART(iso_week, DATEADD(wk, 1, d.PKDate)) = DATEPART(iso_week, @d)
AND DATEPART(YEAR, DATEADD(wk, 1, d.PKDate)) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END END = 1 OR
CASE
WHEN DATEPART(qq, d.PKDate) = DATEPART(qq, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, @d) THEN 1
ELSE 0 END = 1 OR
CASE
WHEN DATEPART(qq, d.PKDate) = DATEPART(qq, @d)
AND DATEPART(YEAR, d.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, @d)) THEN 1
ELSE 0 END = 1;
Edit: Sorry, I got interrupted. I would like to replace @d in the code above with GetDate() and create computed columns. However the code returns the 1st of Jan 2017 as current week in the example above, and I cannot figure this one out!
Please Help!
Dave J
April 7, 2018 at 12:57 am
You are over-complicating this since you already have the calculations in the calendar table.
😎
This should help you out.
USE TEEST;
GO
SET NOCOUNT ON;
;WITH CURR_DATE AS
(
SELECT
CAL.PKDate
,DATEDIFF(DAY,0,CAL.PKDate) AS DFZ
,CAL.year
,CAL.week_in_year
,CAL.quarter
,CAL.day_in_year
FROM dbo.calendar CAL
WHERE CAL.PKDate = CONVERT(DATE,GETDATE(),0)
)
,LAST_WEEK_LAST_YEAR AS
(
SELECT
MAX(CAL.week_in_year) AS LWNO
FROM dbo.calendar CAL
WHERE CAL.year = YEAR(GETDATE()) - 1
)
SELECT
CA.PKDate
,CD.DFZ
,CASE
WHEN CA.year = CD.year AND CA.week_in_year = CD.week_in_year THEN 1
ELSE 0
END AS currentWeek
,CASE
WHEN ((DATEDIFF(DAY,0,CA.PKDate) / 7) + 1) = (CD.DFZ / 7) THEN 1
ELSE 0
END AS LastWeek
,CASE
WHEN (CD.DFZ - DATEDIFF(DAY,0,CA.PKDate)) BETWEEN 0 AND 7 THEN 1
ELSE 0
END AS LastSevenDays
,CASE
WHEN (CD.DFZ - DATEDIFF(DAY,0,CA.PKDate)) BETWEEN 8 AND 14 THEN 1
ELSE 0
END AS PreviousSevenDays
,CASE
WHEN CA.year = (CD.year - 1) AND CA.week_in_year = CD.week_in_year THEN 1
ELSE 0
END AS currentWeekLastYear
,CASE
WHEN CA.year = (CD.year - 1) AND CA.week_in_year = LWLY.LWNO THEN 1
ELSE 0
END AS LastWeekLastYear
,CASE
WHEN CA.year = CD.year AND CA.quarter = CD.quarter THEN 1
ELSE 0
END AS currentQuarter
,CASE
WHEN CA.year = (CD.year - 1) AND CA.quarter = CD.quarter THEN 1
ELSE 0
END AS currentQuarterLastYear
,CA.year
,CA.quarter
,CA.quarter_desc
,CA.month
,CA.month_name_long
,CA.month_name_short
,CA.week_in_year
,CA.week_in_month
,CA.day_in_year
,CA.day_in_week
,CA.day_in_month
,CA.dmy_name_long
,CA.dmy_name_long_with_suffix
,CA.day_name_long
,CA.day_name_short
,CA.description
,CA.is_weekend
,CA.is_holiday
,CA.is_workday
FROM dbo.calendar CA
CROSS APPLY CURR_DATE CD
CROSS APPLY LAST_WEEK_LAST_YEAR LWLY;
April 7, 2018 at 5:07 am
Something I am often accused of 😀
This is tremendous and very nearly meets my requirements. There is nothing wrong with the code, what is at fault is my description of the problem. I need to match and mark the current week with the equivalent week last year, as well as the current last week. The issue is when there are 53 weeks in a year as the previous year will have only 52. That's what is intended for the last week last year column. I'm sure when I'm back at the office on Monday with a large monitor I will be able to apply the technique you have so very elegantly shown here (Aside: How do people write code on laptops?)
Thank you very much for your time, and I'll post the solution if I am capable enough to code it! :Whistling:
Dave J
April 9, 2018 at 5:41 am
Hi Eirikur
I have modified my query to what you see below, and using your nifty DFZ calculation means I can zero in on the days required to be marked off.
Once again many thanks
Dave J
SELECT
cal.PKDate,
CASE
WHEN DATEPART(iso_week, cal.PKDate) = DATEPART(iso_week, CAST(GETDATE() AS DATE))
AND DATEPART(YEAR, cal.PKDate) BETWEEN DATEPART(YEAR, CAST(GETDATE() AS DATE)) - 1 AND DATEPART(YEAR,CAST(GETDATE() AS DATE)) + 1
AND DATEDIFF(DAY, 0, CAST(GETDATE() AS DATE)) BETWEEN DATEDIFF(DAY, 0, cal.PKDate) - 30 AND DATEDIFF(DAY,0,cal.PKDate)+ 30 THEN 1
ELSE 0
END currentWeek,
CASE
WHEN DATEPART(iso_week, DATEADD(wk, 1, cal.PKDate)) = DATEPART(iso_week, CAST(GETDATE() AS DATE))
AND DATEDIFF(DAY, 0, CAST(GETDATE() AS DATE)) BETWEEN DATEDIFF(DAY, 0, cal.PKDate) - 30 AND DATEDIFF(DAY,0,cal.PKDate) + 30 THEN 1
ELSE 0
END LastWeek,
CASE
WHEN cal.PKDate BETWEEN DATEADD(dd, -6, CAST(GETDATE() AS DATE)) AND CAST(GETDATE() AS DATE) THEN 1
ELSE 0
END LastSevenDays,
CASE
WHEN cal.PKDate BETWEEN DATEADD(dd, -13, CAST(GETDATE() AS DATE)) AND DATEADD(dd, -7, CAST(GETDATE() AS DATE)) THEN 1
ELSE 0
END PreviousSevenDays,
CASE
WHEN DATEPART(iso_week, CAST(GETDATE() AS DATE)) = 53 THEN
CASE
WHEN DATEPART(iso_week, DATEADD(yy, -1, cal.PKDate)) = 52
AND DATEPART(YEAR, cal.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, CAST(GETDATE() AS DATE))) THEN 1
ELSE 0 END
ELSE
CASE
WHEN DATEPART(iso_week, cal.PKDate) = DATEPART(iso_week, CAST(GETDATE() AS DATE))
AND DATEPART(YEAR, cal.PKDate) BETWEEN DATEPART(YEAR, CAST(GETDATE() AS DATE)) - 1 AND DATEPART(YEAR,CAST(GETDATE() AS DATE)) + 1
AND DATEDIFF(DAY, 0, cal.PKDate) BETWEEN DATEDIFF(DAY, 0, DATEADD(yy, -1, CAST(GETDATE() AS DATE))) - 30 AND DATEDIFF(DAY,0,DATEADD(yy, -1, CAST(GETDATE() AS DATE))) + 30 THEN 1
ELSE 0 END
END currentWeekLastYear,
CASE
WHEN DATEPART(iso_week, cal.PKDate) = DATEPART(iso_week, DATEADD(wk, -1, CAST(GETDATE() AS DATE)))
AND DATEPART(YEAR, cal.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, CAST(GETDATE() AS DATE))) THEN 1
ELSE 0
END LastWeekLastYear,
CASE
WHEN DATEPART(qq, cal.PKDate) = DATEPART(qq, CAST(GETDATE() AS DATE))
AND DATEPART(YEAR, cal.PKDate) = DATEPART(YEAR, CAST(GETDATE() AS DATE)) THEN 1
ELSE 0
END currentQuarter,
CASE
WHEN DATEPART(qq, CAST(GETDATE() AS DATE)) - 1 = 0 THEN
CASE
WHEN DATEPART(qq, cal.PKDate) = 4
AND DATEPART(YEAR, cal.PKDate) = DATEPART(YEAR, CAST(GETDATE() AS DATE)) - 1 THEN 1
ELSE 0 END
ELSE CASE
WHEN DATEPART(qq, cal.PKDate) = DATEPART(qq, CAST(GETDATE() AS DATE)) - 1
AND DATEPART(YEAR, cal.PKDate) = DATEPART(YEAR, CAST(GETDATE() AS DATE)) THEN 1
ELSE 0 END
END LastQuarter,
CASE
WHEN DATEPART(qq, cal.PKDate) = DATEPART(qq, CAST(GETDATE() AS DATE))
AND DATEPART(YEAR, cal.PKDate) = DATEPART(YEAR, DATEADD(yy, -1, CAST(GETDATE() AS DATE))) THEN 1
ELSE 0 END currentQuarterLastYear,
cal.year,
cal.quarter,
cal.quarter_desc,
cal.month,
cal.month_name_long,
cal.month_name_short,
cal.week_in_year,
cal.week_in_month,
cal.day_in_year,
cal.day_in_week,
cal.day_in_month,
cal.dmy_name_long,
cal.dmy_name_long_with_suffix,
cal.day_name_long,
cal.day_name_short,
cal.description,
cal.is_weekend,
cal.is_holiday,
cal.is_workday
FROM dbo.calendar cal;
April 9, 2018 at 7:12 am
David Jackson - Monday, April 9, 2018 5:41 AMHi Eirikur
I have modified my query to what you see below, and using your nifty DFZ calculation means I can zero in on the days required to be marked off.Once again many thanks
Dave J
You are very welcome.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply