July 14, 2008 at 1:57 pm
I have following piece of code what I used to identify previous pay period. On a given day I would like this code to return previous period.
there are two pay periods in each month. first is 1-15 and second is 16th to the end of the month. I store these periods in a table. I have put the insert statement below for that. here is the code:
---------------------------------------------------
-- Identify Date Range of two periods
declare @sys_date_dt DateTime
declare @sys_date_v varchar(11)
select @sys_date_dt=getdate()
set @sys_date_v=SUBSTRING(CONVERT(VARCHAR,@sys_date_dt,106),1,2) + '-' +
SUBSTRING(CONVERT(VARCHAR,@sys_date_dt,106),4,3) + '-' +
SUBSTRING(CONVERT(VARCHAR,@sys_date_dt,106),8,4)
declare @startdate DateTime
declare @endate DateTime
SELECT
@startdate= MIN(START_DT), -- AS START_DT,
@endate =MAX(END_DT) --AS END_DT
FROM (SELECT
DENSE_RANK() OVER (ORDER BY END_DT DESC) AS rec
,ROW_NUMBER() OVER (PARTITION BY START_DT, END_DT ORDER BY START_DT, END_DT) AS rn
,START_DT, END_DT
FROM
startend
WHERE
END_DT <= @sys_date_dt) AS x
WHERE
rec <= 2 AND rn = 1
declare @secondperiodstartdate DateTime
declare @secondperiodendate DateTime
select @secondperiodstartdate=start_dt from startend
WHERE ts_pd_cd='SEMI' and end_dt =@endate
select @secondperiodendate=end_dt from startend
WHERE ts_pd_cd='SEMI' and end_dt =@endate
print @sys_date_dt
print @sys_date_v
print @startdate
print @endate
print @secondperiodstartdate
print @secondperiodendate
-------------------------------------------------------
output
Jul 14 2008 3:52PM
14-Jul-2008
Jun 1 2008 12:00AM
Jun 30 2008 12:00AM
Jun 16 2008 12:00AM
Jun 30 2008 12:00AM
------------------------------------------------------------
output above looks good. as of today July 14th it would return the range as Jun 16th to Jun 20 for two variable that have defind.
print @secondperiodstartdate
Jun 16 2008 12:00AM
print @secondperiodendate
Jun 30 2008 12:00AM
so far so good.
but once the date turns over to July 15th this same piece of code would return the range to be from Jul 1 - Jul 15. The problem is that current period hasnt finished. so the previous period is still Jun 16- Jun 30. below I add a day to @sys_date_dt variable in line 3. so the date as of today become July 15th.
---------------------------------------------------
-- Identify Date Range of two periods
declare @sys_date_dt DateTime
declare @sys_date_v varchar(11)
select @sys_date_dt=getdate()+1
set @sys_date_v=SUBSTRING(CONVERT(VARCHAR,@sys_date_dt,106),1,2) + '-' +
SUBSTRING(CONVERT(VARCHAR,@sys_date_dt,106),4,3) + '-' +
SUBSTRING(CONVERT(VARCHAR,@sys_date_dt,106),8,4)
declare @startdate DateTime
declare @endate DateTime
SELECT
@startdate= MIN(START_DT), -- AS START_DT,
@endate =MAX(END_DT) --AS END_DT
FROM (SELECT
DENSE_RANK() OVER (ORDER BY END_DT DESC) AS rec
,ROW_NUMBER() OVER (PARTITION BY START_DT, END_DT ORDER BY START_DT, END_DT) AS rn
,START_DT, END_DT
FROM
startend
WHERE
END_DT <= @sys_date_dt) AS x
WHERE
rec <= 2 AND rn = 1
declare @secondperiodstartdate DateTime
declare @secondperiodendate DateTime
select @secondperiodstartdate=start_dt from startend
WHERE ts_pd_cd='SEMI' and end_dt =@endate
select @secondperiodendate=end_dt from startend
WHERE ts_pd_cd='SEMI' and end_dt =@endate
print @sys_date_dt
print @sys_date_v
print @startdate
print @endate
print @secondperiodstartdate
print @secondperiodendate
-------------------------------------------------------
the output of print statement is
Jul 15 2008 3:34PM
15-Jul-2008
Jun 16 2008 12:00AM
Jul 15 2008 12:00AM
Jul 1 2008 12:00AM
Jul 15 2008 12:00AM
print @secondperiodstartdate
Jul 1 2008 12:00AM
print @secondperiodendate
Jul 15 2008 12:00AM
I would like this program to return me the previous periods on the 15 and last day of the month. here are the insert statement. I am not having luck with it. any help would be greatly appreciated. thanks
-------------------------------------------------------
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-05-15 00:00:00.000', START_DT='2008-05-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-05-31 00:00:00.000', START_DT='2008-05-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-06-15 00:00:00.000', START_DT='2008-06-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-06-30 00:00:00.000', START_DT='2008-06-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-07-15 00:00:00.000', START_DT='2008-07-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-07-31 00:00:00.000', START_DT='2008-07-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-08-15 00:00:00.000', START_DT='2008-08-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-08-31 00:00:00.000', START_DT='2008-08-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-09-15 00:00:00.000', START_DT='2008-09-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-09-30 00:00:00.000', START_DT='2008-09-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-10-15 00:00:00.000', START_DT='2008-10-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-10-31 00:00:00.000', START_DT='2008-10-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-11-15 00:00:00.000', START_DT='2008-11-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-11-30 00:00:00.000', START_DT='2008-11-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-12-15 00:00:00.000', START_DT='2008-12-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2008-12-31 00:00:00.000', START_DT='2008-12-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2009-01-15 00:00:00.000', START_DT='2009-01-01 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2009-01-31 00:00:00.000', START_DT='2009-01-16 00:00:00.000'
Insert Into #startend Select TS_PD_CD='SEMI', END_DT='2009-02-15 00:00:00.000', START_DT='2009-02-01 00:00:00.000'
July 14, 2008 at 2:29 pm
Try this, see if it does what you need:
select
case
when datepart(day, getdate()) <= 15 then
dateadd(day, -1 * datepart(day, getdate()), dateadd(month, -1, getdate())) + 1
else
dateadd(day, -1 * datepart(day, getdate()), dateadd(month, -1, getdate())) + 16
end as PriorPayStart,
case
when datepart(day, getdate()) <= 15 then
dateadd(day, -1 * datepart(day, getdate()), dateadd(month, -1, getdate())) + 15
else
dateadd(day, -1 * datepart(day, getdate()), getdate())
end as PriorPayEnd
You can, of course, sub in a datetime variable where this has getdate(). Search and replace should do that just fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 3:42 pm
this gives me the first period
2008-06-01 17:39:13.020 to 2008-06-15 17:39:13.020
how do you remove these timestamps?
thanks
July 15, 2008 at 6:59 am
You can use a bunch of different methods to get the time off the datetime.
dateadd(day, datediff(day, 0, getdate()), 0)
Is the one I generally use these days.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2008 at 7:23 am
shahab (7/14/2008)
how do you remove these timestamps?
Use convert(varchar, getdate(), 112)
Change the number (112) to display/use the date to your needs (see BOL on CONVERT)
July 15, 2008 at 7:40 am
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply