WEEKLY QUERY

[benanamen]

I have the following UNION query that works. I need this same thing so that I don't have to UNION fifty two times to get the weekly results for a year.

 

Attached is the DB Import Dump, Current Query Result, The desired output,  and a chart graphic of what I am looking to end up with. Current data is only going to give three results. Should just have zero count for all other weeks with no data.

SELECT
Count(l.lot_id) AS slab_count,
WEEK ('2015-01-04') AS week_number
FROM
lot l
 
LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id
 
WHERE
l.dried_in_date IS NULL 
AND l.slab_date <= '2015-01-10'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
OR
l.slab_date <= '2015-01-10'
AND l.dried_in_date >= '2015-01-04'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
 
UNION
/*  WEEK 2    */
SELECT
Count(l.lot_id) AS slab_count,
WEEK ('2015-01-11') AS week_number
FROM
lot l
 
LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id
 
WHERE
l.dried_in_date IS NULL 
AND l.slab_date <= '2015-01-17'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
OR
l.slab_date <= '2015-01-17'
AND l.dried_in_date >= '2015-01-11'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
 
 
UNION
/*  WEEK 3    */
SELECT
Count(l.lot_id) AS slab_count,
WEEK ('2015-01-18') AS week_number
FROM
lot l
 
LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id
 
WHERE
l.dried_in_date IS NULL 
AND l.slab_date <= '2015-01-24'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
OR
l.slab_date <= '2015-01-24'
AND l.dried_in_date >= '2015-01-18'

data.sql.txt

Edited January 7, 2016 by benanamen

[mac_gyver]

one single query would look more like this - 

SELECT
Count(l.lot_id) AS slab_count,
WEEK (l.slab_date) AS week_number
FROM
lot l

LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id

WHERE
YEAR(l.slab_date) = 2015
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)

GROUP BY week_number

however, i/we don't know what your rules are concerning how the dried_in_date should factor into the result. the query you showed doesn't produce the result you want, therefore it doesn't show us the correct rules for the value.

 

the following is the same basic query as above without the GROUP BY - 

SELECT
l.slab_date,
WEEK (l.slab_date) AS week_number,
l.dried_in_date,
WEEK (l.dried_in_date) AS dry_week_number
FROM
lot l

LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id

WHERE
YEAR(l.slab_date) = 2015
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)

ORDER BY week_number

it produces this result from your data -

 

 

so, from that, how do you get your desired slab count of 2,1,2 for week 1,2,3?

 

 

 

 

 

 

 

 

 

 

 

[Barand]

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.

[benanamen]

My Bad, made error on desired results count image. Corrected image attached

 

Slab count should be 2, 3, and 4 as in the current.jpg. With the posted data and query, those results are the correct counts.

 

Ok, the rules are fairly simple once you get a grasp of this. For clarification we are talking about housing lots where a house will eventually get built. A 'Slab' is the date when they pour the drive way cement. dried_in is the date when the cement is dried enough to continue work.

 

The "rules" are in the where condition but can be confusing.

 

A 'slab' should be counted as a slab every week that it is NOT a dried_in in the same week.

 

Say week one and two we have a slab with no dried in date during those two weeks, the count is 1 for each week. Week 3 now has a dried_in date for that week, so we dont count it as a slab anymore (Next phase of this we will be doing dried_in counts but not yet.)

 

So basically for each individual week, they are wanting to know how many lots were a slab status. (Final query will actually be getting a weekly count of the dried_in statuses per week. See chart image for example)

 

 

 

* Week Starts Sunday.

Edited January 8, 2016 by benanamen

[benanamen]

Just got some clarification

 

Say week one and two we have a slab with no dried in date during those two weeks, the count is 1 for each week. Week 3 now has a dried_in date for that week, so we dont count it as a slab anymore (Next phase of this we will be doing dried_in counts but not yet.)

 

Say week one and two we have a slab with no dried in date during those two weeks, the count is 1 for each week. Week 3 now has a dried_in date for that week, so this will be the last week it is counted as a slab. So all three weeks will have a count of 1 but week 4 and on, it will no longer be counted as a slab.

 

Part2: That same lot is now going to be started to be counted as a dried_in in part2 coming up. (Week 3 lot is a slab AND a dried in status)

Edited January 8, 2016 by benanamen

[Barand]

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)
;

[Barand]

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)
;

[benanamen]

Thanks @Barand,

We are looking good on the slab_count side. The following is a tested query for the dried in count. Notable changes are the join of the schedule table and conditions regarding that table. These only apply to the dried in count. The following should be used to update the dried_in result of the previous query you posted. 

 

!IMPORTANT

Also note the updated SQL attachment. The slab_count should still be the same as it was before. Expected dried_in results are week1=0, week2=1, week3=1 (Slab should still be counts of 2,3,4).

 

Using the live data I sent you in PM I am seeing some negative counts for some slab records, not sure why. ie: -xxx.

 

The only other addition I am looking for which you did in private chat query is the display of the dates equal to the week numbers. Optimally a concat of the week_start - week_end dates for the display shown in the previous chart image, but at minimum the start date of each week starting on Sundays. 

 

Thank you so much. It isn't often I get stumped so that says a lot about your skills.

SELECT
Count(l.lot_id) AS dried_in_count,
WEEK ('2015-01-04') AS week_number
FROM
lot l

LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id
LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id

WHERE
l.dried_in_date <= '2015-01-10'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
    AND NOT (
      s.work_type_id IN (1)
      AND s.complete_date IS NOT NULL
    )

OR
l.dried_in_date <= '2015-01-10'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
AND (s.work_type_id=1 AND s.complete_date >= '2015-01-04')

UNION

SELECT
Count(l.lot_id) AS dried_in_count,
WEEK ('2015-01-11') AS week_number
FROM
lot l

LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id
LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id

WHERE
l.dried_in_date <= '2015-01-17'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
    AND NOT (
      s.work_type_id IN (1)
      AND s.complete_date IS NOT NULL
    )

OR
l.dried_in_date <= '2015-01-17'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
AND (s.work_type_id=1 AND s.complete_date >= '2015-01-11')

UNION

SELECT
Count(l.lot_id) AS dried_in_count,
WEEK ('2015-01-18') AS week_number
FROM
lot l

LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id
LEFT JOIN block AS b ON l.block_id = b.block_id
LEFT JOIN community AS c ON b.community_id = c.community_id

WHERE
l.dried_in_date <= '2015-01-24'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
    AND NOT (
      s.work_type_id IN (1)
      AND s.complete_date IS NOT NULL
    )

OR
l.dried_in_date <= '2015-01-24'
AND (c.contract_type_id = 1 OR c.contract_type_id= 3)
AND (s.work_type_id=1 AND s.complete_date >= '2015-01-18')

new_data.sql.txt

Edited January 9, 2016 by benanamen

[Barand]

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.

[Barand]

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 |
+--------+------------+-----------+---------------+------------+------------------+

Edited January 9, 2016 by Barand

[benanamen]

1. I assume you used the latest sql dump I posted, otherwise it wont work.

 

2. I posted a standalone query for the dried_in only. Using the latest DB dump and running the standalone query you will get the results of 0,1,1

 

Please verify you are at this point while I work on getting the correct non technical explanation.

 

* Your previous post has no consideration for conditions with the schedule table which is why you are not getting the correct result as you would from my dried_in query.

 

In a nutshell, For a dried_in lot we need to know if tile flooring has been scheduled to be installed or not and if it has been scheduled, has the job been completed. This will determine if it is counted or not. Plain detailed explanation coming shortly.

Edited January 9, 2016 by benanamen

[Barand]

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?

Edited January 9, 2016 by Barand

[benanamen]

Ok, plain version

 

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.

 

 

 

Yes, I saw #7. It is showing the start end weeks. No problem there. There are no double not nots. The example is a union query, to display a week at a time with the exact conditions just different week start/end dates

 

Just to double check, I downloaded the data from that post #8 in a new db, copy/pasted the posted query from post#8 and get  0,1,1 as it should.

Edited January 9, 2016 by benanamen

[Barand]

What should happen if the tiling is completed before both the slab and the dried in processes happen? (As in your data)

[benanamen]

What should happen if the tiling is completed before both the slab and the dried in processes happen? (As in your data)

 

It will never happen that way. The order of the process is forced in the app. It is impossible for a user to do it in the wrong order.

 

Process

First status=lot

Second=slab

Third=dried_in

Fourth= Frame

Fith and final=Drywall

[Barand]

your data would suggest otherwise

[benanamen]

You cant go by the test data as far as that goes. Minimal records are in there for testing. If you looked over the actual live data I gave you in pm you should see it it not so. If you see whatever problem you are referring to is in the live full database, let me know as it should not be so and would indicate a problem in the application. The test data has actually been manipulated directly on the DB and not through the app.

 

If your interested I could PM you the url and superadmin login to the app on the dev server.

Edited January 9, 2016 by benanamen

[Barand]

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.

[Barand]

If you looked over the actual live data I gave you in pm you should see it it not so. If you see whatever problem you are referring to is in the live full database, let me know as it should not be so and would indicate a problem in the application.

 

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)

[Barand]

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

[benanamen]

Yes, @Barand,

You are correct. Those results are bad data. Thank you

 

The reason it was able to get in there is that this app has been in continual development for over a year and at the time those records were added, there were no business rules or code in place to keep that from happening. At present, it is just as I told you, impossible through the app for that to happen now. Fortunately, it is someone else job to "Fix" the data.

 

So back to the dried_count, did you understand the explanation? It is really the same as the slab count except for considering conditions related to the scheduling/completion of tile flooring.

 

Let me know if you need anything else. You have been a tremendous help. This app is the most complex app I have ever built and I have learned quite a bit doing it.

Edited January 9, 2016 by benanamen

[Barand]

 

At present, it is just as I told you, impossible through the app for that to happen now.

How recent is "now". It was still happening in November.

 

 

So back to the dried_count, did you understand the explanation?

Yes.

[benanamen]

Ahhh, ok, Just realized the live is still running phase one version. Phase two with all the changes has not gone live yet, so as of now, they can do many bad things that have been fixed or changed in phase two. Supposed to go live with phase two in a week or so and actually this Chart is the last part to finish Phase 2.

Edited January 9, 2016 by benanamen

[Barand]

That gives you a week or so, then, to do some data cleansing.

[benanamen]

Yeah, there's no point doing it now since they can still make the same mistakes until everything gets updated. Will you be able to help me with the last part of the query?