April 17, 2008 at 10:18 am
well.. lets see if I can explain this..
based on today's date I want to caputre start date and end date of two periods before today's date.
each month has two period. first period starts on 1st of the month and ends on 15th.
second period starts on 16th of the month and end on last day of the month.
lets say today's date = april 17 2008
then date range of previous two periods is from 16 March 2008 to 15 April 2008.
within this date range i have two periods. For first period date range is
16 March to 31st March
for second period date range is
1 April to 15 April.
if today's date is March 3rd 2008 then the date range of previous two periods is from Feb 1st to Feb 28th of 2008.
first period range: from Feb 1st to Feb 15th
second period range: from Feb 16 to Feb 28th.
how can I calculate the start and end date of previous two periods?
April 17, 2008 at 11:07 am
declare @Today datetime
--select @today = '4/14/08'
select @today = getdate()
declare @MonthBack datetime, @FirstPeriod datetime, @SecondPeriod datetime
select @MonthBack = convert(varchar(20), dateadd(month, -1, @Today), 1)
select
@FirstPeriod =
case
when datepart(day, @Today) > 15 then
dateadd(day, 16,
dateadd(day, -1 * datepart(day, @MonthBack), @MonthBack))
else dateadd(day, -1 * datepart(day, @MonthBack), @MonthBack) + 1
end,
@SecondPeriod =
case
when datepart(day, @Today) < 15 then
dateadd(day, 16,
dateadd(day, -1 * datepart(day, @MonthBack), @MonthBack))
else dateadd(day, -1 * datepart(day, @Today), @Today) + 1
end
select @FirstPeriod, @SecondPeriod
Try that, see if it gets you the begin-dates you want.
- 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
April 17, 2008 at 11:25 am
Ok, just to see if I could get this with a quick recursive CTE:
[font="Courier New"]; WITH DatePeriods (DateValue, StartOrEnd)
AS
(
SELECT DATEADD(DAY,-DATEPART(Day,GETDATE())+1,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))), 1
UNION ALL
SELECT DATEADD(DAY,-DATEPART(Day,GETDATE())+15,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))), 2
UNION ALL
SELECT DATEADD(Month,-1,DateValue), StartOrEnd FROM DatePeriods WHERE DateValue >= DATEADD(Month,-1,GETDATE())
)
SELECT
DateValue, StartOrEnd
FROM
DatePeriods
UNION
SELECT
CASE WHEN DATEPART(DAY,DateValue)=1 THEN DateValue-1
ELSE DateValue+1
END
, CASE WHEN DATEPART(DAY,DateValue)=1 THEN 2
ELSE 1
END
FROM
DatePeriods
ORDER BY
DateValue DESC[/font]
April 17, 2008 at 11:26 am
...and yes I know that was a rather inefficient recusive CTE.
April 17, 2008 at 12:17 pm
Of course, the other way to do this, which might be more efficient, is to create a table of Periods, including begin and end dates, and then do your queries using that.
- 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply