WEEKLY QUERY

[Barand]

 

A lot is started to be counted as a dried in every week starting from the date in lot.dried_in_date

 

The next thing that happens is that same lot is scheduled to have tile flooring installed in the house. (lot_id and work_type_id=1 is inserted in to schedule table). Nothing changes. We are still counting the lot as a dried in every week. It has only been scheduled at this point

 

Now the change:

 

The final step is the tile flooring is installed and the job has been completed (schedule.complete_date is is not null for that lot with a schedule.work_type_id of 1 which is tile). This is the last week we count it as dried in.

 

Let's make sure we both understand the rules.

 

Lot #187 has a dried_in date in week 1 so is counted as dried_in for that week. Its schedule for work_type_1 is completed so, according to the bold type in the explanation you gave above, this is the last week we count it as dried in.

 

This would give counts of 1, 1, 1 for the three weeks if you apply your rules

[Barand]

How long should a slab take to dry in?

 

In 2015, there were 760 which dried_in on the same day as the slab_date. Of the rest there were significant numbers taking several weeks, months even, to dry in.

 

In 2014, some took over 1 year to dry in (one took 502 days).

SELECT
  drytime as drying_time
, COUNT(lot_id) as No_of_lots
FROM (
	SELECT 
	  lot_id
	, datediff(dried_in_date, slab_date) as drytime
	FROM ben.lot
	WHERE dried_in_date >= slab_date
    AND year(slab_date)=2015
	) diffs
GROUP BY drying_time

[Barand]

these are the results I'm getting so far

 

[Barand]

Revised results taking into account the slabs and dried_ins outstanding prior to the start of the year

 

[benanamen]

I apologize for the trouble with some data not matching the rules I provided. I am dealing with a fourth party to the third party and getting the right explanations is sometimes muddied. I have no problem making a donation to you for your help on this. I know it is time consuming and on the complicated side.

 

 

Lot #187 has a dried_in date in week 1 so is counted as dried_in for that week. Its schedule for work_type_1 is completed so, according to the bold type in the explanation you gave above, this is the last week we count it as dried in.

 

This would give counts of 1, 1, 1 for the three weeks if you apply your rules

 

Yes, your right, it SHOULD be 1,1,1 by the rules. What I was not told was that they were accounting for users currently being able to insert dates in the wrong order of the business rule flow. I will be building in restrictions in the app to force the correct flow. To get straight for our purpose here, just change the schedule.complete_date to 2015-01-08. That should give you a correct result of 1,1,1 per the rules.

 

 

 

How long should a slab take to dry in?

 

Ok, update on what dried in means. Doesn't change anything for our purpose though. A slab becomes a dried_in status when they put the roof on the house, therefore, the time for slab to dried_in could be same day, a week, or years and will be counted as a dried_in until the tile floor is finished which in our case means through the week of schedule.complete_date.

 

As you discovered, there was going to need accounting going back to the prior year. I was trying to keep things as simple as possible initially. Just an FYI on the final requirement when all is good is they will have an option to select a start and end date range from a weekly drop-down select. I should have no problem with that once the query is right.

 

Your help is greatly appreciated.

[Barand]

An explanation of what this query is doing:

First it counts the slab, dried_in and completed type1 dates for each week.

The slab count is a cumulative total but, each week, subtracting those that were dried_in in the previous week (@driedcf).

Similarly, a cumulative total of the dried_ins as also maintained and the number of completed type 1 from the previous week (@tiledcf) is subtracted.

There is also an initialization subquery which counts the outstanding slabs and drieds ins prior to the start date to get the starting figures.

SELECT s.week
, CONCAT(DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 DAY, '%e %b'),' - ',
     DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 + 6 DAY, '%e %b')) as wkcomm
, @slab:=@slab+ifnull(slabcount,0)-@driedcf as slab_count
, @dried:=@dried+ifnull(driedcount,0)-@tiledcf as dried_count
, @driedcf:=ifnull(driedcount,0) as x1
, @tiledcf:=ifnull(tiledcount,0) as x2
FROM (
    SELECT COUNT(lot_id) as slabcount
    , DATE_FORMAT(slab_date, '%V') as week
    FROM lot
    INNER JOIN block b USING (block_id)
    INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X')
    GROUP BY week
    ) s
