Help With INNER JOINS

[stanlew]

Happy New Year everyone! I'm new to both T-SQL and this forum. I'm currently doing an internship and my first task was to create a small program which will send an email detailing the sales of the previous day versus monthly targets and sales. Most of the parts were figured out and eveything was done in Visual Studio. The gist of the code was written in one large chunk of SQL code, as below:

SELECT     derivedtbl_1.family AS 'Family', FLOOR(SUM(derivedtbl_1.sales)) AS 'Previous Day Sales', FLOOR(derivedtbl_2.target) AS 'Sales Target', 
                      FLOOR(derivedtbl_4.totalSales) AS 'Total Sales', (CASE WHEN (FLOOR((derivedtbl_2.target - derivedtbl_4.totalSales) / derivedtbl_5.remDays) > 0) 
                      THEN FLOOR((derivedtbl_2.target - derivedtbl_4.totalSales) / derivedtbl_5.remDays) ELSE 0 END) AS 'Required Sales', 
                      FLOOR(derivedtbl_4.totalSales / derivedtbl_5.totalDays) AS 'Sales Average'
FROM         (SELECT     Brand_Family_NEW.strSuperFamily AS family, 
                                              SMS_RPT_SALES_NAT_DAY.qtySales + SMS_RPT_SALES_NAT_DAY.qtyShopSales AS sales, 
                                              SMS_RPT_SALES_NAT_DAY.dteValue AS date
                       FROM          SMS_RPT_SALES_NAT_DAY INNER JOIN
                                              Brand_Family_NEW ON SMS_RPT_SALES_NAT_DAY.ideBrand = Brand_Family_NEW.ideBrand) AS derivedtbl_1 INNER JOIN
                          (SELECT     TOP (1) dteValue
                            FROM          SMS_RPT_TMP_SP010
                            WHERE      (dteValue < DATEADD(d, - 1, GETDATE()))
                            ORDER BY dteValue DESC) AS derivedtbl_6 ON derivedtbl_6.dteValue = derivedtbl_1.date INNER JOIN
                          (SELECT     SUM(target) AS target, family
                            FROM          (SELECT     SUM(SMS_RPT_TARGET.qtySalesTarget) AS target, SMS_RPT_TARGET.ideBrand, 
                                                                           Brand_Family_NEW.strSuperFamily AS family
                                                    FROM          SMS_RPT_TARGET INNER JOIN
                                                                           Brand_Family_NEW ON SMS_RPT_TARGET.ideBrand = Brand_Family_NEW.ideBrand AND DATEDIFF(month, SMS_RPT_TARGET.dteTarget, GETDATE())= 0
                                                    GROUP BY SMS_RPT_TARGET.ideBrand, Brand_Family_NEW.strSuperFamily) AS derivedtbl_3
                            GROUP BY family) AS derivedtbl_2 ON derivedtbl_1.family = derivedtbl_2.family INNER JOIN
                          (SELECT     SUM(SMS_RPT_SALES_NAT_DAY.qtySales) AS totalSales, Brand_Family_NEW.strSuperFamily AS family
                            FROM          SMS_RPT_SALES_NAT_DAY INNER JOIN
                                                   Brand_Family_NEW ON SMS_RPT_SALES_NAT_DAY.ideBrand = Brand_Family_NEW.ideBrand
                            WHERE      (DATEDIFF(month, SMS_RPT_SALES_NAT_DAY.dteValue, GETDATE()) = 0)
                            GROUP BY Brand_Family_NEW.strSuperFamily) AS derivedtbl_4 ON derivedtbl_4.family = derivedtbl_2.family CROSS JOIN
                          (SELECT     COUNT(DISTINCT a.dteValue) + 1 AS remDays, COUNT(DISTINCT b.dteValue) - 1 AS totalDays
                            FROM          SMS_RPT_TMP_SP010 AS a CROSS JOIN
                                                   SMS_RPT_TMP_SP010 AS b
                            WHERE      (a.dteValue > GETDATE()) AND (b.dteValue < GETDATE())) AS derivedtbl_5
GROUP BY derivedtbl_1.family, derivedtbl_2.target, derivedtbl_4.totalSales, derivedtbl_5.remDays, derivedtbl_5.totalDays
ORDER BY 'Previous Day Sales' DESC

I know this is messy code but I'm not good enough to do optimization (can anyone give me pointers on that?). The problem now is that when I try to change the conditions (lines 18 and 24)

DATEDIFF(month, SMS_RPT_TARGET.dteTarget, GETDATE())= 0

to

DATEDIFF(month, SMS_RPT_TARGET.dteTarget, derivedtbl_1.date)= 0

and

DATEDIFF(month, SMS_RPT_SALES_NAT_DAY.dteValue, GETDATE()) = 0

to

DATEDIFF(month, SMS_RPT_SALES_NAT_DAY.dteValue, derivedtbl_1.date) = 0

, it gives me a "The multi-part identifier "derivedtbl_1.date" could not be bound" error, twice. I know it's a problem with the nested joins but I don't know how to fix it. Please help me. Many thanks.