CODEWITHSUNDEEP
sqlsql-server-2005
HEEN
HEEN

Reputation: 4721

SQL query error not working

I have written a SQL query but I am getting error as

Incorrect syntax near 'company_name'.

I am using SQL Server 2005.

My query:

SELECT TOP 1 
    MONTH(a.dt_of_leave) MONTH,
    YEAR(a.dt_of_leave) YEAR 
FROM 
    emp_mst a 
WHERE 
    MONTH(a.dt_of_leave) >= MONTH(getdate()) - 1
    AND YEAR(a.dt_of_leave) = CASE 
                                 WHEN MONTH(getdate()) = 1  
                                 THEN YEAR(getdate()) - 1 
                                 ELSE YEAR(getdate()) company_name 'COMPANY NAME',
    Deputed_Company_Name 'DEPUTED COMPANY NAME' emp_card_no 'EMP CODE',
    emp_name 'EMPLOYEE NAME',
    LWP,
   '' Remarks,
   Adj_Days Gain_Loss_LOP_Days,
   VAL_DAY LOP_Days_Desc,
   MONTH,
   YEAR
 FROM XXACL_EMP_INFO_LWP_OTDAYS_HRS_V WHERE emp_type='C'
  AND MONTH = '3'
 AND YEAR = '2016'
 AND emp_card_no IN(312,
                 1250,
                 362)

UPDATE

I have two working queries:

1st query

select top 1 
    month(a.dt_of_leave) month,  
    year(a.dt_of_leave) year
from 
    emp_mst a 
where 
    month(a.dt_of_leave) >= month(getdate())-1  
    and year(a.dt_of_leave) = case 
                                 when month(getdate()) = 1 
                                    then year(getdate()) - 1 
                                    else year(getdate()) 
                              end
    and emp_card_no IN (312, 1250, 362)
order by 
    emp_name

2nd query:

select 
    company_name 'COMPANY NAME',
    Deputed_Company_Name 'DEPUTED COMPANY NAME', 
    emp_card_no 'EMP CODE',
    emp_name 'EMPLOYEE NAME',
    LWP, '' Remarks, 
    Adj_Days Gain_Loss_LOP_Days, 
    VAL_DAY LOP_Days_Desc, month, year 
from 
    XXACL_EMP_INFO_LWP_OTDAYS_HRS_V 
where 
    emp_type = 'C' 
    and month = '3' and year = '2015' 
    and emp_card_no in (312, 1250, 362)

What I want is in second query, I want to add month and year which i get from first query..

I need to merge that in second query

Upvotes: 1

Views: 61

Answers (1)

marc_s
marc_s

Reputation: 754220

Try something like this - it will use the three emp_card_no given in the outer WHERE clause, and get their most recent month/year entry:

; WITH MonthAndYear AS 
(
    SELECT 
        MONTH(a.dt_of_leave) month, 
        YEAR(a.dt_of_leave) year,
        emp_card_no,
        RowNum = ROW_NUMBER() OVER (PARTITION BY emp_card_no ORDER BY dt_of_leave DESC)
    FROM
        emp_mst a
    WHERE
        MONTH(a.dt_of_leave) >= MONTH(GETDATE()) - 1
        AND YEAR(a.dt_of_leave) = CASE 
                                     WHEN MONTH(GETDATE()) = 1 
                                        THEN YEAR(GETDATE()) - 1
                                        ELSE YEAR(GETDATE())
                                     END
)
SELECT
    x.company_name 'COMPANY NAME', 
    x.Deputed_Company_Name 'DEPUTED COMPANY NAME', 
    x.emp_card_no 'EMP CODE', 
    x.emp_name 'EMPLOYEE NAME', 
    x.LWP, '' Remarks,
    x.Adj_Days Gain_Loss_LOP_Days, 
    x.VAL_DAY LOP_Days_Desc, 
    x.month, x.year
FROM
    XXACL_EMP_INFO_LWP_OTDAYS_HRS_V x
INNER JOIN 
    MonthAndYear my ON x.emp_card_no = my.emp_card_no 
                    AND x.Month = my.Month AND x.Year = my.Year
                    AND my.RowNum = 1
WHERE
    x.emp_type = 'C'
    AND x.emp_card_no IN (312, 1250, 362);

I hope that's what you're looking for ! If not - please share table structure, sample data, expected output etc.

Upvotes: 1

Related Questions