Sql error - need result using decimal not int

SQLHelp · September 19, 2016, 6:38pm

with

-- Generate the Pay Code Table using VP_TOTALS

Tbl_PCode as (

SELECT ROW_NUMBER() OVER(ORDER BY a.PERSONNUM DESC) AS pos,
a.PERSONNUM as ID,
a.APPLYDATE as Date,
a.PAYCODENAME as PCode,
(sum(a.TIMEINSECONDS * 1.0)/3600) as Hours,
b.PAYRULENAME 
FROM 
VP_TOTALS a  
LEFT OUTER JOIN VP_ALLPERSONV42 b 
ON a.PERSONNUM = b.PERSONNUM 
where 
(a.LABORLEVELNAME1 = '87') and (a.paycodetype = 'P') and (b.HOMELABORLEVELNM1 = '87') and (b.payrulename LIKE 'Hour%') 
and (a.applydate between '8-22-2016' and '9-4-2016') 
group by 
a.[PERSONNUM],[applydate],[PAYCODENAME], [PAYRULENAME]

),

-- Generate the Transfer Code Table using VP_TIMESHEETITMV42

Tbl_TCode as (

SELECT ROW_NUMBER() OVER(ORDER BY a.PERSONNUM DESC) AS pos,
a.PERSONNUM as ID,
a.EVENTDATE as Date,
a.WORKRULENAME as TCode,

(sum(DATEDIFF (second,a.STARTDTM,a.ENDDTM)) * 1.0/3600 ) as Hours,

b.PAYRULENAME
FROM
VP_TIMESHEETITMV42 a
LEFT OUTER JOIN VP_ALLPERSONV42 b
ON a.PERSONNUM = b.PERSONNUM
where (a.LABORLEVELNAME1 = '87') and (b.payrulename LIKE 'Hour%') and (a.eventdate between '8-22-2016' and '9-4-2016')
group by a.[PERSONNUM],[EVENTDATE],[WORKRULENAME],[payrulename]
),

tally as (
select top(500000) rn= row_number() over(order by (select null))
from sys.all_objects
),
-- Layout the SQL Query

t_P as (
select *, rn=row_number() over(partition by ID,Date order by pos)
from Tbl_PCode

cross apply (select top(Hours) 1 n 
             from tally
            ) x

),

t_T as (
select *, rn=row_number() over(partition by ID,Date order by pos)
from Tbl_TCode

cross apply (select top(Hours) 1 n 
             from tally
            ) x

)
select t_P.ID as PersonNum, t_P.PCode as [Pay Code],
count(*) as Hours,

t_T.TCode as [Transfer Code]

from t_P
join t_T on t_P.ID=t_T.ID and t_P.Date=t_T.Date and t_P.rn=t_T.rn
group by t_P.ID, t_P.PCode, t_T.TCode

SQLHelp · September 19, 2016, 6:39pm

SQL ERROR: Msg 1060, Level 15, State 1, Line 56
The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.

ScottPletcher · September 19, 2016, 7:07pm

Round hours up and cast it to the needed data type:
...
select top(CAST(CEILING(Hours) AS int)) 1 n
...
select top(CAST(CEILING(Hours) AS int)) 1 n
...

SQLHelp · September 19, 2016, 8:28pm

Sorry we cannot use Rounding - result should be in decimals format and calculate correctly. Result should be like this for ID: 450001

450001 REG 30.50 FIELDC
450001 REG 10.00 SHOPC
450001 OT 1.75 SHOPC
450001 OT 8.25 FIELDC

SQLHelp · September 19, 2016, 8:33pm

--PLEASE USE THIS SQL QUERY - I HAVE DATA IN DECIMALS.

with
-- sample data
Tbl_PCode as (
select *
from (values
(450001,cast('8/1/2016' as date), 'REG', 40.50, 1),
(450001,cast('8/1/2016' as date), 'OT', 10.25, 2),
(450002,cast('8/2/2016' as date), 'REG', 29.50, 3),
(450003,cast('8/3/2016' as date), 'REG', 32.50, 4),
(450003,cast('8/3/2016' as date), 'VACC', 8.50, 5),
(450004,cast('8/3/2016' as date), 'VACC', 8.50, 6),
(450005,cast('8/3/2016' as date), 'VACC', 35.50,7)

   ) t(ID,Date,PCode,Hours,pos)

),
Tbl_TCode as (
select *
from (values
(450001,cast('8/1/2016' as date), 'SHOPC', 10.25, 1),
(450001,cast('8/1/2016' as date), 'FIELDC',30.50, 2),
(450001,cast('8/1/2016' as date), 'SHOPC', 1.75, 3),
(450001,cast('8/1/2016' as date), 'FIELDC', 8.25, 4),

   (450002,cast('8/2/2016' as date), 'SHOPC', 2.00,   5),
   (450002,cast('8/2/2016' as date), 'FIELDC', 27.50, 6),
   (450003,cast('8/3/2016' as date), 'SHOPC', 20.50,  7),
   (450003,cast('8/3/2016' as date), 'FIELDC', 12.50, 8),
   (450003,cast('8/3/2016' as date), 'SHOPC', 8.00,   9),
   (450003,cast('8/3/2016' as date), 'FIELDC', 0.00,  10),
   (450004,cast('8/3/2016' as date), 'SHOPC', 8.50,   11),
   (450005,cast('8/3/2016' as date), 'SHOPC', 35.50,  12)

   ) t(ID,Date,TCode,Hours,pos)

),
tally as (
select top(500000) rn= row_number() over(order by (select null))
from sys.all_objects
),
-- query
t_P as (
select *, rn=row_number() over(partition by ID,Date order by pos)
from Tbl_PCode
cross apply (select top(CAST(CEILING(Hours) AS int)) 1 n

         from tally
        ) x

),
t_T as (
select , rn=row_number() over(partition by ID,Date order by pos)
from Tbl_TCode
cross apply (select top(CAST(CEILING(Hours) AS int)) 1 n
from tally
) x
)
select t_P.ID as PersonNum, t_P.Date, t_P.PCode as PayCode,
convert(DECIMAL(16,3),count()) as Hours,
t_T.TCode as TransferCode
from t_P
join t_T on t_P.ID=t_T.ID and t_P.Date=t_T.Date and t_P.rn=t_T.rn
group by t_P.ID, t_P.Date, t_P.PCode, t_T.TCode
order by t_P.id asc