INNER JOIN (
    SELECT @wk:=0
    , @day1:='2015-01-04'
    , @slab:=(SELECT count(lot_id) 
                FROM lot 
                INNER JOIN block b USING (block_id)
                INNER JOIN community c on b.community_id = c.community_id 
                    AND c.contract_type_id IN (1,3)
                WHERE slab_date < @day1 AND IFNULL(dried_in_date,'9999-12-31')>=@day1)
    , @dried:=(SELECT COUNT(lot.lot_id) FROM lot
                INNER JOIN block b USING (block_id)
                INNER JOIN community c on b.community_id = c.community_id 
                    AND c.contract_type_id IN (1,3)
                LEFT JOIN schedule s ON lot.lot_id=s.lot_id 
                    AND work_type_id=1 AND complete_date < @day1
                WHERE dried_in_date < @day1 AND s.lot_id IS NULL)
    , @driedcf:=0
    , @tiledcf:=0
    ) init
LEFT JOIN
    (
    SELECT COUNT(lot_id) as driedcount
    , DATE_FORMAT(dried_in_date, '%V') as week
    FROM lot
    INNER JOIN block b USING (block_id)
    INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') AND dried_in_date is not null
    GROUP BY week
    ) d    using (week)
LEFT JOIN
    (
    SELECT COUNT(s.lot_id) as tiledcount
    , DATE_FORMAT(complete_date, '%V') as week
    FROM lot
    INNER JOIN block b USING (block_id)
    INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    INNER JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date IS NOT NULL
    WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X')
    GROUP BY week
    ) t    using (week)
 ;

 

[benanamen]

Thanks Barand!

 

 

EDIT* !IMPORTANT: For some reason the first run of the query gives no results, then running again will give results. Doing an update that changes a count will show the change on a single run of the query after that first empty one. This was also tested by someone else outside my network.

 

What are the x1 and x2 columns and results?

 

Also, you posted a chart. What did you use for that output? I was going to use google charts but I like the display you did.

Edited January 11, 2016 by benanamen

[Barand]

It may be that the @day1 value isn't setting properly at start of first run.

 

x1 and x2 are just the carry forward values in the SELECT clause. I named them as that as they are just "dummy" values as far the required output is concerned.

 

I pasted my results into MS Excel to get the chart

[Barand]

Moving the "init" subquery to the top seems to cure the problem

SELECT s.week
, CONCAT(DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 DAY, '%e %b'),' - ',
     DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 + 6 DAY, '%e %b')) as wkcomm
, @slab:=@slab+ifnull(slabcount,0)-@driedcf as slab_count
, @dried:=@dried+ifnull(driedcount,0)-@tiledcf as dried_count
, @driedcf:=ifnull(driedcount,0) as x1
, @tiledcf:=ifnull(tiledcount,0) as x2
FROM 
    (
	SELECT @wk := 0 as init0
    , @day1 := '2015-01-04' as init1
    , @slab := (SELECT count(lot_id) 
				FROM lot 
                INNER JOIN block b USING (block_id)
				INNER JOIN community c on b.community_id = c.community_id 
					AND c.contract_type_id IN (1,3)
                WHERE slab_date < @day1 AND IFNULL(dried_in_date,'9999-12-31')>=@day1) as init2
    , @dried := (SELECT COUNT(lot.lot_id) FROM lot
				INNER JOIN block b USING (block_id)
                INNER JOIN community c on b.community_id = c.community_id 
					AND c.contract_type_id IN (1,3)
				LEFT JOIN schedule s ON lot.lot_id=s.lot_id 
					AND work_type_id=1 AND complete_date < @day1
				WHERE dried_in_date < @day1 AND s.lot_id IS NULL) as init3
    , @driedcf := 0 as init4
    , @tiledcf := 0 as init5
    ) init
INNER JOIN (
	SELECT COUNT(lot_id) as slabcount
	, DATE_FORMAT(slab_date, '%V') as week
	FROM lot
	INNER JOIN block b USING (block_id)
	INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X')
    GROUP BY week
	) s
LEFT JOIN
    (
	SELECT COUNT(lot_id) as driedcount
	, DATE_FORMAT(dried_in_date, '%V') as week
	FROM lot
	INNER JOIN block b USING (block_id)
	INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') AND dried_in_date is not null
    GROUP BY week
    ) d	using (week)
