pivot issues can what is wrong with this code please

Question

select * From(
Select
Emp.empid                                                    AS 'Employee Id',
             Emp.Firstname+' '+Emp.lastname     As 'Employee Full Name',
   Mng.Firstname+' '+Mng.lastname     As 'Manager Full Name',
  Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))  As 'Order Date',
   Sum(sod.qty*sod.unitprice)                 As  'Order Value'
From Hr.Employees AS Emp
inner join
Hr.Employees             AS Mng    On Emp.mgrid=Mng.empid
inner join
Sales.Orders                As So          On Mng.empid=So.empid
inner join     
Sales.OrderDetails    As Sod       On So.orderid=Sod.orderid
where year(so.orderdate)='2007' and month(so.orderdate)<='6'
Group By Emp.empid, Emp.Firstname+' '+Emp.lastname,Mng.Firstname+' '+Mng.lastname,   
  Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))
--Order by 'order date'
) As managerOrderValue
pivot (
sum(ordervalue)  for orderdate in (2007-01,2007-02,2007-03,2007-04,2007-05,2007-06)
)as pvt
  ;
Go

this is the error message

 Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '2007'.

Answer 1

To solve the syntax error, try

   … for orderdate in ('2007-01', '2007-02', '2007-03', …

or

   … for orderdate in ('2007-01-01', '2007-02-01', '2007-03-01', …

But if results are not good, then describe the problem accordingly.

Answer 2

Error:

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '2007'.

Problem Clause : where year(so.orderdate)='2007' 

Problem : Your trying to compare int with varchar in this clause

Left Side:

Your orderdate I assume is a date variable and you are storing the dates

When you select year(so.orderdate) to type returned is Int.

Try these command to confirm

DECLARE @a date
SELECT @a = getdate()
SELECT YEAR(@a)
SELECT SQL_VARIANT_PROPERTY((SELECT YEAR(@a)),'BaseType') BaseType

Right Side:

However on the right side is taking the varchar type

Try these command to verify 

SELECT SQL_VARIANT_PROPERTY('2020','BaseType') BaseType

Resolution:

Do and explicit conversion to '2007' as INT

Example

where year(so.orderdate) =(CAST ('2007' as int))

Let me know if it works and do mark as answered or up vote this reply if it helps

Answer 3

ok thank you very much ,let me try it

Answer 4

ok thank you

Answer 5

ok I tried it and it didn't work the thing is I m actually trying to pivot this using a derived table

select Emp.empid                                                    AS 'Employee Id',
             Emp.Firstname+' '+Emp.lastname     As 'Employee Full Name',
   Mng.Firstname+' '+Mng.lastname     As 'Manager Full Name',
  Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))  As 'Order Date',
   Sum(sod.qty*sod.unitprice)                 As  'Order Value'
From Hr.Employees AS Emp
inner join
Hr.Employees             AS Mng    On Emp.mgrid=Mng.empid
inner join
Sales.Orders                As So          On Mng.empid=So.empid
inner join     
Sales.OrderDetails    As Sod       On So.orderid=Sod.orderid
where year(so.orderdate)='2007' and month(so.orderdate)<='6'
Group By Emp.empid, Emp.Firstname+' '+Emp.lastname,Mng.Firstname+' '+Mng.lastname,   
  Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))
Order by 'order date'
  ;
Go

Answer 6

Error:

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '2007'.

Problem Clause : where year(so.orderdate)='2007' 

Problem : Your trying to compare int with varchar in this clause

Left Side:

Your orderdate I assume is a date variable and you are storing the dates

When you select year(so.orderdate) to type returned is Int.

Try these command to confirm

DECLARE @a date
SELECT @a = getdate()
SELECT YEAR(@a)
SELECT SQL_VARIANT_PROPERTY((SELECT YEAR(@a)),'BaseType') BaseType

Right Side:

However on the right side is taking the varchar type

Try these command to verify 

SELECT SQL_VARIANT_PROPERTY('2020','BaseType') BaseType

Resolution:

Do and explicit conversion to '2007' as INT

Example

where year(so.orderdate) =(CAST ('2007' as int))

Let me know if it works and do mark as answered or up vote this reply if it helps

ok I tried it and it didn't work the thing is I m actually trying to pivot this using a derived table

select Emp.empid                                                    AS 'Employee Id',
             Emp.Firstname+' '+Emp.lastname     As 'Employee Full Name',
   Mng.Firstname+' '+Mng.lastname     As 'Manager Full Name',
  Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))  As 'Order Date',
   Sum(sod.qty*sod.unitprice)                 As  'Order Value'
From Hr.Employees AS Emp
inner join
Hr.Employees             AS Mng    On Emp.mgrid=Mng.empid
inner join
Sales.Orders                As So          On Mng.empid=So.empid
inner join     
Sales.OrderDetails    As Sod       On So.orderid=Sod.orderid
where year(so.orderdate)='2007' and month(so.orderdate)<='6'
Group By Emp.empid, Emp.Firstname+' '+Emp.lastname,Mng.Firstname+' '+Mng.lastname,   
  Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))
Order by 'order date'
  ;
Go

Answer 7

Hi ulianna,

Please use the [ ]:

pivot (
sum(ordervalue)  for orderdate in ([2007-01],[2007-02],[2007-03],[2007-04],[2007-05],[2007-06])
)as pvt

Best regards,
Cris

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 8

Hi ulianna,

Is there any update on this case? Was your issue resolved? 

If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
In addition, if you have another questions, please feel free to ask.

Best regards,
Cris

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.