difficult TSQL Problem -

Hello !
I have a difficult TSQL Problem.
Perhaps there is an expert, who can help me ...
I have 2 tables: "WorkingDays" and "Planned Pieces".
Table 1
year - month - workingdays
2004 - 12 - 21
2005 - 01 - 20
2005 - 02 - 19
...
Table 2
customernr - year - plannedpieces
11339 - 2004 - 25000
11442 - 2005 - 36000
...
Now:
I share the plannedpieces proportional on every month:
--PLANZAHLEN
declare @.zaehler int
set @.zaehler = 1
while @.zaehler <= 12
BEGIN
insert FAKT_SALESFOLLOWUP
select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
+ cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
from xdatplankunde p
set @.zaehler = @.zaehler +1
END
Future:
Based on these 2 tables I want to create a new table (=source for
analysis services) with a row for each month.
But I want to share the year-plannedpieces in the in the proportion of
the workingdays.
Sample:
sum(workingdays) where (year=2005) = 240
February have 19 workingdays
Pieces of Feburary: 25000 / (19/240)
Do you have any idea ?
Thanks
aaapaulTry,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays /
nullif(t_y.sum_workingdays * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"aaapaul" wrote:

> Hello !
> I have a difficult TSQL Problem.
> Perhaps there is an expert, who can help me ...
> I have 2 tables: "WorkingDays" and "Planned Pieces".
> Table 1
> year - month - workingdays
> 2004 - 12 - 21
> 2005 - 01 - 20
> 2005 - 02 - 19
> ...
> Table 2
> customernr - year - plannedpieces
> 11339 - 2004 - 25000
> 11442 - 2005 - 36000
> ...
> Now:
> I share the plannedpieces proportional on every month:
> --PLANZAHLEN
> declare @.zaehler int
> set @.zaehler = 1
> while @.zaehler <= 12
> BEGIN
> insert FAKT_SALESFOLLOWUP
> select p.kdnr,convert(datetime,'01.' + cast(p.jahr as varchar) + '-'
> + cast(@.zaehler as varchar) ,104),p.stueck_ori/12 as planstueck
> from xdatplankunde p
> set @.zaehler = @.zaehler +1
> END
>
> Future:
> Based on these 2 tables I want to create a new table (=source for
> analysis services) with a row for each month.
> But I want to share the year-plannedpieces in the in the proportion of
> the workingdays.
> Sample:
> sum(workingdays) where (year=2005) = 240
> February have 19 workingdays
> Pieces of Feburary: 25000 / (19/240)
> Do you have any idea ?
>
> Thanks
> aaapaul
>|||Correction,
select
t1.[year],
t1.[month],
t2.customernr,
t2.plannedpieces / nullif((t_ym.sum_workingdays_ym /
nullif(t_y.sum_workingdays_y * 1.00, 0)), 0)
from
table1 as t1
inner join
table2 as t2
on t1.[year] = t2.[year]
inner join
(
select
[year],
[month],
sum(workingdays) as sum_workingdays_ym
from
table1
group by
[year],
[month]
) as t_ym
on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
inner join
(
select
[year],
sum(workingdays) as sum_workingdays_y
from
table1
group by
[year]
) as t_y
on t1.[year] = t_y.[year];
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try,
> select
> t1.[year],
> t1.[month],
> t2.customernr,
> t2.plannedpieces / nullif((t_ym.sum_workingdays /
> nullif(t_y.sum_workingdays * 1.00, 0)), 0)
> from
> table1 as t1
> inner join
> table2 as t2
> on t1.[year] = t2.[year]
> inner join
> (
> select
> [year],
> [month],
> sum(workingdays) as sum_workingdays_ym
> from
> table1
> group by
> [year],
> [month]
> ) as t_ym
> on t1.[year] = t_ym.[year] and t1.[month] = t_ym.[month]
> inner join
> (
> select
> [year],
> sum(workingdays) as sum_workingdays_y
> from
> table1
> group by
> [year]
> ) as t_y
> on t1.[year] = t_y.[year];
>
> AMB
>
> "aaapaul" wrote:
>|||Many Thanks SQLGod !
It works fine.
aaapaul