مشکل در نوشتن کوئری های مورد نظر در SQL Server 2008

[sall_2005_1363]

باسلام من یک یک سوال رو به 20 نفر بانک نویس خوب داده ام ولی از عهده اون برنیامده اند؟
راهنماییم کنید.

سوال شامل 3 جدول است
جدول یک(کالا)

شماره فرم ...............کالا.......... تعداد تولید ...........زمان شروع ...................زمان پایان
1.......................... خودکار............... 10 ......................7 ..............................10
1 ..........................پاک کن.............. 20 ....................10 ..............................12
1 ..........................مداد ..................12 ....................12 ..............................15
2 ..........................مداد................ .. 20 ......................7 ..............................12
3.......................... خودکار.............. 10 .......................7 ...............................9........
3 ..........................مداد................ . 12 .....................10 ..............................15

جدول دوم(پرسنل)

شماره فرم ..........................پرسنل .........................زمان شروع ........................زمان پایان
1 ......................................احمد.... ............................. 7 .......................................12
1 ......................................رضا...... ............................. 8 .......................................9
1 .....................................محمود ...............................9 .......................................11
1.................................... حسن............................ 7:20 .......................................15
1 ....................................رضا ...................................11............. ..................... 14:10
1 ....................................رضا........ ........................... 11 ......................................12

جدول سوم (نرخ)

ردیف ..................نام کالا......................... تعداد پرسنل....................... تعداد تولید برحسب پرسنل
1 .......................خودکار............... ...................... 1 ..................................5
2....................... خودکار..................................... 2.................................. 7
3....................... خودکار..................................... 3.................................. 10
4 ......................خودکار................ ...................... 4.................................. 15
5 ......................خودکار................ ...................... 5.................................. 18
6 ......................مداد.................... ..................... 1.................................. 7
7 ......................مداد.................... ..................... 2.................................. 12
8 ......................مداد.................... ..................... 3.................................. 13
9 ......................مداد.................... ..................... 4.................................. 14
1 ......................پاک کن..................................... 1 ..................................5
2 ......................پاک کن..................................... 2.................................. 7
3 ......................پاک کن..................................... 3.................................. 10
4 ......................پاک کن..................................... 4.................................. 15
5 ......................پاک کن..................................... 5.................................. 18


حال اطلاعات بالا مفروض است گزارشات مد نظر
1) هر کالا با هر تعداد پرسنل یک نرخ تولیدی دارد چطور راندمان هر پرسنل را بر اساس کارکردشان بدست آوریم؟ یا هر پرسنل کارکرد خالص آنها چقدر است؟

2) برای هر کالا چند نفر زمان(ساعت) پرسنل در هر شماره فرم و در مجموع یک بازه زمانی برده است؟

3) از آنجایی که امکان ندارد یک نفر چند کالا را هم زمان تولید کند چند پرسنل نرم افزار را دور زده اند ؟(جواب :پرسنل رضا )

[محمد سلیم آبادی]

سلام،
ظاهرا مثالی که زدین مشکل داره.

1 ....................................رضا ...................................11............. ..................... 14:10
1 ....................................رضا........ ........................... 11 ......................................12

چرا برای "رضا" با یک شماره فرم یکسان دو سطر با تاریخ شروع یکسان در نظر گرفتین؟
منظورتون از "ردیف" در جدول سوم چیه؟ 8 و 9 و دوباره 1 ؟!

می تونید یک نمونه از نتیجه ی مورد نظرتون را پست کنید؟ (برای هر پرس وجوی دلخواه)

[sall_2005_1363]

گاهی وقتا نمی تونم یک مسئله ساده رو خوب بیان کنم از صبوری شما پیشاپیش تشکر می کنم و بابت این موضوع عذر خواهم.