LEFT JOIN
    (
	SELECT COUNT(s.lot_id) as tiledcount
	, DATE_FORMAT(complete_date, '%V') as week
	FROM lot
	INNER JOIN block b USING (block_id)
	INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    INNER JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date IS NOT NULL
    WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X')
    GROUP BY week
    ) t	using (week)

 ; 

[Barand]

Have you had a look at HighCharts

 

http://www.highcharts.com/demo/line-basic

[benanamen]

!IMPORTANT: New data file attached

 

After testing this is the response I got. If you don't understand it let me know and I will get clarification.

 

 

 

The slab and dried in queries are not looking at the year…

 

Right now I have all the data date fields set in 2015 and I am showing the correct counts no matter how I change the dates unless I put 2016 for the year.

 

Week 1, 2, & 3 are all in January 2015…

 

Query results as of right now with the same year are:

 

                              Slabs      Dried Ins

 

Week 1:               2             2

Week 2:               2             3

Week 3:               3             2

 

ISSUES:

 

1)      Slab count year issue:      change lot id 195 dried in date year to 2016 (set to 1-9-15 right now) and you should have results of     week 1: 2             week 2: 3             week 3: 4             it does not change because it is counting it as if it were in January 2015.

2)      Dried In Count issue: change schedule id 25 complete date year to 2016 (set to 1-12-15 right now) and you should get results of            week 1: 2             week 2: 3             week 3: 3             it does not change because its counting it as if it were in January 2015

 

So it is counting the ssame month and day no matter what year it is.

 

barand-1-11-15.sql.txt

[Barand]

I think it's fixed. Try

SELECT s.week
, CONCAT(DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 DAY, '%e %b'),' - ',
     DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 + 6 DAY, '%e %b')) as wkcomm
, @slab:=@slab+ifnull(slabcount,0)-@driedcf as slab_count
, @dried:=@dried+ifnull(driedcount,0)-@tiledcf as dried_count
, @driedcf:=ifnull(driedcount,0) as x1
, @tiledcf:=ifnull(tiledcount,0) as x2
FROM 
    (
	SELECT @wk := 0 as init0
    , @day1 := '2015-01-04' as init1
    , @slab := (SELECT count(lot_id) 
				FROM lot 
                INNER JOIN block b USING (block_id)
				INNER JOIN community c on b.community_id = c.community_id 
					AND c.contract_type_id IN (1,3)
                WHERE slab_date < @day1 AND IFNULL(dried_in_date,'9999-12-31')>=@day1) as init2
    , @dried := (SELECT COUNT(lot.lot_id) FROM lot
				INNER JOIN block b USING (block_id)
                INNER JOIN community c on b.community_id = c.community_id 
					AND c.contract_type_id IN (1,3)
				LEFT JOIN schedule s ON lot.lot_id=s.lot_id 
					AND work_type_id=1 AND complete_date < @day1
				WHERE dried_in_date < @day1 AND s.lot_id IS NULL) as init3
    , @driedcf := 0 as init4
    , @tiledcf := 0 as init5
    ) init
INNER JOIN (
	SELECT COUNT(lot_id) as slabcount
	, DATE_FORMAT(slab_date, '%V') as week
	FROM lot
	INNER JOIN block b USING (block_id)
	INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X')
    GROUP BY week
	) s
LEFT JOIN
    (
	SELECT COUNT(lot_id) as driedcount
	, DATE_FORMAT(dried_in_date, '%V') as week
	FROM lot
	INNER JOIN block b USING (block_id)
	INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    WHERE DATE_FORMAT(dried_in_date, '%X') = DATE_FORMAT(@day1, '%X') AND dried_in_date is not null
    GROUP BY week
    ) d	using (week)
LEFT JOIN
    (
	SELECT COUNT(s.lot_id) as tiledcount
	, DATE_FORMAT(complete_date, '%V') as week
	FROM lot
	INNER JOIN block b USING (block_id)
	INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3)
    INNER JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date IS NOT NULL
    WHERE DATE_FORMAT(complete_date, '%X') = DATE_FORMAT(@day1, '%X')
    GROUP BY week
    ) t	using (week)
 ; 

[Barand]

revised results

[benanamen]

Response I got:

 

 

 

WOOOOHOOOO!!!!!!!  It’s perfectoJJJ

 

Thanks man! You are awesome!