How to set date parameters for Quarters

[renee35]

I need any help that I can get. What I am trying to do is pull data based on the parameter @qtr. I need to figure out how to set the date range so SQL will know what Q1, Q2, etc. should include. Below is what I currently have:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @YearStartDate datetime
DECLARE @RunDate datetime
DECLARE @Official int
DECLARE @Qtr int
DECLARE @Userdate int
DECLARE @Year varchar (4)

-- determine dates for @qtr parameter
select
@userdate = case
when @qtr = 1 then '1/1'
when @qtr = 2 then '4/1'
when @qtr = 3 then '7/1'
when @qtr = 4 then '10/1'
when @qtr = 5 then '1/1' + @year-- should include entire year?
end

SET @Rundate = getdate()
SET @StartDate = cast(cast(month((@qtr*3)-2) as varchar(1)) + '/1/' + cast(@year as varchar(4)) as datetime)
SET @EndDate = cast(cast(month(@qtr*3) as varchar(1)) + '/1/' + cast(@year as varchar(4)) as datetime)
SET @YearStartDate = dateadd (yy, datediff(yy,0,@Qtr),0) --'1/1/2008'
SET @Qtr = 3
SET @Year = 2008


I am not returning any data? I know something is not right with the way the @qtr is set up because without it I get results back!

Thanks a bunch!!!!

Thanks a bunch!!

-T

 

[andrea96]

What is your select statement for the data?

 

[renee35]

create table #work_SUTAReport (sessionid int, employeeid int, startdate datetime NULL, enddate datetime NULL,
socsecno varchar (11), Vrtxtaxid int, abbrev varchar (10), taxentityid int, thispdtaxablewages decimal(19,2),
sutataxablewages decimal(19,2), rate decimal(19,2), taxcap decimal(19,2), createtime datetime NULL, w2box1amt decimal(19,2),
priorytdtaxablewages decimal(19,2), sutawageexcess decimal(19,2), calctaxamount decimal(19,2), calctaxablewages decimal(19,2),
periodsworked int, sutataxamount decimal(19,2),)



IF @Official = 1 /*An 'Official' report, so we have to make sure the check dates are less than the period close date*/
BEGIN

insert into #work_SUTAReport
(
-- SessionID,
EmployeeID,
StartDate,
EndDate,
SocSecNo,
VrtxTaxID,
Abbrev,
TaxEntityID,
ThisPdTaxableWages,
SUTATaxableWages,
Rate,
TaxCap
)
select
-- @SessionID,
b.EmployeeID,
@StartDate,
@EndDate,
max(b.SocSecNo),
max(d.VrtxTaxID),
max(d.Abbrev),
c.TaxEntityID,
isnull(sum(isnull(c.Gross,0)),0),
isnull(sum(isnull(c.TaxableGross,0)),0),
max(isnull(e.Rate,0.0)),
max(isnull(e.TaxCap,0.0))

from prCheckHeader a (READCOMMITTED)
LEFT OUTER JOIN empEmployee b (READCOMMITTED)
ON b.EmployeeID = a.EmployeeID
LEFT OUTER JOIN prCheckTax c (READCOMMITTED)
LEFT OUTER JOIN TaxEntity d (READCOMMITTED)
LEFT OUTER JOIN sysTaxRegInfo e (READCOMMITTED)
ON e.TaxEntityID = d.TaxEntityID
ON d.TaxEntityID = c.TaxEntityID
ON c.CheckHistID = a.CheckHistID
where d.VrtxTaxID = 29
and a.CheckDate between @StartDate AND @EndDate
and a.EffDate <= @EndDate
and isnull(a.[1099Flag],0) <> 1
and isnull(c.ExemptFlag,0) = 0
--and isnull(c.Gross,0) <> 0
/* and a.CheckNumber IS NOT NULL*/
group by c.TaxEntityID,b.EmployeeID
order by c.TaxEntityID,b.EmployeeID

