Date Range Choosing Minimum Value

[luvtitans50]

I am trying to create a date parameter (@startdate) that includes the minimum of the 2: 1-beginning of the year or 2- @enddate-13?

It needs to be something like:

I have an @enddate paramater being passed in and I need to always go back 13 weeks from that date. If I pass in @enddate = '2-26-1010' my range would be '11/29/09-2/26/1010? What I am trying to accomplish is to always get the minimum of either the beginning week of the year or the week that is 13 weeks back from @enddate.

I need some help on accomplishing that; please advise!!

Thanks,

 

[gmmastros]

Something like this?

Declare @StartDate DateTime
Declare @EndDate DateTime

Set @EndDate = '20100226'

Select Case When DateAdd(Week, -13, @EndDate) < DateAdd(Year, DateDiff(Year, 0, @EndDate), 0)
            Then DateAdd(Week, -13, @EndDate)
            Else DateAdd(Year, DateDiff(Year, 0, @EndDate), 0)
            End As StartDate

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jamfool]

if you want to go back 13wks:

WHERE dt BETWEEN DATEADD(wk,-13,@varDT) AND @varDT
[code]

if you do not want to go into the previous year, then:

[code]
AND YEAR(dt) >= YEAR(@varDT)
[code]


The other thing you will need to tweak will be when you consider to be the start of your week.

 

[luvtitans50]

Thanks a million!! Now I am getting the below error with this code:

Syntax error converting datetime from character string...

DECLARE @endDate datetime
SET @endDate = '2010-02-26'

Declare @StartDate Datetime
Select @StartDate = Case When DateAdd(Week, -13, @EndDate) < DateAdd(Year, DateDiff(Year, 0, @EndDate), 0)
Then DateAdd(Week, -13, @EndDate)
Else DateAdd(Year, DateDiff(Year, 0, @EndDate), 0)
End --As StartDate


Select
cr.rollupid,
s.periodnum as workperiodnum,
s.year as fiscalyear,
'TempsonStreet' = count (distinct o.candidateid),
'Clients' = count (distinct o.clientid),
'TotalHrs' = sum (isnull(o.totalhours,0)),
'RegHrs' = sum(isnull(o.reghours,0)),
'OtherHrs' = sum(isnull(o.otherhours,0)),
'AvgPayRate' = Case when sum (isnull(o.totalhours,0)) = 0 then 0 else sum(isnull(o.pay,0))/sum (isnull(o.totalhours,0)) end,
'AvgBillRate' = Case when sum (isnull(o.totalhours,0)) = 0 then 0 else sum(isnull(o.billing,0))/sum (isnull(o.totalhours,0)) end,
'FullTimeEqiv' = Case when sum (isnull(o.totalhours,0)) = 0 then 0 else sum(o.totalhours)/35 end,
'AvgTotHrs' = Case when count (distinct o.candidateid) = 0 then 0 else sum(o.totalhours)/count (distinct o.candidateid) end,
'AvgRegHrs' = Case when count (distinct o.candidateid) = 0 then 0 else sum(o.reghours)/count (distinct o.candidateid) end,
'AveOtherHrs' = Case when count (distinct o.candidateid) = 0 then 0 else sum(o.otherhours)/count (distinct o.candidateid) end,
'MarkupPct' = Case when sum(isnull(o.pay,0)) = 0 then 0 else sum((isnull(o.billing,0) - isnull(o.pay,0)))/sum(isnull(o.pay,0))*100 end,
'TimeCardNormal' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)<7) then 1 else 0 end),
'TimeCardExceptions' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)>7) then 1 else 0 end),
'TotalTimeCards' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)<7) then 1 else 0 end)+
SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)>7) then 1 else 0 end)
from customers m
LEFT OUTER JOIN CustIDtoCustMap ccr ON ccr.custrowid = m.rowid
LEFT OUTER JOIN Clientrollup cr on cr.rollupid = ccr.parentcustid
LEFT OUTER JOIN sysworkperiods s on workscheduleid = 1 and periodnum between @startdate and @enddate
LEFT OUTER JOIN omsdetail o on o.clientid = m.vurvid and o.workperiodnum = s.periodnum and o.fiscalyear = s.year
LEFT OUTER JOIN clientplanned cp on cp.rollupid = cr.rollupid and cp.workperiodnum = s.periodnum and cp.fiscalyear = s.year
/*
from omsdetail o
INNER JOIN customers m ON m.vurvid = o.clientid
LEFT OUTER JOIN CustIDtoCustMap ccr ON ccr.custrowid = m.rowid
LEFT OUTER JOIN Clientrollup cr on cr.rollupid = ccr.parentcustid
*/
where cr.rollupid = 43
Group by cr.rollupid, s.periodnum, s.year


What am I doing wrong?

 

[luvtitans50]

Disregard I figured it out!! Thanks for all your help!!!