Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Hi All,

Below is a query that I am running and it is kind of slow (30 + secs). Is there a way to optimize it. Also the query will be incorrect for the month of January, if the franchise did not post anything:

Thank you for your help in advance

Below is a query that I am running and it is kind of slow (30 + secs). Is there a way to optimize it. Also the query will be incorrect for the month of January, if the franchise did not post anything:

```
CREATE TEMPORARY TABLE GrossRev1
SELECT c.ID, IFNULL(R.GrossRevenue,0) AS GROSSREVENUE, IFNULL(R.RoyaltiesOwed,0) ROYALTIESOWED,
IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS, IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS,
IFNULL(Total_hours,0) TOTAL_HRS, c.year,c.month
FROM
(SELECT a.ID, a.franchise_no, a.closeddate, b.year,b.month FROM franchises AS a,
(SELECT DISTINCT year, month FROM royalties WHERE year = 2015
) as b) as c
LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month
LEFT JOIN (SELECT h.franchiseno AS fn, h.Month, h.year,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hoursbilled) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.Year = 2015 -- and h.month = 9
GROUP BY
h.franchiseno,h.reportmonth, h.reportyear) h1 ON h1.fn = R.franchiseno AND h1.ReportMonth = R.Month AND h1.year = R.year
WHERE (c.closeddate IS NULL OR c.closeddate = '0000-00-00')
ORDER BY c.year DESC, c.month DESC,c.ID ASC;
SELECT GR.*,
CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.GROSSREVENUE
ELSE GR.GROSSREVENUE END CURRENT_MONTH_FORECAST_AGR,
CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.RoyaltiesOwed
ELSE GR.ROYALTIESOWED END CURRENT_MONTH_FORECAST_TOTAL_ROYALTIES
FROM GrossRev1 GR
LEFT JOIN (SELECT RL.franchiseno, RL.GROSSREVENUE, RL.RoyaltiesOwed, RL.year, RL.month
FROM royalties_log RL
) R ON R.franchiseno = GR.ID AND R.year = GR.year AND R.month = GR.month - 1;
DROP TEMPORARY TABLE GrossRev1;
```

Thank you for your help in advance

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Have you done an EXPLAIN on the slow query?

EXPLAIN select ....

This is your first tool to help, and it helps us too, so you should do one and provide the result here please.

===

please please please use the explicit "CROSS JOIN" when forming a deliberate Cartesian product (which you are doing as alias c) it will help make your queries more maintainable.

```
select *
FROM
(SELECT a.ID, a.franchise_no, a.closeddate, b.year,b.month FROM franchises AS a,
(SELECT DISTINCT year, month FROM royalties WHERE year = 2015
) as b) as c
LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month
```

It seems to me a totally unnecessary complication to cross join distinct month from royalty to franchise and then join again the whole thing to royalty. That has to be simplified to simple joins with ON clauses.Make sure you have the followingg indexes:

franchises - closeddate

royalties - Year, Month

hours_log - Year, franchiseno, reportmonth, reportyear

You build that cross joined table c and then you left join with R(royalties) on c.ID = R.franchiseno. However

c.ID comes from a.ID, which is franchise table, which also has franchise_no. Why do you join royalties.franchiseno with franchise.ID instead of franchise.franchise_no?

That whole query needs refactored. It si very badly written.

The Cross Join and then Left join was a solution to another question by the same author.

http://www.experts-exchange.com/questions/28744465/MySQL-Left-join-question.html

The author is building on that question.

Properly understanding the current question may lead to some modifications.

@thomasm1948

Can you type few records, and list the required output. Supply the new table structures and the insert statements of the test records.

CREATE TEMPORARY TABLE GrossRev1

Dump the query in a temporary table. The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for a month. They use it for forecasting

SELECT c.ID, IFNULL(R.GrossRevenue,0) AS GROSSREVENUE, IFNULL(R.RoyaltiesOwed,0) ROYALTIESOWED,

IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS, IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS,

IFNULL(Total_hours,0) TOTAL_HRS, c.year,c.month

This portion allows me to do a left join and fill in the year and month that a franchisee did not post anything. If I do not use this then when I run the query for multiple months then the report does not show exactly which month the franchisee did not post to

FROM

