August 1, 2016 at 6:54 pm
Hi,
I am trying to filter data by date in the SQL stmt to get data for
date range of report to the previous week Sunday - saturday.
Example:
report runs from any day from Sun 7/26 to Sat 7/31 . Report data should be for 7/17-7/23
Can you please advise how to do this?
Thanks.
Dee
August 2, 2016 at 12:10 am
Quick example which should be enough to get you passed this hurdle. The sample creates date set and a week group identifier starting every Sunday and ending on the next Saturday after.
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- CREATE A SET OF DATES FROM 2015-01-01 TO 2016-01-01
DECLARE @FIRST_DATE DATE = CONVERT(DATE,'20150101',112);
DECLARE @SAMPLE_SIZE INT = 365;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6)
,DATE_SET AS
(
SELECT
DATEADD(DAY,NM.N,@FIRST_DATE) AS DATE_VAL
FROM NUMS NM
)
SELECT
DS.DATE_VAL
-- Adding 1 to the datediff from 0 (1900-01-01) shifts the week group
-- start date to a Sunday.
,(DATEDIFF(DAY,0,DS.DATE_VAL) + 1) / 7 AS WEEK_GROUP
FROM DATE_SET DS;
Sample output
DATE_VAL WEEK_GROUP
---------- -----------
2015-01-01 6000
2015-01-02 6000
2015-01-03 6000
2015-01-04 6001
2015-01-05 6001
2015-01-06 6001
2015-01-07 6001
2015-01-08 6001
2015-01-09 6001
2015-01-10 6001
2015-01-11 6002
2015-01-12 6002
2015-01-13 6002
2015-01-14 6002
~~~~~~~~~~~~~~~
2015-12-18 6050
2015-12-19 6050
2015-12-20 6051
2015-12-21 6051
2015-12-22 6051
2015-12-23 6051
2015-12-24 6051
2015-12-25 6051
2015-12-26 6051
2015-12-27 6052
2015-12-28 6052
2015-12-29 6052
2015-12-30 6052
2015-12-31 6052
2016-01-01 6052
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply