Query for table with days 1-365 not working when querying week at end of year

Question

I have a query for a report in SSRS that pulls 8 days worth of data Monday to Monday, the table has a id for each day of the year. This past week it failed,

ID_Table

Country ID_Day ID_Num
  US    1      1111
  US    2      2222
  US    3      3333
  US    4      4444
   …    …      …
   …    …      …
  US    362    1234
  US    363    5678
  US    365    3456


  DECLARE @begindate DATETIME ,
  @datestart INT ,
  @dateend INT ,
  @firstthisyear DATETIME ,
  @diff INT ,
  @firstnextyear DATETIME ,
  @endofyear DATETIME; 

 SET @begindate = '12/28/15';
 SET @datestart = DATEPART(DY, @begindate);
 SET @endofyear = DATEADD(yy, DATEDIFF(yy, 0, @begindate) + 1, -1);
 SET @dateend = 0;
 SET @diff = ( DATEPART(DY, @endofyear) ) - @datestart + 1;

 SET @firstthisyear = DATEADD(yy, DATEDIFF(yy, 0, @begindate), 0);
 SET @firstnextyear = DATEADD(yy, DATEDIFF(yy, 0, @begindate) + 1, 0);

 IF @diff < 7
    BEGIN
    SET @dateend = @diff;
    END;         

 SELECT DATEADD(dd, ID_Day - 1, @firstthisyear) AS [Active Date] ,
    ID_Day AS [Screen Number] ,
    ID_Num [Password]
 FROM   [Server].DB.dbo.DAY_ID_CONFIG
 WHERE  ID_Day >= @datestart
    AND ID_Day <= @datestart + 7

 UNION ALL
 SELECT DATEADD(dd, ID_Day - 1, @firstnextyear) AS [Active Date] ,
    ID_Day AS [Screen Number] ,
    ID_Num [Password]
 FROM   [Server].DB.dbo.DAY_ID_CONFIG
 WHERE  ID_Day <= @dateend

This works well for 12/28/15 - 01/04/16 which was the problem but now when i run it for other dates such as 12/27/15 it pulls 10 rows instead of the 8 i need, how can i change my query so it only pulls 8 rows of data.

Thanks

You are best to initialize dates with ISO-8601 formatted dates. — TT.
I'm thinking i need to do something like if there are 8 rows in the the first select then don't union if there are less than 8 union only enough rows make it 8. — NewGuy
So is 01/04/16 represented by 359? Or does it start over and you have the year stored separately? — Steve Mangiameli

Jeffrey Van Laethem Jeffrey Van Laethem · Accepted Answer · 2015-12-30T15:59:26

I would do (You'll probably need to change some of this, but this is the idea I'd run with):

IF DatePart(DAYOFYEAR, GETDATE()) >= 359
BEGIN
    SELECT DATEADD(dd, ID_Day - 1, @firstthisyear) AS [Active Date] ,
    ID_Day AS [Screen Number] ,
    ID_Num [Password]
    FROM   [Server].DB.dbo.DAY_ID_CONFIG
    WHERE ID_Day >= DatePart(DAYOFYEAR, GETDATE()) OR 
    ID_Day <= DatePart(DAYOFYEAR, DateAdd(DAY, 7, GETDATE()))
END
ELSE
BEGIN
    SELECT DATEADD(dd, ID_Day - 1, @firstthisyear) AS [Active Date] ,
    ID_Day AS [Screen Number] ,
    ID_Num [Password]
    FROM   [Server].DB.dbo.DAY_ID_CONFIG
    WHERE ID_Day >= DatePart(DAYOFYEAR, GETDATE()) AND 
    ID_Day <= DatePart(DAYOFYEAR, DateAdd(DAY, 7, GETDATE()))
END

Query for table with days 1-365 not working when querying week at end of year

1 Answers