View conversion error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

View conversion error

Author

Topic

munkee
Starting Member

5 Posts

Posted - 2011-11-03 : 05:09:24

Hi all,

I am trying to modify one of the outputs of my view.

The sql code is:

SELECT     dbo.tbldept.Department AS DeptRaisedBy, tbldept_1.Department AS DeptResp, dbo.tbllog.DteReport, dbo.tbllog.DteOccur, dbo.tbllog.NCC_ID, 
                      dbo.tbllog.Pdetected, SUM(dbo.tblCosts.CostFig) AS Total_Cost, dbo.tbllog.NCStatus, DATEDIFF(d, dbo.tblStatusTiming.[Day Entered], 
                      dbo.DateOnly(GETDATE())) AS Days_In_Status, DATEDIFF(d, dbo.tbllog.DateToBeCompleted, dbo.DateOnly(GETDATE())) AS Days_To_Complete, 
                      dbo.tblStatusTiming.StatusType, CASE StatusType WHEN 5 THEN 'Closed' ELSE DATEDIFF(d, dbo.tbllog.DateToBeCompleted, 
                      dbo.DateOnly(GETDATE())) END AS Expr1
FROM         dbo.tbllog INNER JOIN
                      dbo.tbldept AS tbldept_1 ON dbo.tbllog.DeptResp = tbldept_1.DeptID INNER JOIN
                      dbo.tbldept ON dbo.tbllog.DeptRaisedBy = dbo.tbldept.DeptID INNER JOIN
                      dbo.tblStatusTiming ON dbo.tbllog.NCC_ID = dbo.tblStatusTiming.NCIDLINK LEFT OUTER JOIN
                      dbo.tblCosts ON dbo.tbllog.NCC_ID = dbo.tblCosts.NCC_ID
GROUP BY dbo.tbldept.Department, tbldept_1.Department, dbo.tbllog.DteReport, dbo.tbllog.DteOccur, dbo.tbllog.NCC_ID, dbo.tbllog.Pdetected, 
                      dbo.tbllog.NCStatus, DATEDIFF(d, dbo.tblStatusTiming.[Day Entered], dbo.DateOnly(GETDATE())), DATEDIFF(d, dbo.tbllog.DateToBeCompleted, 
                      dbo.DateOnly(GETDATE())), dbo.tblStatusTiming.StatusType
HAVING      (dbo.tblStatusTiming.StatusType = dbo.tbllog.NCStatus)

I am getting a conversion failed when converting the varchar value 'Closed' to data type int.

The view displays the following:

DeptRaisedBy	DeptResp	DteReport	DteOccur	NCC_Id	Pdetected	Total_Cost	NCStatus	Days_In_Status	Days_To_Complete	StatusType	Expr1
BE	BE	00:00.0	00:00.0	2	Tom 	34343	5	1	3	5

I wanted my final column to display 'closed' if the StatusType = 5. For every other status it should display the Days_To_Complete.

Expr1 contains:

CASE StatusType WHEN 5 THEN 'Closed' ELSE DATEDIFF(d , dbo.tbllog.DateToBeCompleted , dbo.DateOnly(GETDATE())) END

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-03 : 05:13:13

SELECT dbo.tbldept.Department AS DeptRaisedBy, tbldept_1.Department AS DeptResp, dbo.tbllog.DteReport, dbo.tbllog.DteOccur, dbo.tbllog.NCC_ID,
dbo.tbllog.Pdetected, SUM(dbo.tblCosts.CostFig) AS Total_Cost, dbo.tbllog.NCStatus, DATEDIFF(d, dbo.tblStatusTiming.[Day Entered],
dbo.DateOnly(GETDATE())) AS Days_In_Status, DATEDIFF(d, dbo.tbllog.DateToBeCompleted, dbo.DateOnly(GETDATE())) AS Days_To_Complete,
dbo.tblStatusTiming.StatusType, CASE StatusType WHEN 5 THEN 'Closed' ELSE CONVERT(VARCHAR,DATEDIFF(d, dbo.tbllog.DateToBeCompleted,
dbo.DateOnly(GETDATE()))) END AS Expr1
FROM dbo.tbllog INNER JOIN
dbo.tbldept AS tbldept_1 ON dbo.tbllog.DeptResp = tbldept_1.DeptID INNER JOIN
dbo.tbldept ON dbo.tbllog.DeptRaisedBy = dbo.tbldept.DeptID INNER JOIN
dbo.tblStatusTiming ON dbo.tbllog.NCC_ID = dbo.tblStatusTiming.NCIDLINK LEFT OUTER JOIN
dbo.tblCosts ON dbo.tbllog.NCC_ID = dbo.tblCosts.NCC_ID
GROUP BY dbo.tbldept.Department, tbldept_1.Department, dbo.tbllog.DteReport, dbo.tbllog.DteOccur, dbo.tbllog.NCC_ID, dbo.tbllog.Pdetected,
dbo.tbllog.NCStatus, DATEDIFF(d, dbo.tblStatusTiming.[Day Entered], dbo.DateOnly(GETDATE())), DATEDIFF(d, dbo.tbllog.DateToBeCompleted,
dbo.DateOnly(GETDATE())), dbo.tblStatusTiming.StatusType
HAVING (dbo.tblStatusTiming.StatusType = dbo.tbllog.NCStatus)

munkee
Starting Member

5 Posts

Posted - 2011-11-03 : 05:21:21

Works a treat! I had tried to convert the 'closed' to int stupidly and had no luck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources