server存储过程联合所有

server存储过程联合所有

关注
发信
关注(28)粉丝(399)

Sql server存储过程联合所有

本文介绍了Sql server存储过程联合所有的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

ALTER PROCEDURE [dbo].[MRDB_CDTS_FACTORY]
@dtdate VARCHAR(50)
AS
BEGIN
CREATE TABLE #TEMP (CITY VARCHAR(MAX),Capability VARCHAR(MAX),
Tool VARCHAR(MAX),Country varchar(max),[GPS Company] VARCHAR(MAX),
[GPS Service Group] VARCHAR(MAX),[GPS Career Track] VARCHAR(MAX),[GPS Rate Descriptor] VARCHAR(MAX),
[GCPM Rate Type] VARCHAR(MAX),[DATA Career Track] VARCHAR(MAX),[DATA Rate Type] VARCHAR(MAX),
[DATA Load Type] VARCHAR(MAX),[LevelOfEmp] VARCHAR(MAX),[Bill Code] VARCHAR(MAX),
[GCPM Bill Rate] VARCHAR(MAX),[PD&S Load] DECIMAL(18,1),[GS Load] DECIMAL(18,1),
[Unloaded Cost Rate] DECIMAL(18,2),[Billable Hours] DECIMAL(18,0),[Utillised Hours] DECIMAL(18,0),
[PD&R] DECIMAL(18,0),[LOADCOST] DECIMAL(18,1))
INSERT INTO #TEMP
SELECT 
       CITY,
       Capability,
       Tool,
       Country,
       [GPS Company],
       [GPS Service Group],
       [GPS Career Track],
       [GPS Rate Descriptor],
       [GCPM Rate Type],
       [DATA Career Track],
       [DATA Rate Type],
       [DATA Load Type],
       [LevelOfEmp],
       [Bill Code],
       [GCPM Bill Rate],
       [PD&S Load],
       [GS Load],
       [Unloaded Cost Rate],
       [Billable Hours],
       [Utillised Hours],
       [PD&R],
       [LOADCOST]
       
  FROM    
       
       
