دیتاهای مدیران پایه تحقیقات کلان کشور
یکی از وظایف شرکت مدیران پشتیبانی مشتریان عزیز در خصوص نیازهای اطلاعاتی است. مراکز تحقیقاتی کشور نیز از این قاعده مستثنا نبوده و از منابع اطلاعاتی بهره مند می گردد. در این راستا دیتاهای یکپارچه در نرم افزار مدیران بهترین فرصت مطالعاتی و تحقیقاتی را در اختیار محققان کشور قرار می دهد.
بخش دوم یک تحقیق قابل اطمینان الگوریم های گزارش سازی می باشد که تسلط بر آنها در دوره های آموزشی مدیران و یا فیلم های آموزشی تدریس می گردد.
کلیه سازمان ها و مراکز تحقیقاتی می توانند نیاز خود را برای حصول از الگوریتم های صحیح را به شرکت مدیران اعلام و نتایح آن را به صورت قابل اجرا در کلیه دامداری ها تحویل بگیرند. نمونه ای از درخواست موسسه تحقیقات علوم دامی کشور به صورت پیوست ارائه گردیده است.
موسسه تحقیقات علوم دامی کشور به عنوان موسسه ملی و مرجع در امر تحقیقات دام، طیور و زنبور عسل در کشور، در اجرای طرح های تحقیقاتی نقشی راهبردی و هدایت گرانه دارد. فعالیت تحقیقاتی موسسه عمدتاً در گرایش های مختلف علوم دامی شامل تغذیه و فیزیولوژی، ژنتیک و اصلاح نژاد، سیتوژنتیک و بیوتکنولوژی، فراوری تولیدات دامی و حشره شناسی می باشد.
آقای دکتر نوبری فارغ التحصیل دانشگاه فردوسی مشهد در سال ۹۱ و در حال حاضر از اعضاء هیئت مدیره مرکز تحقیقات و آموزش کشاورزی و منابع طبیعی گلستان ( زیر نظر موسسه تحقیقات علوم دامی کشور ) در زمینه ژنتیک و اصلاح نژاد دام در حال تحقیق و پژوهش می باشد.
در راستای پروژه های تحقیقاتی این موسسه آقای دکتر نوبری جهت تجزیه تحلیل مدیریتی ژنوتیپ های مختلف گاوهای شیری طی نامه ارسالی درخواست گزارش جهت استخراج داده های گاوداری را داشته که بخشه کوچکی از گزارش در زیر آمده است.
دستور گزارش به شرح زیر می باشد:
select
R1.serial,R1.Father as [پدر],R1.Tmom as [مادر],GSSir_RegF as [ پدر بزرگ پدری],GDam_Sire as [مادر بزرگ پدری],R2.tmom as [مادر بزرگ مادری],R2.Father As [پدر بزرگ مادری], R1.body as [شماره بدن],R1.BDate as [تاریخ تولد],R1.Blood as [خلوص خونی],C.DateTalghih as [تاریخ اخرین تلقیح],T.SpermNum as [شماره اسپرم],C.ZDate as [تاریخ اخرین زایش],Z.ZTime as [ساعت زایش ], Z.Milkperiod as [شکم زایش],Z.Zsituation as [وضعیت زایش],Z.BrithKind as [تعداد قل],Z.ZComment as [شرح زایش],B.PEar as [گوش گوساله],B.Serial as [سریال گوساله],b.Sex as [جنس گوساله],C.DryStartDate as [تاریخ اخرین خشکی], Meture.MValue as [وزن۱], T11.T1 as [تاریخ وزن۱], T11.Mv2 as [وزن۲], T11.T2 as [تاریخ وزن۲], T11.Mv3 as [وزن۳], T11.T3 as [تاریخ وزن۳], T11.Mv4 as [وزن۴] ,T11.T4 as [تاریخ وزن۴], T11.Mv5 as [وزن۵], T11.T5 as [تاریخ وزن۵], T11.Mv6 as [وزن۶], T11.T6 as [تاریخ وزن۶], T11.Mv7 as [وزن۷], T11.T7 as [تاریخ وزن۷], T11.Mv8 as [وزن۸], T11.T8 as [تاریخ وزن۸],FirstRec.MilkAmountSum as [شیر۱], R11.R1 as [تاریخ رکورد۱], R11.m2 as [شیر۲], R11.R2 as [تاریخ رکورد۲],R11.m3 as [شیر۳], R11.R3 as [تاریخ رکورد۳], R11.m4 as [شیر۴],R11.R4 as [تاریخ رکورد۴], R11.m5 as [شیر۵], R11.R5 as [تاریخ رکورد۵], R11.m6 as [شیر۶], R11.R6 as [تاریخ رکورد۶], R11.m7 as [شیر۷], R11.R7 as [تاریخ رکورد۷],R11.m8 as [شیر۸], R11.R8 as [تاریخ رکورد۸],Talghih.TNum as [نوبت تلقیح۱] ,Ta11.Ta1 as [تاریخ تلقیح۱], Ta11.TD2 as [تلقیح تاریخ۲], Ta11.Ta2 As [تاریخ تلقیح۲], Ta11.TD3 as [نوبت تلقیح۳], Ta11.Ta3 As [تلقیح تاریخ۳],Ta11.TD4 as [نوبت تلقیح۴], Ta11.Ta4 As [تلقیح تاریخ۴], Ta11.TD5 as [نوبت تلقیح۵], Ta11.Ta5 As [تاریخ تلقیح۵], Ta11.TD6 as [نوبت نلقیح۶], Ta11.Ta6 As [تاریخ تلقیح۶], Ta11.TD7 as [نوبت تلقیح۷], Ta11.Ta7 As [تاریخ تلقیح۷] ,Ta11.TD8 as [نوبت تلقیح۸], Ta11.Ta8 As [تاریخ تلقیح۸],CaseHistory.Resone as [دلیل ویزیت۱] ,CasH11.CasH1 as [تاریخ ویزیت۱], CasH11.Res2 as [ویزیت تاریخ۲], CasH11.CasH2 As [تاریخ ویزیت۲], CasH11.Res3 as [دلیل ویزیت۳], CasH11.CasH3 As [ویزیت تاریخ۳],CasH11.Res4 as [دلیل ویزیت۴], CasH11.CasH4 As [ویزیت تاریخ۴], CasH11.Res5 as [دلیل ویزیت۵], CasH11.CasH5 As [تاریخ ویزیت۵], CasH11.Res6 as [دلیل ویزیت۶], CasH11.CasH6 As [تاریخ ویزیت۶],CasH11.Res7 as [دلیل ویزیت۷], CasH11.CasH7 As [تاریخ ویزیت۷] ,CasH11.Res8 as [دلیل ویزیت۸], CasH11.CasH8 As [تاریخ ویزیت۸]
CasH11.CasH6 As [تاریخ ویزیت۶],CasH11.Res7 as [دلیل ویزیت۷], CasH11.CasH7 As [تاریخ ویزیت۷] ,CasH11.Res8 as [دلیل ویزیت۸], CasH11.CasH8 As [تاریخ ویزیت۸]
From CowStatusView C Inner Join Main R1 on C.Serial=R1.Serial
Left Outer Join Main R2 On (R1.TMom=R2.Serial)
Left Outer Join DosSperm Do on Do.RegNo_SPF=R1.Father
Left Outer Join Talghih T on R1.Serial=T.Serial And T.TDate=C.DateTalghih
Left Outer Join Zayesh Z on R1.Serial=Z.Serial And z.ZDate=C.ZDate
left outer JOin Main B on B.BDate=Z.ZDate and B.TMom=R1.Serial
left outer JOin (SELECT T22.*,(SELECT MAX(MDate ) FROM Meture WHERE Serial = S AND MDate < T2 and unit like ‘%کیلو %’) AS T1,T7T.MValue As Mv8, T7T.MValue As Mv7,T6T.MValue As Mv6, T5T.MValue As Mv5,T4T.MValue As Mv4, T3T.MValue As Mv3, T2T.MValue As Mv2
FROM (SELECT *,(SELECT MAX(MDate ) FROM Meture WHERE Serial = S AND MDate < T3 and unit like ‘%کیلو %’) AS T2 FROM (SELECT *,(SELECT MAX(MDate ) FROM Meture WHERE Serial = S AND MDate < T4 and unit like ‘%کیلو %’) AS T3
FROM (SELECT *,(SELECT MAX(MDate ) FROM Meture WHERE Serial = S AND MDate < T5 and unit like ‘%کیلو %’) AS T4
FROM (SELECT *,(SELECT MAX(MDate ) FROM Meture WHERE Serial = S AND MDate < T6 and unit like ‘%کیلو %’) as T5
FROM (select *,(SELECT MAX(MDate ) FROM Meture WHERE Serial = S AND MDate < T7 and unit like ‘%کیلو %’) As T6
From (select*,(SELECT MAX(MDate ) FROM Meture WHERE Serial = S AND MDate < T8 and unit like ‘%کیلو %’) As T7
From (select E.Serial S, MAX(E.MDate ) AS T8, M.MilkPeriod, M.Body
From Meture E INNER JOIN
Main M ON e.Serial = M.Serial
where unit like ‘%کیلو %’
GROUP BY e.Serial, M.MilkPeriod, M.Body)R88)T77)T66)T55) T44) T33) T22 LEFT OUTER JOIN
Meture T2T ON T22.S= T2T.Serial AND T22.T2= T2T.MDate LEFT OUTER JOIN
Meture T4T ON T22.S= T4T.Serial AND T22.T4= T4T.MDate LEFT OUTER JOIN
Meture T5T ON T22.S= T5T.Serial AND T22.T5= T5T.MDate LEFT OUTER JOIN
Meture T6T ON T22.S= T6T.Serial AND T22.T6= T6T.MDate LEFT OUTER JOIN
Meture T7T ON T22.S= T7T.Serial AND T22.T7= T7T.MDate LEFT OUTER JOIN
Meture R8T ON T22.S= R8T.Serial AND T22.T8= R8T.MDate LEFT OUTER JOIN
Meture T3T ON T22.S= T3T.Serial AND T22.T3= T3T.MDate ) T11 on T11.S=c.Serial
LEFT OUTER JOIN Meture ON T11.S= Meture.Serial AND T11.T1= Meture.MDate
LEFT OUTER JOIN (SELECT R22.*,(SELECT MAX(RDate) FROM FirstRec WHERE Serial = S AND RDate < R2) AS R1,
R7T.MilkAmountSum AS M8, R7T.MilkAmountSum AS M7,R6T.MilkAmountSum AS M6, R5T.MilkAmountSum AS M5,
R4T.MilkAmountSum AS M4, R3T.MilkAmountSum AS M3, R2T.MilkAmountSum AS M2
FROM (SELECT *,(SELECT MAX(RDate) FROM FirstRec WHERE Serial = S AND RDate < R3) AS R2
FROM (SELECT *,(SELECT MAX(RDate) FROM FirstRec WHERE Serial = S AND RDate < R4) AS R3
FROM (SELECT *,(SELECT MAX(RDate) FROM FirstRec WHERE Serial = S AND RDate < R5) AS R4
FROM (SELECT *,(SELECT MAX(RDate) FROM FirstRec WHERE Serial = S AND RDate < R6) as R5
FROM (select *,(SELECT MAX(RDate) FROM FirstRec WHERE Serial = S AND RDate < R7) As R6
From (select*,(SELECT MAX(RDate) FROM FirstRec WHERE Serial = S AND RDate < R8) As R7
From (select T .Serial S, MAX(T.RDate) AS R8, M.MilkPeriod, M.Body
From FirstRec T INNER JOIN
Main M ON T .Serial = M.Serial
GROUP BY T.Serial, M.MilkPeriod, M.Body)R88)R77)R66)R55) R44) R33) R22 LEFT OUTER JOIN
FirstRec R2T ON R22.S= R2T.Serial AND R22.R2= R2T.RDate LEFT OUTER JOIN
FirstRec R4T ON R22.S= R4T.Serial AND R22.R4= R4T.RDate LEFT OUTER JOIN
FirstRec R5T ON R22.S= R5T.Serial AND R22.R5= R5T.RDate LEFT OUTER JOIN
FirstRec R6T ON R22.S= R6T.Serial AND R22.R6= R6T.RDate LEFT OUTER JOIN
FirstRec R7T ON R22.S= R7T.Serial AND R22.R7= R7T.RDate LEFT OUTER JOIN
FirstRec R8T ON R22.S= R8T.Serial AND R22.R8= R8T.RDate LEFT OUTER JOIN
FirstRec R3T ON R22.S= R3T.Serial AND R22.R3= R3T.RDate) R11 on R11.S=c.Serial
LEFT OUTER JOIN FirstRec ON R11.S= FirstRec .Serial AND R11.R1= FirstRec .RDate
LEFT OUTER JOIN (SELECT Ta22.*,(SELECT MAX(TDate) FROM Talghih WHERE Serial = S AND TDate < Ta2) AS Ta1,
Ta7T.TNum As TD8, Ta7T.TNum As TD7,Ta6T.TNum As TD6, Ta5T.TNum As TD5,
Ta4T.TNum As TD4, Ta3T.TNum As TD3, Ta2T.TNum As TD2
FROM (SELECT *,(SELECT MAX(TDate) FROM Talghih WHERE Serial = S AND TDate < Ta3) AS Ta2
FROM (SELECT *,(SELECT MAX(TDate) FROM Talghih WHERE Serial = S AND TDate < Ta4) AS Ta3
FROM (SELECT *,(SELECT MAX(TDate) FROM Talghih WHERE Serial = S AND TDate < Ta5) AS Ta4
FROM (SELECT *,(SELECT MAX(TDate) FROM Talghih WHERE Serial = S AND TDate < Ta6) as Ta5
FROM (select *,(SELECT MAX(TDate) FROM Talghih WHERE Serial = S AND TDate < Ta7) As Ta6
From (select*,(SELECT MAX(TDate) FROM Talghih WHERE Serial = S AND TDate < Ta8) As Ta7
From (select T .Serial S, MAX(T.TDate) AS Ta8, M.MilkPeriod, M.Body
From Talghih T INNER JOIN
Main M ON T .Serial = M.Serial
GROUP BY T.Serial, M.MilkPeriod, M.Body)Ta88)Ta77)Ta66)Ta55) Ta44) Ta33) Ta22 LEFT OUTER JOIN
Talghih Ta2T ON Ta22.S= Ta2T.Serial AND Ta22.Ta2= Ta2T.TDate LEFT OUTER JOIN
Talghih Ta4T ON Ta22.S= Ta4T.Serial AND Ta22.Ta4= Ta4T.TDate LEFT OUTER JOIN
Talghih Ta5T ON Ta22.S= Ta5T.Serial AND Ta22.Ta5= Ta5T.TDate LEFT OUTER JOIN
Talghih Ta6T ON Ta22.S= Ta6T.Serial AND Ta22.Ta6= Ta6T.TDate LEFT OUTER JOIN
Talghih Ta7T ON Ta22.S= Ta7T.Serial AND Ta22.Ta7= Ta7T.TDate LEFT OUTER JOIN
Talghih Ta8T ON Ta22.S= Ta8T.Serial AND Ta22.Ta8= Ta8T.TDate LEFT OUTER JOIN
Talghih Ta3T ON Ta22.S= Ta3T.Serial AND Ta22.Ta3= Ta3T.TDate) Ta11 on Ta11.S=c.Serial
LEFT OUTER JOIN Talghih ON Ta11.S= Talghih .Serial AND Ta11.Ta1= Talghih.TDate
Left Outer Join (SELECT CasH22.*,(SELECT MAX(CaseDate) FROM CaseHistory WHERE Serial = S AND CaseDate < CasH2 and CaseKind=’درمانی ‘) AS CasH1,
CasH7T.Resone As Res8, CasH7T.Resone As Res7,CasH6T.Resone As Res6, CasH5T.Resone As Res5,
CasH4T.Resone As Res4, CasH3T.Resone As Res3, CasH2T.Resone As Res2
FROM (SELECT *,(SELECT MAX(CaseDate) FROM CaseHistory WHERE Serial = S AND CaseDate < CasH3 and CaseKind=’درمانی ‘) AS CasH2
FROM (SELECT *,(SELECT MAX(CaseDate) FROM CaseHistory WHERE Serial = S AND CaseDate < CasH4 and CaseKind=’درمانی ‘) AS CasH3
FROM (SELECT *,(SELECT MAX(CaseDate) FROM CaseHistory WHERE Serial = S AND CaseDate < CasH5 and CaseKind=’درمانی ‘) AS CasH4
FROM (SELECT *,(SELECT MAX(CaseDate) FROM CaseHistory WHERE Serial = S AND CaseDate < CasH6 and CaseKind=’درمانی ‘) as CasH5
FROM (select *,(SELECT MAX(CaseDate) FROM CaseHistory WHERE Serial = S AND CaseDate < CasH7 and CaseKind=’درمانی ‘) As CasH6
From (select*,(SELECT MAX(CaseDate) FROM CaseHistory WHERE Serial = S AND CaseDate < CasH8 and CaseKind=’درمانی ‘) As CasH7
From (select T .Serial S, MAX(T.CaseDate) AS CasH8, M.MilkPeriod, M.Body
From CaseHistory T INNER JOIN
Main M ON T .Serial = M.Serial
where CaseKind=’درمانی ‘
GROUP BY T.Serial, M.MilkPeriod, M.Body)CasH88)CasH77)CasH66)CasH55) CasH44) CasH33) CasH22 LEFT OUTER JOIN
CaseHistory CasH2T ON CasH22.S= CasH2T.Serial AND CasH22.CasH2= CasH2T.CaseDate LEFT OUTER JOIN
CaseHistory CasH4T ON CasH22.S= CasH4T.Serial AND CasH22.CasH4= CasH4T.CaseDate LEFT OUTER JOIN
CaseHistory CasH5T ON CasH22.S= CasH5T.Serial AND CasH22.CasH5= CasH5T.CaseDate LEFT OUTER JOIN
CaseHistory CasH6T ON CasH22.S= CasH6T.Serial AND CasH22.CasH6= CasH6T.CaseDate LEFT OUTER JOIN
CaseHistory CasH7T ON CasH22.S= CasH7T.Serial AND CasH22.CasH7= CasH7T.CaseDate LEFT OUTER JOIN
CaseHistory CasH8T ON CasH22.S= CasH8T.Serial AND CasH22.CasH8= CasH8T.CaseDate LEFT OUTER JOIN
CaseHistory CasH3T ON CasH22.S= CasH3T.Serial AND CasH22.CasH3= CasH3T.CaseDate) CasH11 on CasH11.S=c.Serial
LEFT OUTER JOIN CaseHistory ON CasH11.S= CaseHistory .Serial AND CasH11.CasH1= CaseHistory.CaseDate
where R1.ExistCode<2
زبان خود را انتخاب کنید