benanamen Posted January 15, 2016 Share Posted January 15, 2016 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 queryResults: Correct result for year 2015New 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) ; Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/ Share on other sites More sharing options...
Barand Posted January 15, 2016 Share Posted January 15, 2016 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? Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529806 Share on other sites More sharing options...
benanamen Posted January 16, 2016 Author Share Posted January 16, 2016 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529825 Share on other sites More sharing options...
Barand Posted January 16, 2016 Share Posted January 16, 2016 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. Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529847 Share on other sites More sharing options...
benanamen Posted January 16, 2016 Author Share Posted January 16, 2016 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. Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529860 Share on other sites More sharing options...
Barand Posted January 16, 2016 Share Posted January 16, 2016 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 . . . Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529861 Share on other sites More sharing options...
benanamen Posted January 16, 2016 Author Share Posted January 16, 2016 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. Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529862 Share on other sites More sharing options...
benanamen Posted January 16, 2016 Author Share Posted January 16, 2016 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529863 Share on other sites More sharing options...
benanamen Posted January 16, 2016 Author Share Posted January 16, 2016 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529865 Share on other sites More sharing options...
benanamen Posted January 16, 2016 Author Share Posted January 16, 2016 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529867 Share on other sites More sharing options...
Barand Posted January 16, 2016 Share Posted January 16, 2016 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? Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529868 Share on other sites More sharing options...
benanamen Posted January 16, 2016 Author Share Posted January 16, 2016 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529869 Share on other sites More sharing options...
Barand Posted January 16, 2016 Share Posted January 16, 2016 Does this version give you the same problem? If so, post your revised data. benanamen_1.php benanamen_2.php date_opt_funcs.php Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529874 Share on other sites More sharing options...
benanamen Posted January 17, 2016 Author Share Posted January 17, 2016 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529921 Share on other sites More sharing options...
Barand Posted January 17, 2016 Share Posted January 17, 2016 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529924 Share on other sites More sharing options...
benanamen Posted January 17, 2016 Author Share Posted January 17, 2016 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/300404-weekly-chart-part-2/#findComment-1529927 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.