So all of a sudden one of my query is not working - no change to the query and DB for the past year. Thanks in advance.
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '
' to data type int.
Completion time: 2021-05-26T10:58:53.0967882-05:00
Query:
/****** Script for SelectTopNRows command from SSMS ******/
;With Orders as
(
Select left(soh.[CreatedDate],4) as Year
,substring(soh.[CreatedDate], 10, 2) as Month
,soi.[ItemCategory]
,count([Order No]) as EdiOrders
,soh.[SalesDocType]
,count(soi.[Item]) as Lines
,soh.[CreatedBy]
From [dbo].[T-SOHeader] soh left join [dbo].[T-SOItem] soi
on soh.[SalesDoc] = soi.[SalesDoc]
Left join [View_EDIorders] edi -- EDI sanitized view that has no duplicates. Has to be rewritten each month!
on cast(soh.[SalesDoc] as float) = [Order No]
Where soh.[CreatedDate] like '202104%'
Group by left(soh.[CreatedDate],4)
,substring(soh.[CreatedDate], 10, 2)
,soi.[ItemCategory]
,soh.[SalesDocType]
,soh.[CreatedBy]
)
, GM as
(Select
left([CalYearMonth],2) as Month
,right([CalYearMonth],4) as Year
,[OrderType]
,[OrderTypeDescr]
,[ItemCat]
,[ItemCatDescr]
,gm.[CreatedBy]
,[PayrollName]
,[Job Title Description]
,[Home Department Code]
,sum(cast([Sell] as float)) as NetSales
,sum(cast([GM] as float)) as GrossMargin
,[termination date]
,[Hire Date]
FROM [Business Analytics].[dbo].[T-GrossMargins_AM_CSR] gm left join [dbo].[HR_SAP_ID]
on [CreatedBy] = [SAPuserID]
Left join [dbo].[HREmployee]
on [FileNumber] = [File Number]
Where [OrderType] not in ('ZRE','ZCR','ZKR', 'ZDR') --exclude "Returns","Credit Memo Request","Consignment Returns","Debit Memo Requests"
and right([Home Department Code],2)=05
and [CalYearMonth] like '04/2021'
Group by
left([CalYearMonth],2)
,right([CalYearMonth],4)
,[OrderType]
,[OrderTypeDescr]
,[ItemCat]
,[ItemCatDescr]
,gm.[CreatedBy]
,[PayrollName]
,[Job Title Description]
,[Home Department Code]
,[Termination Date]
,[Hire Date]
)
SELECT gm.Month
,gm.Year
,[OrderType]
,[OrderTypeDescr]
,[ItemCat]
,[ItemCatDescr]
,gm.[CreatedBy]
,[PayrollName]
,[Job Title Description]
,[Home Department Code]
,gm.NetSales
,gm.GrossMargin
,orders.Lines
,orders.[EdiOrders]
,[termination date]
,[Hire Date]
FROM GM left join Orders
on GM.[OrderType] = orders.SalesDocType
and GM.[ItemCat] = orders.ItemCategory
and GM.month = orders.Month
and GM.Year = Orders.Year
and GM.CreatedBy = Orders.[CreatedBy]
Order by [Year],[Month] asc