Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  more select in the same table

Author  Topic  sqlteamR Starting Member 1 Post Posted - 2011-07-12 : 00:10:35 ho gays, i want to write a stored proc or function the original is a access query it may be difficult to wrote the same appears this is a access query there are all a query in access --R_Rapport_MaxCCS4: ISUP_MaxCCS2->MaxCCS3-- Maximum de CCS ISUP SELECT [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Bureau, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Mois, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Nb_jour, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Heure, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Peg_out, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Peg_in, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Blocage_out, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].[Moy_%blocage], [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Use_total, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_CCS_offert, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Maintenance, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Faisceau FROM [R_Rapport_MaxCCS3: ISUP_MaxCCS] LEFT JOIN [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure] ON ([R_Rapport_MaxCCS3: ISUP_MaxCCS].Max_CCS_offert = [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_CCS_offert) AND ([R_Rapport_MaxCCS3: ISUP_MaxCCS].Mois = [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Mois) AND ([R_Rapport_MaxCCS3: ISUP_MaxCCS].Bureau = [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Bureau) AND ([R_Rapport_MaxCCS3: ISUP_MaxCCS].Faisceau = [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Faisceau) GROUP BY [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Bureau, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Mois, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Nb_jour, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Heure, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Peg_out, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Peg_in, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Blocage_out, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].[Moy_%blocage], [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_Use_total, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_CCS_offert, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Maintenance, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Faisceau ORDER BY [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Bureau, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Mois, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Faisceau; --R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure-- Regroupe les données Outgoing avec les données Incoming SELECT [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Bureau, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Mois, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Nb_jour, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Heure, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Faisceau, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Peg_out AS Moy_Peg_out, [R_Rapport_MaxBlocage/CCS1: ISUP_Incoming].Peg_in AS Moy_Peg_in, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Overflow_out AS Moy_Blocage_out, CASE ([Moy_Peg_out]+[Moy_Blocage_out])=0 then 0 ElSE ([Moy_Blocage_out]/([Moy_Peg_out]+[Moy_Blocage_out]))*100) AS [Moy_%blocage], [Use_out]+[Use_in] AS Moy_Use_total, CASE [Peg_out]=0 then [Use_in] ELSE (([Peg_out]+[Overflow_out]*0.35)*([Use_out]/[Peg_out]))+[Use_in] AS Moy_CCS_offert, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Maintenance_out AS Maintenance FROM [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing] INNER JOIN [R_Rapport_MaxBlocage/CCS1: ISUP_Incoming] ON ([R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Mois = [R_Rapport_MaxBlocage/CCS1: ISUP_Incoming].Mois) AND ([R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Faisceau = [R_Rapport_MaxBlocage/CCS1: ISUP_Incoming].Faisceau) AND ([R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Heure = [R_Rapport_MaxBlocage/CCS1: ISUP_Incoming].Heure) AND ([R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Bureau = [R_Rapport_MaxBlocage/CCS1: ISUP_Incoming].Bureau) ORDER BY [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Bureau, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Mois, [R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing].Heure; --R_Rapport_MaxCCS3: ISUP_MaxCCS-- Détermine le maximum de CCS par bureau par mois pour les faisceaux ISUP SELECT [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Bureau, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Mois, Max([R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Moy_CCS_offert) AS Max_CCS_offert, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Faisceau FROM [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure] GROUP BY [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Bureau, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Mois, [R_Rapport_MaxBlocage/CCS2: ISUP_Moyenne/heure].Faisceau; --R_Rapport_MaxBlocage/CCS1: ISUP_Outgoing-- Extrait les faisceaux Outgoing ISUP de la table T_TNQ_moyenne SELECT T_TNQ_moyenne.Bureau, T_TNQ_moyenne.Mois, T_TNQ_moyenne.Nb_jour, T_TNQ_moyenne.Heure, T_TNQ_moyenne.Faisceau, T_TNQ_moyenne.Direction, T_TNQ_moyenne.Peg AS Peg_out, T_TNQ_moyenne.Overflow AS Overflow_out, T_TNQ_moyenne.Use AS Use_out, T_TNQ_moyenne.Maintenance AS Maintenance_out FROM T_TNQ_moyenne INNER JOIN T_Info_faisceaux ON (T_TNQ_moyenne.Bureau = T_Info_faisceaux.Acronyme) AND (T_TNQ_moyenne.Faisceau = T_Info_faisceaux.Faisceau) WHERE (((T_TNQ_moyenne.Mois) Between [Forms]![F_Rapport_MaxCCS/Blocage]![Mois_Debut] And [Forms]![F_Rapport_MaxCCS/Blocage]![Mois_Fin]) AND ((T_TNQ_moyenne.Nb_jour)>=5) AND ((T_TNQ_moyenne.Direction)="Outgoing") AND ((T_Info_faisceaux.Type_faisceau)="ISUP")) ORDER BY T_TNQ_moyenne.Bureau, T_TNQ_moyenne.Mois, T_TNQ_moyenne.Heure; --R_Rapport_MaxBlocage/CCS1: ISUP_Incoming-- Extrait les faisceaux Incoming ISUP de la table T_TNQ_moyenne SELECT T_TNQ_moyenne.Bureau, T_TNQ_moyenne.Mois, T_TNQ_moyenne.Nb_jour, T_TNQ_moyenne.Heure, T_TNQ_moyenne.Faisceau, T_TNQ_moyenne.Direction, T_TNQ_moyenne.Peg AS Peg_in, T_TNQ_moyenne.Overflow AS Overflow_in, T_TNQ_moyenne.Use AS Use_in, T_TNQ_moyenne.Maintenance AS Maintenance_in FROM T_TNQ_moyenne INNER JOIN T_Info_faisceaux ON (T_TNQ_moyenne.Bureau = T_Info_faisceaux.Acronyme) AND (T_TNQ_moyenne.Faisceau = T_Info_faisceaux.Faisceau) WHERE (((T_TNQ_moyenne.Mois) Between @pdatedebut And @pdatefin) AND ((T_TNQ_moyenne.Nb_jour)>=@pNbrejour) --5 AND ((T_TNQ_moyenne.Direction)=@pdirection) AND ((T_Info_faisceaux.Type_faisceau)=@pfaisc)) ORDER BY T_TNQ_moyenne.Bureau, T_TNQ_moyenne.Mois, T_TNQ_moyenne.Heure; and i have write this query but with cross join but in the original is inner join i know it's not the same result but i'm new and i work in this way 10 days ago SELECT ResOutIn.Bureau, ResOutIn.Mois, ResOutIn.Nb_jour, ResOutIn.Heure, ResOutIn.Faisceau, ResOutIn.Moy_Peg_out AS My_peg_out, ResOutIn.Moy_Peg_In, ResOutIn.Moy_Blocage_out, CASE WHEN (ResOutIn.Moy_Peg_out+ResOutIn.Moy_Blocage_out=0) THEN 0 ELSE (ResOutIn.Moy_Blocage_out/(ResOutIn.Moy_Peg_out+ResOutIn.Moy_Blocage_out)*100) END AS [My_%Blocage], (ResOutIn.Use_out+ResOutIn.Use_in) as Moy_use_Total, CASE WHEN ResOutIn.Moy_Peg_out=0 THEN ResOutIn.Moy_Peg_In ELSE ((ResOutIn.Moy_Peg_out+ResOutIn.Moy_Peg_In*0.35)*(ResOutIn.Use_out/ResOutIn.Moy_Peg_out)) END AS Moy_CCS_offert, ResOutIn.Maintenance_out as Maintenance FROM (SELECT Resout.Bureau,Resout.Mois,ResOut.Nb_jour,Resout.Heure,ResOut.Faisceau, Resout.Peg_out AS Moy_Peg_out, resin.Peg_in AS Moy_Peg_In, Resout.Overflow_out AS Moy_Blocage_out, Resout.Use_out, Resin.Use_in, Resout.Maintenance_out, Resin.Maintenance_in FROM ((SELECT T_TNQ_moyOut.Bureau, T_TNQ_moyOut.Mois, T_TNQ_moyOut.Nb_jour, T_TNQ_moyOut.Heure, T_TNQ_moyOut.Faisceau, T_TNQ_moyOut.Direction, T_TNQ_moyOut.Peg AS Peg_out, T_TNQ_moyOut.Overflow AS Overflow_out, T_TNQ_moyOut.[Use] AS Use_out, T_TNQ_moyOut.Maintenance AS Maintenance_out FROM T_TNQ_moyenne as T_TNQ_moyOut INNER JOIN T_Info_faisceaux as T_Info_faiscOut ON (T_TNQ_moyOut.Bureau = T_Info_faiscOut.Acronyme) AND (T_TNQ_moyOut.Faisceau = T_Info_faiscOut.Faisceau) WHERE (((T_TNQ_moyOut.Mois) Between CONVERT(date,@pdatedebut,102) And CONVERT(date,@pdatedebut,102)) AND ((T_TNQ_moyOut.Nb_jour)>=5) AND ((T_TNQ_moyOut.Direction)='Outgoing') AND ((T_Info_faiscOut.Type_faisceau)=@pfaisc))) as Resout CROSS JOIN (SELECT T_TNQ_moyIn.Bureau, T_TNQ_moyIn.Mois, T_TNQ_moyIn.Nb_jour, T_TNQ_moyIn.Heure, T_TNQ_moyIn.Faisceau, T_TNQ_moyIn.Direction, T_TNQ_moyIn.Peg AS Peg_in, T_TNQ_moyIn.Overflow AS Overflow_in, T_TNQ_moyIn.[Use] AS Use_in, T_TNQ_moyIn.Maintenance AS Maintenance_in FROM T_TNQ_moyenne as T_TNQ_moyIn INNER JOIN T_Info_faisceaux as T_Info_faiscIn ON (T_TNQ_moyIn.Bureau = T_Info_faiscIn.Acronyme) AND (T_TNQ_moyIn.Faisceau = T_Info_faiscIn.Faisceau) WHERE (((T_TNQ_moyIn.Mois) Between CONVERT(date,@pdatedebut,102) And CONVERT(date,@pdatedebut,102)) AND ((T_TNQ_moyIn.Nb_jour)>=5) AND ((T_TNQ_moyIn.Direction)='Incoming') AND ((T_Info_faiscIn.Type_faisceau)=@pfaisc))) as Resin)) AS ResOutIn; please if you have some ideas thank's gays