Ambiguous column name 'Created

I utilize SQL SSMS. Due to the following, SQL prompts error. After I utilized the same script with many other inner joins the following error remains. I have tried to modify my script, and I keep getting the same messages:

Msg 209, Level 16, State 1, Line 9 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 17 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 18 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 19 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 20 Ambiguous column name 'Created_Date'.

[My script]

 SELECT 
 rs.Resident_ID, 
 [UserName] = rsdt.First_Name   ' '   rsdt.Last_Name,
 in.Invoice_Amount,
 in.Due_Date as due,
 in.Created_Date,
 DATEDIFF(day, [Created_Date], Getdate()) as " Number_of_Days ",
 in.Paid_Date as paid,
 in.Description,  
 ar.Payment_Type, 
 ar.Receipt_Descriptions,

 CASE 
     WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 90 AND 119 Then '90 days'
     WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 120 AND 179 Then '120 days'
     WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 180 AND 364 Then '180 days'
     WHEN DATEDIFF(day, [Created_Date], Getdate()) >= 365 Then '365  days'
     END As [Days Outstanding]

   FROM dbo.ar_receipts AS ar
   INNER JOIN dbo.residents AS rs
   ON ar.Resident_ID = rs.Resident_ID 

   INNER JOIN dbo.invoices AS in
   ON ar.Created_Date = in.Created_Date

   WHERE
   DATEDIFF(Day, in.Created_Date, GETDATE ()) > = 90
   AND in.Created_Date >= DATEADD(MONTH, -48, GETDATE())
   Order by in.Created_Date

CodePudding user response：

You have:

CASE WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 90 AND 119

But the error message is clear: more than one table in your query has a column named Created_Date. So, you need to use the table aliases you created to tell SQL Server which one you mean. Presumably it is from dbo.invoices, so:

CASE WHEN DATEDIFF(day, in.[Created_Date], Getdate()) Between 90 AND 119

Though in is also a poor name choice for an alias because, like double, it is a reserved word/keyword and at the very least will light up in various editors or, in some contexts, cause a syntax error.

More importantly:

DATEDIFF(day, in.[Created_Date], Getdate()) >= 365

This is much more efficiently written as:

in.Created_Date < DATEADD(DAY, -365, CONVERT(date, GETDATE())

It's a few extra characters, I know, but that at least has a shot of using an index.

Also, if you traverse the opposite way (oldest category to newest), you don't have to handle the BETWEEN scenario at all, because the first scenario that matches will render the rest of the comparisons no-ops:

CASE
WHEN in.Created_Date < DATEADD(DAY, -365, CONVERT(date, GETDATE())
  THEN '365  Days'
WHEN in.Created_Date < DATEADD(DAY, -180, CONVERT(date, GETDATE())
  THEN '180 Days'
WHEN in.Created_Date < DATEADD(DAY, -120, CONVERT(date, GETDATE())
  THEN '120 Days'
WHEN in.Created_Date < DATEADD(DAY, -90,  CONVERT(date, GETDATE())
  THEN '90 Days'
END AS [Days Outstanding]

You could also simplify it by:

DECLARE @today date = GETDATE();

CASE
WHEN in.Created_Date < DATEADD(DAY, -365, @today)
  THEN '365  Days'
...

CodePudding user response：

Both dbo.invoices and dbo.ar_receipts have a Created_Date column.

   INNER JOIN dbo.invoices AS in
   ON ar.Created_Date = in.Created_Date

You need to fully qualify each use of Created_Date as you did above.

For example, DATEDIFF(day, [Created_Date], Getdate()) as " Number_of_Days " needs to specify which table's Created_Date. Probably in.Created_Date, but only you can answer that for sure.