October 23, 2014 at 4:52 am
Hi All!
I have this CURSOR in a stored procedure:
DECLARE CounterBal CURSOR FOR
SELECT
SUM(CASE WHEN A.posetive_negative = 1 AND (B.dutyrosternorm = 0 OR A.Timesum = 1) AND A.Counterbalance_date BETWEEN @loc_dateFrom AND @loc_dateTo THEN A.Minutes ELSE 0 END) / 6000,
SUM(CASE WHEN A.posetive_negative = 1 AND (B.dutyrosternorm = 0 OR A.Timesum = 1) AND A.Counterbalance_date BETWEEN @Typ_Start_Date AND @loc_dateTo THEN A.Minutes ELSE 0 END) / 6000,
SUM(CASE WHEN A.posetive_negative = 0 AND (B.dutyrosternorm = 0 OR A.Timesum = 1) AND A.Counterbalance_date BETWEEN @loc_dateFrom AND @loc_dateTo THEN A.Minutes ELSE 0 END) / 6000,
SUM(CASE WHEN A.posetive_negative = 0 AND (B.dutyrosternorm = 0 OR A.Timesum = 1) AND A.Counterbalance_date BETWEEN @Typ_Start_Date AND @loc_dateTo THEN A.Minutes ELSE 0 END) / 6000,
SUM(CASE WHEN A.posetive_negative = 1 AND A.Timesum = 1 AND A.Counterbalance_date < @Typ_Start_Date THEN A.Minutes ELSE 0 END) / 6000,
SUM(CASE WHEN A.posetive_negative = 0 AND A.Timesum = 1 AND A.Counterbalance_date < @Typ_Start_Date THEN A.Minutes ELSE 0 END) / 6000,
A.Employee_id
FROM dbo.CounterBalance A LEFT JOIN dbo.DutyRoster B ON A.Roster_ID = B.ID WHERE A.CounterBalanceType_id = @loc_CounterBalanceType_id GROUP BY A.Employee_id;
OPEN CounterBal;
OK - I know that many do not like Cursors, but this is how i got it from another developer....
Problem is, that some of the SUM is too big for an INT, and needs a BIGINT instead
How do i force it to get into a BIGINT?
Best regards
Edvard Korsbæk
October 23, 2014 at 5:00 am
SUM supports BIGINTs, so just cast the case statements into BIGINT, something like this:
SELECT
SUM(CAST(CASE WHEN A.posetive_negative = 1 AND (B.dutyrosternorm = 0 OR A.Timesum = 1) AND A.Counterbalance_date BETWEEN @loc_dateFrom AND @loc_dateTo THEN A.Minutes ELSE 0 END) AS BIGINT) / 6000,
October 23, 2014 at 5:01 am
Cast the operand of the SUM to bigint:
...
SUM(
CAST(
CASE
WHEN A.posetive_negative = 1 AND (B.dutyrosternorm = 0 OR A.Timesum = 1)
AND A.Counterbalance_date BETWEEN @loc_dateFrom AND @loc_dateTo
THEN A.Minutes
ELSE 0
END
AS bigint)
)
...
-- Gianluca Sartori
October 23, 2014 at 5:02 am
Gazareth (10/23/2014)
SUM supports BIGINTs, so just cast the case statements into BIGINT, something like this:SELECT
SUM(CAST(CASE WHEN A.posetive_negative = 1 AND (B.dutyrosternorm = 0 OR A.Timesum = 1) AND A.Counterbalance_date BETWEEN @loc_dateFrom AND @loc_dateTo THEN A.Minutes ELSE 0 END) AS BIGINT) / 6000,
Did the trick!
THANKS!
October 23, 2014 at 5:20 am
Edvard Korsbæk (10/23/2014)
Gazareth (10/23/2014)
SUM supports BIGINTs, so just cast the case statements into BIGINT, something like this:SELECT
SUM(CAST(CASE WHEN A.posetive_negative = 1 AND (B.dutyrosternorm = 0 OR A.Timesum = 1) AND A.Counterbalance_date BETWEEN @loc_dateFrom AND @loc_dateTo THEN A.Minutes ELSE 0 END) AS BIGINT) / 6000,
Did the trick!
THANKS!
No problem.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply