Hpw to make SUM() return in BIGINT

• Edvard Korsbæk

  SSCertifiable

  Points: 5999

  More actions

  October 23, 2014 at 4:52 am

  #397550

  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

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 23, 2014 at 5:00 am

  #1754285

  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,

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 23, 2014 at 5:01 am

  #1754286

  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

• Edvard Korsbæk

  SSCertifiable

  Points: 5999

  More actions

  October 23, 2014 at 5:02 am

  #1754287

  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!

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 23, 2014 at 5:20 am

  #1754294

  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.