Date functions in SQL Server

Question

Task: display the customer names and their orders happened in Pune and Kolkata locations during March month

Customers:

CUST_ID    CUST_NAME    LOCATION
---------------------------------
   1          A         HYD
   2          B         PUNE
   3          C         DELHI
   4          D         KOLKATA

Orders:

ORDER_ID    CUST_ID AMOUNT  DATE
----------------------------------
100 3   1000    2019-03-22
101 2   2000    2019-03-12
103 1   3000    2019-04-11
104 2   5000    2019-04-10
105 3   6000    2019-02-18

Query:

SELECT
    c.cust_name, c.location,
    CONVERT(VARCHAR(3), Date1, 100) AS Month
FROM
    customers1 c 
FULL OUTER JOIN
    orders o ON o.cust_id = c.cust_id
WHERE
    c.LOCATION = 'PUNE' OR c.LOCATION = 'KOLKATA'
GROUP BY 
    date1
HAVING
    Month = 'Mar'

I am getting this error:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Month'

Answer 1

Can't refer to an alias name in your HAVING clause. For performance reasons, I suggest filtering on the month number, instead of the month abbreviation.

SELECT
     c.cust_name
    ,c.location
    ,CONVERT(varchar(3), Date1, 100) as Month
FROM customers1 c
    INNER JOIN orders o on o.cust_id=c.cust_id
WHERE
    (c.LOCATION='PUNE' or c.LOCATION='KOLKATA')
WHERE
    MONTH(Date1) = 3

Answer 2

What you are trying to do does not work in SQL Server, You cannot use a SELECT alias in a WHERE or HAVING clause (the way you are doing this).

Also, an outer join is not needed. You can express what you want using apply:

select c.cust_name, c.location, v.month
from customers1 c join
     orders o
     on o.cust_id = c.cust_id cross apply
     (values (CONVERT(varchar(3), Date1, 100))) v(Month)
where c.LOCATION in ('PUNE', 'KOLKATA') and
      v.month = 'Mar';

I have no idea why you have group by date1. date1 is not in the select list and you have no aggregation functions, so the aggregation seems unnecessary.

Of course, I would simplify this to:

select c.cust_name, c.location, 
       left(datename(month, date1))
from customers1 c join
     orders o
     on o.cust_id = c.cust_idv(Month)
where c.LOCATION in ('PUNE', 'KOLKATA') and
      month(date1) = 3;