Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding Fields In SQL by Increments

Status
Not open for further replies.
wheels0323

wheels0323

Programmer
Oct 15, 2008
17
US
Code: 
select 
personnum,
timeinseconds,
wageamount,
applydate,
startdtm,
enddtm
from vp_totals
where personnum = '103786'
and applydate >= '2008-11-30 00:00:00.000'
and applydate <= '2008-12-06 00:00:00.000'
and paycodename = '01  REG'

Let's say I have the following code. The attachment file is a picture of the results after a run.

I want to add the 'timeinseconds' column by record in a seperate column. I don't want total. I want it to add. So after looking at the results the first record would show 2220. The 2nd record would show 10980 (because 22200 plus 8760. Then so on till the end which would be the grand total for that person.

How would I do that with SQL code to add just in increments by record?

Thanks for your time.



 
Sort by date Sort by votes
You need some way to distinguish the records, so i would set an id column in a temp table.

Code: 
select
personnum,
timeinseconds,
wageamount,
applydate,
startdtm,
enddtm,
IDCol = IDENTITY(INT,1,1)
into #temp
from vp_totals
where personnum = '103786'
and applydate >= '2008-11-30 00:00:00.000'
and applydate <= '2008-12-06 00:00:00.000'
and paycodename = '01  REG'

Then you can do something like this

Code: 
select
personnum,
timeinseconds,
wageamount,
applydate,
startdtm,
enddtm,
(select sum(timeinseconds) from #temp t2
where t2.idcol <= t1.idcol) 'runningtotal'
from #temp t1
where personnum = '103786'
and applydate >= '2008-11-30 00:00:00.000'
and applydate <= '2008-12-06 00:00:00.000'
and paycodename = '01  REG'

Dodge20
 
Upvote 0 Downvote
Code: 
DECLARE @ITbl TABLE
 ( laborlevelname3       VARCHAR(50),
   laborlevelname4       VARCHAR(50),
   personfullname        VARCHAR(64),
   personnum             VARCHAR(15),
   Age                   INT,
   applydate             DATETIME, 
   personid              INT,
   TotalSecondsDay       INT,
   ShiftTotal            VARCHAR(5),
   DayTotal              VARCHAR(5),
   TotalWeekHours        VARCHAR(5),
   ShiftTotalMinutes     VARCHAR(5),
   DayTotalMinutes       DECIMAL(8,2),
   TotalWeekHoursMinutes DECIMAL(8,2),
   minorrulenm           VARCHAR(50),
   minorruleid           INT,
   schoolcalendarid      INT,
   schoolcalendardt      DATETIME,
   NextSchoolCalendardt  DATETIME,
   CurrSCtypid           TINYINT,
   NextSCtypid           TINYINT,
   MinorDayTypeID        TINYINT,
   MinorWeekTypeID       TINYINT,
   nightstartminnum      INT,
   nightendminnum        INT,
   schclndrdaytypid1	 INT,
   schclndrdaytypid2	 INT,
   schclndrdaytypid3	 INT,
   schclndrdaytypid4	 INT,
   schclndrdaytypid5	 INT,
   schclndrdaytypid6	 INT,
   schclndrdaytypid7	 INT,
   StartDtm				 DATETIME,
   EndDtm			     DATETIME,
   InPunchDTM			 DATETIME,
   OutPunchDtm			 DATETIME,
   StartDtmHours		 DECIMAL(8,2),
   EndDtmHours			 DECIMAL(8,2)
 )

  INSERT INTO @ITbl
  select distinct
                                a.laborlevelname3,
                                a.laborlevelname4,
                                a.personfullname,
                                a.personnum,
                                (CONVERT(INT, CONVERT(VARCHAR(8), GetDate(), 112)) - CONVERT(INT, CONVERT(VARCHAR(8), b.BIRTHDTM, 112))) /10000 As Age,
                                a.applydate,
                                b.personid,
                                sum(a.timeinseconds) AS TotalSecondsDay,
                                Convert(VarChar(5), DateAdd(Minute, sum(a.timeinseconds)/60, 0), 108) As ShiftTotal,
                                Convert(VarChar(5), DateAdd(Minute, (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate = f.schoolcalendardt and a.paycodename ='00 TOTAL Worked Hours' group by a.personnum)/60,0),108) As DayTotal,
                                Convert(VarChar(5), DateAdd(Minute, (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate >= '2008-11-30' and applydate <='2008-12-06' and a.paycodename = '00 TOTAL Worked Hours' group by personnum)/60,0),108) As TotalWeekHours,
                                Convert(VarChar(5), sum(a.timeinseconds) / 60) As ShiftTotalMinutes,
                                Convert(VarChar(5), (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate = f.schoolcalendardt and a.paycodename = '00 TOTAL Worked Hours' group by a.personnum) / 60) As DayTotalMinutes,
                                Convert(Varchar(5), (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate >='2008-11-30' and applydate <='2008-12-06' and a.paycodename = '00 TOTAL Worked Hours' group by personnum) / 60) As TotalWeekHoursMinutes,
                                d.minorrulenm,
                                d.minorruleid,
                                f.schoolcalendarid,
                                f.schoolcalendardt,
                                DateAdd(day, 1, f.schoolcalendardt) as NextSchoolcalendardt,
                                f.schclndrdaytypid as fsch,
                                g.schclndrdaytypid as gsch,
                                CASE WHEN f.schclndrdaytypid = 0 AND g.schclndrdaytypid = 0 THEN 3
                                     WHEN f.schclndrdaytypid = 0 AND g.schclndrdaytypid = 1 THEN 2
                                     WHEN f.schclndrdaytypid = 1 AND g.schclndrdaytypid = 0 THEN 1
                                     WHEN f.schclndrdaytypid = 1 AND g.schclndrdaytypid = 1 THEN 0
                                     WHEN f.schclndrdaytypid = 2 AND g.schclndrdaytypid = 2 THEN 4
                                END AS MinorDayTypeID, 
                                CASE WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 2 THEN 0
                                     WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid > 10 THEN 1
                                     WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 7 THEN 2
                                     WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 3 THEN 3
                                     WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 4 THEN 4
                                     WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 5 THEN 5
                                     WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 6 THEN 6
                                END AS MinorWeekTypeID, 
                                d.nightstartminnum,
                                d.nightendminnum,
								h.schclndrdaytypid as hsch,
								i.schclndrdaytypid as isch,
								j.schclndrdaytypid as jsch,
								k.schclndrdaytypid as ksch,
								l.schclndrdaytypid as lsch,
								m.schclndrdaytypid as msch,
								n.schclndrdaytypid as nsch,
						        ISNULL(o.inpunchdtm, o.STARTDTM),
								ISNULL(o.outpunchdtm, o.ENDDTM),
								o.inpunchdtm,
								o.outpunchdtm,
                                --(datepart(hh, o.inpunchdtm) * 60) + datepart(n, o.inpunchdtm) as StartDtmHours,
                                --(datepart(hh, o.outpunchdtm) * 60) + datepart(n, o.outpunchdtm) as EndDtmHours
						        ISNULL((datepart(hh, o.inpunchdtm) * 60) + datepart(n, o.inpunchdtm), (datepart(hh, o.startdtm) * 60) + datepart(n, o.startdtm)) as StartDtmHours,
								ISNULL((datepart(hh, o.outpunchdtm) * 60) + datepart(n, o.outpunchdtm),(datepart(hh, o.enddtm) * 60) + datepart(n, o.enddtm)) as EndDtmHours							


								
