Can't Group By an internal alias?

Question

i have a table called information_livraison

Num_bloc    date_livraison
14104       2012-03-22 09:18:43.000
14202       2012-03-22 09:18:43.000
13276       2012-03-22 09:19:39.000
12775       2012-03-22 09:24:59.000
12967       2012-03-22 09:29:18.000
13809       2012-03-22 09:50:14.000
14611       2012-03-22 10:04:56.000
10320       2012-03-22 10:05:33.000
14593       2012-03-22 10:06:20.000
15179       2012-03-22 10:06:43.000

i have to build a query that select a range of dates and return counts of every date in it is available

my query :

Declare @Days Table (DateField datetime)

Declare @CurrentDate datetime
Declare @EndDate datetime

Set @CurrentDate = (SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
Set @EndDate = (SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))))


While @CurrentDate <= @EndDate
  Begin
     Insert Into @Days Values(@CurrentDate)
     Set @CurrentDate = DateAdd(d,1,@CurrentDate)
  End 


Select convert(varchar(10),DateField,103) as DT ,IsNull(Livre,0) AS Livre

From @Days

     LEFT OUTER JOIN 
      (SELECT DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101') , Count(*) AS Livre
        FROM Colis.dbo.information_livraison 
        GROUP BY DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101')) LV 
      ON DateField = DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101')


 ORDER BY DateField ASC

 GO

an error was generated

Msg 8155, Level 16, State 2, Line 17
Aucune colonne spécifiée pour la colonne 1 de 'LV'.
Msg 207, Level 16, State 1, Line 25
Nom de colonne non valide : 'date_livraison'.

i want this result

DT          Livre   
01/03/2012  0
02/03/2012  0
03/03/2012  0
04/03/2012  0
05/03/2012  0
......
21/03/2012  10
22/03/2012  0
......
29/03/2012  0
30/03/2012  0
31/03/2012  0

no @bluefeet ! the order by is for the "Select convert(varchar(10),DateField,103) as DT ,IsNull(Livre,0) AS Livre" . — AymenRM, Mar 22 '12 at 13:41

score 1 · Answer 1 · answered Mar 22 '12 at 13:41

I think you want this for your select:

Select convert(varchar(10),DateField,103) as DT ,IsNull(Livre,0) AS Livre
From @Days
 LEFT OUTER JOIN 
  (SELECT DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101') DT ,     Count(*) AS Livre
    FROM @information_livraison
    GROUP BY DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101')) LV 
  ON DateField = DT
 ORDER BY DateField ASC

I added a column name for the DATEADD then used that for the join, this appears to give the results you are after.

score 1 · Answer 2 · answered Mar 22 '12 at 13:42

Within the subquery, you need to add an alias to the first column. Then, in the ON clause that joins to the subquery, use the alias. Here, I used xxx, to make it obvious (and added alias "dd" to table @Days, for clarity)

Select
   convert(varchar(10), dd.DateField,103) as DT
  ,IsNull(LV.Livre,0) AS Livre
 From @Days dd
  LEFT OUTER JOIN (SELECT
                      DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101') xxx
                     ,Count(*) AS Livre
                    FROM Colis.dbo.information_livraison        
                    GROUP BY DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101')) LV  
   ON dd.DateField = LV.xxx
 ORDER BY dd.DateField ASC

score 1 · Accepted Answer · answered Mar 22 '12 at 13:42

Try explicitly giving your derived column an alias in the subquery:

Select DT ,IsNull(Livre,0) AS Livre
From @Days
LEFT OUTER JOIN 
(SELECT DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101') as DT, 
        Count(*) AS Livre
 FROM Colis.dbo.information_livraison 
 GROUP BY DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101')) LV 
ON DateField = DT
ORDER BY DateField ASC

score 0 · Answer 4 · answered Mar 22 '12 at 13:48

You need to provide an alias for your date field in your subquery:

Select convert(varchar(10),DateField,103) as DT ,IsNull(Livre,0) AS Livre
From @Days
LEFT OUTER JOIN 
(
    SELECT DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101') as LDate, Count(*) AS Livre
    FROM Colis.dbo.information_livraison 
    GROUP BY DATEADD(day, DATEDIFF(day, '20040101', [date_livraison]), '20040101')
) LV 
    ON DateField = LV.LDate
ORDER BY DateField ASC

One you do that then when you join on the fields it would read:

ON DateField = LV.LDate

Can't Group By an internal alias?

4 Answers4