Query time after specified date

Question

Query time after specified date

jham55

SSC Enthusiast

Points: 115
More actions
April 10, 2020 at 3:54 pm

#3742030

I need help with a query. If I have a list of people each with a service date and I want to sum their spend one year and two years after their service date what is the best way to approach it?
So I have a field that identifies the member, one that identifies the specific service and then a field that is the spend. Thoughts would be helpful.

thanks
Andy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

use DATEADD in your WHERE clause. you can use this to add 1 and 2 years to the date, and then limit the data returned in an aggregate.]

Something like:

select userid
, sum(somecol)
from table
where mydate > servicedate and < dateadd(year, 1, servicedate)
group by userid

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

jham55 wrote:

I need help with a query. If I have a list of people each with a service date and I want to sum their spend one year and two years after their service date what is the best way to approach it?
So I have a field that identifies the member, one that identifies the specific service and then a field that is the spend. Thoughts would be helpful.
thanks
Andy

Not enough info. Please see the first link in my signature line below. At least some column names would be helpful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jham55 SSC Enthusiast Points: 115 More actions · Answer 3

Thanks Steve,

So here is what I think has gotten me the closest. I'm getting almost all the results I want. This query gets me a userid, an anchor date ,a date 1 year later and sum of 1 day. Now I need to sum the dollars between the anchor date and the date 1 year later.

Select *

from

( select distinct (1)

userid

servicedate as anchordate

,dateadd(year,1,servicedate) as year1date

,sum(sales) as salestotal

from table

where sales>0

group by

userid

service date) as S