(select 
 distinct(apt.city) AS CITY,
 APT.Capability AS Capability,
 'All' as Tool,      
 apt.country as Country, 
 'Accenture' as [GPS Company],
 'All' as [GPS Service Group], 
 'Services' as [GPS Career Track],
 'Exception -BPO :'+apt.Capability+'-Day-'+apt.Language+'-'+apt.city as [GPS Rate Descriptor],
 '' as [GCPM Rate Type],
 'Services' as [DATA Career Track],
 'Exception -BPO :'+apt.Capability+'-Day-'+apt.Language+'-'+apt.city as [DATA Rate Type],
lds.[Total Low SU]*100 as [Total Low SU],
lds.[Total Medium SU]*100 as [Total Medium SU],
lds.[Total High SU]*100 as [Total High SU],
lds.[Total Accenture Office/Dedicated]*100 as [Total Accenture Office/Dedicated],
lds.[Total Client Provided Facility / WFH]*100 as [Total Client Provided Facility / WFH],
lds.[Total Client provided facility with Tech]*100 as [Total Client provided facility with Tech],
apt.Level as [LevelOfEmp],
'' as [Bill Code],
'' as [GCPM Bill Rate],
99999.0 AS [PD&S Load],
99999.0 AS [GS Load],
apt.currency,CAST((NULLIF(nullif((apt.Regular),0)/nullif((sc.[Billable Hours]),0),0)) AS DECIMAL(18,2)) as [Unloaded Cost Rate], 
CAST(sc.[Billable Hours] as decimal(18,0)) as [Billable Hours],
CAST(sc.[Utillised Hours] as decimal(8,0)) AS [Utillised Hours],
sc.[PD&R] as [PD&R]
FROM AnnualPayRoll_Table apt
join SeatCharge sc on apt.Level=sc.level join Loads lds on lds.Location_Name=sc.country where apt.Capability NOT IN ('Capital Project Management' , 'Emerging - Industry Specific','Emerging Cross Industry') AND APT.Regular <> 0 AND APT.city=SC.CITY
AND apt.Datetime=@dtdate and sc.DATETIME=@dtdate And lds.Location_Name=apt.country
and lds.Center_name=apt.city and lds.Center_name=sc.CITY and apt.Capability=lds.Capability and lds.Datecol=@dtdate 

       
                            
union all

select 
     distinct(apt.city) as CITY,
     apt.Capability as Capability,
     'All' as Tool,
     apt.country as Country,
     'Accenture' as [GPS Company],
     'All' as [GPS Service Group],
     'Services' as [GPS Career Track], 
     'Exception -BPO :'+apt.Capability+'-Night-'+apt.Language+'-'+apt.city as [GPS Rate Descriptor],
     '' as [GCPM Rate Type],
     'Services' as [DATA Career Track],
     'Exception -BPO :'+apt.Capability+'-Night-'+apt.Language+'-'+apt.city as [DATA Rate Type],
     lds.[Total Low SU]*100 as [Total Low SU],
     lds.[Total Medium SU]*100 as [Total Medium SU],
     lds.[Total High SU]*100 as [Total High SU],
     lds.[Total Accenture Office/Dedicated]*100 as [Total Accenture Office/Dedicated],
     lds.[Total Client Provided Facility / WFH]*100 as [Total Client Provided Facility / WFH],
     lds.[Total Client provided facility with Tech]*100 as [Total Client provided facility with Tech],
     apt.Level as [LevelOfEmp],
     '' as [Bill Code],
     '' as [GCPM Bill Rate],
     99999.0 AS [PD&S Load],
     99999.0 AS [GS Load],
     apt.currency,CAST((nullif((apt.Night),0)/nullif((sc.[Billable Hours]),0)) AS DECIMAL(18,2)) as [Unloaded Cost Rate],
      CAST(sc.[Utillised Hours] as decimal(18,0)) as [Utillised Hours],
      CAST(sc.[Billable Hours] as decimal(18,0)) as [Billable Hours],
      sc.[PD&R] as [PD&R]
 from AnnualPayRoll_Table apt
join SeatCharge sc on apt.Level=sc.level join Loads lds on lds.Location_Name=sc.country
  where apt.Capability NOT IN ('Capital Project Management' , 'Emerging - Industry Specific','Emerging Cross Industry') AND APT.Night <> 0 AND APT.city=SC.CITY
and apt.Datetime=@dtdate and sc.DATETIME=@dtdate And lds.Location_Name=apt.country
and lds.Center_name=apt.city and lds.Center_name=sc.CITY and apt.Capability=lds.Capability and lds.Datecol=@dtdate 

 

union all

select 
     distinct(hrsnondcso.Center_name) as CITY,
      
      hrsnondcso.Capability as Capability,
      
      'All' as Tool,
      
    hrsnondcso.Location_Name as Country,
      
     'Accenture' as [GPS Company],
      
     'All' as [GPS Service Group],
      
     'Services' as [GPS Career Track],
      
     'Exception -BPO :'+hrsnondcso.Capability+' '+' ' +' '+hrsnondcso.Center_name as [GPS Rate Descriptor],
      
     '' as [GCPM Rate Type],
      
     'Services' as [DATA Career Track],
      
     'Exception -BPO :'+hrsnondcso.Capability+' '+' '+' '+hrsnondcso.Center_name as [DATA Rate Type],
      
      lds.[Total Low SU]*100 as [Total Low SU],
      lds.[Total Medium SU]*100 as [Total Medium SU],
      lds.[Total High SU]*100 as [Total High SU],
      lds.[Total Accenture Office/Dedicated]*100 as [Total Accenture Office/Dedicated],
      lds.[Total Client Provided Facility / WFH]*100 as [Total Client Provided Facility / WFH],
      lds.[Total Client provided facility with Tech]*100 as [Total Client provided facility with Tech],
      
      hrsnondcso.Level as [LevelOfEmp],
      
     '' as [Bill Code],
     
     '' as [GCPM Bill Rate],
     
     99999.0 AS [PD&S Load],
     
     99999.0 AS [GS Load],
     
     0.00  as [Unloaded Cost Rate],
      
    CAST(hrsnondcso.[Utilised Hours] as decimal(18,0)) as [Utillised Hours],
     
    CAST(hrsnondcso.[Billable Hours] as decimal(18,0))as [Billable Hours],

     hrsnondcso.[PDR Hours] as [PD&R]
      
from Hours_NonDcso hrsnondcso join Loads lds on lds.Location_Name=hrsnondcso.Location_Name
and lds.Center_name=hrsnondcso.Center_name and lds.Capability=hrsnondcso.Capability and hrsnondcso.Datecol=lds.Datecol
where hrsnondcso.Datecol=@dtdate and lds.Datecol=@dtdate 


) p
           
