How do I write a record for each day in a date range???

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  September 7, 2018 at 8:36 am

  #378771

  Hi
  To make it simple
  I  have a @StartDate and @EndDate
  I choose students with an admit date in that range from the "Pupils" table 
  write those out to a temp table #temp1
  I then want to write each date  between @StartDate and @EndDate with the pupils name
  for example
  @StartDate = 8/1/18
  @EndDate = 8/4/18
  output would be
  Pupil1 8/1/18
  Pupil1 8/2/18
  Pupil1 8/3/18
  Pupil1 8/4/18
  Pupil2 8/1/18
  Pupil2 8/2/18
  etc....

  Thanks

• Thom A

  SSC Guru

  Points: 99130

  More actions

  September 7, 2018 at 8:50 am

  #2004889

  One method is to use a Calendar table: http://www.sqlservercentral.com/articles/calendar/145206/

  Alternatively, you can create the table on the fly. The below would generate all of the days, provided the range was of 1,000 days or less:

  DECLARE @StartDate date = '20180101', @EndDate date = '20191231';

  WITH N AS(

    SELECT N

    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),

  Tally AS (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I

    FROM N N1

     CROSS JOIN N N2

     CROSS JOIN N N3),

  Dates AS(

    SELECT DATEADD(DAY, T.I,@StartDate) AS DateValue

    FROM Tally T

    WHERE DATEADD(DAY, T.I,@StartDate) <= @EndDate)

  SELECT *

  FROM Dates;

  You can then use that against your table using a LEFT JOIN:

  DECLARE @StartDate date = '20180101', @EndDate date = '20191231';

  WITH N AS(

    ...)

  SELECT D.DateValue, YT.{YourColumns}

  FROM Dates D

    LEFT JOIN YourTable YT ON D.DateValue = YT.YourDateColumn;

  Any questions, please do ask.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  September 7, 2018 at 9:05 am

  #2004897

  Thanks for the quick reply
  Actually I worded the select wrong
  The pupil would be active in  that range could be admitted before the StartDate but still active during
  So Actually all I am pulling are the names then attaching each date in the range to each name

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  September 7, 2018 at 9:09 am

  #2004898

  I think Thom's advice still stands. The only differences would be minor. You essentially want to cross join the two sets (People, Calendar).

  SELECT p.PersonID, c.CalendarDate
FROM Person p CROSS JOIN Calendar c
WHERE p.PersonID IN (1,2,3)
AND c.CalendarDate >= @StartDate
AND c.CalendarDate <= @EndDate

• Thom A

  SSC Guru

  Points: 99130

  More actions

  September 7, 2018 at 9:10 am

  #2004899

  jbalbo - Friday, September 7, 2018 9:05 AM

  Thanks for the quick reply
  Actually I worded the select wrong
  The pupil would be active in  that range could be admitted before the StartDate but still active during
  So Actually all I am pulling are the names then attaching each date in the range to each name

  not sure what you mean here. Use CROSS JOIN?
  SELECT {Columns}
    FROM YourTable YT
         CROSS JOIN Dates D;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 7, 2018 at 9:12 am

  #2004901

  jbalbo - Friday, September 7, 2018 9:05 AM

  Thanks for the quick reply
  Actually I worded the select wrong
  The pupil would be active in  that range could be admitted before the StartDate but still active during
  So Actually all I am pulling are the names then attaching each date in the range to each name

  You have been given a starting point, take it and run with it.  If you have problems or questions come back here, post what you have tried and ask your question(s).

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  September 7, 2018 at 9:22 am

  #2004903

  Thank you for the help....
  Here is what I finally came up with

  DECLARE @startDate DATETIME;
  DECLARE @endDate DATETIME;
  --DECLARE @dateDiff INT;
  SET @startDate = '2018-08-12';
  SET @endDate = '2018-08-14';
  SELECT TOP 10 [Last Name] AS Name,
       @startDate AS StartDate,
       @endDate AS EndDate
  INTO #temp
  FROM [dbo].[pupils]
  -- could put a where clause here.....
  ;
  DECLARE @maxdate DATETIME=
  (
    SELECT MAX([EndDate])
    FROM #temp
  );
  WITH cte
    AS (
    SELECT Name,
      StartDate
    -- EndDate
    FROM #temp
    UNION ALL
    SELECT Name,
      DATEADD(day, 1, StartDate)
    FROM cte
    WHERE StartDate < @maxdate)
    SELECT *
    FROM cte
    ORDER BY Name,
       StartDate;
  DROP TABLE #temp;

