WEEKLY CHART PART 2

[benanamen]

Weekly Chart Part Two

 

Need default view to be last 3 months based on start date of current week start date

 

Need to be able to dynamically select a start and end week maximum 52 week range from drop-down select

 

Will need to be able to cross years. Current query is for one particular full year.

 

The following query will currently give correct results for one particular full year.

 

 

What I tried: The following query
Results: Correct result for year 2015
New Expected results: See above

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]

That looks more like a requirements specification than a request for help.

 

What have you tried so far to vary the dates? Do you have a form?

[benanamen]

For starters I am just trying to get the query to read across years. This query is way beyond my current knowledge of Mysql.

 

The ONLY line that has any hard coded date to change is: 

, @day1 := '2015-01-04' as init1

I am testing with three lot records that are slabs only. One each of Jan 2014,2015, and 2016. If I change the only hard coded date to the start of week one in 2014 (2015-01-05) I expect to get a slab count of three. I only get a slab count of one which is the one record in 2014. Same thing if I change it to 2015, I should get a count of two, but I get one (Expect the one slab in 2015 and the one slab in 2016)

 

I have no idea whatsoever of what to do to be able to set a start week to start counting from, let alone an end week. As is the query only accurately counts for a specific year.

 

I also noticed that it doesnt care about the day or month. I can put the day and month after the slab date and it still gets counted. I also noticed it requires a complete mysql date to function even though the query seems to only be looking at the year portion.

Edited January 16, 2016 by benanamen

[Barand]

First thing you need is a form for the user to select the start and end dates. This is where you would preset the start and end with the defaults of 3 months ago and now. I would recommend using date pickers, or date select dropdown menus, so you have control over the format of the dates that are submitted. As you are charting in weeks you would also want to ensure that start dates fall on Sundays and end dates fall on Saturdays. (Alternatively, enter a start date and the number of weeks to be plotted in the form instead of two dates).

 

You would then change the query to accept @date2 as the end date (in the same way that @date1 is set to the start date). When checking dates in the query, it currently checks that dates are in the required year. You would change this to check that the dates fall between @date1 and @date2.

[benanamen]

First thing you need is a form for the user to select the start and end dates. This is where you would preset the start and end with the defaults of 3 months ago and now. I would recommend using date pickers, or date select dropdown menus, so you have control over the format of the dates that are submitted. As you are charting in weeks you would also want to ensure that start dates fall on Sundays and end dates fall on Saturdays. (Alternatively, enter a start date and the number of weeks to be plotted in the form instead of two dates).

I already understood every bit of that already but thanks for telling me.

You would then change the query to accept @date2 as the end date (in the same way that @date1 is set to the start date). When checking dates in the query, it currently checks that dates are in the required year. You would change this to check that the dates fall between @date1 and @date2.

The only problem is there is no @date2. For initial testing I shouldn't need a form yet. I should just be able to hard code the start/end dates in the query and only use the console.

 

If I need to add the @date2 somehow, somewhere, I am completely clueless about doing that.

[Barand]

 

You would then change the query to accept @date2 as the end date (in the same way that @date1 is set to the start date)

    SELECT @wk:=0 as init0
    , @day1 := '2015-10-11' as init1
    , @day2 := '2016-01-09' as init6
      . . .

[benanamen]

Are we looking at the same query?  There is NO @date2.

 

Do I need to add it? And if so, I have no idea where it would be added or how it would be referenced to read the date as an end date.

[benanamen]

Here is my guess which does not work. I get no results. I expect to get a slab count of 3 crossing three years. (Not in code tags so I can highlight my changes) My slab dates are 2014-01-15, 2015-01-15, and 2016-01-15

 

 

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 := '2014-01-04' as init1 // Start Date

    , @day2 := '2016-01-16' as init6 // End Date

    , @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 BETWEEN @day1 AND @day2 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)

 ;

Edited January 16, 2016 by benanamen

[benanamen]

Ok, so if I take the query apart and run the slab count portion I get the expected result of 3, but it doesnt work as a whole.