در خصوص پرسنل به نام رضا اون در روز روی یک دستگاه که یک شماره فرم داره در دو ساعت کاری مختلف کار کرده که به این صورت در حال ثبت دستی می باشند.
در یک مجموعه چند(10 تا 12) دستگاه وجود دارد که هر کدام از دستگاها چند کالای (هر دستگاه تا 20 کالا تولید می کند)مختلف می زن که برای اون اطلاعات جدول یک رو روی یک کاغذ هر روز یادداشت می کنند.
تعداد محدودی پرسنل مثلاً 20 تا به صورت چرخشی برای تولید هر کالا کار می کنند بطوری که بسته به سفارش هر کالا جای آنها عوض می شود و تعداد آنها در هر لحظه متفاوت می باشد
حال آنها این کار رو بصورت دستی انجام می دهندکه جدول نرخ هم بکار می رود.
همین مثال بالا رو حل می کنم بصورت دستی.
از ساعت 7 تا 720 احمد تنها کار کرده پس 20 دقیقه از روی جدول کالا خودکار تولید کرده و باید در ساعتی 5 تا طبق جدول نرخ تولید می کرده که سهم اون 1.7 میشه
از ساعت 720 تا 8 احمد و حس 40 دقیقه 2 نفری خودکار تولید کرده اند که از جدول نرخ باید 2 نفری ساعتی 7 تا تولید می کردند سهم این 2 با هم 4.7 تولید باید کنند.
از ساعت 8 تا 9 احمد رضا و حسن یک ساعت خودکار تولید کردند با نرخ ساعتی 10 تا پس با هم 10 تا باید کار کنند
از ساعت 9 تا 11 احمد محمود حسن کارکردند با توجه به جدول کالا و نرخ باید 10 تا خودکار و 10 تا پاک کن تولید می کرند
از ساعت 11 تا 12 احمد حسن و رضا باید 10 تا پاک کن تولید می کردند
از ساعت 12 تا 1420 حسن و رضا باید 15.7 پاک کن تولید کنن
از ساعت 1420 تا 15 حسن تنها کار کرده که باید 3.3 تا پاک کن تولید کند

تولید خودکار این مجموعه باید 1.7+4.7+10+10=26.4 تا خودکار باید تولید می کردند که در جدول کالا آنها 10 تا تولید کرده اند 26.4-10=16.4 تا کم کاری هر کردام به نسبت تولید کسر کار بر ساعت می خورند
تولید پاک کن هم باید 10+10+15.7+3.3=39 پاک کن باشه که 12 تا تولید کرده اند که 39-12=27 تا معادل 5:40 دقیقه کسر کار دارند با توجه به اینکه باید ساعتی 5 تا یک پرسنل تولید کند و این بین پرسنل سر شکن می شود.
البته از ساعت 12 تا 15 مداد تولید کرده اند که من برای سنگین نشدن مسئله تولید اون ساعت رو پاکن زدم.
وقتی روزی 2 تا محصول رو اینطوری وقت می گیره تولید 10 محصول در یک فرم و روزی 20 فرم ............خیلی زیاد هست/؟
خیلی سخت توش موندم؟

[محمد سلیم آبادی]

من کلیات سوال را متوجه شدم. سوال بیشتر به مبحث "شبیه سازی" مربوط میشه تا SQL !
فکر نمی کنم بشه به این سادگی ها این مساله را با Query حل کرد.
مثلا در مثالی که در پست اولتان قرار دادین احمد از ساعت 7 تا 12 کار کرده سه ساعت اول خودکار تولید کرده و دو ساعت آخر پاک کن تا اینجا مشکلی نیست ولی رضا ساعت هشت به کمک احمد میاد که طبق جدول سوم از ساعت 8 تا 9 تعداد تولید به 7 عدد در ساعت می رسد....

[محمد سلیم آبادی]

ببینید فکر می کنم ابتدا باید تاریخ شروع و پایان هر کارگر از هم تفکیک و تجزیه بشن. بطور مثال با در نظر گرفتن مثال پست اول :
سطر مربوط به احمد که از ساعت 7 تا 12 کار کرده باید به سطرهای زیر تجزیه بشه:
7 تا 10
10 تا 12

همینطور برای محمود داریم:
9 تا 10
10 تا 11

با این کار میشه سپس سطرها را بر اساس تاریخ جدول شماره یک گروه بندی کنیم و Count اشان را ضرب در نرخی که در جدول سوم وجود دارد بکنیم تا تعداد کالاهایی که تولید شدن بدست آیند.