UNPIVOT(   [LOADCOST] FOR [DATA Load Type] IN 
                                         (
                                          [Total Low SU],
                                          [Total Medium SU],
                                          [Total High SU],
                                          [Total Accenture Office/Dedicated],
                                          [Total Client Provided Facility / WFH],
                                          [Total Client provided facility with Tech]
                                          )
                                          
                                          )AS UNPVT ORDER BY CITY,Country,Capability 
                                          




UPDATE #TEMP SET [PD&S Load]=LD.[PDS Low SU]*100 FROM #TEMP TM JOIN Loads LD ON LD.Location_Name=TM.Country and ld.Center_name=tm.CITY and ld.Capability=tm.Capability
where tm.[DATA Load Type]='Total Low SU' and ld.Datecol=@dtdate
UPDATE #TEMP SET [PD&S Load]=LD.[PDS Medium SU]*100 FROM #TEMP TM JOIN Loads LD ON LD.Location_Name=TM.Country and ld.Center_name=tm.CITY and ld.Capability=tm.Capability
where tm.[DATA Load Type]='Total Medium SU' and ld.Datecol=@dtdate
UPDATE #TEMP SET [PD&S Load]=LD.[PDS High SU]*100 FROM #TEMP TM JOIN Loads LD ON LD.Location_Name=TM.Country and ld.Center_name=tm.CITY and ld.Capability=tm.Capability
where tm.[DATA Load Type]='Total High SU' and ld.Datecol=@dtdate
UPDATE #TEMP SET [PD&S Load]=LD.[PDS Accenture Office/Dedicated]*100 FROM #TEMP TM JOIN Loads LD ON LD.Location_Name=TM.Country and ld.Center_name=tm.CITY and ld.Capability=tm.Capability
where tm.[DATA Load Type]='Total Accenture Office/Dedicated' and ld.Datecol=@dtdate
UPDATE #TEMP SET [PD&S Load]=LD.[PDS Client provided facility/WFH]*100 FROM #TEMP TM JOIN Loads LD ON LD.Location_Name=TM.Country and ld.Center_name=tm.CITY and ld.Capability=tm.Capability
where tm.[DATA Load Type]='Total Client Provided Facility / WFH' and ld.Datecol=@dtdate
UPDATE #TEMP SET [PD&S Load]=LD.[GS Client provided facility with Tech]*100 FROM #TEMP TM JOIN Loads LD ON LD.Location_Name=TM.Country and ld.Center_name=tm.CITY and ld.Capability=tm.Capability
where tm.[DATA Load Type]='Total Client provided facility with Tech' and ld.Datecol=@dtdate
select * from #TEMP
END

--exec MRDB_CDTS_FACTORY 'FY-14'





嗨我收到以下错误...我花了一整天但是找不到我正在做的错误请帮帮我.. 。







hi i am getting below error...i spend whole day but could not found the mistake i am doing please help me...


Msg 205, Level 16, State 1, Procedure MRDB_CDTS_FACTORY, Line 14
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

推荐答案



这篇关于Sql server存储过程联合所有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

09-16 09:54
Powered by LMLPHP ©2024 server存储过程联合所有 0.005943