(SELECT a.ID, a.franchise_no, a.closeddate, b.year,b.month FROM franchises AS a,

(SELECT DISTINCT year, month FROM royalties WHERE year = 2015

) as b) as c

I join on franchises.id because the franchise_no (this is the actual franchise identification) but the royalty table and hours_log table use the id not the franchise_no…I do not know why they designed it this way.

LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month

This is the hours_log. This is where the franchisee post their weekly hours in a single table. Once again the franchiseno is actually the ID field in the franchises table. I need to show all of weeks in a single row for the year and month. Each record in the table list only the year, month and week number.

LEFT JOIN (SELECT h.franchiseno AS fn, h.Month, h.year,

MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week1,

MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week2,

MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week3,

MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week4,

MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week5,

CAST(SUM(h.hoursbilled) AS DECIMAL(30,2)) AS Total_hours

FROM

hours_log h

WHERE h.Year = 2015 -- and h.month = 9

GROUP BY

h.franchiseno,h.reportmont

WHERE (c.closeddate IS NULL OR c.closeddate = '0000-00-00')

ORDER BY c.year DESC, c.month DESC,c.ID ASC;

The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for the current month. They use it for forecasting. I could not think of another way to get this info.

I am stuck at this point as well because January’s calculation will be incorrect do to that it must know find the previous year and Decembers (12) grossrev and royalties

SELECT GR.*,

CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.GROSSREVENUE

ELSE GR.GROSSREVENUE END CURRENT_MONTH_FORECAST_AGR

CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.RoyaltiesOwed

ELSE GR.ROYALTIESOWED END CURRENT_MONTH_FORECAST_TOT

FROM GrossRev1 GR

LEFT JOIN (SELECT RL.franchiseno, RL.GROSSREVENUE, RL.RoyaltiesOwed, RL.year, RL.month

FROM royalties_log RL

) R ON R.franchiseno = GR.ID AND R.year = GR.year AND R.month = GR.month - 1;

DROP TEMPORARY TABLE GrossRev1;

Thank you for all of your help

test tables and data

```
CREATE TABLE franchise (
FIN_NUM INT,
Franchise_no INT,
Business_Entity varchar (50)
);
CREATE TABLE Royalties (
FIN_NUM INT,
GrossRev DECIMAL(30,2),
RMonth INT,
RYear Int
);
CREATE TABLE hours_log (
FIN_NUM INT,
Hours INT,
RMonth INT,
RYear INT,
reportweek INT
);
-- insert data franchise
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0001, 101, 'test1');
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0002, 102, 'test2');
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0003, 103,'test3');
-- insert data royalty
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 500, 12, 2014);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 2000, 2, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 2500, 2, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 4000, 1, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 2000, 1, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 1000, 3, 2015);
-- insert data hours_log
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 10, 12, 2014, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 20, 12, 2014, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 45, 2, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 47, 2, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 50, 2, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 65, 2, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 55, 2, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 70, 2, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 30, 1, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 10, 3, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 20, 3, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 4);
```

report

```
CREATE TEMPORARY TABLE test1
SELECT c.FIN_NUM, c.Franchise_no, IFNULL(R.grossrev,0.00) As grossrev, IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS,
IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS, IFNULL(Total_hours,0) TOTAL_HRS,
c.ryear, c.rmonth
FROM
(SELECT a.FIN_NUM, a.Franchise_no, b.ryear,b.rmonth FROM franchise as a,
(Select distinct ryear, rmonth from royalties) as b) as c
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth
LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear
order by c.ryear DESC, c.rmonth DESC,c.FIN_NUM ASC;
SELECT GR.franchise_no, GR.GROSSREV, GR.WEEK1_HRS, GR.WEEK2_HRS, GR.WEEK3_HRS, GR.WEEK4_HRS,
GR.WEEK5_HRS, GR.TOTAL_HRS, GR.ryear,GR.rmonth,
CASE WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
ELSE GR.GROSSREV END FORECAST_GR
-- R.AdjGrossRevenue, R.RoyaltiesOwed
FROM test1 GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by GR.franchise_no, GR.ryear asc, GR.rmonth;
DROP TEMPORARY TABLE test1;
```

This is a new company that I am working for and I cannot use SPs as well. So I am trying to work within the parameters that they have given me

```
SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear
```

Royalties Table:

1 rmonth

2 Franchiseno, ryear, rmonth

Hours_log:

no indexes

Franchise:

1. franchise_no

This is a new company that I am working for and I cannot use SPs as well. So I am trying to work within the parameters that they have given meIf they don't want SPs is one thing but if they want to pull out data from the database in the form of reports there is no way you can bypass index creation. Beside that it doesn't make any sense because it will not affect the functionality but it WILL affect the performance when they will run these queries and it will be felt by the entire system.

```
SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear
```

Hours_log:That is your big problem because I believe that is the largest table in terms of records. That is why I asked you about that last query...

no indexes

```
SELECT
GR.franchise_no,
GR.GROSSREV,
GR.WEEK1_HRS,
GR.WEEK2_HRS,
GR.WEEK3_HRS,
GR.WEEK4_HRS,
GR.WEEK5_HRS,
GR.TOTAL_HRS,
GR.ryear,
GR.rmonth,
CASE
WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
ELSE GR.GROSSREV
END FORECAST_GR
FROM
(
SELECT c.FIN_NUM, c.Franchise_no, IFNULL(R.grossrev,0.00) As grossrev, IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS,
IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS, IFNULL(Total_hours,0) TOTAL_HRS,
c.ryear, c.rmonth
FROM
(SELECT a.FIN_NUM, a.Franchise_no, b.ryear,b.rmonth FROM franchise as a,
(Select distinct ryear, rmonth from royalties) as b) as c
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth
LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear
)
GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by
GR.franchise_no,
GR.ryear asc,
GR.rmonth;
```

Tell me how long does this take.
Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialwith temp table

Affected rows: 6,084 Found rows: 6,084 Warnings: 0 Duration for 3 queries: 4.727 sec. (+ 27.643 sec. network)

without

Affected rows: 0 Found rows: 6,084 Warnings: 0 Duration for 1 query: 31.434 sec. (+ 2.325 sec. network)

```
EXPLAIN
SELECT
GR.franchise_no,
GR.GROSSREV,
GR.WEEK1_HRS,
GR.WEEK2_HRS,
GR.WEEK3_HRS,
GR.WEEK4_HRS,
GR.WEEK5_HRS,
GR.TOTAL_HRS,
GR.ryear,
GR.rmonth,
CASE
WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
ELSE GR.GROSSREV
END FORECAST_GR
FROM
(
SELECT c.FIN_NUM, c.Franchise_no, ISNULL(R.grossrev,0.00) As grossrev, ISNULL(week1,0) WEEK1_HRS, ISNULL(week2,0) WEEK2_HRS,
ISNULL(week3,0) WEEK3_HRS, ISNULL(week4,0) WEEK4_HRS, ISNULL(week5,0) WEEK5_HRS, ISNULL(Total_hours,0) TOTAL_HRS,
c.ryear, c.rmonth
FROM
(SELECT * from franchise as a,
(Select distinct ryear, rmonth from royalties) as b) as c
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth
LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear
)
GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by
GR.franchise_no,
GR.ryear asc,
GR.rmonth;
```

My last part of the code for selecting the previous grossrev if the franchisee did not post anything. how would I have it select the previous year and 12 (month) if the current month is 1

currently the code is

```
EFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
```

Which i know is incorrect

In regards to your question the best way to go is to use the actual datetime columns instead of Year,Month approach, from which you extract 12 months. There are functions for this:

SELECT (NOW(),INTERVAL -12 MONTH)

1. Current year

2. Last year

3. 12 months from current date

For example let's say we had a date range from 1/2013 to 10/2015 then the current query would be correct for all but 1/2015, 1/1014 and 1/2013 if a franchisee did not post anything for those months

The query will have to look at the month and if its 1 for the month then it will have to look at the previous year and December. For example 1/2015 will have to get 12/2014 Grossrev for its forecast

I applied your supplied sql and created the current tables and the test data. This includes franchise (3 records), hours_log (24 records), and royalties (6 records).

List the required output.

What I didn't understand is why is a problem for month=1?

MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I helped in one question involving the franchise, and royalties tables.

If you can rephrase the question using these two existing tables, I might be able to help.

In the meantime, one needs to find out which part is causing the delay