October 30, 2014 at 1:51 pm
I have read so many articles discussing how best to handle non-Dec fiscal year-ends, etc... using user-defined functions or even custom calendar tables. However, what I'm really hoping to do is slightly different. I want to translate the calendar-based week # to a client's cycle #.
Ex: Client's Cycle Start Date: 10/1/2013
Client's Cycle End Date: 3/31/2014
So, the client's cycle is only 25 weeks.
DECLARE
@StartDate DATETIME='10/1/2013'
, @EndDate DATETIME='03/31/2014'
SELECT DATEPART(wk, @Startdate)
This gives me 40. So, 10/1/2013 equates to week 40 if it were a calendar year. I want that to actually be week 1. I have a table-valued function that is returning the last x weeks and it works great but only for a standard calendar. What I'm wanting to do is add an extra start, end, and week # that matches the calendar week to the week of the client's cycle.
Any help much appreciated.
October 30, 2014 at 2:55 pm
I think I found my answer in a previous post!
http://qa.sqlservercentral.com/Forums/Topic1320527-392-2.aspx#bm1322829
October 31, 2014 at 7:49 am
Well, I thought I could use that article to get what I need, but it's still not quite right. This is my ultimate goal:
DECLARE @CycleStartDate DATETIME='09-16-2014'
, @CycleEndDate DATETIME='10-26-2015'
I am supposed to be pull the last 8 weeks (based on the last completed Saturday). I got all that working. Notice, though, that the Interval Nbr is based on the calendar year week #. I need it to be based on @CycleStartDate instead.
I have a date function that is giving me everything except the CycleIntervalNbr. Figuring out the last 9 weeks and their start/end dates, the wk# of the calendar yr, etc... wasn' hard. It's getting the wk# of the client's cycle that is killing me.
This is what I want:
ClientKey IntervalNbr CycleIntervalNbr StartDate StartDateKey EndDate EndDateKey
9 38 1 2014-09-14 00:00:00.000 20140914 2014-09-20 00:00:00.000 20140920
9 39 2 2014-09-21 00:00:00.000 20140921 2014-09-27 00:00:00.000 20140927
9 40 3 2014-09-28 00:00:00.000 20140928 2014-10-04 00:00:00.000 20141004
9 41 4 2014-10-05 00:00:00.000 20141005 2014-10-11 00:00:00.000 20141011
9 42 5 2014-10-12 00:00:00.000 20141012 2014-10-18 00:00:00.000 20141018
9 43 6 2014-10-19 00:00:00.000 20141019 2014-10-25 00:00:00.000 20141025
9 99 99 2014-09-16 00:00:00.000 20140916 2014-10-25 00:00:00.000 20141025 (This is Cycle to Date)
Here's my code that works (without finding the CycleInterval#)
ALTER FUNCTION [dbo].[fnReportDatesPartialPeriod]
(
@ClientKey int
, @TimePeriodType int --0 for Week; 1 for Month
)
RETURNS
@SelectDates TABLE
(
ClientKey int
, IntervalNbr int
, CycleIntervalNbr int
, StartDate DATETIME
, StartDateKey int
, EndDate DATETIME
, EndDateKey int
)
AS
BEGIN
DECLARE @EndDate DATETIME
, @ClientStartDate DATETIME
, @ClientEndDate DATETIME
, @DateCheck DATETIME
/*Test*/
--, @ClientKey int=3
--, @TimePeriodType int=0
--WEEKLY
--Get End Date of Client's Current Cycle
SELECT @ClientEndDate =
COALESCE(MAX(CycleEndDate),'2999-01-01')
FROM dbo.dimCycle
GROUP BY ClientKey
HAVING ClientKey=@ClientKey AND COALESCE(MAX(CycleEndDate),'2999-01-01')>GETDATE()
--Get Start Date of Client's Current Cycle (based on the most current end date)
SELECT @ClientStartDate=
CycleStartDate
FROM dbo.dimCycle
WHERE ClientKey=@ClientKey
AND COALESCE(CycleEndDate, '2999-01-01')=@ClientEndDate
IF @TimePeriodType=0 --Weekly
BEGIN
--most recently completed Saturday
--This gives the Sunday which STARTS the week ending in the most recently completed Saturday.
SELECT @EndDate= DATEADD(DAY, -7, CAST(CONVERT(VARCHAR(10), DATEADD(DAY, (DATEPART(DW, GETDATE()) - 1) * -1, GETDATE()), 101) AS DATETIME))
--print @EndDate
--This gives the most recently completed Saturday.
SELECT @DateCheck=DATEADD(D, DATEDIFF(D,0, DATEADD(MS, -3, DATEADD(WEEK, 1, @EndDate))), 0)
--only return records if the last completed saturday falls within the client's current cycle.
IF @ClientStartDate <= @DateCheck and @ClientEndDate >=@DateCheck
-- print 1
-- Print @ClientStartDate
-- Print @ClientEndDate
-- Print @DateCheck
-- Print @EndDate
-- print DATEADD(DAY, DATEDIFF(DAY, -1, @ClientStartDate) / 7 * 7, -1)
-- end
--end
BEGIN
INSERT @SelectDates
(ClientKey, IntervalNbr,StartDate,StartDateKey,EndDate,EndDateKey)
SELECT @ClientKey
, DATEPART(WEEK, w.StartDate) AS IntervalNbr
, w.StartDate AS StartDate
, NULL AS StartDateKey
--, dateadd(ms, -3, dateadd(week, 1, StartDate)) as EndDate
, DATEADD(D, DATEDIFF(D,0, DATEADD(MS, -3, DATEADD(WEEK, 1, STARTDATE))), 0) AS EndDate
, NULL AS EndDateKey
FROM
(SELECT @EndDate AS StartDate
UNION SELECT DATEADD(WEEK, -1, @EndDate) AS StartDate
UNION SELECT DATEADD(WEEK, -2, @EndDate) AS StartDate
UNION SELECT DATEADD(WEEK, -3, @EndDate) AS StartDate
UNION SELECT DATEADD(WEEK, -4, @EndDate) AS StartDate
UNION SELECT DATEADD(WEEK, -5, @EndDate) AS StartDate
UNION SELECT DATEADD(WEEK, -6, @EndDate) AS StartDate
UNION SELECT DATEADD(WEEK, -7, @EndDate) AS StartDate
) AS w
UNION
SELECT
@ClientKey
, 99 AS IntervalNbr
, @ClientStartDate AS StartDate
, NULL AS StartDateKey
--, Getdate() as EndDate
--, dateadd(ms, -3, cast(convert(varchar(10), dateadd(day, (DATEPART(dw, GETDATE()) - 1) * -1, GETDATE()), 101) as datetime))
, CASE WHEN @TimePeriodType=0 THEN DATEADD(d, DATEDIFF(d,0,DATEADD(ms, -3, CAST(CONVERT(VARCHAR(10), DATEADD(DAY, (DATEPART(dw, GETDATE()) - 1) * -1, GETDATE()), 101) AS DATETIME))), 0)
ELSE DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
END
, NULL AS EndDateKey
DELETE @SelectDates
WHERE StartDate < DATEADD(DAY, DATEDIFF(DAY, -1, @ClientStartDate) / 7 * 7, -1)
END
ELSE
RETURN
END
--MONTHLY
IF @TimePeriodType=1 --Monthly
BEGIN
--Last Day of Most recently completed month
SELECT @EndDate=DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
--First Day of Most recently completed month
SELECT @DateCheck =DATEADD(mm, DATEDIFF(m,0,@EndDate), 0)
IF @ClientStartDate <= @EndDate and @ClientEndDate >=@DateCheck
BEGIN
INSERT @SelectDates
(ClientKey, IntervalNbr,StartDate,StartDateKey,EndDate,EndDateKey)
SELECT @ClientKey
, DATEPART(MONTH, w.EndDate) AS IntervalNbr
, DATEADD(mm, DATEDIFF(m,0,w.EndDate), 0) AS StartDate
, NULL AS StartDateKey
--, w.EndDate as EndDate
--,dateadd(ms, -3, DATEADD(mm, DATEDIFF(m,-1,w.EndDate), 0)) as EndDate
, DATEADD(D, DATEDIFF(D,0, DATEADD(MS, -3, DATEADD(MM, DATEDIFF(M,-1,w.EndDate), 0))), 0)
, NULL AS EndDateKey
FROM
(SELECT @EndDate AS EndDate
UNION SELECT DATEADD(MONTH, -1, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -2, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -3, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -4, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -5, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -6, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -7, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -8, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -9, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -10, @EndDate) AS EndDate
UNION SELECT DATEADD(MONTH, -11, @EndDate) AS EndDate
) AS w
UNION
SELECT
@ClientKey
, 99 AS IntervalNbr
, @ClientStartDate AS StartDate
, NULL AS StartDateKey
--, Getdate() as EndDate
--, dateadd(ms, -3, cast(convert(varchar(10), dateadd(day, (DATEPART(dw, GETDATE()) - 1) * -1, GETDATE()), 101) as datetime))
, CASE WHEN @TimePeriodType=0 THEN DATEADD(d, DATEDIFF(d,0,DATEADD(ms, -3, CAST(CONVERT(VARCHAR(10), DATEADD(DAY, (DATEPART(dw, GETDATE()) - 1) * -1, GETDATE()), 101) AS DATETIME))), 0)
ELSE DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
END
, NULL as EndDateKey
DELETE @SelectDates
WHERE StartDate < DATEADD(month, DATEDIFF(month, 0, @ClientStartDate), 0)
END
ELSE
RETURN
END
UPDATE s
SET StartDateKey=dl.DateKey
, EndDateKey=dl2.DateKey
FROM @SelectDates s
JOIN dbo.dimDate dl
ON s.StartDate=dl.FullDate
JOIN dbo.dimDate dl2
ON s.EndDate=dl2.FullDate
RETURN
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply