Jump to content

WEEKLY QUERY


benanamen
Go to solution Solved by Barand,

Recommended Posts

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

post-179806-0-29495800-1452208975_thumb.jpg

post-179806-0-27001300-1452208991_thumb.jpg

post-179806-0-33501200-1452209138_thumb.png

Edited by benanamen
Link to comment
Share on other sites

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 -

 

post-144491-0-10897400-1452259768_thumb.png

 

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

 

 

 

 

 

 

 

 

 

 

 

Link to comment
Share on other sites

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.

post-179806-0-99632400-1452276040_thumb.jpg

Edited by benanamen
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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)
;
Link to comment
Share on other sites

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)
;
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.