• Thom A

  SSC Guru

  Points: 99130

  More actions

  September 7, 2018 at 9:27 am

  #2004905

  Ahhh!!! You ruined my "beautiful" tally table and introduced an RBAR CTE. Why would you do that... :crying:

  You have a TOP 10 in there as well, but no ORDER BY, so you want just 10 random last names? What are you actually trying to achieve here? I think it's time for some sample data, expected results and a re-explanation of your goals.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  September 7, 2018 at 9:36 am

  #2004907

  Thanks for the quick reply
  Actually I worded the select wrong
  The pupil would be active in  that range could be admitted before the StartDate but still active during
  So Actually all I am pulling are the names then attaching each date in the range to each name

  haha....
  I'm just being sloppy, to test this...
  The ultimate goal is  user enters a start and end date, names that are active in that range are written to the temp table
  The CTE then writes the  name, and each date in the range 
  Gives the user a  checkoff type list 

  BTW:  Google came up with most of it!!  lol

• Thom A

  SSC Guru

  Points: 99130

  More actions

  September 7, 2018 at 9:44 am

  #2004910

  jbalbo - Friday, September 7, 2018 9:36 AM

  Thanks for the quick reply
  Actually I worded the select wrong
  The pupil would be active in  that range could be admitted before the StartDate but still active during
  So Actually all I am pulling are the names then attaching each date in the range to each name

  haha....
  I'm just being sloppy, to test this...
  The ultimate goal is  user enters a start and end date, names that are active in that range are written to the temp table
  The CTE then writes the  name, and each date in the range 
  Gives the user a  checkoff type list 

  BTW:  Google came up with most of it!!  lol

  How do you tell if a pupil is active?

  Also, why do you need:
  DECLARE @maxdate datetime = (SELECT MAX([EndDate])FROM #temp);
  The value of EndDate for every row is the value of @endDate, so there's no reason to check the value; it's already known (and the MAX value of the same value is the value itself 🙂 ).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  September 7, 2018 at 9:49 am

  #2004911

  There is an admission and discharge date on the table so the pupil would have to fall in that date range....
  Sorry for being sloppy, sometimes its in my head but cant get to the keyboard....

• Thom A

  SSC Guru

  Points: 99130

  More actions

  September 7, 2018 at 9:57 am

  #2004912

  jbalbo - Friday, September 7, 2018 9:49 AM

  There is an admission and discharge date on the table so the pupil would have to fall in that date range....
  Sorry for being sloppy, sometimes its in my head but cant get to the keyboard....

  Ok, IF I understand correctly, would this not give the right result then?

  DECLARE @StartDate date = '20180108',

     @EndDate date = '20180408';

  WITH N AS

    (SELECT N

    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),

  Tally AS

    (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I

    FROM N N1

      CROSS JOIN N N2

     CROSS JOIN N N3),

  Dates AS

    (SELECT DATEADD(DAY, T.I, @StartDate) AS DateValue

    FROM Tally T

    WHERE DATEADD(DAY, T.I, @StartDate) <= @EndDate)

  SELECT *

  FROM Dates D

    JOIN Pupil P ON D.DateValue >= P.AdmissionDate

        AND (D.DateValue <= P.DischargeDate

         OR P.DischargeDate IS NULL); --I assume a Pupil that hasn't left doesn't have a value

  So, that'll return every day the Pupil was a active, provided they were active in the date range.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  September 7, 2018 at 10:17 am

  #2004913

  Actually just tried and it does.....
  Honestly I never thought of using a join with a  Date range

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  September 7, 2018 at 2:45 pm

  #2004947

  jbalbo - Friday, September 7, 2018 8:36 AM

  Hi
  To make it simple
  I  have a @StartDate and @EndDate
  I choose students with an admit date in that range from the "Pupils" table 
  write those out to a temp table #temp1
  I then want to write each date  between @StartDate and @EndDate with the pupils name
  for example
  @StartDate = 8/1/18
  @EndDate = 8/4/18
  output would be
  Pupil1 8/1/18
  Pupil1 8/2/18
  Pupil1 8/3/18
  Pupil1 8/4/18
  Pupil2 8/1/18
  Pupil2 8/2/18
  etc....

  Thanks

  I've just written an inline table valued function to select a date range which you could use (not fully tested).
  IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
  EXEC ('CREATE FUNCTION [dbo].[DateRange] () RETURNS TABLE AS RETURN SELECT 1 X')
END
GO
/* -- **********************************************************************
-- FUNCTION:  DateRange
--     Returns a table of datetime values based on the parametes
-- Parameters:
-- @Start  :Start date of the series
-- @End   :End date of the series
-- @Unit  :The time unit for @interval
--      ms : milliseconds
--      ss : seconds
--      mi : minutes
--      hh : hours
--      dd : days
--      ww : weeks
--      mm : months
--      qq : quarters
--      yy : years
-- @Interval :The number of units between each row
-- Sample Call
--  SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss',2)
--  SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', 'dd', 9)
-- **********************************************************************/      
ALTER FUNCTION [dbo].[DateRange] (@Start datetime, @End datetime, @Unit nchar(2), @interval int)
RETURNS TABLE
AS
  RETURN SELECT TOP(CASE @Unit
                          WHEN 'ms' THEN DATEDIFF(ms,@Start,@End)/@Interval
                          WHEN 'ss' THEN DATEDIFF(ss,@Start,@End)/@Interval
                          WHEN 'mi' THEN DATEDIFF(mi,@Start,@End)/@Interval
                          WHEN 'hh' THEN DATEDIFF(hh,@Start,@End)/@Interval
                          WHEN 'dd' THEN DATEDIFF(dd,@Start,@End)/@Interval
                          WHEN 'ww' THEN DATEDIFF(ww,@Start,@End)/@Interval
                          WHEN 'mm' THEN DATEDIFF(mm,@Start,@End)/@Interval
                          WHEN 'qq' THEN DATEDIFF(qq,@Start,@End)/@Interval
                          WHEN 'yy' THEN DATEDIFF(yy,@Start,@End)/@Interval
                          ELSE DATEDIFF(dd,@Start,@End)/@Interval
                      END+1)
    CASE @Unit
     WHEN 'ms' THEN DATEADD(ms, @interval * RowNum, @Start)
     WHEN 'ss' THEN DATEADD(ss, @interval * RowNum, @Start)
     WHEN 'mi' THEN DATEADD(mi, @interval * RowNum, @Start)
     WHEN 'hh' THEN DATEADD(hh, @interval * RowNum, @Start)
     WHEN 'dd' THEN DATEADD(dd, @interval * RowNum, @Start)
     WHEN 'ww' THEN DATEADD(ww, @interval * RowNum, @Start)
     WHEN 'mm' THEN DATEADD(mm, @interval * RowNum, @Start)
     WHEN 'qq' THEN DATEADD(qq, @interval * RowNum, @Start)
     WHEN 'yy' THEN DATEADD(yy, @interval * RowNum, @Start)
     ELSE DATEADD(dd, @interval * RowNum, @Start)
    END AS Value
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY sc1.name) - 1 AS RowNum
      FROM [master].[dbo].[syscolumns] sc1 WITH (NOLOCK)
      CROSS JOIN [master].[dbo].[syscolumns] sc2 WITH (NOLOCK)) AS A
GO

  Once you've installed the function all you need to do is write queries like this:
  SELECT p.name, d.Value [Date]
FROM Pupils p
CROSS JOIN dbo.DateRange(@StartDate,@EndDate,1 , 'dd') d
ORDER BY p.name, d.Value

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  September 7, 2018 at 3:07 pm

  #2004950

  You want to be careful here.  The DATEDIFF function causes an overflow quite quickly when using milliseconds.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA