locked
pivot issues can what is wrong with this code please RRS feed

 > 

  • السؤال

  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    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'.

    • تم التحرير بواسطة ulianna 18/ذو الحجة/1441 07:38 م
    18/ذو الحجة/1441 07:36 م

جميع الردود

  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    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.


    • تم التحرير بواسطة Viorel_MVP 18/ذو الحجة/1441 08:08 م
    18/ذو الحجة/1441 08:06 م
  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    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

    18/ذو الحجة/1441 08:13 م
  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    ok thank you very much ,let me try it

    18/ذو الحجة/1441 08:16 م
  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت
    ok thank you
    18/ذو الحجة/1441 08:17 م
  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    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


    18/ذو الحجة/1441 08:25 م
  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    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


    18/ذو الحجة/1441 09:19 م
  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    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.

    20/ذو الحجة/1441 06:15 ص
  • Question
    تسجيل الدخول للتصويت
    0
    تسجيل الدخول للتصويت

    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.

    21/ذو الحجة/1441 12:50 ص