Date Diff logic
Last Post 24 May 2013 03:31 PM by Wolfgang Miska. 18 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dexum
Veteran Member
Veteran Member
Posts:1158
Avatar

--
27 Feb 2013 08:54 AM

I have a PHP program that I am trying to calculate a drivers violation level.

Each month a driver gets a violation he goes up one level.

Each month he does not have a violation he goes down a level.

I tried calculating the number of days (in PHP) between violations but the fact that a month might  have 28, 29, 30 or 31 days caused problems.

I tried using SQL month(date2) - month(date1) and that didn't work. Jan31-Oct31 equals 1-10= -9 not 1 as needed.

Any help in PHP or SQL in logic would be greatly appreciated. Thanks

Sample date if forum will let me:

Code Name Violation Date
TOM Tom Smith 9/30/2012
TOM Tom Smith 11/30/2012
TOM Tom Smith 12/31/2012
TOM Tom Smith 1/31/2013
TOM Tom Smith 2/28/2013
TOM Tom Smith 3/31/2013
TOM Tom Smith 5/31/2013

Results would be:

 

Code Name Violation Date Level
TOM Tom Smith 9/30/2012 1
TOM Tom Smith 11/30/2012 1 Did not have vio in Oct 1-1+1=1
TOM Tom Smith 12/31/2012 2
TOM Tom Smith 1/31/2013 3
TOM Tom Smith 2/28/2013 4
TOM Tom Smith 3/31/2013 5
TOM Tom Smith 5/31/2013 5 Did not have vio in Apr 5-1+1=5

 

Danny Exum Model 515 OS Level 6.1
Thomas Sullivan
New Member
New Member
Posts:44

--
27 Feb 2013 09:16 AM

How about a SQL count by Driver, year and month?

Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
27 Feb 2013 09:29 AM
perhaps you should be doing month(date1 - date2)
dexum
Veteran Member
Veteran Member
Posts:1158
Avatar

--
27 Feb 2013 10:02 AM

@Thomas

I don't think that would take in the fact that a month with no violations would drop them one level and two months of violations would drop them two levels.

  A driver could have a violation this month then not one next month for years. Every month he got a violation he would be fined a dollar amount based on his level. Of course the month with no violations he would not be fined because he is on the right track.

 

@Tommy

Correct me if I am wrong but the SQL month BIF only returns a value for the month. OCT = 10 and MAY = 5. It does not take into account the year.

Thanks guys.

Danny Exum Model 515 OS Level 6.1
Jack Callahan
Basic Member
Basic Member
Posts:303

--
27 Feb 2013 10:07 AM

 Just some thoughts:

