I have 2 tables :
Tbl_PCode:
ID : 54321
Date : 8/1/2016
PCode: REG
Hours: 8
ID : 54321
Date : 8/1/2016
PCode: OT
Hours: 2
Tbl_TCode:
ID : 54321
Date : 8/1/2016
TCode: InsideC
Hours: 6
ID : 54321
Date : 8/1/2016
TCode: OutsideC
Hours: 4
Need to display:
ID PCode Hours TCode
54321 REG 6 InsideC
54321 REG 2 OutsideC
54321 OT 2 OutsideC
What are the rules/logic for distributing hours the way you do?
How do you know that the 2 hours with pcode=ot should be distributed to tcode=outsidec?
Could the following output be just as right as the output you showed:
ID PCode Hours TCode
54321 REG 4 InsideC
54321 REG 4 OutsideC
54321 OT 2 InsideC
or
ID PCode Hours TCode
54321 REG 5 InsideC
54321 REG 3 OutsideC
54321 OT 1 InsideC
54321 OT 1 OutsideC
It will look first at REG value (8 hours) and find out if both InsideC and OutsideC values available. If both of them are available then REG will be distributed among InsideC and OutsideC example: InsideC (value: 6) is the higher than (4) so that gets selected as shown as :
54321 REG 6 InsideC
54321 REG 2 OutsideC
In other words , the REG rows get split up among InsideC and OutsideC.
Let me ask another way then - what (which field) determines what REG should be "handles" before OT?
The reason I'm asking is, SQL doesn't garantee the order in which we receive the rows, unless we specify order by.
Another thing: is date relevant here? Must date from Tbl_PCode match date from Tbl_TCode?
Sorry. I have a new request. The date field is now removed. Please review the 2 sample tables.
Tbl_PayCode:
Tbl_TransferCode:
OUTPUT SHOULD BE:
Summary:
Each Paycode will be required to be distributed between Inside and Outside that is from Tbl_TransferCode
I have color coded the group sets (red,yellow,green,blue) for you to easily understand the logic.
Set # 1 explanation: (this is most difficult in my opinion) 
Please note Set # 1 has two rows of paycode . Since Inside (6hrs) is higher than Outside (4hrs) the Inside hours will come first so 6 hrs will be displayed in the first row, The next row will then be calculated from the previous row i.e 8-6 = 2 as Outside. The third row will be for OTC as 10 - 6 - 2 = 2 hrs
Set # 2,3,4 explanation:
Since Set # 2,3,4 has only 1 row of paycode it will get split up among Inside and Outside hours accordingly.
Please note the toal hours of each set will be equal.
Thank you for your help.
Answer to your previous question:
REG should always be populated before OTC or any other paycode.
Try this:
/* Break into single hour */
with cte_paycode1
as (select personnum
,paycode
,[hours]-1 as rest
from tbl_paycode
union all
select personnum
,paycode
,rest-1 as rest
from cte_paycode1 as a
where rest>0
)
/* Make ready to join in right order */
,cte_paycode2
as (select personnum
,paycode
,row_number() over(partition by personnum
order by case
when paycode='REG' then 0
else 1
end
,paycode
)
as rn
from cte_paycode1
)
/* Break into single hour */
,cte_transfercode1
as (select personnum
,transfercode
,[hours]-1 as rest
from tbl_transfercode
union all
select personnum
,transfercode
,rest-1 as rest
from cte_transfercode1 as a
where rest>0
)
/* Make ready to join in right order */
,cte_transfercode2
as (select a.personnum
,a.transfercode
,row_number() over(partition by a.personnum
order by b.[hours] desc
)
as rn
from cte_transfercode1 as a
inner join tbl_transfercode as b
on b.personnum=a.personnum
and b.transfercode=a.transfercode
)
/* Now join and sum it all up */
select a.personnum
,a.paycode
,count(*) as [hours]
,b.transfercode
from cte_paycode2 as a
left outer join cte_transfercode2 as b
on b.personnum=a.personnum
and b.rn=a.rn
group by a.personnum
,a.paycode
,b.transfercode
order by a.personnum
,case
when a.paycode='REG' then 0
else 1
end
,a.paycode
,count(*) desc
,b.transfercode desc
;
Error: Invalid object name. I want to input some sample data.
here is the code:
DECLARE
@tbl_paycode TABLE
(
personnum VARCHAR(32),
paycode VARCHAR(32),
hours INT
);
INSERT @tbl_paycode VALUES
('100444', 'REG', 8),
('100444', 'OTC', 2),
('700000', 'REG', 38),
('800000', 'VAC', 12),
('900000', 'REG', 35);
DECLARE
@tbl_transfercode TABLE
(
personnum VARCHAR(32),
transfercode VARCHAR(32),
hours INT
);
INSERT @tbl_transfercode VALUES
('100444', 'Outside', 4),
('100444', 'Inside', 6),
('700000', 'Outside', 20),
('700000', 'Inside', 18),
('800000', 'Inside', 12),
('900000', 'Inside', 35);
;
with cte_paycode1
as (select personnum
,paycode
,[hours]-1 as rest
from tbl_paycode
union all
select personnum
,paycode
,rest-1 as rest
from cte_paycode1 as a
where rest>0
)
/* Make ready to join in right order /
,cte_paycode2
as (select personnum
,paycode
,row_number() over(partition by personnum
order by case
when paycode='REG' then 0
else 1
end
,paycode
)
as rn
from cte_paycode1
)
/ Break into single hour /
,cte_transfercode1
as (select personnum
,transfercode
,[hours]-1 as rest
from tbl_transfercode
union all
select personnum
,transfercode
,rest-1 as rest
from cte_transfercode1 as a
where rest>0
)
/ Make ready to join in right order /
,cte_transfercode2
as (select a.personnum
,a.transfercode
,row_number() over(partition by a.personnum
order by b.[hours] desc
)
as rn
from cte_transfercode1 as a
inner join tbl_transfercode as b
on b.personnum=a.personnum
and b.transfercode=a.transfercode
)
/ Now join and sum it all up /
select a.personnum
,a.paycode
,count() as [hours]
,b.transfercode
from cte_paycode2 as a
left outer join cte_transfercode2 as b
on b.personnum=a.personnum
and b.rn=a.rn
group by a.personnum
,a.paycode
,b.transfercode
order by a.personnum
,case
when a.paycode='REG' then 0
else 1
end
,a.paycode
,count(*) desc
,b.transfercode desc
;
You need to adjust tablenames to the "new" names you gave them.
Try this:
/* Break into single hour */
with cte_paycode1
as (select personnum
,paycode
,[hours]-1 as rest
from @tbl_paycode
union all
select personnum
,paycode
,rest-1 as rest
from cte_paycode1 as a
where rest>0
)
/* Make ready to join in right order */
,cte_paycode2
as (select personnum
,paycode
,row_number() over(partition by personnum
order by case
when paycode='REG' then 0
else 1
end
,paycode
)
as rn
from cte_paycode1
)
/* Break into single hour */
,cte_transfercode1
as (select personnum
,transfercode
,[hours]-1 as rest
from @tbl_transfercode
union all
select personnum
,transfercode
,rest-1 as rest
from cte_transfercode1 as a
where rest>0
)
/* Make ready to join in right order */
,cte_transfercode2
as (select a.personnum
,a.transfercode
,row_number() over(partition by a.personnum
order by b.[hours] desc
)
as rn
from cte_transfercode1 as a
inner join @tbl_transfercode as b
on b.personnum=a.personnum
and b.transfercode=a.transfercode
)
/* Now join and sum it all up */
select a.personnum
,a.paycode
,count(*) as [hours]
,b.transfercode
from cte_paycode2 as a
left outer join cte_transfercode2 as b
on b.personnum=a.personnum
and b.rn=a.rn
group by a.personnum
,a.paycode
,b.transfercode
order by a.personnum
,case
when a.paycode='REG' then 0
else 1
end
,a.paycode
,count(*) desc
,b.transfercode desc
;
Viola. It works. Here is the complete code:
DECLARE
@tbl_paycode TABLE
(
personnum VARCHAR(32),
paycode VARCHAR(32),
hours INT
);
INSERT @tbl_paycode VALUES
('100444', 'REG', 8),
('100444', 'OTC', 2),
('700000', 'REG', 38),
('800000', 'VAC', 12),
('900000', 'REG', 35);
DECLARE
@tbl_transfercode TABLE
(
personnum VARCHAR(32),
transfercode VARCHAR(32),
hours INT
);
INSERT @tbl_transfercode VALUES
('100444', 'Outside', 4),
('100444', 'Inside', 6),
('700000', 'Outside', 20),
('700000', 'Inside', 18),
('800000', 'Inside', 12),
('900000', 'Inside', 35);
with cte_paycode1
as (select personnum
,paycode
,[hours]-1 as rest
from @tbl_paycode
union all
select personnum
,paycode
,rest-1 as rest
from cte_paycode1 as a
where rest>0
)
/* Make ready to join in right order /
,cte_paycode2
as (select personnum
,paycode
,row_number() over(partition by personnum
order by case
when paycode='REG' then 0
else 1
end
,paycode
)
as rn
from cte_paycode1
)
/ Break into single hour /
,cte_transfercode1
as (select personnum
,transfercode
,[hours]-1 as rest
from @tbl_transfercode
union all
select personnum
,transfercode
,rest-1 as rest
from cte_transfercode1 as a
where rest>0
)
/ Make ready to join in right order /
,cte_transfercode2
as (select a.personnum
,a.transfercode
,row_number() over(partition by a.personnum
order by b.[hours] desc
)
as rn
from cte_transfercode1 as a
inner join @tbl_transfercode as b
on b.personnum=a.personnum
and b.transfercode=a.transfercode
)
/ Now join and sum it all up /
select a.personnum
,a.paycode
,count() as [hours]
,b.transfercode
from cte_paycode2 as a
left outer join cte_transfercode2 as b
on b.personnum=a.personnum
and b.rn=a.rn
group by a.personnum
,a.paycode
,b.transfercode
order by a.personnum
,case
when a.paycode='REG' then 0
else 1
end
,a.paycode
,count(*) desc
,b.transfercode desc
;
Can you please with this query: I have an error message
with
-- sample data
Tbl_PCode as (
select *
from (values
(54321,'REG', 8.10, 1),
(54321,'OTC', 2.10, 2)
) t(ID,PCode,Hours,pos)
),
Tbl_TCode as (
select *
from (values
(54321,'InsideC', 6.00, 1),
(54321,'OutsideC',4.20, 2)
) t(ID,TCode,Hours,pos)
),
tally as (
select top(24) rn= row_number() over(order by (select null))
from sys.all_objects
),
-- query
t_P as (
select *, rn=row_number() over(partition by ID 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 order by pos)
from Tbl_TCode
cross apply (select top(Hours) 1 n
from tally
) x
)
select t_P.ID, t_P.PCode, count(), t_T.TCode
from t_P
join t_T on t_P.ID=t_T.ID and t_P.rn=t_T.rn
group by t_P.ID, t_P.PCode, t_T.TCode
and...what's the error message?
Msg 1060, Level 15, State 1, Line 26
The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.
I need to display the value in decimals because hours need to be reported accurately.
but you have Top(Hours) and Hours is a decimal. That's the problem. You can cast it to an integer.