SQLHelp · September 19, 2016, 8:45pm

If you use this data in your query we should get the following result:

with
-- sample data
Tbl_PCode as (
select *
from (values
(450001,cast('8/1/2016' as date), 'REG', 40.75, 1),
(450001,cast('8/1/2016' as date), 'OT', 10.25, 2),
(450002,cast('8/2/2016' as date), 'REG', 29.50, 3),
(450003,cast('8/3/2016' as date), 'REG', 32.50, 4),
(450003,cast('8/3/2016' as date), 'VACC', 8.50, 5),
(450004,cast('8/3/2016' as date), 'VACC', 8.50, 6),
(450005,cast('8/3/2016' as date), 'VACC', 35.50,7)
) t(ID,Date,PCode,Hours,pos)

),
Tbl_TCode as (
select *
from (values
(450001,cast('8/1/2016' as date), 'SHOPC', 10.25, 1),
(450001,cast('8/1/2016' as date), 'FIELDC',30.50, 2),
(450001,cast('8/1/2016' as date), 'SHOPC', 2.00, 3),
(450001,cast('8/1/2016' as date), 'FIELDC', 8.25, 4),
(450002,cast('8/2/2016' as date), 'SHOPC', 2.00, 5),
(450002,cast('8/2/2016' as date), 'FIELDC', 27.50, 6),
(450003,cast('8/3/2016' as date), 'SHOPC', 20.50, 7),
(450003,cast('8/3/2016' as date), 'FIELDC', 12.50, 8),
(450003,cast('8/3/2016' as date), 'SHOPC', 8.00, 9),
(450003,cast('8/3/2016' as date), 'FIELDC', 0.00, 10),
(450004,cast('8/3/2016' as date), 'SHOPC', 8.50, 11),
(450005,cast('8/3/2016' as date), 'SHOPC', 35.50, 12)

) t(ID,Date,TCode,Hours,pos)

RESULT SHOULD BE:

450001 REG 30.50 FIELDC
450001 REG 10.25 SHOPC
450001 OT 2.00 SHOPC
450001 OT 8.25 FIELDC

Hope I have not confused you more.

ScottPletcher · September 19, 2016, 9:02pm

You have to round only in the TOP statements, not in any other places where "Hours" is used. A decimal is meaningless, and thus invalid, in the TOP statement.

SQLHelp · September 20, 2016, 6:30pm

Still the hours is still showing incorrectly. Please advise the solution.

SQLHelp · September 20, 2016, 6:32pm

Please use this FULL SQL for testing

with
-- sample data
Tbl_PCode as (
select *
from (values
(450001,cast('8/1/2016' as date), 'REG', 40.75, 1),
(450001,cast('8/1/2016' as date), 'OT', 10.25, 2)

) t(ID,Date,PCode,Hours,pos)

),
Tbl_TCode as (
select *
from (values
(450001,cast('8/1/2016' as date), 'SHOPC', 10.25, 1),
(450001,cast('8/1/2016' as date), 'FIELDC',30.50, 2),
(450001,cast('8/1/2016' as date), 'SHOPC', 2.00, 3),
(450001,cast('8/1/2016' as date), 'FIELDC', 8.25, 4)

) t(ID,Date,TCode,Hours,pos)
),
tally as (
select top(500000) rn= row_number() over(order by (select null))
from sys.all_objects
),
-- query
t_P as (
select *, rn=row_number() over(partition by ID,Date order by pos)
from Tbl_PCode
cross apply (select top(CAST(CEILING(Hours) AS int)) 1 n

     from tally
    ) x

),
t_T as (
select , rn=row_number() over(partition by ID,Date order by pos)
from Tbl_TCode
cross apply (select top(CAST(CEILING(Hours) AS int)) 1 n
from tally
) x
)
select t_P.ID as PersonNum, t_P.Date, t_P.PCode as PayCode,
convert(DECIMAL(16,3),count()) as Hours,
t_T.TCode as TransferCode
from t_P
join t_T on t_P.ID=t_T.ID and t_P.Date=t_T.Date and t_P.rn=t_T.rn
group by t_P.ID, t_P.Date, t_P.PCode, t_T.TCode
order by t_P.id asc