from      vp_totals a,
          vp_person b,
          PRSNMINORRULEMM c,
          minorrule d,
          PRSNSCHCLNDRMM e,
          SCHOOLCLNDRDAY f,
          SCHOOLCLNDRDAY g,
          SCHOOLCLNDRDAY h,
          SCHOOLCLNDRDAY i,
          SCHOOLCLNDRDAY j,
          SCHOOLCLNDRDAY k,
          SCHOOLCLNDRDAY l,
          SCHOOLCLNDRDAY m,
          SCHOOLCLNDRDAY n,
          vp_timesheetpunch o

where			a.personnum = b.personnum
and             a.timesheetitemid = o.timesheetitemid
and             b.personid = c.personid
and             c.minorruleid = d.minorruleid
and             b.personid = e.personid
and             e.schoolcalendarid = f.schoolcalendarid
and             f.schoolcalendardt = a.applydate
and             e.schoolcalendarid = g.schoolcalendarid
and             g.schoolcalendardt = DateAdd(day, 1, f.schoolcalendardt)
and				h.schoolcalendardt = '2008-11-30'
and             e.schoolcalendarid = h.schoolcalendarid
and             i.schoolcalendardt = DateAdd(day, 1,'2008-11-30')
and             e.schoolcalendarid = i.schoolcalendarid
and             j.schoolcalendardt = DateAdd(day, 2, '2008-11-30')
and             e.schoolcalendarid = j.schoolcalendarid
and             k.schoolcalendardt = DateAdd(day, 3, '2008-11-30')
and             e.schoolcalendarid = k.schoolcalendarid
and             l.schoolcalendardt = DateAdd(day, 4, '2008-11-30')
and             e.schoolcalendarid = l.schoolcalendarid
and             m.schoolcalendardt = DateAdd(day, 5, '2008-11-30')
and             e.schoolcalendarid = m.schoolcalendarid
and             n.schoolcalendardt = DateAdd(day, 6,'2008-11-30')
and             e.schoolcalendarid = n.schoolcalendarid
and             a.paycodename not like '00 %'
and             a.paycodename not like '24 %'
and             a.paycodename not like '04 %'
and             a.paycodename not like '08 %'
and             a.paycodename not like '61 %'
and             a.paycodename not like '55 %'
and             a.paycodename not like '03 %'
and             a.paycodename not like '22 %'
and             a.paycodename not like '17 %'
and             a.paycodename not like '41 %'
and             a.paycodename not like '08 %'
and             a.paycodename not like '18 %'
and             a.paycodename not like '06 %'
and             a.paycodename not like '11 %'
and             a.paycodename not like '12 %'
and             a.paycodename not like '13 %'
and             a.paycodename not like '14 %'
and             a.paycodename not like '56 %'
and             a.paycodename not like '57 %'
and             a.paycodename not like '58 %'
and             a.paycodename not like '62 %'
and             a.paycodename not like '63 %'
and             a.paycodename not like '94 %'