فکر می کنم بشه داده های اولیه را بدست آورد ولی گزارش آماری که مد نظرتان هست را فکر نمی کنم بتونم بدست بیارم...

[محمد سلیم آبادی]

شاید به دلیل اینکه زیاد توضیح دادم صورت مسئله گنده(بزرگ) شده و کسی مایل به پاسخ نیست!
:لبخندساده:
شایدم اینکه سوالت اصلا ربطی به SQL و مشتقات اون نداره!
شایدم هم کسی مایل نیست وقتی برای ایجاد راه حل برای این مساله صرف کنه!
شایدم اصلا کسی سوادش به حل این مساله قد نده!
شایدم اگر زیاد Cross Post کنی مجبور بشم در این تاپیک رو تخته کنم!

در ضمن سایت های دیگه ای هم در دنیا وجود داره که بتونی روش حساب کنی و جواب بگیری مثلا:
sqlservercentral.com
sqlteam.com

[sall_2005_1363]

با عرض پوزش از مدیر بخش:

تا حدودی مشکلم حله فقط چطور می تونیم تجزیه کنیم زمان رو

من فکر می کنم اگه بتونم زمان رو تجزیه کنم و در آن زمان نوع کالا و پرسنل مربوطه رو در بیارم مشکلم حل می شه

[محمد سلیم آبادی]

من فکر می کنم اگه بتونم زمان رو تجزیه کنم و در آن زمان نوع کالا و پرسنل مربوطه رو در بیارم مشکلم حل می شه
ابتدا پست قبل از این پست را حذف کنید چون تکراری شده و واقعا تایپ خیلی شلوغ شد.

ولی من فکر نمی کنم با تجزیه کردن زمان ها کار تمام بشه یا عظم مشکل حل بشه. قبلا این کار را انجام دادم.
تازه بعد از تجزیه باید مشکلات شروع بشه.
چند ساعت دیگه کوئری مربوط به تجزیه ی زمان ها را پست می کنم.

[محمد سلیم آبادی]

اینم تجزیه زمان ها.

--kalaTime
declare @kalaTime table
(form_nbr int,
kala_nbr int,
number int,
start real,
[end] real);
insert @kalaTime values
(1,1,10,7,10),
(1,2,20,10.1,12),
(1,3,12,12.1,15);
--Peson
declare @person table
(form_nbr int,
person_nbr int,
start real,
[end] real);
insert @person values
(1,1,7,12),
(1,2,8,9),
(1,3,9,11),
(1,2,11,12);
--Nerkh
declare @nerkh table
(kala_nbr int,
person_count int,
nerkh int);
insert @nerkh values
(1,1,5),
(1,2,7),
(1,3,10),
(1,4,15),
(1,5,18),
(2,1,5),
(2,2,7),
(2,3,10),
(2,4,15),
(2,5,18),
(3,1,7),
(3,2,12),
(3,3,13),
(3,4,14);
--=========================================
SET NOCOUNT ON;
SELECT P.form_nbr,
kala_nbr,
person_nbr,
[start_intersect] = CASE WHEN KT.start BETWEEN P.start AND P.[end] THEN KT.start
ELSE P.start
END,
[end_intersect] = CASE WHEN KT.[end] BETWEEN P.start AND P.[end] THEN KT.[end]
ELSE P.[end]
END
FROM @person P
CROSS APPLY
@kalaTime KT
WHERE KT.form_nbr = P.form_nbr
AND (
(
KT.start BETWEEN P.start AND P.[end]
OR
KT.[end] BETWEEN P.start AND P.[end]
)
OR
(
P.start BETWEEN KT.start AND KT.[end]
OR
P.[end] BETWEEN KT.start AND KT.[end]
)
)
ORDER BY P.person_nbr;
/*
form_nbr kala_nbr person_nbr start_intersect end_intersect
----------- ----------- ----------- --------------- -------------
1 1 1 7 10
1 2 1 10.1 12
1 1 2 8 9
1 2 2 11 12
1 1 3 9 10
1 2 3 10.1 11
*/