Operand type clash: int is incompatible with date

Question

Post reply

Operand type clash: int is incompatible with date

andrewalex.r

Old Hand

Points: 328
More actions
May 4, 2013 at 5:51 am

#394717

ALTER PROCEDURE [dbo].[bnd_ProjwiseCategory_rblockNODATE]
@M_Acc_ID AS INT,
@M_ProjVers_ID AS INT,
@Category AS INT,
@Action AS INT,
@From_Date DATE,
@To_DATE DATE,
@Cate AS INT --ddlcate value
AS
BEGIN
IF @Action=-1 AND @Cate=-3 --ddlaccount and ddlcate ALL
BEGIN
SELECT TSM.Account,
TSM.[Project & Version],
TSM.Resources,
TSM.Category,
SUM(TSM.Utilization)AS Utilization,
SUM(TSM.NONBILLABLE) AS NONBILLABLE,
ROUND(((SUM(TSM.NONBILLABLE)/(SUM(TSM.NONBILLABLE)+SUM(TSM.Utilization)))*100),2) AS Percentage
FROM
(SELECT T.Account,
T.[Project & Version],
T.Resources,
T.Category,
SUM(T.Utilization) AS Utilization ,
SUM(T.NONBILLABLE) AS NONBILLABLE
FROM
(SELECT AC.Acc_Name AS Account,
MPV.FullProjName AS [Project & Version],
MU.[User_Name] AS Resources ,
TSC.TaskCategory AS Category ,
TS.TotalHours AS Utilization,
TS.StartTime,
TS.EndTime,
TS.TSM_Day,
0 AS NONBILLABLE,
CONVERT(DATETIME,TS.StartTime, 108) AS'From',
CONVERT(DATETIME,TS.EndTime, 108) AS 'To',
CONVERT(DATETIME, DATEADD(SS,DATEDIFF(SS ,[StartTime], [EndTime]),0),108) AS TotalHours
FROM TimeSheet TS
INNER JOIN Accounts AC ON TS.M_Acc_ID=AC.M_Acc_ID
INNER JOIN mProjectVersions MPV ON TS.M_ProjVer_ID=MPV.M_ProjVersion_ID
INNER JOIN mUser MU ON TS.TaskOwner=MU.M_User_ID
INNER JOIN mTimeSheetCategory TSC ON TS.M_TaskCat_ID=TSC.M_TaskCat_ID
WHERE TS.M_TaskCat_ID<>1 AND Block=0 AND TS.TSM_Day BETWEEN @From_Date AND @To_Date
GROUP BY AC.Acc_Name,MPV.FullProjName,TaskOwner,MU.[User_Name],
TSC.TaskCategory,TS.TotalHours,TS.StartTime,TS.EndTime,TS.TSM_Day
UNION
SELECT AC.Acc_Name AS Account,
MPV.FullProjName AS [Project & Version],
MU.[User_Name] AS Resources ,
TSC.TaskCategory AS Category ,
0 AS UTilization,
TS.TotalHours AS Utilization,
TS.StartTime,
TS.EndTime,
TS.TSM_Day,
CONVERT(DATETIME,TS.StartTime, 108) AS'From',
CONVERT(DATETIME,TS.EndTime, 108) AS 'To',
CONVERT(DATETIME, DATEADD(SS,DATEDIFF(SS ,[StartTime], [EndTime]),0),108) AS TotalHours
FROM TimeSheet TS
INNER JOIN Accounts AC ON TS.M_Acc_ID=AC.M_Acc_ID
INNER JOIN mProjectVersions MPV ON TS.M_ProjVer_ID=MPV.M_ProjVersion_ID
INNER JOIN mUser MU ON TS.TaskOwner=MU.M_User_ID
INNER JOIN mTimeSheetCategory TSC ON TS.M_TaskCat_ID=TSC.M_TaskCat_ID
WHERE TS.M_TaskCat_ID=1 AND Block=0 AND TS.TSM_Day BETWEEN @From_Date AND @To_Date
GROUP BY AC.Acc_Name,MPV.FullProjName,TaskOwner,MU.[User_Name],
TSC.TaskCategory,TS.TotalHours,TS.StartTime,TS.EndTime,TS.TSM_Day)T
GROUP BY T.Account,T.[Project & Version],T.Resources,
T.Category,T.NONBILLABLE,T.UTilization,T.StartTime,T.EndTime)TSM
GROUP BY TSM.Account,TSM.[Project & Version],TSM.Resources,
TSM.Category,TSM.NONBILLABLE,TSM.UTilization
END
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nice procedure....

Which part's causing the error, what are the data types of the columns?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 2

Look at your code where you are unioning 2 select.

They are not mapped right.

In the first select 0 AS NONBILLABLE should come after TSC.TaskCategory AS Category.

andrewalex.r Old Hand Points: 328 More actions · Answer 3

I need to Get the utilization in the hour format of HH:MM .......

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

andrewalex.r (5/5/2013)
I need to Get the utilization in the hour format of HH:MM .......

This doesn't help us understand the issue here. We can't see your screen, we are not familiar with your tables or your project. In short, we have nowhere near enough information to even begin asking for the information that is still missing. Start by answering Gail's question.

Which part's causing the error, what are the data types of the columns?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 5

Look at the attached xls and check for the fields marked in red and answer me if you have mapped them right.