SELECT
@day1 := '2014-01-04' as init1,
@day2 := '2016-01-16' as init6,

@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 BETWEEN @day1 AND @day2 AND IFNULL(dried_in_date,'9999-12-31')>=@day1) as init2

[benanamen]

Not sure what was going on before. I am getting a result with the entire query I posted with changes in #8 (Minus the comments), but, I am getting a slab count of 4. There are only three records in the DB so it should not be possible to count more slab dates than exist.

 

Even still, running the query from post #9 does give a correct count of 3.

 

 

By the way, why do you put the commas from the previous line at the start of the next lines after it. I find that very confusing to work with.

Edited January 16, 2016 by benanamen

[Barand]

You may have only 3 but joining to a table with >1 matching records can increase that.

 

You check slab date is within the date range but what about dried_in and complete dates?

[benanamen]

You check slab date is within the date range but what about dried_in and complete dates?

 

It should work with just slab dates only. If the 3 records are slab status, their would not be dried_in dates or complete dates for those 3 records so, I should have a count of 3 for slabs and a count of 0 for dried ins. Dried count is zero as expected, but I get 4 slabs and there are only 3 slabs (three records with a slab date only.)

Edited January 16, 2016 by benanamen

[Barand]

Does this version give you the same problem? If so, post your revised data.

 

benanamen_1.php

benanamen_2.php

date_opt_funcs.php

[benanamen]

This is not correct. I think we are really close though.

 

Lets forget the count calculations for a moment (They work right). If our start/end date range covers 3 weeks, we should have three rows of data. If our start/end date range covers twenty weeks, we should have 20 rows of data etc, etc..

 

Pseudo Php but needs in SQL

$week_date_range = 5 consecutive week periods that can cross years if needed

 foreach ($week_date_range AS $week){

// do slab calculation

// do dried calculation

echo "Slabs=$slab_count, Dried= $dried_count</br>"; // This would give us 5 rows regardless of the calculations and the results

}

Lets say there is only one record in the lot table and it only has a slab date of Dec 30, 2013. No dried_in date. This record is a slab status from Dec 30, 2013 until today 2016.

 

Our start date per the earliest drop down is is Dec 29, 2013, so we are within the start date using that date. If we were to select the latest end date of Jan 9, 2016, we should get a row for every week starting from Dec 29 2013 with a count of one slab_count per row until the week of Jan 9, 2016. If I counted right that would be 104 rows (weeks) with a count of one slab_count per row. Since it is still a slab to today, it gets counted as a slab every week period up to the end date selected.

 

What they want to know is, every week period that a lot is a slab status it should be counted in that week period and displayed in that week period

 

So if a lot is a slab status during three start/end week periods, we would have three rows with a count of 1 per row. If it was a slab status for 10 start/end week periods we would have 10 rows with a count of one per row. Same thing applies for the dried count per the conditions we have already worked out.

 

Right now with one record with the earliest drop down start date and the latest drop down end date, there is only one row returned for the first week. There should be a row for every week up to the end week selected.

 

The good news is the calculations for slab and dried are good. What we are missing is a per week result row for the selected date ranges.

 

I know it is confusing. I am still barely grasping it.

Edited January 17, 2016 by benanamen

[Barand]

With a single record (slab) then to get "1" for every week for the year you would need to create a "week" table and left join lot ON weekno. At present you only get weeks for which there is data

[benanamen]

At present you only get weeks for which there is data

That is what I was thinking was happening.

 

With a single record (slab) then to get "1" for every week for the year you would need to create a "week" table and left join lot ON weekno. 

 

Ok, now I am back to being completely lost again. For what that means, will that will also suffice for the dried part?

 

to get "1" for every week for the year 

 

Just to be clear, it may not be a year. The time period will be whatever the select start/end date range is which may be a week, months, a year, or cross a year or more.

 

I know I will be studying what you have done for some time to come. You are quite the Mysql Master.

Edited January 17, 2016 by benanamen