SQLTeam.com | Weblogs | Forums

Sql distribution query


#1

Can someone please help me with this SQL:


#2

We need CREATE TABLE and INSERT statements for the data. A picture of data helps as explanation, but it doesn't help at all for writing code.


#3

To see what Scott is talking about, please read and heed the following article to give your post the best chance for getting a good, tested answer. It usually takes less time than it does to create and post a pretty picture.
http://www.sqlservercentral.com/articles/Best+Practices/61537/


#4

Please use the this SQL Code - Please review the error message and let me know. I would need the answer in 2 decimal places. Appreciate your help.


#5

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


#6

Sorry for not obeying the proper way. Here is the complete SQL which you can use to create and load data for you. The 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 as Hours, b.TCode from @tbl_PCode a, @tbl_TCode b
group by a.ID,a.PCode, a.PHours, b.TCode
)
select * from cte