You need some way of generating "non violation" months, and tracking whether non violation months reduced prior month levels (level can't go below zero)

For each month with a violation, level= +1, For each month without a violation, level=-1.

Driver violation level for any given month  would be the prior month's level plus current month.

So for violations in Sept, Jan, Feb, Mar, the levels would be.

 Month   Level
 
Sept +1
1
 
Oct  -1 0  non violation offsets initial violation
Nov  -1 0  no effect. level must be >= zero
Dec  -1 0
no effect. level must be >= zero 
 Jan  +1 1  
 Feb  +1 2
 
 Mar  +1 3
 
 Apr  -1 2  

Smacks of recursion,since you need to evaluate the prior month's level to calculate the level for the current month

dexum
Veteran Member
Veteran Member
Posts:1158
Avatar

--
27 Feb 2013 10:28 AM

@jack

Thanks

I don't want to enter a record in the data base for a driver without a violation that month.

That would be a lot data entry for hopefully a few violators.

I fetch a record in SQL. Place date in a holding variable. Fetch next record and some how compare the two dates and determine if I add 1 to level or subtract some number (based on number of months between violations) from level until level equals zero.

Danny Exum Model 515 OS Level 6.1
Jack Callahan
Basic Member
Basic Member
Posts:303

--
27 Feb 2013 11:21 AM

You wouldn't need a row for each non violation month, but you need to build a calender table to evaluate when violations have or haven't occurred.

You would  project the non violation rows using a cross product of the calendar and the violation table.Here's a sample to play around with:

--
CREATE SCHEMA myexample;

DROP TABLE myexample.calendar;
--
--
CREATE TABLE myexample.CALENDAR
	(
	CALID INTEGER GENERATED ALWAYS AS IDENTITY ( 
	START WITH 1 INCREMENT BY 1 
	NO MINVALUE NO MAXVALUE 
	NO CYCLE NO ORDER 
	CACHE 20 )   ,
	CALYEAR CHAR (4 ) NOT NULL WITH DEFAULT,
	CALPERiod CHAR (20 ) NOT NULL WITH DEFAULT,
	 CALSTART DATE NOT NULL WITH DEFAULT,
	 CALEND DATE NOT NULL WITH DEFAULT,
	CONSTRAINT myexample.calendar_PK PRIMARY KEY( CALID ) )   
	  ;
--   
INSERT INTO  myexample.calendar (calyear, calperiod,calstart, calend)
VALUES
('2011','January', '2011-01-01','2011-01-31') ,
('2011','February', '2011-02-01','2011-01-28'), 
('2011','March', '2011-03-01','2011-03-31') ,
('2011','April', '2011-04-01','2011-04-30') ,
('2011','May', '2011-05-01','2011-05-31') ,
('2011','June', '2011-06-01','2011-06-30') ,
('2011','July', '2011-07-01','2011-07-31') ,
('2011','August', '2011-08-01','2011-08-31'), 
('2011','September', '2011-09-01','2011-09-30'), 
('2011','October', '2011-10-01','2011-10-31'),
('2011','November', '2011-11-01','2011-11-30'), 
('2011','December', '2011-12-01','2011-12-31') ,
('2012','January', '2012-01-01','2012-01-31') ,
('2012','February', '2012-02-01','2012-02-29'), 
('2012','March', '2012-03-01','2012-03-31') ,
('2012','April', '2012-04-01','2012-04-30'), 
('2012','May', '2012-05-01','2012-05-31'), 
('2012','June', '2012-06-01','2012-06-30'), 
('2012','July', '2012-07-01','2012-07-31') ,
('2012','August', '2012-08-01','2012-08-31'), 
('2012','September', '2012-09-01','2012-09-30') ,
('2012','October', '2012-10-01','2012-10-31'),
('2012','November', '2012-11-01','2012-11-30'), 
('2012','December', '2012-12-01','2012-12-31') ,
('2013','January', '2013-01-01','2013-01-31') ,
('2013','February', '2013-02-01','2013-02-28'), 
('2013','March', '2013-03-01','2013-03-31') ,
('2013','April', '2013-04-01','2013-04-30') ,
('2013','May', '2013-05-01','2013-05-31') ,
('2013','June', '2013-06-01','2013-06-30') ,
('2013','July', '2013-07-01','2013-07-31') ,
('2013','August', '2013-08-01','2013-08-31'), 
('2013','September', '2013-09-01','2013-09-30'), 
('2013','October', '2013-10-01','2013-10-31'),
('2013','November', '2013-11-01','2013-11-30') ,
('2013','December', '2013-12-01','2013-12-31') ;

Select * from myexample.calendar;
drop table myexample.violation;

Create table myexample.violation
(
	VIOID INTEGER GENERATED ALWAYS AS IDENTITY ( 
	START WITH 1 INCREMENT BY 1 
	NO MINVALUE NO MAXVALUE 
	NO CYCLE NO ORDER 
	CACHE 20 )   ,
	VIONaME CHAR (20 ) NOT NULL WITH DEFAULT,
	VIODATE  DATE NOT NULL WITH DEFAULT,
	CONSTRAINT myexample.VIOLATION_PK PRIMARY KEY( VIOID ) )   
	  ; 
DELETE from myexample.violation;
INSERT INTO myexample.violation (vioname, viodate)
VALUES
('TOM','2012-09-25'),
('TOM','2013-01-07'),
('TOM','2013-01-15'),
('TOM','2013-02-12'),
('TOM','2013-03-31'),

('JACK','2012-08-27'),
('JACK','2012-11-07'),
('JACK','2013-01-05'),
('JACK','2013-02-12'); 

SELECT * from myexample.violation;

With TOMList AS
	( 
	SELECT min( Viodate ) StartDate, max ( viodate) enddate FROM myexample.violation WHERE vioname='TOM')

Select c.*, IFNULL((select DISTINCT 1 FROM myexample.violation v1 WHERE v1.viodate between calstart and calend and vioname='TOM'), -1) as levelchange
 from myexample.calendar c
CROSS join TomList t
Where calend > sTartdate and calstart < enddate 
ORDER BY Calstart;
 
dexum
Veteran Member
Veteran Member
Posts:1158
Avatar

--
27 Feb 2013 12:25 PM

I see what you are saying.  I need to think about that a bit.

I guess I could fetch a driver who had a violation in month report is run.

Do a second SQL script with a Violation date and calender date join.

Load results in array and process array for level calculations.

or..

instead of a join let PHP calculate missing months between fetches and load array for processing.

Then I wouldn't have to keep a separate table updated.

I will have to think about the number of processes and speed.

 

Theoretically 300 drivers could have a violation each month times how ever years I keep this program. 

That shouldn't  be a problem. I will try to code it up and test.

Thanks

Danny Exum Model 515 OS Level 6.1
Jack Callahan
Basic Member
Basic Member
Posts:303

--
27 Feb 2013 01:25 PM

Results from the query:

21 2012 September 2012-09-01 2012-09-30 1
22 2012 October 2012-10-01 2012-10-31 -1
23 2012 November 2012-11-01 2012-11-30 -1
24 2012 December 2012-12-01 2012-12-31 -1
25 2013 January 2013-01-01 2013-01-31 1
26 2013 February 2013-02-01 2013-02-28 1
27 2013 March 2013-03-01 2013-03-31 1

You'd need to calculate the level based on what happened in prior periods.

 

With TOMList AS
	( 
	SELECT min( Viodate ) StartDate, max ( viodate) enddate FROM myexample.violation WHERE vioname='TOM')

Select c.*, IFNULL((select DISTINCT 1 FROM myexample.violation v1 WHERE v1.viodate between calstart and calend and vioname='TOM'), -1) as levelchange
 from myexample.calendar c
CROSS join TomList t
Where calend >sTartdate and calstart

dexum
Veteran Member
Veteran Member
Posts:1158
Avatar

--
27 Feb 2013 01:39 PM

Thanks close but not quite there. 

Using your data the levels should be as below.

 

('TOM','2012-09-25'),  Level 1
('TOM','2013-01-07'),  Level 1 because months with no violations
('TOM','2013-01-15'),  Level 1 same month as before
('TOM','2013-02-12'),  Level 2 two consecutive months
('TOM','2013-03-31'),  Level 3 three consecutive months

Driver will only have one record per month although he might have many violations.
I use the last day on month as record date

I am still coding up a test. I am slow with fat fingers.
Danny Exum Model 515 OS Level 6.1
Jack Callahan
Basic Member
Basic Member
Posts:303

--
27 Feb 2013 01:43 PM

May not even need the CROSS JOIN:

 

SELECT c.*, IFNULL( (SELECT DISTINCT 1 FROM myexample.violation v1 WHERE v1.viodate BETWEEN calstart AND calend AND vioname='JACK'), -1) AS  levelchange
FROM myexample.calendar c
WHERE calend > (SELECT min( viodate ) FROM myexample.violation WHERE vioname='JACK' )  AND calstart< CURRENT DATE 
ORDER BY calstart;

Yields :
20 2012 August 2012-08-01 2012-08-31 1
21 2012 September 2012-09-01 2012-09-30 -1
22 2012 October 2012-10-01 2012-10-31 -1
23 2012 November 2012-11-01 2012-11-30 1
24 2012 December 2012-12-01 2012-12-31 -1
25 2013 January 2013-01-01 2013-01-31 1
26 2013 February 2013-02-01 2013-02-28 1

Jack Callahan
Basic Member
Basic Member
Posts:303

--
27 Feb 2013 01:56 PM
Posted By dexum on 27 Feb 2013 02:39 PM

Thanks close but not quite there. 

Using your data the levels should be as below.

 

('TOM','2012-09-25'),  Level 1
('TOM','2013-01-07'),  Level 1 because months with no violations
('TOM','2013-01-15'),  Level 1 same month as before
('TOM','2013-02-12'),  Level 2 two consecutive months
('TOM','2013-03-31'),  Level 3 three consecutive months

Driver will only have one record per month although he might have many violations.
I use the last day on month as record date

I am still coding up a test. I am slow with fat fingers.
 

 You' would still need to calculate the level- just trying to demonstrate how you can project non violation rows for months without having to maintain said rows manually. The -1 value for would reduce the prior level if prior level>0, otherwise its ignored.

 

vazymimil
New Member
New Member
Posts:5

--
28 Feb 2013 03:28 AM
Here's my try. No doubt there's a simpler and more effifient way, but I think it does the job
with 
Base as ( -- Select the driver
    select viodate from violations where code = 'TOM'
),
FirstViolation as ( -- Select the driver's first violation to have a start date
    select min(viodate) as firstdate from Base
),
LastViolation as ( -- Select the driver's last violation to have an end date, current date if later
    select max(max(viodate), current_date) as lastdate from Base
),
AllMonthsInAYear (month#) as ( -- Generate all months
    select 01 from sysibm.sysdummy1
    union select 02 from sysibm.sysdummy1
    union select 03 from sysibm.sysdummy1
    union select 04 from sysibm.sysdummy1
    union select 05 from sysibm.sysdummy1
    union select 06 from sysibm.sysdummy1
    union select 07 from sysibm.sysdummy1
    union select 08 from sysibm.sysdummy1
    union select 09 from sysibm.sysdummy1
    union select 10 from sysibm.sysdummy1
    union select 11 from sysibm.sysdummy1
    union select 12 from sysibm.sysdummy1),
AllYears (year#) as ( -- Generate recursively all years starting from first violation
    select year(firstdate) as year# from FirstViolation
    union all
    select year# + 1 as year# from AllYears
    where year# < year((select lastdate from LastViolation))
),
AllMonths as ( -- Generate all months of all years between start and end dates
    select year#, month# from AllYears, AllMonthsInAYear, FirstViolation, LastViolation
     where (year# > year(firstdate) or (month# >= month(firstdate) and year# = year(firstdate)))
       and (year# < year(lastdate) or (month# <= month(lastdate) and year# = year(lastdate)))
),
ViolationsPerMonth as ( -- Select months with violation(s)
    select
    year(viodate) as year#,
    month(viodate) as month#,
    case when count(*) > 0 then 1 else 0 end as violation_count
    from Base
    group by year(viodate), month(viodate)
),
LevelChangePerMonth as ( -- Level change for all months, +1 per month with violation, -1 else
    select
        rownumber() over (order by year#, month#) as row#,
        year#, month#, ifnull(violation_count, -1) as LevelChange
      from AllMonths
      left join ViolationsPerMonth using(year#, month#)
),
LevelPerMonth (firstrow, row#, year#, month#, Level) as (
    -- Calculate level for each month with a recursive query
    select
        row# as firstrow, -- we need to keep track of the first row
        row#, year#, month#, LevelChange as Level
        from LevelChangePerMonth
    union all
    select LevelPerMonth.firstrow, LevelChangePerMonth.row#, LevelChangePerMonth.year#, LevelChangePerMonth.month#,
        -- minimum level is 0
        max(LevelPerMonth.Level + LevelChangePerMonth.LevelChange, 0) as Level
    from LevelPerMonth, LevelChangePerMonth
    where LevelPerMonth.row# = LevelChangePerMonth.row# - 1
)
select
    LevelPerMonth.year#, LevelPerMonth.month#, Level
  from LevelPerMonth
  inner join ViolationsPerMonth using(year#, month#)
  where firstrow = 1 -- only select calculations starting from the first month
union
select -- ensure we have last level if last violation happened earlier than last month
    LevelPerMonth.year#, LevelPerMonth.month#, Level
  from LevelPerMonth
  inner join LastViolation on (year#, month#) = (year(lastDate), month(lastdate))
  where firstrow = 1
order by year#, month#
Ringer
Veteran Member
Veteran Member
Posts:1750
Avatar

--
28 Feb 2013 07:25 AM
Wow vazymimil. Good luck to the next programmer who has to read, modify, and debug that SQL statement and make it run quickly.

Chris Ringer
Joseph Harriman
New Member
New Member
Posts:42

--
28 Feb 2013 09:01 AM
Look here Dex http://stackoverflow.com/questions/...-using-php
dexum
Veteran Member
Veteran Member
Posts:1158
Avatar

--
28 Feb 2013 03:42 PM

@vazymimi

I really appreciate  the effort you put into that. WOW LOL

 

OK no laughing at code (if it looks stupid I am blaming it on forum LOL) but I got a working test program.

When I put it into my production program I will code all the necessary error checking and cleaning up the code. 

Thanks to Jack's input it got me to thinking about loading an array and processing data.

Here goes trying to get the code to display correctly.







") ; $days = dateDiff('-', $vdate, $datehold); // with first var as date seperator If ($days >= 33 ){ // check to see hold date over a month ago while ($datehold < $vdate and $days >= 33){ // do until previous month $day = substr($datehold,8,2); $month = substr($datehold,5,2); $year = substr($datehold,0,4); If ($month == 12 ){ // allow for dec rollover $month = 1 ; $year = $year + 1 ; }else{ $month = $month + 1 ; } $from = mktime(1,1,1,$month,'1',$year) ; $from = date('Y-m-d', $from) ; //calculate next month $from = lastDay($from) ; // convert day to last day of month $list[] = array ("code" => "$code" , "month" => "$from", "vio" => "No"); $datehold = $from ; $days = dateDiff('-', $vdate, $datehold); // calc days for while loop } } $list[] = array ("code" => "$code" , "month" => "$vdate", "vio" => "Yes"); // load array $datehold = $vdate ; } } db2_close($conn); $records = count($list); // get number or records in array // calculate level and output for ($row = 0; $row <=($records-1); $row++) { If ($list[$row]['vio'] == "Yes"){ $level = $level + 1; }else{ $level = $level -1; } echo $list[$row]['code']." Date ".$list[$row]['month']." Violation ".$list[$row]['vio']." Level ".$level.".
"; } ?>

Output from my test file.

BROTL Date 2012-08-31 Violation Yes Level 1.
BROTL Date 2012-09-30 Violation Yes Level 2.
BROTL Date 2012-10-31 Violation No Level 1.
BROTL Date 2012-11-30 Violation Yes Level 2.
BROTL Date 2012-12-31 Violation Yes Level 3.
BROTL Date 2013-01-31 Violation Yes Level 4.
BROTL Date 2013-02-28 Violation No Level 3.
BROTL Date 2013-03-31 Violation Yes Level 4.
BROTL Date 2013-04-30 Violation No Level 3.
BROTL Date 2013-05-31 Violation Yes Level 4.

Thanks again folks.

Danny Exum Model 515 OS Level 6.1
Ringer
Veteran Member
Veteran Member
Posts:1750
Avatar

--
28 Feb 2013 07:08 PM
date('t', $myTimestamp) will return the number of days in month. cal_days_in_month() too.
You might want to consider using db2_prepare if the field used in your SQL statement is from a user, to prevent SQL injection.

Chris
dexum
Veteran Member
Veteran Member
Posts:1158
Avatar

--
01 Mar 2013 07:30 AM

Thanks Chris I will look at all of those.

This is just a test mockup.

Now to clean up code and test some more.

Thanks for info on date routines especially.

Danny Exum Model 515 OS Level 6.1
Wolfgang Miska
New Member
New Member
Posts:9

--
24 May 2013 03:31 PM
I am not sure if I am to late bbut here is my solution in SQL
I added an other driver to show how it would work for more than one driver
The table driver_vio only consits of two columns ID (Driver) and viodate
:


with dates (id, yyyy, mm) as
(select id, year(min(viodate)), month(min(viodate))
from driver_viol root
group by id
union all
select dates.id, case when mm+1 > 12 then yyyy+1 else yyyy end,
case when mm+1 > 12 then 1 else mm+1 end
from dates
where digits(dates.yyyy) || digits( dates.mm) < (select digits(year(max(viodate))) || digits(month(max(viodate))) from driver_viol)),

points (yyyy, mm, id, viodate, point) as
(select yyyy, mm, a.id, viodate, case when viodate is null then -1 else 1 end
from dates a
left outer join driver_viol b on a.id = b.id and yyyy = year(viodate) and mm = month(viodate)),

levels ( yyyy, mm, id, viodate, level) as
(select root.yyyy, root.mm, root.id, root.viodate, root.point
from points root
where (root.id, root.viodate) in (select id, min(viodate) from driver_viol group by id)
union all
select child.yyyy, child. mm, child.id, child.viodate, level + point
from levels parent, points child
where parent.id = child.id and child.yyyy = case when parent.mm + 1 > 12 then parent.yyyy + 1 else parent.yyyy end
and child.mm = case when parent.mm + 1 > 12 then 1 else parent.mm + 1 end)

select * from levels order by id, yyyy, mm
You are not authorized to post a reply.

Acceptable Use Policy