• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

tSQL multiple date ranges in one query


I need to create table with 3 different time ranges

(ONE) Targets table had TargetID as PK
to
(MANY) Visits table has TargetID

I am getting counts of targets and Business in the visit table..
How could I create it so there is a column for 30 days 60 days and 90 Days


The code below works for 30 days but i want it to have 2 more columns for 60 days and 90 days

any suggestions would be greatly appreciated

Select t.BusinessUnitID, Count(1) as Days30
from dbo.Targets t (nolock)
inner join dbo.VIEW_Visits vv (nolock) 
on vv.CreationTime > getdate()-30
and t.TargetID = vv.TargetID  
and t.businessUnitID in (9,18,1)
Group by  t.BusinessUnitID

Open in new window

0
Leo Torres
Asked:
Leo Torres
2 Solutions
 
LIONKINGCommented:
You could try something along the lines of:

Select t.BusinessUnitID,
c30=SUM(CASE WHEN DATEDIFF(d,vv.CreationTime, getdate()) BETWEEN 0 AND 30 THEN 1 ELSE 0 END),
c60=SUM(CASE WHEN DATEDIFF(d,vv.CreationTime, getdate()) BETWEEN 30 AND 60 THEN 1 ELSE 0 END),
c90=SUM(CASE WHEN DATEDIFF(d,vv.CreationTime, getdate()) BETWEEN 60 AND 90 THEN 1 ELSE 0 END)
from dbo.Targets t (nolock)
inner join dbo.VIEW_Visits vv (nolock)
on t.TargetID = vv.TargetID  
and t.businessUnitID in (9,18,1)
Group by  t.BusinessUnitID

Let us know how it goes...
0
 
HainKurtSr. System AnalystCommented:
also try this:

Select t.BusinessUnitID,
       c30=SUM(CASE when vv.CreationTime-getdate() between 0 and 30 THEN 1 ELSE 0 END),
       c60=SUM(CASE when vv.CreationTime-getdate() between 31 and 60 THEN 1 ELSE 0 END),
       c90=SUM(CASE when vv.CreationTime-getdate() between 61 and 90 THEN 1 ELSE 0 END),
  from dbo.Targets t (nolock)
       inner join dbo.VIEW_Visits vv (nolock) on t.TargetID = vv.TargetID
 where  t.businessUnitID in (9,18,1) and vv.CreationTime > getdate()-90
 Group by  t.BusinessUnitID
0
 
Leo TorresSQL DeveloperAuthor Commented:
haincurt is better it had a time constriant
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now