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'

DBro · Accepted Answer · 2019-04-11 19:10:26Z

3

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

answered Apr 11, 2019 at 19:10

DBro

4321 gold badge3 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-04-11 19:07:48Z

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;

edited Apr 11, 2019 at 19:07

answered Apr 11, 2019 at 18:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

DBro Over a year ago

Seems silly to convert each date to a month abbreviation, and filter on that. I'd filter on month number instead, and dump the APPLY( ). WHERE MONTH(Date1) = 3

Collectives™ on Stack Overflow

Date functions in SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related