--update table, add number of pay periods worked for reporting period
UPDATE #work_SUTAReport
SET PeriodsWorked =
(select count(distinct a.PayPeriodID) from prCheckHeader a (READCOMMITTED)
where a.EmployeeID = #work_SUTAReport.EmployeeID
and a.CheckDate between @StartDate and @EndDate)
-- where #work_SUTAReport.SessionID = @SessionID
--update table, add amount which will appear in W2 Box 1
UPDATE #work_SUTAReport
SET W2Box1Amt =
isnull((select ISNULL(SUM(ISNULL(e.TaxableGross,0)),0) /*Taxable Wages*/
FROM prCheckHeader a (readcommitted)
LEFT OUTER JOIN prCheckTax e (readcommitted)
ON e.CheckHistID = a.CheckHistID
-- WHERE #work_SUTAReport.SessionID = @SessionID
AND a.EmployeeID = #work_SUTAReport.EmployeeID
AND e.TaxEntityID = 1
AND isnull(e.ExemptFlag,0) = 0
AND isnull(a.[1099Flag],0) <> 1
-- AND a.CheckPrinted = 1
-- AND a.ExpenseChk = 0
AND year(a.CheckDate) BETWEEN @StartDate AND @EndDate
AND a.EffDate <= @EndDate
GROUP BY a.EmployeeID),0)

--update table, add Tax Withheld Amount for reporting period
UPDATE #work_SUTAReport
SET SUTATaxAmount =
(
SELECT isnull(sum(isnull(c.TaxAmount,0)),0)
from prCheckHeader a (READCOMMITTED)
LEFT OUTER JOIN empEmployee b (READCOMMITTED)
ON b.EmployeeID = a.EmployeeID
LEFT OUTER JOIN prCheckTax c (READCOMMITTED)
ON c.CheckHistID = a.CheckHistID
-- where #work_SUTAReport.SessionID = @SessionID
and a.EmployeeID = #work_SUTAReport.EmployeeID
and c.TaxEntityID = #work_SUTAReport.TaxEntityID
AND isnull(c.ExemptFlag,0) = 0
AND isnull(a.[1099Flag],0) <> 1
-- AND a.CheckPrinted = 1
-- AND a.ExpenseChk = 0
and a.CheckDate between @StartDate and @EndDate
and a.EffDate <= @EndDate
)

--update table, add Taxable Gross Prior YTD (prior to reporting period, that is)
UPDATE #work_SUTAReport
SET PriorYTDTaxableWages =
(
SELECT isnull(sum(isnull(c.TaxableGross,0)),0)
from prCheckHeader a (READCOMMITTED)
LEFT OUTER JOIN empEmployee b (READCOMMITTED)
ON b.EmployeeID = a.EmployeeID
LEFT OUTER JOIN prCheckTax c (READCOMMITTED)
ON c.CheckHistID = a.CheckHistID
-- where #work_SUTAReport.SessionID = @SessionID
and a.EmployeeID = #work_SUTAReport.EmployeeID
and c.TaxEntityID = #work_SUTAReport.TaxEntityID
and a.CheckDate >= @YearStartDate
and a.CheckDate < @StartDate
and a.EffDate <= @EndDate
AND isnull(c.ExemptFlag,0) = 0
AND isnull(a.[1099Flag],0) <> 1
-- AND a.CheckPrinted = 1
-- AND a.ExpenseChk = 0
)

