Day wise and date range calculation with looping or dynamic data

• Patchai001

  SSC Veteran

  Points: 290

  More actions

  May 20, 2015 at 2:08 pm

  #297110

  Hi All,

  I initially created forum topic with the same name in the below link.

  http://www.sqlservercentral.com/Forums/Topic1685563-3077-1.aspx

  Thanks to Alan and Eirikur who has guided me post effective forum question.

  I created a new topic as i am trying to divide my initial question into multiple pieces.

  I have created a SQL Fiddle link with table and sample data.

  http://sqlfiddle.com/#!3/2ee295

  Table creation

  CREATE TABLE unit

  (

  Customer nvarchar(4) NULL,

  unit float NULL,

  Model nvarchar(7) NULL,

  insv_date DATETIME NULL,

  Order1 nvarchar(15) NULL,

  )

  Data insert

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8400,'Toyota','2014-08-09','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8401,'Toyota','2014-08-09','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8402,'Toyota','2014-08-13','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8515,'Toyota','2014-10-31','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8516,'Toyota','2014-10-22','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8517,'Toyota','2014-10-22','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8518,'Toyota','2014-10-31','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8757,'Toyota','2013-06-24','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8758,'Toyota','2013-06-30','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8759,'Toyota','2013-06-30','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8799,'Toyota','2013-09-28','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9050,'Toyota','2012-07-18','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9051,'Toyota','2012-07-30','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9052,'Toyota','2012-07-30','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9053,'Toyota','2012-07-21','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9054,'Toyota','2012-07-21','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9055,'Toyota','2012-08-03','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9135,'Toyota','2008-01-25','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9136,'Toyota','2008-02-04','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9137,'Toyota','2008-01-31','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9138,'Toyota','2008-02-04','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9305,'Toyota','2009-02-09','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9312,'Toyota','2009-03-05','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9313,'Toyota','2009-03-06','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9314,'Toyota','2009-02-23','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9330,'Toyota','2006-09-09','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9336,'Toyota','2006-11-07','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9337,'Toyota','2006-10-12','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9338,'Toyota','2006-09-11','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9339,'Toyota','2006-09-05','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9370,'Toyota','2006-04-04','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9376,'Toyota','2006-04-03','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9377,'Toyota','2006-04-05','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9378,'Toyota','2006-03-31','A1056729')

  My Current Query based on Order1

  SELECT Order1,COUNT(UNit.UNIT) As Units,sum(CASE

  WHEN (datediff(dd,INSV_DATE,'2015-01-21')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-01-21')) < 0 THEN 0

  ELSE (datediff(dd,INSV_DATE,'2015-01-21'))END) as Days31

  FROM UNIT WHERE Unit.INSV_DATE < '2015-01-21' AND

  UNIT.MODEL in('Toyota')AND(UNIT.Customer in('Jona' ))

  group by [Order1],customer

  I want the 31 days output calculated for day wise.

  So the question is how do i loop and pass date dynamically in the Datediff for a period of one month

  WHEN (datediff(dd,INSV_DATE,'Dynamic date')) >= 31 THEN 31

  Thank you all in advance.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 20, 2015 at 2:26 pm

  #1799478

  arulbabuvp (5/20/2015)

  ---

  Hi All,

  I initially created forum topic with the same name in the below link.

  http://www.sqlservercentral.com/Forums/Topic1685563-3077-1.aspx

  Thanks to Alan and Eirikur who has guided me post effective forum question.

  I created a new topic as i am trying to divide my initial question into multiple pieces.

  I have created a SQL Fiddle link with table and sample data.

  http://sqlfiddle.com/#!3/2ee295

  Table creation

  CREATE TABLE unit

  (

  Customer nvarchar(4) NULL,

  unit float NULL,

  Model nvarchar(7) NULL,

  insv_date DATETIME NULL,

  Order1 nvarchar(15) NULL,

  )

  Data insert

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8400,'Toyota','2014-08-09','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8401,'Toyota','2014-08-09','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8402,'Toyota','2014-08-13','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8515,'Toyota','2014-10-31','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8516,'Toyota','2014-10-22','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8517,'Toyota','2014-10-22','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8518,'Toyota','2014-10-31','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8757,'Toyota','2013-06-24','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8758,'Toyota','2013-06-30','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8759,'Toyota','2013-06-30','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8799,'Toyota','2013-09-28','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9050,'Toyota','2012-07-18','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9051,'Toyota','2012-07-30','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9052,'Toyota','2012-07-30','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9053,'Toyota','2012-07-21','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9054,'Toyota','2012-07-21','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9055,'Toyota','2012-08-03','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9135,'Toyota','2008-01-25','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9136,'Toyota','2008-02-04','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9137,'Toyota','2008-01-31','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9138,'Toyota','2008-02-04','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9305,'Toyota','2009-02-09','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9312,'Toyota','2009-03-05','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9313,'Toyota','2009-03-06','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9314,'Toyota','2009-02-23','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9330,'Toyota','2006-09-09','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9336,'Toyota','2006-11-07','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9337,'Toyota','2006-10-12','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9338,'Toyota','2006-09-11','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9339,'Toyota','2006-09-05','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9370,'Toyota','2006-04-04','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9376,'Toyota','2006-04-03','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9377,'Toyota','2006-04-05','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9378,'Toyota','2006-03-31','A1056729')

  My Current Query based on Order1

  SELECT Order1,COUNT(UNit.UNIT) As Units,sum(CASE

  WHEN (datediff(dd,INSV_DATE,'2015-01-21')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-01-21')) < 0 THEN 0

  ELSE (datediff(dd,INSV_DATE,'2015-01-21'))END) as Days31

  FROM UNIT WHERE Unit.INSV_DATE < '2015-01-21' AND

  UNIT.MODEL in('Toyota')AND(UNIT.Customer in('Jona' ))

  group by [Order1],customer

  I want the 31 days output calculated for day wise.

  So the question is how do i loop and pass date dynamically in the Datediff for a period of one month

  WHEN (datediff(dd,INSV_DATE,'Dynamic date')) >= 31 THEN 31

  Thank you all in advance.

  What exactly do you mean for a period of one month? Do you mean all data for a specific month or the past 31 days based on todays date?

• Patchai001

  SSC Veteran

  Points: 290

  More actions

  May 20, 2015 at 2:32 pm

  #1799484

  The past 31 days based on todays date.

  for example

  WHEN (datediff(dd,INSV_DATE,'2015-01-20')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-01-21')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-01-22')) >= 31 THEN 31 and so on..

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 20, 2015 at 2:36 pm

  #1799487

  arulbabuvp (5/20/2015)

  ---

  Hi All,

  I initially created forum topic with the same name in the below link.

  http://www.sqlservercentral.com/Forums/Topic1685563-3077-1.aspx

  Thanks to Alan and Eirikur who has guided me post effective forum question.

  I created a new topic as i am trying to divide my initial question into multiple pieces.

  I have created a SQL Fiddle link with table and sample data.

  http://sqlfiddle.com/#!3/2ee295

  Table creation

  CREATE TABLE unit

  (

  Customer nvarchar(4) NULL,

  unit float NULL,

  Model nvarchar(7) NULL,

  insv_date DATETIME NULL,

  Order1 nvarchar(15) NULL,

  )

  Data insert

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8400,'Toyota','2014-08-09','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8401,'Toyota','2014-08-09','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8402,'Toyota','2014-08-13','A1136925')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8515,'Toyota','2014-10-31','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8516,'Toyota','2014-10-22','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8517,'Toyota','2014-10-22','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8518,'Toyota','2014-10-31','A1116581')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8757,'Toyota','2013-06-24','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8758,'Toyota','2013-06-30','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8759,'Toyota','2013-06-30','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',8799,'Toyota','2013-09-28','A1126869')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9050,'Toyota','2012-07-18','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9051,'Toyota','2012-07-30','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9052,'Toyota','2012-07-30','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9053,'Toyota','2012-07-21','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9054,'Toyota','2012-07-21','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9055,'Toyota','2012-08-03','A1116683')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9135,'Toyota','2008-01-25','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9136,'Toyota','2008-02-04','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9137,'Toyota','2008-01-31','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9138,'Toyota','2008-02-04','A1066862')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9305,'Toyota','2009-02-09','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9312,'Toyota','2009-03-05','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9313,'Toyota','2009-03-06','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9314,'Toyota','2009-02-23','A1086021')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9330,'Toyota','2006-09-09','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9336,'Toyota','2006-11-07','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9337,'Toyota','2006-10-12','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9338,'Toyota','2006-09-11','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9339,'Toyota','2006-09-05','A1056766')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9370,'Toyota','2006-04-04','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9376,'Toyota','2006-04-03','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9377,'Toyota','2006-04-05','A1056729')

  INSERT INTO unit (Customer,unit,Model,insv_date,Order1) values('Jona',9378,'Toyota','2006-03-31','A1056729')

  My Current Query based on Order1

  SELECT Order1,COUNT(UNit.UNIT) As Units,sum(CASE

  WHEN (datediff(dd,INSV_DATE,'2015-01-21')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-01-21')) < 0 THEN 0

  ELSE (datediff(dd,INSV_DATE,'2015-01-21'))END) as Days31

  FROM UNIT WHERE Unit.INSV_DATE < '2015-01-21' AND

  UNIT.MODEL in('Toyota')AND(UNIT.Customer in('Jona' ))

  group by [Order1],customer

  I want the 31 days output calculated for day wise.

  So the question is how do i loop and pass date dynamically in the Datediff for a period of one month

  WHEN (datediff(dd,INSV_DATE,'Dynamic date')) >= 31 THEN 31

  Thank you all in advance.

  Based on the above table structure and sample data, what would be the expected results using today's date?

• Patchai001

  SSC Veteran

  Points: 290

  More actions

  May 20, 2015 at 2:59 pm

  #1799492

  ok lets take todays date May 20th

  The output should be like

  Date Order1 Unit Day31

  May20 90909 5 128

  May19 90909 4 124

  May17 90909 2 62

  I actually want to do something like the following.

  SELECT Order1,COUNT(UNit.UNIT) As Units,sum(CASE

  WHEN (datediff(dd,INSV_DATE,'2015-05-20')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-05-20')) < 0 THEN 0

  ELSE (datediff(dd,INSV_DATE,'2015-05-20'))END) as Days31

  FROM UNIT WHERE Unit.INSV_DATE < '2015-05-20' AND

  UNIT.MODEL in('Toyota')AND(UNIT.Customer in('Jona' ))

  group by [Order1],customer

  SELECT Order1,COUNT(UNit.UNIT) As Units,sum(CASE

  WHEN (datediff(dd,INSV_DATE,'2015-05-19')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-05-19')) < 0 THEN 0

  ELSE (datediff(dd,INSV_DATE,'2015-05-19'))END) as Days31

  FROM UNIT WHERE Unit.INSV_DATE < '2015-05-19' AND

  UNIT.MODEL in('Toyota')AND(UNIT.Customer in('Jona' ))

  group by [Order1],customer

  SELECT Order1,COUNT(UNit.UNIT) As Units,sum(CASE

  WHEN (datediff(dd,INSV_DATE,'2015-05-18')) >= 31 THEN 31

  WHEN (datediff(dd,INSV_DATE,'2015-05-18')) < 0 THEN 0

  ELSE (datediff(dd,INSV_DATE,'2015-05-18'))END) as Days31

  FROM UNIT WHERE Unit.INSV_DATE < '2015-05-18' AND

  UNIT.MODEL in('Toyota')AND(UNIT.Customer in('Jona' ))

  group by [Order1],customer

  Running the same query for everyday with the different date.

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  May 21, 2015 at 2:52 am

  #1799572

  Using your setup above

  DECLARE @dt DATETIME = cast('2014-11-25' AS DATETIME);

  SELECT cast(dt as date) dt

  ,Order1

  ,COUNT(UNit.UNIT) AS Units

  ,SUM(CASE

  WHEN (datediff(dd, INSV_DATE, d.dt)) >= 31

  THEN 31

  WHEN (datediff(dd, INSV_DATE, d.dt)) < 0

  THEN 0

  ELSE (datediff(dd, INSV_DATE, d.dt))

  END) AS Days31

  FROM UNIT

  CROSS APPLY (

  SELECT TOP (31) @dt - row_number() OVER (

  ORDER BY (

  SELECT NULL

  )

  ) AS dt

  FROM sys.all_columns

  ) d

  WHERE Unit.INSV_DATE < @dt

  AND UNIT.MODEL IN ('Toyota')

  AND (UNIT.Customer IN ('Jona'))

  GROUP BY dt

  ,[Order1]

  ,customer

  Does the result make any sense for you?

• Patchai001

  SSC Veteran

  Points: 290

  More actions

  May 26, 2015 at 1:27 pm

  #1800690

  Thank you . It worked.