# WEEKLY QUERY

Go to solution Solved by Barand,

## Recommended Posts

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.

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

##### Share on other sites

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

##### Share on other sites

these are the results I'm getting so far

##### Share on other sites

Revised results taking into account the slabs and dried_ins outstanding prior to the start of the year

##### Share on other sites

I apologize for the trouble with some data not matching the rules I provided. I am dealing with a fourth party to the third party and getting the right explanations is sometimes muddied. I have no problem making a donation to you for your help on this. I know it is time consuming and on the complicated side.

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

Yes, your right, it SHOULD be 1,1,1 by the rules. What I was not told was that they were accounting for users currently being able to insert dates in the wrong order of the business rule flow. I will be building in restrictions in the app to force the correct flow. To get straight for our purpose here, just change the schedule.complete_date to 2015-01-08. That should give you a correct result of 1,1,1 per the rules.

How long should a slab take to dry in?

Ok, update on what dried in means. Doesn't change anything for our purpose though. A slab becomes a dried_in status when they put the roof on the house, therefore, the time for slab to dried_in could be same day, a week, or years and will be counted as a dried_in until the tile floor is finished which in our case means through the week of schedule.complete_date.

As you discovered, there was going to need accounting going back to the prior year. I was trying to keep things as simple as possible initially. Just an FYI on the final requirement when all is good is they will have an option to select a start and end date range from a weekly drop-down select. I should have no problem with that once the query is right.

##### Share on other sites

An explanation of what this query is doing:

First it counts the slab, dried_in and completed type1 dates for each week.

The slab count is a cumulative total but, each week, subtracting those that were dried_in in the previous week (@driedcf).

Similarly, a cumulative total of the dried_ins as also maintained and the number of completed type 1 from the previous week (@tiledcf) is subtracted.

There is also an initialization subquery which counts the outstanding slabs and drieds ins prior to the start date to get the starting figures.

```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 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
INNER JOIN (
SELECT @wk:=0
, @day1:='2015-01-04'
, @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)
, @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)
, @driedcf:=0
, @tiledcf:=0
) init
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(slab_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(slab_date, '%X') = DATE_FORMAT(@day1, '%X')
GROUP BY week
) t    using (week)
;
```

##### Share on other sites

Thanks Barand!

EDIT* !IMPORTANT: For some reason the first run of the query gives no results, then running again will give results. Doing an update that changes a count will show the change on a single run of the query after that first empty one. This was also tested by someone else outside my network.

What are the x1 and x2 columns and results?

Also, you posted a chart. What did you use for that output? I was going to use google charts but I like the display you did.

Edited by benanamen
##### Share on other sites

It may be that the @day1 value isn't setting properly at start of first run.

x1 and x2 are just the carry forward values in the SELECT clause. I named them as that as they are just "dummy" values as far the required output is concerned.

I pasted my results into MS Excel to get the chart

##### Share on other sites

Moving the "init" subquery to the top seems to cure the problem

```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(slab_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(slab_date, '%X') = DATE_FORMAT(@day1, '%X')
GROUP BY week
) t	using (week)

;
```
##### Share on other sites

!IMPORTANT: New data file attached

After testing this is the response I got. If you don't understand it let me know and I will get clarification.

The slab and dried in queries are not looking at the year…

Right now I have all the data date fields set in 2015 and I am showing the correct counts no matter how I change the dates unless I put 2016 for the year.

Week 1, 2, & 3 are all in January 2015…

Query results as of right now with the same year are:

Slabs      Dried Ins

Week 1:               2             2

Week 2:               2             3

Week 3:               3             2

ISSUES:

1)      Slab count year issue:      change lot id 195 dried in date year to 2016 (set to 1-9-15 right now) and you should have results of     week 1: 2             week 2: 3             week 3: 4             it does not change because it is counting it as if it were in January 2015.

2)      Dried In Count issue: change schedule id 25 complete date year to 2016 (set to 1-12-15 right now) and you should get results of            week 1: 2             week 2: 3             week 3: 3             it does not change because its counting it as if it were in January 2015

So it is counting the ssame month and day no matter what year it is.

barand-1-11-15.sql.txt

##### Share on other sites

• Solution

I think it's fixed. Try

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

revised results

##### Share on other sites

Response I got:

Thanks man! You are awesome!

##### 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.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.