Sql server存储过程联合所有 [英] Sql server stored procedure union all
本文介绍了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.
推荐答案
Don'浪费整天，只是goo gle it
参考为什么你得到这个
Don't waste the Whole Day,Just google it
Refer why you getting this
在第二个和第三个子查询中，[Billable Hours]和[Utilized Hours]列是相反的顺序。
在第三个子查询中，缺少collumnapt.currency。
In the second and third sub-query the column [Billable Hours] and [Utilised Hours] are inverse order.
In the third sub-query the collumn "apt.currency" is missing.
这篇关于Sql server存储过程联合所有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
