Can I increment a WHILE inside a SELECT statement

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)

Can I increment a WHILE inside a SELECT statement

Author

Topic

nerdygirl61
Starting Member

21 Posts

Posted - 2011-09-18 : 23:05:22

I am working on converting a staff time accounting system from a VAX environment to sql server 2008r2. When the payroll file doesn't have data for all of the payperiods I need to project the pay period. For example if I am processing pay periods 201115 through 201117 and the maximum payperiod in the file is 201115 I need to project pay periods 201116 and 201117 by copying all the data for pay period 201115 and create a record for 201116 and 201117. I need to use the payperiod from the table to increment. Can this be done? Below is one of the things I have tried unsuccesfully

-- exec spTestBuild @ppCalc = 201117

-- Project pay periods missing from payroll file

declare @pp nvarchar(6)
set @pp = 0

WHILE @pp < @ppCalc
BEGIN
SET @pp = a.PayPeriod
-- INSERT INTO #FirstPayroll
SELECT a.CEID
--,a.HoursPaid
,SUM (a.hoursPaid) as HoursPaid
--,@pp = a.PayPeriod + 1
,@pp = @pp + 1
,a.PayPeriod
,a.PayStartDate
,a.PayEndDate

FROM #FirstPayroll as a
right join #MaxPP as b
on b.CEID = a.CEID
and b.PayPeriod = a.PayPeriod
where b.AccrualFlag is NULL
and ((b.EndDate is NULL)
or (b.EndDate < a.PayEndDate)
)
group by a.CEID
--,a.HoursPaid
,a.PayPeriod
,a.PayStartDate
,a.PayEndDate
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 00:00:03

no need of while loop. you can use a tally or number table for generating the missing periods and copy projected values for them from existing values.

see a similar logic here to generate a calendar on the fly

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

if you want more help post your table structure with some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nerdygirl61
Starting Member

21 Posts

Posted - 2011-09-20 : 12:42:41

Thank you so much!

I didn't quite get it yesterday, but I was busy with other things. I had a few minutes this morning and the light came on in my brain. I actually already had the table and needed to pull data in from it, and realized I needed to pull data from the orignal payroll file instead of the temp table. It works great as coded below for any that are following.

INSERT INTO #FirstPayroll
SELECT a.CEID
,SUM (a.hoursPaid) AS HoursPaid
,a.PayPeriod AS MaxPP
,(c.PeriodYear + c.PeriodNumber) AS PayPeriod
,c.StartDate AS PayStartDate
,c.EndDate AS PayEndDate
,SUM (a.GrossEarnings * (c.PpAllocPct/100)) AS GrossEarnings
,SUM (a.Dollars641 * (c.PpAllocPct/100)) AS Dollars641
,SUM (a.Bene641 * (c.PpAllocPct/100)) AS Bene641
,SUM (a.Dollars644 * (c.PpAllocPct/100)) AS Dollars644
,SUM (a.Bene644 * (c.PpAllocPct/100)) AS Bene644
,SUM (a.Dollars645 * (c.PpAllocPct/100)) AS Dollars645
,SUM (a.Bene645 * (c.PpAllocPct/100)) AS Bene645
,SUM (a.Dollars646 * (c.PpAllocPct/100)) AS Dollars646
,SUM (a.Bene646 * (c.PpAllocPct/100)) AS Bene646
,SUM (a.Dollars647 * (c.PpAllocPct/100)) AS Dollars647
,SUM (a.Bene647 * (c.PpAllocPct/100)) AS Bene647
,SUM (a.DollarsMisc * (c.PpAllocPct/100)) AS DollarsMisc
,SUM (a.BeneMisc * (c.PpAllocPct/100)) AS BeneMisc
,c.PpAllocPct/100 AS PpAllocPct
FROM tblPayrollFiles AS a
right join #MaxPP AS b
on b.CEID = a.CEID
and b.PayPeriod = a.PayPeriod
, lkpPayPerAlloc AS c
WHERE b.AccrualFlag is NULL
and ((b.EndDate is NULL)
or (b.EndDate < a.PayEndDate))
and ((c.PeriodYear + c.PeriodNumber) <> a.PayPeriod)
and ((c.PeriodYear + c.PeriodNumber) > b.PayPeriod)
GROUP BY a.CEID
,a.PayPeriod
,c.PeriodYear
,c.PeriodNumber
,c.StartDate
,c.EndDate
,c.PpAllocPct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 12:58:53

no probs...you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources