Calculating each record using another Table

Transact-SQL
1

Please use the SQL QUERY. I want to calculate percentage using sum from another record.

End Result should look like this:

SQL QUERY:
DECLARE @tbl_PCode table
(
ID int,
PCode VARCHAR(7),
PHours DECIMAL (16,1)
)

DECLARE @tbl_TCode table
(
ID int,
TCode VARCHAR(7),
THours DECIMAL (16,1)
)

INSERT INTO @tbl_PCode
Values

(450001,'REGC',40.00),
(450001,'OTC',10.00)

INSERT INTO @tbl_TCode
Values

(450001,'FieldC',12.00),
(450001,'ShopC',38.00)

;with cte as (
select a.ID,a.PCode, a.PHours, b.TCode as TCODE, b.thours as THours,
sum(a.phours) as "Percent" from @tbl_PCode a, @tbl_TCode b
group by a.ID,a.PCode, a.PHours, b.TCode, b.thours
)
select * from cte

2

There's no clean way to join those table as shown, but roughly something like this will give you what you need:

;with cte as (
select ID, SUM(PHours) AS PHours_Total
from @tbl_PCode
group by ID
)
select p.ID,p.PCode, p.PHours, t.TCode as TCODE, t.thours as THours, 
    cast(max(t.THours / c.PHours_Total * 100) as int) as [percent]
from @tbl_PCode p
cross join @tbl_TCode t
inner join cte c on c.ID = p.ID
group by p.ID,p.PCode, p.PHours, t.TCode, t.thours
3

Viola! I figured it out using pos column.

Here is the full SQL QUERY:

DECLARE @tbl_PCode table
(
ID int,
PCode VARCHAR(7),
PHours DECIMAL (16,1),
pos int
)

DECLARE @tbl_TCode table
(
ID int,
TCode VARCHAR(7),
THours DECIMAL (16,1),
pos int
)

INSERT INTO @tbl_PCode
Values

(450001,'REGC',40.00,1),
(450001,'OTC',10.00,2)

INSERT INTO @tbl_TCode
Values

(450001,'ShopC',12.00,1),
(450001,'FieldC',38.00,2)

;with cte as (
select a.ID,a.PCode, a.PHours, b.TCode as TCODE, b.thours as THours,

b.thours / (select (sum(b.thours)) from @tbl_PCode a, @tbl_TCode b
where a.pos = b.pos) as "Percent"

from @tbl_PCode a, @tbl_TCode b
where a.pos = b.pos
group by a.ID,a.PCode, a.PHours, b.TCode, b.thours
)
select * from cte order by ID desc, pcode desc, tcode asc

4

I thought of using a similar ROW_NUMBER, generated in a subquery/cte, but I wasn't sure both tables would always have exactly the same number of rows.

5

Sorry for not letting you know. Now I want the data to show like this - Can you please help

6

Here is a better explanation. Now I want to split each record into 2 records. See below diagram

7

Aha, I think I understand much better now:

;with cte as (
select ID, SUM(PHours) AS PHours_Total
from @tbl_PCode
group by ID
)
select p.ID, p.PCode, 
    cast(t.THours * [percent] as decimal(9, 2)) as Hours, t.TCode
from @tbl_PCode p
inner join cte c on c.ID = p.ID
inner join @tbl_TCode t on t.ID = p.ID
cross apply (
    select cast(p.PHours * 1.0 / c.PHours_Total as decimal(9, 2)) as [percent]
) as ca1
8

Wow Scott! This works like a charm!
THANK YOU

9

Hi - Sorry - Just realized that the total REG hours is not matching.

Your outpu is showing:

450001 REGC 33.11 FieldC
450001 REGC 4.25 ShopC
450001 OTC 9.34 FieldC
450001 OTC 1.20 ShopC

REGC = 33.11 + 4.25 <> 37.28
I am not sure why it is not matching the total ?????

Please use this SQL QUERY

DECLARE @tbl_PCode table
(
ID varchar(50),
PCode varchar(50),
PHours DECIMAL (16,2)
)

DECLARE @tbl_TCode table
(
ID varchar(50),
TCode varchar(50),
THours DECIMAL (16,2)
)

INSERT INTO @tbl_PCode
Values

('450001','REGC',37.28),
('450001','OTC',10.62)

INSERT INTO @tbl_TCode
Values

('450001','FieldC',42.45),
('450001','ShopC',5.45)

;with cte as (
select ID, SUM(PHours) AS PHours_Total
from @tbl_PCode
group by ID
)
select p.ID, p.PCode,
cast(t.THours * [percent] as decimal(16, 2)) as Hours, t.TCode
from @tbl_PCode p
inner join cte c on c.ID = p.ID
inner join @tbl_TCode t on t.ID = p.ID
cross apply (
select cast(p.PHours * 1.0 / c.PHours_Total as decimal(16, 2)) as [percent]
) as ca1