and             a.applydate >= '2008-11-30'
and	    	    a.applydate <='2008-12-06'
and             (CONVERT(INT, CONVERT(VARCHAR(8), GetDate(), 112)) - CONVERT(INT, CONVERT(VARCHAR(8), b.BIRTHDTM, 112))) /10000 < 18
group by						 a.personfullname,
                                 b.PERSONNUM,
                                 a.laborlevelname3, 
                                 a.laborlevelname4, 
                                 a.personnum, 
                                 b.birthdtm,
                                 a.applydate, 
                                 d.minorrulenm,
                                 d.minorruleid,
                                 b.personid, 
                                 f.schoolcalendarid,
                                 f.schoolcalendardt, 
                                 f.schclndrdaytypid, 
                                 d.nightstartminnum, 
                                 d.nightendminnum,
								 g.SCHCLNDRDAYTYPID,
								 h.schclndrdaytypid,
							     i.schclndrdaytypid,
								 j.schclndrdaytypid,
								 k.schclndrdaytypid,
								 l.schclndrdaytypid,
								 m.schclndrdaytypid,
								 n.schclndrdaytypid,
								 o.startdtm,								 
								 o.enddtm,
								 o.inpunchdtm,
								 o.outpunchdtm

SELECT laborlevelname3, laborlevelname4, personfullname, personnum, Age, applydate,personid, TotalSecondsDay, ShiftTotal, DayTotal, TotalWeekHours,
ShiftTotalMinutes, DayTotalMinutes, TotalWeekHoursMinutes, minorrulenm, i.minorruleid, schoolcalendarid, schoolcalendardt, NextSchoolCalendardt, 
CurrSCtypid, NextSCtypid, MinorDayTypeID, MinorWeekTypeID, nightstartminnum, nightendminnum,   schclndrdaytypid1, schclndrdaytypid2, 
schclndrdaytypid3, schclndrdaytypid4, schclndrdaytypid5, schclndrdaytypid6,schclndrdaytypid7,startdtm, enddtm,  inpunchdtm, outpunchdtm, StartDtmHours,EndDtmHours, 
MRD.MINORRULEID, MRD.MINORRLDAYTYPEID, MRD.MINORAGENUM, MRD.MAXHOURSNUM, MRD.EARLIESTSTARTTM, MRD.LATESTENDTM, MRD.SAFETYMARGINNUM, MRW.MINORRULEID,
MRW.MINORRLWEEKTYPID, MRW.MAXHOURSNUM, MRW.MAXDAYSNUM, MRW.MAXCONSECDAYSNUM, MRW.MAXNIGHTSNUM, MRW.MAXCONSECNIGHTSNUM,


CASE WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM AND i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt AND i.StartDtmHours < MRD.EARLIESTSTARTTM AND i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)  + ' and ' + 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108) 
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.StartDtmHours < MRD.EARLIESTSTARTTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) 
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108) 
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.EndDtmHours > MRD.LATESTENDTM  THEN 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)  + ' and ' + 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108) 
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt and i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) + ' and ' + 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108) 
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' 
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) 
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)  
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) 
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.EndDtmHours > MRD.LATESTENDTM and i.applydate = i.schoolcalendardt THEN 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108) 
WHEN i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt and i.EndDtmHours > MRD.LATESTENDTM THEN 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)  + ' and ' + 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108) 
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' 
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day'
WHEN i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before '  + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) 
WHEN i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked passed '  + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108) 
ELSE 'No Infraction'
END AS Infraction 



FROM @ITbl i, MinorRuleDay MRD, MinorRuleWeek MRW

WHERE
  i.Age = MRD.MinorAgeNum AND
  i.MinorRuleId = MRD.MinorRuleID AND
  i.MinorDayTypeID = MRD.MinorRLDayTypeID AND
  i.Age = MRW.MinorAgeNum AND
  i.MinorRuleId = MRW.MinorRuleID AND
  i.MinorWeekTypeID = MRW.MinorRLWeekTypID 
   
order by i.personfullname, i.startdtm


Let's make this more complicated. How would I do that in this code of mine?

"""""""""sum(a.timeinseconds) AS TotalSecondsDay""""""""

is the field that we identified as timeinseconds above.
Do I do something like IDCol IDENTITY(INT,1,1) in the declare table statement?

Then the (select sum(timeinseconds) from #temp t2
where t2.idcol <= t1.idcol) 'runningtotal' in the insert statement?

Thanks if you can help. Just stuck on this part.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
469
pjw001
pjw001
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
332
SaltyTheFrog
SaltyTheFrog
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
455
Volvere
V
leifoet
  • Locked
  • Question
How te filter by date ?
Replies
9
Views
244
dhookom
dhookom
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
371
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top