you might have to add a day... the math is slightly off, but this works otherwise:

SELECT name, SUM(DaysActive) AS TotalActiveDays

FROM

(

SELECT name, datediff(d,DateAdded, InactivationDate) AS DaysActive

FROM personstatus) x

GROUP BY name;

One way is to use a numbers or Tally table. Here is a handy function:

http://www.sqlservercentral.com/scripts/tally/100338/

Then you can use that to calculate intervals and add to some date:

DECLARE @StartDate DATE...

Yes, you can... Must be getting myopic... can't see the obvious answer right in front of me. The hassle with sorting with parameters is that it appears that you...

or this... http://sqlblog.com/blogs/stacia_misner/archive/2011/03/16/34197.aspx

When ever a new event is fetched, i need send the data to the mail ids on the same.

You can't add a trigger to a SELECT statement. How is...

let your fingers do the walking...

http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/12/26/dynamic-sorting-in-ssrs/

Tried it - works a charm. The author even covers ASC/DESC sorts.

This is how I did it:

1. Create your four parameters. I did...

Some variation of this should work:

http://social.technet.microsoft.com/Forums/sqlserver/en-US/7fff2ba4-a9f5-426b-a022-91772e886728/parameterized-sorting-in-ssrs-2008?forum=sqlreportingservices

the fact that you're sorting by multiple columns might complicate things.

Not the fully exploded version (I used a couple of views to simplify the later queries a little)... but that's what I was thinking originally when I suggested that LAG()...

To add to Alexander's advice... (which is spot on, BTW)...

The major wrinkle is going to be the fact that the source tables don't all have the same number of columns....

Jack,

I think this is close. Only Activity_Date is not in the dataset, just Month(Activity_Date) and YEAR(Activity_Date). It's somewhat of a cheat, but since the data is being partitioned...

[SomeDateColumn]<=TODAY

what I did was to do a weird join. Say you have two CTEs, one for "Current Year" and one for "Previous Year". You can do a terrible...

FWIW, sometimes people just don't know what an answerable question looks like (complete with sample data etc). I was sorely tempted to post a link to Jeff's article when...

Jack,

Cool solution! After stepping away from what I was thinking before, I realized that I could use a CTE to do the thing... (and I saw you did that)....

I got a start on the solution... hopefully this will send you in the right direction. If you have 2012, you can use LAG to compare. The only...