--update table
UPDATE #work_SUTAReport
SET ThisPdTaxableWages =
ISNULL((
SELECT
SUM( ISNULL( c1.gross, 0 ) )
FROM
prCheckHeader a1 (READCOMMITTED)
INNER JOIN prCheckTax c1 (READCOMMITTED)
ON c1.CheckHistID = a1.CheckHistID
WHERE
a1.EmployeeID = #work_SUTAReport.EmployeeID
AND
a1.CheckDate BETWEEN @StartDate AND @EndDate
AND
ISNULL( a1.[1099Flag], 0 ) = 0
AND
a1.ManualFlag = 0
AND
c1.TaxentityID = #work_SUTAReport.TaxEntityID
AND
NOT EXISTS ( SELECT * FROM prCheckPay WHERE CheckHistID = a1.CheckHistID )
),0)
+
/* (isnull((
SELECT isnull(sum(isnull(c1.GrossPay,0)),0)
from prCheckHeader a1 (READCOMMITTED)
LEFT OUTER JOIN prCheckPay c1 (READCOMMITTED)
LEFT OUTER JOIN sysPayCodes d1 (READCOMMITTED)
ON d1.PayCodeID = c1.PayCodeID
ON c1.CheckHistID = a1.CheckHistID
where a1.EmployeeID = #work_SUTAReport.EmployeeID
and a1.CheckDate between @StartDate and @EndDate
and a1.EffDate <= @EndDate
and isnull(a1.[1099Flag],0) <> 1
-- and a1.ExpenseChk = 0
and isnull(d1.ExpenseFlag,0) = 0
and a1.manualflag = 0
and exists (select * from prchecktax where checkhistid = a1.checkhistid and taxentityid = #work_SUTAReport.taxentityid and isnull(gross,0) <> 0)
),0)
*/
(isnull((
select sum(grosspayroll)
from vw_prchecksummary a1
inner join prcheckheader b1 on a1.checkhistid = b1.checkhistid
where a1.EmployeeID = #work_SUTAReport.EmployeeID
and a1.CheckDate between @StartDate and @EndDate
and a1.EffDate <= @EndDate
and isnull(b1.[1099Flag],0) <> 1
--and b1.manualflag = 0
and b1.SUIEntityID = #work_SUTAReport.taxentityid
),0)
+
isnull((
SELECT sum(isnull(c1.gross,0))
from prCheckHeader a1 (READCOMMITTED)
LEFT OUTER JOIN prCheckTax c1 (READCOMMITTED)
ON c1.CheckHistID = a1.CheckHistID
where a1.EmployeeID = #work_SUTAReport.EmployeeID
and a1.CheckDate between @StartDate and @EndDate
and a1.EffDate <= @EndDate
and isnull(a1.[1099Flag],0) <> 1
-- and a1.ExpenseChk = 0
and a1.manualflag = 1
and c1.taxentityid = #work_SUTAReport.taxentityid
),0)
- --subtract non-wage deductions from total
isnull((SELECT isnull(sum(isnull(a.ActualAmount,0)),0)
FROM prCheckDeduct a
LEFT OUTER JOIN prCheckHeader b
ON b.CheckHistID = a.CheckHistID
WHERE b.EmployeeID = #work_SUTAReport.EmployeeID
and b.CheckDate between @StartDate and @EndDate
and b.EffDate <= @EndDate
and isnull(b.[1099Flag],0) <> 1
and a.PreTaxStatus = 3 --Foreign Key to SSDedState, DedStateID, Non-wage deduction
and exists (select * from prchecktax where checkhistid = a.checkhistid and suientityid = #work_SUTAReport.taxentityid)
-- and exists (select * from prchecktax where checkhistid = a.checkhistid and taxentityid = #work_SUTAReport.taxentityid)
),0)
) -- where #work_SUTAReport.SessionID = @SessionID
--update table, calculate and insert suta wage excess
UPDATE #work_SUTAReport
SET SUTAWageExcess = ThisPdTaxableWages - SUTATaxableWages
/* case
when (PriorYTDTaxableWages + ThisPdTaxableWages) - TaxCap < 0 then 0
else (PriorYTDTaxableWages + ThisPdTaxableWages) - TaxCap
end*/
-- where #work_SUTAReport.SessionID = @SessionID
--update table, add Calced Tax Amount for reporting period
UPDATE #work_SUTAReport
SET CalcTaxAmount = SUTATaxableWages * (Rate/100)
-- where #work_SUTAReport.SessionID = @SessionID
--update table, add Calc Taxable Wages for reporting period
UPDATE #work_SUTAReport
SET CalcTaxableWages = ThisPdTaxableWages - SUTAWageExcess
-- where #work_SUTAReport.SessionID = @SessionID
END

ELSE /*Not an 'official' report, so we don't have to check the EffDates*/

BEGIN
insert into #work_SUTAReport
(
-- SessionID,
EmployeeID,
StartDate,
EndDate,
SocSecNo,
VrtxTaxID,
Abbrev,
TaxEntityID,
ThisPdTaxableWages,
SUTATaxableWages,
Rate,
TaxCap
)
select
-- @SessionID,
b.EmployeeID,
@StartDate,
@EndDate,
max(b.SocSecNo),
max(d.VrtxTaxID),
max(d.Abbrev),
c.TaxEntityID,
isnull(sum(isnull(c.Gross,0)),0),
isnull(sum(isnull(c.TaxableGross,0)),0),
max(isnull(e.Rate,0.0)),
max(isnull(e.TaxCap,0.0))

from prCheckHeader a (READCOMMITTED)
LEFT OUTER JOIN empEmployee b (READCOMMITTED)
ON b.EmployeeID = a.EmployeeID
LEFT OUTER JOIN prCheckTax c (READCOMMITTED)
LEFT OUTER JOIN TaxEntity d (READCOMMITTED)
LEFT OUTER JOIN sysTaxRegInfo e (READCOMMITTED)
ON e.TaxEntityID = d.TaxEntityID
ON d.TaxEntityID = c.TaxEntityID
ON c.CheckHistID = a.CheckHistID
where d.VrtxTaxID = 29
and a.CheckDate between @StartDate AND @EndDate
and isnull(a.[1099Flag],0) <> 1
and isnull(c.ExemptFlag,0) = 0
--and isnull(c.Gross,0) <> 0
/* and a.CheckNumber IS NOT NULL*/
group by c.TaxEntityID,b.EmployeeID
order by c.TaxEntityID,b.EmployeeID

--update table, add number of pay periods worked for reporting period
UPDATE #work_SUTAReport
SET PeriodsWorked =
(select count(distinct a.PayPeriodID) from prCheckHeader a (READCOMMITTED)
where a.EmployeeID = #work_SUTAReport.EmployeeID
and a.CheckDate between @StartDate and @EndDate)
-- where #work_SUTAReport.SessionID = @SessionID
--update table, add amount which will appear in W2 Box 1
UPDATE #work_SUTAReport
SET W2Box1Amt =
isnull((select ISNULL(SUM(ISNULL(e.TaxableGross,0)),0) /*Taxable Wages*/
FROM prCheckHeader a (readcommitted)
LEFT OUTER JOIN prCheckTax e (readcommitted)
ON e.CheckHistID = a.CheckHistID
-- WHERE #work_SUTAReport.SessionID = @SessionID
AND a.EmployeeID = #work_SUTAReport.EmployeeID
AND e.TaxEntityID = 1
AND isnull(e.ExemptFlag,0) = 0
-- AND a.CheckPrinted = 1
AND isnull(a.[1099Flag],0) <> 1
-- AND a.ExpenseChk = 0
AND year(a.CheckDate) BETWEEN @StartDate AND @EndDate
GROUP BY a.EmployeeID),0)

--update table, add Tax Withheld Amount for reporting period
UPDATE #work_SUTAReport
SET SUTATaxAmount =
(
SELECT isnull(sum(isnull(c.TaxAmount,0)),0)
from prCheckHeader a (READCOMMITTED)
LEFT OUTER JOIN empEmployee b (READCOMMITTED)
ON b.EmployeeID = a.EmployeeID
LEFT OUTER JOIN prCheckTax c (READCOMMITTED)
ON c.CheckHistID = a.CheckHistID
-- where #work_SUTAReport.SessionID = @SessionID
and a.EmployeeID = #work_SUTAReport.EmployeeID
and c.TaxEntityID = #work_SUTAReport.TaxEntityID
and isnull(c.ExemptFlag,0) = 0
and a.CheckDate between @StartDate and @EndDate
and isnull(a.[1099Flag],0) <> 1
)

--update table, add Taxable Gross Prior YTD (prior to reporting period, that is)
UPDATE #work_SUTAReport
SET PriorYTDTaxableWages =
(
SELECT isnull(sum(isnull(c.TaxableGross,0)),0)
from prCheckHeader a (READCOMMITTED)
LEFT OUTER JOIN empEmployee b (READCOMMITTED)
ON b.EmployeeID = a.EmployeeID
LEFT OUTER JOIN prCheckTax c (READCOMMITTED)
ON c.CheckHistID = a.CheckHistID
-- where #work_SUTAReport.SessionID = @SessionID
and a.EmployeeID = #work_SUTAReport.EmployeeID
and c.TaxEntityID = #work_SUTAReport.TaxEntityID
and isnull(c.ExemptFlag,0) = 0
and a.CheckDate >= @YearStartDate
and a.CheckDate < @StartDate
and isnull(a.[1099Flag],0) <> 1
)

--update table
UPDATE #work_SUTAReport
SET ThisPdTaxableWages =
ISNULL((
SELECT
SUM( ISNULL( c1.gross, 0 ) )
FROM
prCheckHeader a1 (READCOMMITTED)
INNER JOIN prCheckTax c1 (READCOMMITTED)
ON c1.CheckHistID = a1.CheckHistID
WHERE
a1.EmployeeID = #work_SUTAReport.EmployeeID
AND
a1.CheckDate BETWEEN @StartDate AND @EndDate
AND
ISNULL( a1.[1099Flag], 0 ) = 0
AND
a1.ManualFlag = 0
AND
c1.TaxentityID = #work_SUTAReport.TaxEntityID
AND
NOT EXISTS ( SELECT * FROM prCheckPay WHERE CheckHistID = a1.CheckHistID )
),0)
+
/*
(isnull((
SELECT isnull(sum(isnull(c1.GrossPay,0)),0)
from prCheckHeader a1 (READCOMMITTED)
LEFT OUTER JOIN prCheckPay c1 (READCOMMITTED)
LEFT OUTER JOIN sysPayCodes d1 (READCOMMITTED)
ON d1.PayCodeID = c1.PayCodeID
ON c1.CheckHistID = a1.CheckHistID
where a1.EmployeeID = #work_SUTAReport.EmployeeID
and a1.CheckDate between @StartDate and @EndDate
and isnull(a1.[1099Flag],0) <> 1
-- and a1.ExpenseChk = 0
and isnull(d1.ExpenseFlag,0) = 0
and a1.manualflag = 0
and exists (select * from prchecktax where checkhistid = a1.checkhistid and taxentityid = #work_SUTAReport.taxentityid and isnull(gross,0) <> 0)
),0)
*/
(isnull((
select sum(grosspayroll)
from vw_prchecksummary a1
inner join prcheckheader b1 on a1.checkhistid = b1.checkhistid
where a1.EmployeeID = #work_SUTAReport.EmployeeID
and a1.CheckDate between @StartDate and @EndDate
-- and a1.EffDate <= @EndDate
and isnull(b1.[1099Flag],0) <> 1
-- and b1.manualflag = 0
and b1.SUIEntityID = #work_SUTAReport.taxentityid
),0)
+
isnull((
SELECT sum(isnull(c1.gross,0))
from prCheckHeader a1 (READCOMMITTED)
LEFT OUTER JOIN prCheckTax c1 (READCOMMITTED)
ON c1.CheckHistID = a1.CheckHistID
where a1.EmployeeID = #work_SUTAReport.EmployeeID
and a1.CheckDate between @StartDate and @EndDate
and isnull(a1.[1099Flag],0) <> 1
-- and a1.ExpenseChk = 0
and a1.manualflag = 1
and c1.taxentityid = #work_SUTAReport.taxentityid
),0)
- --subtract non-wage deductions from total
isnull((SELECT isnull(sum(isnull(a.ActualAmount,0)),0)
FROM prCheckDeduct a
LEFT OUTER JOIN prCheckHeader b
ON b.CheckHistID = a.CheckHistID
WHERE b.EmployeeID = #work_SUTAReport.EmployeeID
and b.CheckDate between @StartDate and @EndDate
and isnull(b.[1099Flag],0) <> 1
and a.PreTaxStatus = 3 --Foreign Key to SSDedState, DedStateID, Non-wage deduction
and exists (select * from prchecktax where checkhistid = a.checkhistid and suientityid = #work_SUTAReport.taxentityid)
-- and exists (select * from prchecktax where checkhistid = a.checkhistid and taxentityid = #work_SUTAReport.taxentityid)
),0)
)
-- where #work_SUTAReport.SessionID = @SessionID


--update table, calculate and insert suta wage excess
UPDATE #work_SUTAReport
SET SUTAWageExcess = ThisPdTaxableWages - SUTATaxableWages
/* case
when (PriorYTDTaxableWages + ThisPdTaxableWages) - TaxCap < 0 then 0
else (PriorYTDTaxableWages + ThisPdTaxableWages) - TaxCap
end*/

--update table, add Calced Tax Amount for reporting period
UPDATE #work_SUTAReport
SET CalcTaxAmount = SUTATaxableWages * (Rate/100)
-- where #work_SUTAReport.SessionID = @SessionID
--update table, add Calc Taxable Wages for reporting period
UPDATE #work_SUTAReport
SET CalcTaxableWages = ThisPdTaxableWages - SUTAWageExcess
-- where #work_SUTAReport.SessionID = @SessionID

END
--now to get the data
SELECT
a.EmployeeID,
a.SocSecNo,
a.TaxEntityID,
a.VrtxTaxID,
a.Abbrev,
a.PeriodsWorked,
a.W2Box1Amt,
a.ThisPdTaxableWages,
a.PriorYTDTaxableWages,
a.TaxCap,
a.CalcTaxableWages,
a.SUTATaxableWages,
a.SUTAWageExcess,
a.CalcTaxAmount,
a.SUTATaxAmount,
a.Rate,
EmpName =
CASE
WHEN LEN(LTRIM(RTRIM(b.MiddleName))) > 0 THEN b.LastName + ', ' + b.FirstName + ' ' + SUBSTRING(b.MiddleName,1,1) + '.'
ELSE b.LastName + ', ' + b.FirstName --+ ' ' + SUBSTRING(b.MiddleName,1,1)
END


FROM #work_SUTAReport a (READCOMMITTED)
LEFT OUTER JOIN empEmployee b (READCOMMITTED)
ON b.EmployeeID = a.EmployeeID
-- WHERE
-- a.SessionID = @SessionID
ORDER BY
a.Abbrev,EmpName,a.SocSecNo

drop table #work_SUTAReport


Thanks a bunch!!

-T

 

[andrea96]

You need to remove "month" from the @StartDate and @EndDate, and you need to move the statements where you are setting the @Qtr and @Year right after your DECLARE statements, before you use them.

 

[renee35]

Okay, I will make those changs; thanks a lot!!

So SQL will know -
"when @qtr = 1 then '1/1'" then the date range is 1/1 - 3/31?

Also, should I add the year to the end of that or will SQL pull the date bases on the year that is selected for the @year parameter?


Thanks a bunch!!

-T

 

[xlbo]

For future reference, please post questions like this in the SQL Server Programming forum as this has nothing to do with SSRS

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[andrea96]

Yes, you have to use @Year, but you already have it in @StartDate and @EndDate.