SQL date criteria.

• Dee Dee-422077

  Ten Centuries

  Points: 1082

  More actions

  August 1, 2016 at 6:54 pm

  #309871

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182946

  More actions

  August 2, 2016 at 12:10 am

  #1893110

  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