help translate this week query from Oracle PL/SQL to SQL Server 2008
I have the following query that runs in my Oracle database and I want to have the equiv开发者_StackOverflow中文版alent for a SQL Server 2008 database:
SELECT TRUNC( /* Midnight Sunday */
NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL)
) AS week_start,
TRUNC( /* 23:59:59 Saturday */
NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1
) - (1/(60*24)) + (59/(60*60*24)) AS week_end
FROM DUAL
CONNECT BY LEVEL <= 4 /* Get the past 4 weeks */
What the query does is get the start of the week and the end of the week for the last 4 weeks. The number of weeks is arbitrary and should be easily modified in the SQL Server query I want. It generates data like the following:
WEEK_START WEEK_END
2010-03-07 00:00:00 2010-03-13 23:59:59
2010-02-28 00:00:00 2010-03-06 23:59:59
...
The part I'm currently stuck on translating is CONNECT BY LEVEL since it seems SQL Server 2008 doesn't have an equivalent. I would prefer to simply adjust a line like CONNECT BY LEVEL <= 4 and have the query generate more or fewer weeks (i.e., I don't want to have to adjust multiple UNION ALL statements).
Edit: here's what I have so far that gets the beginning and end of the current week:
SELECT week_start,
DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
FROM (
SELECT CAST(
CONVERT(
VARCHAR(10),
DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE()),
111
) AS DATETIME
) AS week_start
) AS week_start_view
I don't mind if the query shows the current week start and end date or if it starts at the previous week.
I modified OMG Ponies' answer because it looked like a good idea, it just had the wrong week_end value on each week and also showed future weeks instead of past weeks. I came up with the following:
WITH dates AS (
SELECT DATEADD(
DD,
1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)),
CONVERT(VARCHAR(10), starting_date, 111)
) AS midnight
FROM (
SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date
) AS starting_date_view
UNION ALL
SELECT DATEADD(DD, 7, midnight)
FROM dates
WHERE DATEADD(DD, 7, midnight) < GETDATE()
) SELECT midnight AS week_start,
DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
FROM dates
It generates the past 4 weeks:
week_start week_end
2010-02-14 00:00:00.000 2010-02-20 23:59:59.000
2010-02-21 00:00:00.000 2010-02-27 23:59:59.000
2010-02-28 00:00:00.000 2010-03-06 23:59:59.000
2010-03-07 00:00:00.000 2010-03-13 23:59:59.000
This is better than my previous answer, I think, because it does not rely on another table having a particular number of rows. The number of weeks generated can be changed by altering only one digit: the 3 in SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date. The current week is included, and that digit represents how many additional weeks prior to the current week should be shown.
Iterate over Past Weeks up to Now, Excluding Current Week
Update: and here's a version that excludes the current week:
WITH dates AS (
SELECT DATEADD(
DD,
1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)),
CONVERT(VARCHAR(10), starting_date, 111)
) AS midnight_sunday
FROM (
SELECT DATEADD(WEEK, -4, GETDATE()) AS starting_date
) AS starting_date_view
UNION ALL
SELECT DATEADD(DD, 7, midnight_sunday)
FROM dates
WHERE DATEADD(DD, 7, midnight_sunday) <
DATEADD(
DD,
1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)),
CONVERT(VARCHAR(10), GETDATE(), 111)
)
) SELECT midnight_sunday AS week_start,
DATEADD(SS, -1, DATEADD(DAY, 7, midnight_sunday)) AS week_end
FROM dates
Its results:
week_start week_end
2010-02-07 00:00:00.000 2010-02-13 23:59:59.000
2010-02-14 00:00:00.000 2010-02-20 23:59:59.000
2010-02-21 00:00:00.000 2010-02-27 23:59:59.000
2010-02-28 00:00:00.000 2010-03-06 23:59:59.000
Iterate over Past Months up to Now
I later found a need for a monthly version of this query. Here is that modification:
WITH dates AS (
SELECT CAST(
FLOOR(CAST(starting_date AS DECIMAL(12, 5))) -
(DAY(starting_date) - 1) AS DATETIME
) AS month_start
FROM (
SELECT DATEADD(MONTH, -3, GETDATE()) AS starting_date
) AS starting_date_view
UNION ALL
SELECT DATEADD(MONTH, 1, month_start)
FROM dates
WHERE DATEADD(MONTH, 1, month_start) < GETDATE()
) SELECT month_start,
DATEADD(SS, -1, DATEADD(MONTH, 1, month_start)) AS month_end
FROM dates
ORDER BY month_start DESC
Use (but don't forget to vote for Sarah):
WITH dates AS (
SELECT DATEADD(DD,
1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)),
CONVERT(VARCHAR(10), starting_date, 111)
) AS midnight
FROM (SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date) AS starting_date_view
UNION ALL
SELECT DATEADD(DD, 7, midnight)
FROM dates
WHERE DATEADD(DD, 7, midnight) < GETDATE())
SELECT midnight AS week_start,
DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
FROM dates
Previously:
Set the first day of the week to be Sunday with the SET DATEFIRST command:
SET DATEFIRST 7The SQL Server 2005+ equivalent to Oracle's
CONNECT BY LEVELis the recursive CTE (ANSI standard btw). Use:WITH dates AS ( SELECT DATEADD(DD, 1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), CONVERT(VARCHAR(10), GETDATE(), 111)) AS date UNION ALL SELECT DATEADD(dd, 7, d.date) FROM dates d WHERE DATEADD(dd, 7, d.date) <= DATEADD(dd, 4*7, GETDATE())) SELECT t.date AS week_start, DATEADD(ss, -1, DATEADD(DAY, 7, t.date)) AS week_end FROM dates t
See this link for explaining how to get the first day of the week. To alter the number of weeks, change the DATEADD(dd, 4*7, GETDATE()), where 4 represents the number of weeks you want generated.
All you need is a set:
;WITH cte(n) AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
)
SELECT week_start,
DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
FROM (
SELECT CAST(
CONVERT(
VARCHAR(10),
DATEADD(WEEK, -n, DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE())),
111
) AS DATETIME
) AS week_start
FROM cte
) AS week_start_view;
However I will caution you that if your data is datetime and you are going to use these boundaries for query ranges, you should use an open-ended range, e.g. >= 03/07 and < 03/14. This way you don't miss out any rows that happened between 23:59:59 and midnight; as rare as they may be, they could be important.
based on your code:
SELECT DATEADD(day, weeks.week * -7, week_start) AS week_start,
DATEADD(SECOND, -1, DATEADD(DAY, (weeks.week-1) * -7, week_start)) AS week_end
FROM (
SELECT CAST(
CONVERT(
VARCHAR(10),
DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE()),
111
) AS DATETIME
) AS week_start
) AS week_start_view,
( SELECT 0 AS week UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS weeks
Here's what I came up with. It's slightly janky in that it relies on at least one of my tables having rows >= the number of weeks I want to select. I could adjust it slightly to include the current week.
SELECT week_start,
DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
FROM (
SELECT CAST(
CONVERT(
VARCHAR(10),
DATEADD(
DAY,
1-DATEPART(DW, GETDATE()),
DATEADD(DAY, -7*level, GETDATE())
),
111
) AS DATETIME
) AS week_start
FROM (
SELECT level
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY RAND()) AS level
FROM my_table_with_at_least_21_rows
) AS all_rows_view
WHERE level <= 21
) AS level_view
) AS week_start_view
This gets the past 21 weeks, starting at last week. Here's sample data:
week_start week_end
2010-02-28 00:00:00.000 2010-03-06 23:59:59.000
2010-02-21 00:00:00.000 2010-02-27 23:59:59.000
2010-02-14 00:00:00.000 2010-02-20 23:59:59.000
2010-02-07 00:00:00.000 2010-02-13 23:59:59.000
...
加载中,请稍侯......
精彩评论