renee35
MIS
- Jan 30, 2007
- 199
I am struggling with the "Incorrect syntax near the keyword 'UNION'" in the below code. Could someone please tell me what I am doing wrong in the below code:
DECLARE @StartDate datetime
SET @StartDate = '2008-1-1'
DECLARE @EndDate datetime
SET @EndDate = '2008-1-31'
Create table #vacationpay_types (paycodeid int, paycodedesc varchar (255), Type varchar (255))
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcsscsa.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill
FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcsscsa.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcsscsa.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcsscsa.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types
UNION
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcssca2.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill
FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcssca2.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcssca2.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcssca2.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types
UNION
(
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_htrnj.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill
FROM ss_htrnj.dbo.vw_RptUSAMstr v
INNER JOIN ss_htrnj.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_htrnj.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_htrnj.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types
Thanks a bunch!!
Thanks a bunch!!
-T
DECLARE @StartDate datetime
SET @StartDate = '2008-1-1'
DECLARE @EndDate datetime
SET @EndDate = '2008-1-31'
Create table #vacationpay_types (paycodeid int, paycodedesc varchar (255), Type varchar (255))
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcsscsa.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill
FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcsscsa.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcsscsa.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcsscsa.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types
UNION
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcssca2.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill
FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcssca2.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcssca2.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcssca2.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types
UNION
(
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_htrnj.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill
FROM ss_htrnj.dbo.vw_RptUSAMstr v
INNER JOIN ss_htrnj.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_htrnj.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_htrnj.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types
Thanks a bunch!!
Thanks a bunch!!
-T