Barand
-
Posts
24,605 -
Joined
-
Last visited
-
Days Won
830
Everything posted by Barand
-
these are the results I'm getting so far
-
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
-
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
-
here's an example <?php $dates = [ '2015-09-12', '2015-10-15', '2015-11-23', '2015-12-08', '2015-12-28' ]; $now = new DateTime(); $tdata = ''; foreach ($dates as $dt) { $closed = new DateTime($dt); $age = $now->diff($closed)->days; $dateout = $closed->format('m/d/Y'); if ($age > 90) { $class = 'over90'; } elseif ($age > 60) { $class = 'over60'; } elseif ($age > 30) { $class = 'over30'; } else $class = 'default'; $tdata .= "<tr><td class='$class'>$dateout</td></tr>"; } ?> <html> <head> <title>Example</title> <style type='text/css'> td.default {color: black;} td.over30 {background-color: green; color: white;} td.over60 {background-color: orange; color: white;} td.over90 {background-color: red; color: white;} </style> </head> <body> <table> <?=$tdata?> </table> </body> </html>
-
That gives you a week or so, then, to do some data cleansing.
-
How recent is "now". It was still happening in November. Yes.
-
Another 21 when complete_date compared against the slab date SELECT lot.lot_id , lot.slab_date , s.complete_date , work_type_id , work_type_description as descrip FROM schedule s INNER JOIN lot USING (lot_id) INNER JOIN work_type USING (work_type_id) WHERE slab_date > complete_date; +--------+------------+---------------+--------------+--------------+ | lot_id | slab_date | complete_date | work_type_id | descrip | +--------+------------+---------------+--------------+--------------+ | 1219 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1220 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1221 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1222 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1223 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1269 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1270 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1271 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1272 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 11 | 2014-10-20 | 2013-09-03 | 1 | Tile | | 144 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 145 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 146 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 264 | 2015-03-24 | 2015-03-17 | 1 | Tile | | 661 | 2015-04-09 | 2015-03-17 | 1 | Tile | | 1273 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 2300 | 2015-08-14 | 2015-05-29 | 1 | Tile | | 1537 | 2015-05-11 | 2015-05-08 | 2 | Backsplash | | 1219 | 2015-04-03 | 2015-03-25 | 4 | Bath tile | | 2300 | 2015-08-14 | 2015-05-19 | 15 | Window sills | | 1932 | 2015-09-01 | 2015-08-24 | 15 | Window sills | +--------+------------+---------------+--------------+--------------+ 21 rows in set (0.04 sec) and another 8 where it was dried_in before the slab was laid SELECT lot_id , slab_date , dried_in_date FROM lot WHERE dried_in_date < slab_date; +--------+------------+---------------+ | lot_id | slab_date | dried_in_date | +--------+------------+---------------+ | 311 | 2014-12-30 | 2014-10-08 | | 315 | 2014-10-08 | 2014-09-12 | | 316 | 2014-10-08 | 2014-09-12 | | 809 | 2015-03-30 | 2015-03-26 | | 942 | 2015-02-02 | 2014-02-02 | | 1541 | 2014-11-25 | 2014-02-19 | | 1601 | 2015-04-17 | 2015-03-17 | | 1629 | 2015-04-18 | 2015-03-18 | +--------+------------+---------------+ 8 rows in set (0.02 sec) Lots of "impossible" things going on. Looks like someone's going to be busy on Monday
-
Found 25 of them SELECT lot.lot_id , lot.dried_in_date , s.complete_date , work_type_id , work_type_description FROM schedule s INNER JOIN lot USING (lot_id) INNER JOIN work_type USING (work_type_id) WHERE dried_in_date > complete_date; +--------+---------------+---------------+--------------+-----------------------+ | lot_id | dried_in_date | complete_date | work_type_id | work_type_description | +--------+---------------+---------------+--------------+-----------------------+ | 1219 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1220 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1221 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1222 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1223 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1269 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1270 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1271 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1272 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 11 | 2015-01-05 | 2013-09-03 | 1 | Tile | | 140 | 2015-11-12 | 2015-03-17 | 1 | Tile | | 144 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 145 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 146 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 264 | 2015-03-24 | 2015-03-17 | 1 | Tile | | 386 | 2016-01-30 | 2015-03-17 | 1 | Tile | | 661 | 2015-04-09 | 2015-03-17 | 1 | Tile | | 843 | 2015-08-19 | 2015-03-17 | 1 | Tile | | 1273 | 2015-04-03 | 2015-0317 | 1 | Tile | | 2300 | 2015-08-14 | 2015-05-29 | 1 | Tile | | 843 | 2015-08-19 | 2015-03-17 | 2 | Backsplash | | 1537 | 2015-05-11 | 2015-05-08 | 2 | Backsplash | | 1219 | 2015-04-03 | 2015-03-25 | 4 | Bath tile | | 2300 | 2015-08-14 | 2015-05-19 | 15 | Window sills | | 1932 | 2015-09-01 | 2015-08-24 | 15 | Window sills | +--------+---------------+---------------+--------------+-----------------------+ 25 rows in set (0.05 sec)
-
By your rules, a lot remains i the dried_in state until the week the tiling is completed. In the case of the first lot (187) there is no such subsequent date and it will therefore remain as dried_in for an infinite period. Right now I am finding it very difficult to summon up any enthusiasm to give up my time when you cannot be bothered to produce test data that is fit for purpose. There is insufficient schedule data to test all situations.
-
How are you storing the date in your db table? It should be type DATE, format YYYY-MM-DD.
-
your data would suggest otherwise
-
What should happen if the tiling is completed before both the slab and the dried in processes happen? (As in your data)
-
I am using the data you posted in reply #8. I am trying to understand your convoluted WHERE clause regarding the schedule data (with its double NOT NOT's) and how your query moves the dried_in dates from one week to another. Regarding the week start and end dates - did you not read my reply #7?
-
Looking at the lot data you have provided there is clearly 1 dried_in date in week 1, another in week 2 and none in week 3 so how do you get counts of 0, 1, 1 for those three weeks? SELECT lot_id , slab_date , DATE_FORMAT(slab_date, '%V') as slab_week , dried_in_date , DATE_FORMAT(dried_in_date, '%V') as dried_week , contract_type_id FROM lot INNER JOIN block USING (block_id) INNER JOIN community USING (community_id) ORDER BY lot_id, slab_date, dried_in_date; +--------+------------+-----------+---------------+------------+------------------+ | lot_id | slab_date | slab_week | dried_in_date | dried_week | contract_type_id | +--------+------------+-----------+---------------+------------+------------------+ | 187 | 2015-01-07 | 01 | 2015-01-08 | 01 | 1 | | 192 | 2015-01-12 | 02 | NULL | NULL | 1 | | 193 | 2015-01-13 | 02 | 2015-01-15 | 02 | 3 | | 194 | 2015-01-19 | 03 | NULL | NULL | 3 | | 195 | 2015-01-07 | 01 | NULL | NULL | 1 | | 196 | 2015-01-19 | 03 | NULL | NULL | 3 | | 197 | 2015-01-07 | 01 | NULL | NULL | 2 | +--------+------------+-----------+---------------+------------+------------------+
-
It looks like your coding is is from the 1990's when register_globals were used. Those param values need to come from the $_POST array. And if your quantity and price columns really are string types then you need to redesign your table.
-
Can you please explain, in English rather than SQL, how the dried_in count should work and the relationship between dried_in and the schedule. Things became a lot clearer after your previous explanation of slab versus dried_in.
-
Revised version to give you the week start and end dates for the chart. You need to pass it the date (2015-01-04) of the first day of the first week. SELECT s.week , @slab:=@slab+slabcount-@dried as slab_count , @dried:=driedcount as dried_in_count , @day1 + INTERVAL (s.week-1)*7 DAY as wkcomm , @day1 + INTERVAL (s.week-1)*7 + 6 DAY as wkend FROM ( SELECT COUNT(lot_id) as slabcount , week(slab_date) 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 (SELECT @wk:=0, @slab:=0, @dried:=0) init WHERE YEAR(slab_date)=2015 GROUP BY week ) s INNER JOIN (SELECT @wk:=0, @slab:=0, @dried:=0, @day1:='2015-01-04') init LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , week(dried_in_date) 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 YEAR(dried_in_date)=2015 GROUP BY week ) d using (week) ;
-
Include Checkbox in Simple PHP Calendar Script
Barand replied to Gazzahbay's topic in PHP Coding Help
Make the date format Y-m-d and NOT d-m-Y -
this should give you both the slab count and the dried_in count SELECT s.week , @slab:=@slab+slabcount-@dried as slab_count , @dried:=driedcount as dried_in_count FROM ( SELECT COUNT(lot_id) as slabcount , week(slab_date) 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 (SELECT @wk:=0, @slab:=0, @dried:=0) init WHERE YEAR(slab_date)=2015 GROUP BY week ) s INNER JOIN (SELECT @wk:=0, @slab:=0, @dried:=0) init LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , week(dried_in_date) 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 YEAR(dried_in_date)=2015 GROUP BY week ) d using (week) ;
-
You have $stocktakename=$_POST['StocktakeName]; but that does not match the name in your form <input name="stocktakename" type="text" required="required" id="stocktakename" size="50">
-
I also rewrote it as a single select but, as I got counts of 2,2,2 for each week and couldn't reproduce the "correct" results of 2,3,4 I gave up.
-
SELECT whatever FROM mytable WHERE mycol BETWEEN '1A' AND '5D'
-
You need to put in some debug code, such as this to check the POST data echo '<pre>',print_r($_POST, true),'</pre>'; and also check if the query was successful if (!$resultSet) die($mysqli->error);
-
Sorry, I have no psychic powers, and, as I cannot see your screen from here, you'll have to post the query and the exact error message. Also, post your table structures with exact column names. Use this type of query for each table SHOW CREATE TABLE users;
-
given the structure is +-------------+ +-------------+ +--------------+ | rank | | user | | group | +-------------+ +-------------+ +--------------+ | rank_id(PK) |-----+ | user_id(PK) |------+ +-------| group_id(PK) | | rankname | | | username | | | | groupname | +-------------+ +----<| user_rank | | +--------------+ | +--------------+ +-------------+ | | user_group | | | +--------------+ | +----<| user_id(PK) | | | group_id(PK) |>--+ +--------------+ then you could SELECT g.groupname , r.rank_id , r.rankname , u.username FROM user_group ug INNER JOIN group g USING (group_id) INNER JOIN user u USING (user_id) INNER JOIN rank r ON u.user_rank = r.rank_id WHERE ug.group_id = 34 ORDER BY u.rank_id, u.username
