Multi Part Identifier Could Not Be Bound 2

[briesen]

Could you please help me correct my formatting? I cannot figure out what I'm doing wrong. Thanks!

The multi-part identifier "tbl.dteEffectiveDate" could not be bound.
The multi-part identifier "tbl.nbrFuelSurcharge" could not be bound.
Invalid Column name 'dteEffectiveDate'.
Invalid column name 'nbrFuelSurcharge'.

SELECT
'Year'=Year(vtr."Billing Date"),
'Month'=Month(vtr."Billing Date"),
'Miles'=SUM(vtr."TotalMiles"),
'Weight'=SUM(vtr.Weight),
'Total Cost'=SUM(vtr.TotalARAmt),
'Base Cost'=SUM(vtr."A/R Invoice Amount"),
'FSC'=AVG(tbl.nbrFuelSurcharge),
'Count'=COUNT(vtr."Evans Order Number")
FROM Mother.dbo.viewTransReporting AS vtr
inner join
(SELECT
'Monthy'=Month(tbl.dteEffectiveDate), 'FSCY'=AVG(tbl.nbrFuelSurcharge)
FROM
Mother.dbo.tblFuelSurcharge
GROUP by tbl.dteEffectiveDate) AS tbl
ON
Month(vtr."Billing Date")=Month(tbl.dteEffectiveDate)
WHERE vtr."CustomerShortName"=N'HELLERMANN'
AND (vtr."Billing Date">= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
AND vtr."Routed"=1 AND (vtr.Mode='LTL' OR vtr.Mode='VOLUME')
AND (tbl.dteEffectiveDate>= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
AND (tbl.dteEffectiveDate< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
GROUP by Month(vtr."Billing Date")

 

[gmmastros]

Your problem is...

You have a derived table with an alias named tbl. Whatever columns are returned by the derived table must be referenced that way. For example, you have:

'Monthy'=Month(tbl.dteEffectiveDate)

This is considered an aliased column of the derived table. Basically, anything outside the parenthesis defining the derived table must reference the column aliases returned by the derived table.

make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briesen]

It sort of does. I changed the formatting inside the second select statement, but still have the issue. Can you clarify what else I'm doing wrong? Can I not use the first 'FSC' alias in the first select statement?

SELECT
'Year'=Year(vtr."Billing Date"),
'Month'=Month(vtr."Billing Date"),
'Miles'=SUM(vtr."TotalMiles"),
'Weight'=SUM(vtr.Weight),
'Total Cost'=SUM(vtr.TotalARAmt),
'Base Cost'=SUM(vtr."A/R Invoice Amount"),
'FSC'=AVG(tbl.nbrFuelSurcharge),
'Count'=COUNT(vtr."Evans Order Number")
FROM Mother.dbo.viewTransReporting AS vtr
inner join
(SELECT
tbl.dteEffectiveDate, tbl.nbrFuelSurcharge
FROM
Mother.dbo.tblFuelSurcharge
GROUP by tbl.dteEffectiveDate) AS tbl
ON
Month(vtr."Billing Date")=Month(tbl.dteEffectiveDate)
WHERE vtr."CustomerShortName"=N'HELLERMANN'
AND (vtr."Billing Date">= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
AND vtr."Routed"=1 AND (vtr.Mode='LTL' OR vtr.Mode='VOLUME')
AND (tbl.dteEffectiveDate>= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
AND (tbl.dteEffectiveDate< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
GROUP by Month(vtr."Billing Date")

 

[chamilz]

(
SELECT 
'Monthy'=Month(dteEffectiveDate), 
'FSCY'=AVG(nbrFuelSurcharge)
FROM Mother.dbo.tblFuelSurcharge  
GROUP by dteEffectiveDate
) AS tbl

 

[briesen]

Thanks for all your help, but it says:

Invalid column name 'dteEffectiveDate'
Invalid column name 'nbrFuelSurcharge'

SELECT 
'Year'=Year(vtr."Billing Date"), 
'Month'=Month(vtr."Billing Date"), 
'Miles'=SUM(vtr."TotalMiles"), 
'Weight'=SUM(vtr.Weight), 
'Total Cost'=SUM(vtr.TotalARAmt), 
'Base Cost'=SUM(vtr."A/R Invoice Amount"), 
'FSC'=AVG(tbl.nbrFuelSurcharge),
'Count'=COUNT(vtr."Evans Order Number")
 FROM   Mother.dbo.viewTransReporting AS vtr 
inner join 
(SELECT 'Monthy'=Month(dteEffectiveDate), 
'FSCY'=AVG(nbrFuelSurcharge)FROM Mother.dbo.tblFuelSurcharge
GROUP by dteEffectiveDate) AS tbl
ON
Month(vtr."Billing Date")=Month(tbl.dteEffectiveDate)
WHERE  vtr."CustomerShortName"=N'CBB' 
AND (vtr."Billing Date">= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) 
AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)) 
AND vtr."Routed"=1 AND (vtr.Mode='LTL' OR vtr.Mode='VOLUME')
AND (tbl.dteEffectiveDate>= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) 
AND (tbl.dteEffectiveDate< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
GROUP by Month(vtr."Billing Date")

 

[gmmastros]

Just for giggles, try this:

SELECT  'Year'=Year(vtr."Billing Date"),
        'Month'=Month(vtr."Billing Date"),
        'Miles'=SUM(vtr."TotalMiles"),
        'Weight'=SUM(vtr.Weight),
        'Total Cost'=SUM(vtr.TotalARAmt),
        'Base Cost'=SUM(vtr."A/R Invoice Amount"),
        'FSC'=tbl.FSCY,
        'Count'=COUNT(vtr."Evans Order Number")
FROM    Mother.dbo.viewTransReporting AS vtr
        inner join (
          SELECT 'Monthy'=Month(dteEffectiveDate),
                 'FSCY'=AVG(nbrFuelSurcharge)
          FROM    Mother.dbo.tblFuelSurcharge
          Where  (tblFuelSurcharge.dteEffectiveDate>= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
                 AND (tblFuelSurcharge.dteEffectiveDate< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
          GROUP by dteEffectiveDate
          ) AS tbl
          ON Month(vtr."Billing Date")=tbl.Monthy
WHERE   vtr."CustomerShortName"=N'CBB'
        AND (vtr."Billing Date">= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
        AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
        AND vtr."Routed"=1 
        AND (vtr.Mode='LTL' OR vtr.Mode='VOLUME')
GROUP by Month(vtr."Billing Date")

You may still get some complaints about the final group by. But... try this and see what (if any) error messages you get.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chamilz]

in your derived table 'Month(dteEffectiveDate)' column has been defined as 'Monthy' and AVG(nbrFuelSurcharge) as 'FSCY'.

run this code alone and see the output and columns

SELECT 
'Monthy'=Month(dteEffectiveDate), 
'FSCY'=AVG(nbrFuelSurcharge)
FROM Mother.dbo.tblFuelSurcharge  
GROUP by dteEffectiveDate

you need to add 'dteEffectiveDate' in your derived table too...

SELECT 
'Year'=Year(vtr."Billing Date"), 
'Month'=Month(vtr."Billing Date"), 
'Miles'=SUM(vtr."TotalMiles"), 
'Weight'=SUM(vtr.Weight), 
'Total Cost'=SUM(vtr.TotalARAmt), 
'Base Cost'=SUM(vtr."A/R Invoice Amount"), 
'FSC'= [blue]AVG(tbl.FSCY)[/blue],
'Count'=COUNT(vtr."Evans Order Number")
 FROM   Mother.dbo.viewTransReporting AS vtr 
inner join 
(
SELECT 
      dteEffectiveDate,
     'Monthly'=Month(dteEffectiveDate), 
     'FSCY'=AVG(nbrFuelSurcharge)
FROM Mother.dbo.tblFuelSurcharge  
GROUP by dteEffectiveDate
) AS tbl
ON 
Month(vtr."Billing Date")= Month(tbl.dteEffectiveDate)
WHERE  vtr."CustomerShortName"=N'HELLERMANN' 
AND (vtr."Billing Date">= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) 
AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)) 
AND vtr."Routed"=1 AND (vtr.Mode='LTL' OR vtr.Mode='VOLUME')
AND (tbl.dteEffectiveDate>= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) 
AND (tbl.dteEffectiveDate< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
GROUP by Month(vtr."Billing Date")

you should use the same

 

[briesen]

I very much appreciate this help.

I had to add Group by Year(vtr."Billing Date"), Month(vtr."Billing Date") to get both of your suggestions to work.

George, yours just gave me a blank screen, but no errors.

chamilz, yours worked, but it isn't what I was looking for.

There are four or five lines of data each month for tblFuelSurcharge.

What I was trying to do is group these all into one month and then use that average to link with vtr.

What is actually happening is that vtr is being multiplied by the number of lines in tbl and is skewing all the totals.

Does this make sense?

 

[briesen]

Actually, George, yours works, I just missed a setting. The issue is still that I want the tbl to group by the month of the dteEffectiveDate so the counts/sums don't get skewed.

 

[briesen]

I got it, thanks!

SELECT 'Year'=Year(vtr."Billing Date"),
'Month'=Month(vtr."Billing Date"),
'Miles'=SUM(vtr."TotalMiles"),
'Weight'=SUM(vtr.Weight),
'Total Cost'=SUM(vtr.TotalARAmt),
'Base Cost'=SUM(vtr."A/R Invoice Amount"),
'FSC'=AVG(tbl.FSCY),
'Count'=COUNT(vtr."Evans Order Number")
FROM Mother.dbo.viewTransReporting AS vtr
inner join (
SELECT 'Monthy'=Month(dteEffectiveDate),
'FSCY'=AVG(nbrFuelSurcharge)
FROM Mother.dbo.tblFuelSurcharge
Where (tblFuelSurcharge.dteEffectiveDate>= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
AND (tblFuelSurcharge.dteEffectiveDate< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
GROUP by Month(dteEffectiveDate)
) AS tbl
ON Month(vtr."Billing Date")=tbl.Monthy
WHERE vtr."CustomerShortName"=N'CBB'
AND (vtr."Billing Date">= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
AND vtr."Routed"=1
AND (vtr.Mode='LTL' OR vtr.Mode='VOLUME')
GROUP by Year(vtr."Billing Date"), Month(vtr."Billing Date")