Help with INNER JOINS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with INNER JOINS

Author

Topic

stanlew
Starting Member

1 Post

Posted - 2008-01-01 : 23:27:31

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 (bolded above)

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

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

and

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

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. It's been 3 hours trying to solve this but I still have no luck. Please help me. Many thanks.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-02 : 18:29:28

Your problem is you are trying to use a value in your derived tables that is outside of its scope.

Example:

The following example does not work because derivedtable1.id is outside of the scope of derivedtable2 and cannot be used as a parameter.

declare @a table ( id int, date1 datetime )
insert @a ( id, date1 )
select 1 as id, '20080101' as val union all
select 2, '20080102'

declare @b table ( id int, date2 datetime )
insert @b ( id, date2 )
select 1, '20080201' union all
select 2, '20080202' union all
select 3, '20080203'

select * from
(
select id, date1
from @a
) derivedtable1
join (
select id, date2
from @b
where DATEDIFF(month, date2, derivedtable1.date1)= 0
) derivedtable2 on a.id = b.id

Try adding that criteria to your join criteria:

declare @a table ( id int, date1 datetime )
insert @a ( id, date1 )
select 1 as id, '20080101' as val union all
select 2, '20080102'

declare @b table ( id int, date2 datetime )
insert @b ( id, date2 )
select 1, '20080101' union all
select 2, '20080201' union all
select 3, '20080301'

select * from
(
select id, date1
from @a
) derivedtable1
join (
select id, date2
from @b
) derivedtable2 on derivedtable1.id = derivedtable2.id
and DATEDIFF(month, derivedtable2.date2, derivedtable1.date1)= 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-03 : 02:05:16

To mkae use of index,

instead of

DATEDIFF(month, date2, derivedtable1.date1)= 0

use
date2>=dateadd(month,datediff(month,0,derivedtable1.date1),0) and
date2<dateadd(month,datediff(month,0,derivedtable1.date1),1)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources