I have an interesting and specific problem. Specific, because I cannot explain it simply, but I think it should be reasonably easy to solve. With that said, I cannot find similar question, because I don’t know how to look for it!
In one table, among other columns, there is a date column, with same multiple entries repeating for a year or so in a specific pattern.
For example:
TermDate
(Every Tuesday)
I need to change those dates to Mondays and Wednesdays, so that ‘2013/07/16’ would become ‘2013/07/15’, ‘2013/07/23’ would then be ‘‘2013/07/17’’, and ‘2013/07/30’ would be next first available Monday – ‘2013/07/22’, etc.
Do you see what I mean? Obviously I would need CASE statement for this, but does anyone have an idea how exactly to write it?
Thanks!
I am using the MS SQL SERVER by the way!
SQL to round a date down to the start of the week...
DATEADD(WEEK, DATEDIFF(WEEK, 0, <theDate>), 0)
NOTE: This is NOT affected by your DATEFIRST environment setting.
SQL to conditionally round it down to a Monday or Wednesday...
CASE WHEN DATEPART(DW, <theDate>) IN (3, 4, 5, 6, 7) THEN
DATEADD(WEEK, DATEDIFF(WEEK, 0, <theDate>), 0)
ELSE
DATEADD(WEEK, DATEDIFF(WEEK, 0, <theDate>), 0) + 2
END
NOTE : DATEPART() _IS_ affected by your DATEFIRST environment setting.
This code has assumed SET DATEFIRST 7
Then you can put all of your records into a sequence. If it's position 1 you add two days to the base date (Monday => Wednesday), if it's position 2 you add seven days (Monday => Next Monday), position three adds nine days, etc.
This simplifies down to (position / 2) * 7 + (position % 2) * 2...
position : (position / 2) * 7 + (position % 2) * 2
--------------------------------------------------
0 : 0
1 : 2
2 : 7
3 : 9
4 : 14
5 : 16
6 : 21
With a note that if your base date happens to be a Wednesday, you need to treat all of your other dates as having a position one higher...
So, I'd do this...
SELECT
term_lookup.TermDate,
base_lookup.base_date
+ ((term_lookup.term_id + base_lookup.base_id) / 2) * 7
+ ((term_lookup.term_id + base_lookup.base_id) % 2) * 2 AS new_date
FROM
(
SELECT
DATEADD(WEEK, DATEDIFF(WEEK, 0, MIN(TermDate)), 0) AS base_date,
CASE WHEN DATEPART(DW, MIN(TermDate)) IN (1, 2) THEN 0 ELSE 1 END AS base_id
FROM
yourTable
)
AS base_lookup
CROSS JOIN
(
SELECT
TermDate,
DENSE_RANK() OVER (ORDER BY TermDate) - 1 AS term_id
FROM
yourTable
GROUP BY
TermDate
)
AS term_lookup
If that gives you the right results, join them back on to your data to do the update.