Verify result is an integer

[BB69]

Hello,

I am trying to know how to verify the result of the division below is an integer. This is a timesheet database which stores the value in minutes as a numeric value. I want to check the value entered is in 4 hour intervals. How should the comparrision be fixed correctly below?

SELECT TSL_KEY, TSL_CHR
FROM dbo.TSLINES
WHERE (TSL_KEY IN
(SELECT TSD_TSL
FROM dbo.TSDETAIL
GROUP BY TSD_TSL
HAVING (CAST(SUM(TSD_MON) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_TUE) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_WED) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_THU) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_FRI) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_SAT) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_SUN) AS INT) / 240 > 0))) AND (TSL_CHR = 206)

Thanks
Brian

 

[BB69]

Hello,

I found my problem. I should have used the % sign instead. It should be HAVING (CAST(SUM(TSD_MON) AS INT) % 240 > 0 )

Brian

 

[BB69]

Looks like I got another problem with it. I get the error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. when it runs. How should it be modified to fix that error?

Brian

 

[BB69]

I found the problem with that error also. I remove the TSL_CHR from SELECT TSL_KEY, TSL_CHR

 

[IanWaterman]

Or you could write as

SELECT TSL_KEY, TSL_CHR
FROM dbo.TSLINES TS
inner join 
(SELECT TSD_TSL
FROM dbo.TSDETAIL
GROUP BY TSD_TSL
HAVING (CAST(SUM(TSD_MON) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_TUE) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_WED) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_THU) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_FRI) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_SAT) AS INT) / 240 > 0) OR
(CAST(SUM(TSD_SUN) AS INT) / 240 > 0)) TD
ON TS.TSL_KEY = TD.TSD_TSL
WHERE (TSL_CHR = 206)

Ian

 

[BB69]

Thanks Ian.

wouldn't it have to be the % used instead of the / correct?

Brian