July 7, 2009 at 6:05 pm
I can't figure how to get a correct result. I need to sum values in a 12 months period depending a parameter input date.
All date values in the field "period" always contain the last day of a month
Example for 6/30/2009:
Sum1 = values from 7/31/2008 - 6/30/2009
Sum2 = values from 6/30/2008 - 5/31/2009
Sum3 = values from 5/31/2008 - 4/30/2009
If I use following statement for months (Feb, April, June ....)
with less than 31 days I get only 11 months back
select period from dbo.wrvu
where period between dateadd(mm, -12, '6/30/2009' )and dateadd(mm, -1, '6/30/2009')
Months which have 31 days are working as expected
select period from dbo.wrvu
where period between dateadd(mm, -12, '5/31/2009' )and dateadd(mm, -1, '5/31/2009')
select period from dbo.wrvu
where period between dateadd(mm, -13, '5/31/2009' )and dateadd(mm, -2, '5/31/2009')
Thanks for all input .... ;)))
July 8, 2009 at 7:31 am
I'm not sure if this is the best route, but if you know that your field will always have the last day of the month, you can say something like:
WHERE MONTH(period) BETWEEN MONTH(DATEADD(mm, -12, '6/30/2009')) AND MONTH('6/30/2009')
Eli
July 8, 2009 at 9:04 am
Thanks for your response - unfortunately it doesn't work for me - it returns all dates with 6/30 no matter what year.
I'll keep looking ...
July 8, 2009 at 9:08 am
gabrielegriffin (7/8/2009)
Thanks for your response - unfortunately it doesn't work for me - it returns all dates with 6/30 no matter what year.I'll keep looking ...
Could you please post your actual code your are trying to write? It would help me figure out what needs to be done. I have an idea, but your original post is a bit too vague to work with.
July 8, 2009 at 9:16 am
Here my code ... hope it makes sense ... THANKS
_______________
@BaseDate & @period are parameters set in the beginning of the proc ...
/***** Calculate 12 months rolling wrvu's 12 months back from current period *******/
DECLARE @rolling_12_wrvu TABLE
(
empl_id varchar(5),
p_base numeric(18,6),
p12 numeric(18,6),
p11 numeric(18,6),
p10 numeric(18,6),
p09 numeric(18,6),
p08 numeric(18,6),
p07 numeric(18,6),
p06 numeric(18,6),
p05 numeric(18,6),
p04 numeric(18,6),
p03 numeric(18,6),
p02 numeric(18,6),
p01 numeric(18,6)
)
INSERT INTO @rolling_12_wrvu
select
empl_id,
sum(
case
when
period between dateadd(mm, -11, @BaseDate) and @BaseDate
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -11, @period )and @period
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -12, @period )and dateadd(mm, -1, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -13, @period )and dateadd(mm, -2, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -14, @period )and dateadd(mm, -3, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -15, @period )and dateadd(mm, -4, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -16, @period )and dateadd(mm, -5, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -17, @period )and dateadd(mm, -6, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -18, @period )and dateadd(mm, -7, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -19, @period )and dateadd(mm, -8, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -20, @period )and dateadd(mm, -9, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -21, @period )and dateadd(mm, -10, @period)
then wrvu else 0 end),
sum(
case
when
period between dateadd(mm, -22, @period )and dateadd(mm, -11, @period)
then wrvu else 0 end)
from
dbo.wrvu
group by empl_id
order by empl_id
SELECT * from @rolling_12_wrvu
July 8, 2009 at 9:20 am
Another request. Could you comment the code a bit to explain what is going on? Also, table DDL, and sample data (readily consummable, and just enough to test, couple of rows for each month).
July 8, 2009 at 9:36 am
Already, let me know if this makes better senses now:
A user selects a date, example 6/30/2009 - from this date I need to calucate 12 sums, as follows:
Sum1 = values from 7/31/2008 - 6/30/2009
Sum2 = values from 6/30/2008 - 5/31/2009
Sum3 = values from 5/31/2008 - 4/30/2009
Sum4 = values from 4/30/2009 - 3/31/2009
Sum5 = values from 3/31/2009 - 2/28/2009
..Sum12 = 7/31/2007 - 6/30/2008
___________________________
The table I pull from ...I need to sum the last column by period and ID
ID (char5) PERIO /Datetime Count numeric SUM THIS VALUE numeric
429142008-05-31 00:00:00.0001913.7400
429142008-05-31 00:00:00.0005042.5900
429142008-06-30 00:00:00.0003431.2000
429142008-06-30 00:00:00.000273602.0700
429142008-07-31 00:00:00.0001716.0500
429142008-07-31 00:00:00.000182323.1700
429142008-08-31 00:00:00.0002942.2900
429142008-08-31 00:00:00.00077112.5700
429142008-09-30 00:00:00.0001412.9600
429142008-09-30 00:00:00.000153241.9400
429142008-10-31 00:00:00.0002013.6100
429142008-10-31 00:00:00.000119198.4100
429142008-11-30 00:00:00.0001721.5300
429142008-11-30 00:00:00.000129262.5700
429142008-12-31 00:00:00.0000.0000
429142008-12-31 00:00:00.0001234.3500
429142008-12-31 00:00:00.000162390.1300
429142009-01-31 00:00:00.0000.0000
429142009-01-31 00:00:00.0002025.6300
429142009-01-31 00:00:00.00091172.4700
429142009-02-28 00:00:00.0000.0000
429142009-02-28 00:00:00.0004246.9700
429142009-02-28 00:00:00.000158470.7400
429142009-03-31 00:00:00.000-1-1.3400
429142009-03-31 00:00:00.0002136.2000
429142009-03-31 00:00:00.000145293.9800
429142009-04-30 00:00:00.000-1-3.4700
429142009-04-30 00:00:00.0004141.7800
429142009-04-30 00:00:00.000174387.1300
429142009-05-31 00:00:00.0002132.6900
429142009-05-31 00:00:00.00015.0600
429142009-05-31 00:00:00.00083211.3000
429142009-06-30 00:00:00.000-1-.9200
429142009-06-30 00:00:00.0002426.9800
429142009-06-30 00:00:00.000144457.8900
July 8, 2009 at 10:13 am
So, to paraphrase for one value, given the date 6/30/2009, you want the past 11 months 7/31/2008 - 6/30/2009 starting with the last day of the 12th month?
I'd think given 6/30/2009 the first summation would be 7/1/2008 - 6/30/2009, the next 6/1/2008 - 5/31/2009, etc for a total of 12 summations.
Please provide clarification. This is what I have gleened so far from your posts, and I want to be sure I do things to meet your requirements.
July 8, 2009 at 10:23 am
Lynn,
yes - exactly!
The period field in the table always contain the last day of a months - it's never like 6/24/2009 always 6/30/2009,
never 5/4/2008 always 5/31/2008 etc.
Thanks so much for taking the time helping me.
July 8, 2009 at 1:36 pm
Okay. I did some testing while SSC was down. The first part of the code shows how to calcualte the various date ranges. The last part show a hypothetical (and untested) method to accomplish your task. Use it as a guide.
declare @Period datetime,
@StartPeriod datetime;
set @Period = '2009-06-30';
set @StartPeriod = dateadd(mm, -22,dateadd(mm, datediff(mm, 0, @Period), -1))
select @Period, @StartPeriod
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -11, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, 0, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -12, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -1, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -13, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -2, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -14, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -3, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -15, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -4, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -16, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -5, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -17, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -6, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -18, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -7, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -19, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -8, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -20, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -9, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -21, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -10, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -22, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -11, @Period)) + 1, -1)
union all
select
dateadd(mm, datediff(mm, 0, dateadd(mm, -23, @Period)) + 1, -1),
dateadd(mm, datediff(mm, 0, dateadd(mm, -12, @Period)) + 1, -1)
select
a.someid,
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -11, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, 0, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -12, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -1, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -13, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -2, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -14, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -3, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -15, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -4, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -16, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -5, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -17, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -6, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -18, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -7, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -19, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -8, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -20, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -9, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -21, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -10, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -22, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -11, @Period)) + 1, -1) then a.somevalue else 0),
sum(case when a.somedate between dateadd(mm, datediff(mm, 0, dateadd(mm, -23, @Period)) + 1, -1)
and dateadd(mm, datediff(mm, 0, dateadd(mm, -12, @Period)) + 1, -1) then a.somevalue else 0)
from
dbo.sometable a
where
a.somedate between @StartPeriod and @Period
group by
a.someid;
July 8, 2009 at 2:38 pm
OMG - thanks. I will give it a try and let you know.
again, thanks so much!!
July 8, 2009 at 2:38 pm
OMG - thanks. I will give it a try and let you know.
again, thanks so much!!
July 8, 2009 at 2:39 pm
OMG - wow, thanks. I will give it a try and let you know.
Again, thanks
July 8, 2009 at 2:40 pm
OMG - wow, thanks. I will give it a try and let you know.
Again, thanks
July 8, 2009 at 2:47 pm
OMG - thanks! I will give it a try and let you know.
Thanks so much for your time!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply