How to get min and max date?

• Charmer

  SSChampion

  Points: 14903

  More actions

  August 1, 2018 at 2:08 am

  #374029

  Hi Friends,

  Can someone help me to get a query to produce the output that showed in the below sql code.? The temp table @ExpectedDateStore is the expected output.

  
DECLARE @OriginalDateStore table (St_id int, CompStoreID smallint, DateCalendar date)
INSERT INTO @OriginalDateStore

  SELECT 37,    1,    '2017-04-01'
UNION
SELECT 37,    1,    '2017-04-02'
UNION
SELECT 37,    1,    '2017-04-03'
UNION
SELECT 37,    1,    '2017-04-04'
UNION
SELECT 37,    1,    '2017-04-05'
UNION
SELECT 37,    2,    '2017-04-06'
UNION
SELECT 37,    2,    '2017-04-07'
UNION
SELECT 37,    2,    '2017-04-08'
UNION
SELECT 37,    2,    '2017-04-09'
UNION
SELECT 37,    1,    '2017-04-10'
UNION
SELECT 37,    1,    '2017-04-11'
UNION
SELECT 37,    1,    '2017-04-12'
UNION
SELECT 37,    2,    '2017-04-13'
UNION
SELECT 37,    2,    '2017-04-14'
UNION
SELECT 37,    1,    '2017-04-15'

  SELECT * FROM @OriginalDateStore ORDER BY 3

  DECLARE @ExpectedDateStore table (St_id int, CompStoreID smallint, MinDateCalendar date, MaxDateCalendar date)
INSERT into @ExpectedDateStore
SELECT 37,    1,    '2017-04-01', '2017-04-05'
UNION
SELECT 37,    2,    '2017-04-06', '2017-04-09'
UNION
SELECT 37,    1,    '2017-04-10', '2017-04-12'
UNION
SELECT 37,    2,    '2017-04-13', '2017-04-14'
UNION
SELECT 37,    1,    '2017-04-15', '2017-04-15'

  SELECT * FROM @ExpectedDateStore
ORDER BY MinDateCalendar


  Thanks,
  Charmer

• Thomas Rushton

  SSC-Insane

  Points: 22648

  More actions

  August 1, 2018 at 2:28 am

  #1999839

  Here's one option using windowing functions and aggregates.

  WITH
CSIDChange -- mark the first line of each batch / island
  AS
  (
   SELECT St_id,
     CompStoreID,
     DateCalendar,
     CASE
       WHEN LAG(CompStoreID) OVER (ORDER BY DateCalendar) = CompStoreID THEN
        0
       ELSE
        1
     END AS changeCompStoreID
   FROM @OriginalDateStore
  ),
CSIDGroups -- sum those to give a groupable ID
  AS
  (
   SELECT CSIDChange.St_id,
     CSIDChange.CompStoreID,
     DateCalendar,
     SUM(CSIDChange.changeCompStoreID) OVER (PARTITION BY CSIDChange.St_id
                   ORDER BY DateCalendar
                   ROWS UNBOUNDED PRECEDING
                  ) AS dategroup
   FROM CSIDChange
  )
SELECT CSIDGroups.St_id,
   CSIDGroups.CompStoreID,
   MIN(CSIDGroups.DateCalendar) AS mindatecalendar,
   MAX(CSIDGroups.DateCalendar) AS maxdatecalendar
FROM CSIDGroups
GROUP BY
  CSIDGroups.St_id,
  CSIDGroups.CompStoreID,
  CSIDGroups.dategroup
ORDER BY
mindatecalendar,
  CSIDGroups.St_id,
  CSIDGroups.CompStoreID

  There may be alternative methods available (quirky update perhaps?) that may prove more efficient, but this came to my mind first.

  Thomas Rushton
  blog: https://thelonedba.wordpress.com

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 1, 2018 at 7:01 am

  #1999868

  This would work if you always have contiguous dates. Otherwise, you might need an additional ROW_NUMBER function.
  
WITH CTE AS(
  SELECT *, DATEDIFF(dd, ROW_NUMBER() OVER (PARTITION BY CompStoreID ORDER BY DateCalendar), DateCalendar) AS dategroup
  FROM @OriginalDateStore
)
SELECT CTE.St_id,
  CTE.CompStoreID,
  MIN(CTE.DateCalendar) AS MinDateCalendar,
  MAX(CTE.DateCalendar) AS MaxDateCalendar
FROM CTE
GROUP BY CTE.St_id,
   CTE.CompStoreID,
   CTE.dategroup
ORDER BY MinDateCalendar;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  August 1, 2018 at 8:06 am

  #1999876

  ThomasRushton - Wednesday, August 1, 2018 2:28 AM

  Here's one option using windowing functions and aggregates.

  WITH
CSIDChange -- mark the first line of each batch / island
  AS
  (
   SELECT St_id,
     CompStoreID,
     DateCalendar,
     CASE
       WHEN LAG(CompStoreID) OVER (ORDER BY DateCalendar) = CompStoreID THEN
        0
       ELSE
        1
     END AS changeCompStoreID
   FROM @OriginalDateStore
  ),
CSIDGroups -- sum those to give a groupable ID
  AS
  (
   SELECT CSIDChange.St_id,
     CSIDChange.CompStoreID,
     DateCalendar,
     SUM(CSIDChange.changeCompStoreID) OVER (PARTITION BY CSIDChange.St_id
                   ORDER BY DateCalendar
                   ROWS UNBOUNDED PRECEDING
                  ) AS dategroup
   FROM CSIDChange
  )
SELECT CSIDGroups.St_id,
   CSIDGroups.CompStoreID,
   MIN(CSIDGroups.DateCalendar) AS mindatecalendar,
   MAX(CSIDGroups.DateCalendar) AS maxdatecalendar
FROM CSIDGroups
GROUP BY
  CSIDGroups.St_id,
  CSIDGroups.CompStoreID,
  CSIDGroups.dategroup
ORDER BY
mindatecalendar,
  CSIDGroups.St_id,
  CSIDGroups.CompStoreID

  There may be alternative methods available (quirky update perhaps?) that may prove more efficient, but this came to my mind first.

  Great answer Thomas

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  August 1, 2018 at 8:07 am

  #1999877

  Luis Cazares - Wednesday, August 1, 2018 7:01 AM

  This would work if you always have contiguous dates. Otherwise, you might need an additional ROW_NUMBER function.
  
WITH CTE AS(
  SELECT *, DATEDIFF(dd, ROW_NUMBER() OVER (PARTITION BY CompStoreID ORDER BY DateCalendar), DateCalendar) AS dategroup
  FROM @OriginalDateStore
)
SELECT CTE.St_id,
  CTE.CompStoreID,
  MIN(CTE.DateCalendar) AS MinDateCalendar,
  MAX(CTE.DateCalendar) AS MaxDateCalendar
FROM CTE
GROUP BY CTE.St_id,
   CTE.CompStoreID,
   CTE.dategroup
ORDER BY MinDateCalendar;


  Awesome solution Luis . I think this is another level.

  Saravanan

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 1, 2018 at 8:17 am

  #1999880

  saravanatn - Wednesday, August 1, 2018 8:07 AM

  Awesome solution Luis . I think this is another level.

  Thank you, but it's only one of the simplest solutions to the gaps and islands problems. You could find a lot more examples in the web.
  Some articles that help are:
   https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/
   http://www.sqlservercentral.com/articles/T-SQL/71550/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Charmer

  SSChampion

  Points: 14903

  More actions

  August 1, 2018 at 8:31 am

  #1999886

  ThomasRushton - Wednesday, August 1, 2018 2:28 AM

  Here's one option using windowing functions and aggregates.

  WITH
CSIDChange -- mark the first line of each batch / island
  AS
  (
   SELECT St_id,
     CompStoreID,
     DateCalendar,
     CASE
       WHEN LAG(CompStoreID) OVER (ORDER BY DateCalendar) = CompStoreID THEN
        0
       ELSE
        1
     END AS changeCompStoreID
   FROM @OriginalDateStore
  ),
CSIDGroups -- sum those to give a groupable ID
  AS
  (
   SELECT CSIDChange.St_id,
     CSIDChange.CompStoreID,
     DateCalendar,
     SUM(CSIDChange.changeCompStoreID) OVER (PARTITION BY CSIDChange.St_id
                   ORDER BY DateCalendar
                   ROWS UNBOUNDED PRECEDING
                  ) AS dategroup
   FROM CSIDChange
  )
SELECT CSIDGroups.St_id,
   CSIDGroups.CompStoreID,
   MIN(CSIDGroups.DateCalendar) AS mindatecalendar,
   MAX(CSIDGroups.DateCalendar) AS maxdatecalendar
FROM CSIDGroups
GROUP BY
  CSIDGroups.St_id,
  CSIDGroups.CompStoreID,
  CSIDGroups.dategroup
ORDER BY
mindatecalendar,
  CSIDGroups.St_id,
  CSIDGroups.CompStoreID

  There may be alternative methods available (quirky update perhaps?) that may prove more efficient, but this came to my mind first.

  This worked... Thanks Thomas

  Thanks,
  Charmer