April 1, 2008 at 2:31 pm
I'm trying to determine when the next date a person would be eligible for taking leave. They person can only utilize 480 hours for the past calendar year. So for today (4/1/08) I go back to 4/2/07 and do a sum of hours. If it's greater than or equal to 480, I want to the do a sum beginning on 4/3/07 and if the total is less than 480, the return date should be = 4/2/08. I would continue incrementing the date until I reach today's date (or my total hours < 480) Does this make sense? I'm sure I'm over complicating this but not sure of any other way to attempt to do this (dates always mess with my head!).
Data is attached in a .csv file. Thanks for any help on this matter.
Table Schema:
CREATE TABLE [dbo].[Hours Summary] (
[HS Company ID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[HS Employee Number] [float] NOT NULL ,
[HS Claim No] [int] NOT NULL ,
[HS WeekEnd Dte] [datetime] NOT NULL ,
[HS EmplSSN] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS Freq] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS Empl Lv Hrs] [float] NULL ,
[HS Fam Lv Hrs] [float] NULL ,
[HS CC Lv Hrs] [float] NULL ,
[HS Mtr Lv Hrs] [float] NULL ,
[HS Program] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS History PushDte] [datetime] NULL ,
[HS AbsCde] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS UpldCde] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS Type] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS TotHrs] [float] NULL ,
[HS Exc] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS ExcNotes] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS AttOCNo] [int] NULL ,
[HS AttUpldCde] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS AttType] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS FedPersHrs] [float] NULL ,
[HS FedPersRsnCde] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS PaidFlg] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS Opr ID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS Update Dte] [datetime] NULL ,
[HS TdyPts] [float] NULL ,
[HS AbsPts] [float] NULL ,
[HS TotPts] [float] NULL ,
[HS FCntr] [int] IDENTITY (1, 1) NOT NULL ,
[HS FText1] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS FText2] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS FText3] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS FText4] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS FText5] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS FText6] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HS FDate1] [datetime] NULL ,
[HS FDate2] [datetime] NULL ,
[HS FDate3] [datetime] NULL ,
[HS FNum1] [smallint] NULL ,
[HS FNum2] [smallint] NULL ,
[HS FNum3] [float] NULL ,
[HS FNum4] [float] NULL ,
[HS FNum5] [float] NULL ,
[HS FNum6] [float] NULL ,
[HS FCur1] [money] NULL ,
[Old_HS_EE_Number] [float] NULL ,
[Old_HS_SSN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL
) ON [PRIMARY]
GO
Query so far:
Declare @datecheck as datetime,
@datenext as datetime,
@x as int,
@tothrs as float
set @datecheck = dateadd(yyyy,-1,dateadd(dd,1,getdate())) -- one year ago tomorrow
while @x < 365
SELECT @tothrs = sum([hs tothrs]) FROM [hours summary]
WHERE [hs employee number]=3049016.0 and [hs company id]='TestCompany'
and [hs weekend dte] >= @datecheck
Group by [hs employee number], [hs company id]
if @tothrs < 480
Begin
set @datenext = dateadd(yyyy,1,@datecheck)
print @datenext
Return
End
set @x = @x + 1
set @datecheck = dateadd(dd,1,@datecheck)
-- You can't be late until you show up.
April 1, 2008 at 3:34 pm
tosscrosby (4/1/2008)
I'm trying to determine when the next date a person would be eligible for taking leave. They person can only utilize 480 hours for the past calendar year. So for today (4/1/08) I go back to 4/2/07 and do a sum of hours. If it's greater than or equal to 480, I want to the do a sum beginning on 4/3/07 and if the total is less than 480, the return date should be = 4/2/08. I would continue incrementing the date until I reach today's date (or my total hours < 480) Does this make sense?
I don't think that that is the approach that you want to use.
Think of it like this: If they are at 480 hours than that means that they took those hours in the past year. They took those hours as discrete events, and so they will also get those hours back in discrete events that are exactly 1 year later.
So what you really want to do is find the first time that they took their hours in the past year (i.e., MIN(leave.date) WHERE leave.date>Today-1.year), add 1 year to that and that's the next time that they have hours available.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2008 at 11:29 am
Ok, I have the query to return the MIN date. How (if) do I return the new date as desired within this select? Of course it's telling me in not in an aggregate function of group by. I tried several variations, including having MIN(([hs weekend dte]), no luck.
SELECT MIN([hs weekend dte]) as NewDate
--, dateadd(yyyy,+1,[hs weekend dte]) <- This is what I want
FROM [hours summary]
WHERE [hs employee number]=3049016.0 and [hs company id]='Teletech'
and [hs weekend dte] >= dateadd(yyyy,-1,dateadd(dd,1,getdate()))
Group by [hs employee number], [hs company id]
-- You can't be late until you show up.
April 2, 2008 at 11:53 am
I got it. Not sure if this is the best (or even proper) method, but it works. Thanks for the MIN suggestion. Sometimes I tend to over complicate things and get so turned around I don't think straight.
SELECT dateadd(yyyy,+1,[hs weekend dte]) as NewDateChk
FROM [hours summary] h2
WHERE h2.[hs employee number]=EmplNo and h2.[hs company id]='TestCompany'
and h2.[hs weekend dte] = (SELECT MIN([hs weekend dte])
FROM [hours summary] h1
WHERE h1.[hs employee number]=EmplNo and h1.[hs company id]='TestCompany'
and h1.[hs weekend dte] >= dateadd(yyyy,-1,dateadd(dd,1,getdate()))
Group by h1.[hs employee number], h1.[hs company id])
-- You can't be late until you show up.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply