September 15, 2014 at 2:12 am
--From the rows I want to know how many number of days a person was active for the given date range.
create table [dbo].[personstatus]
(
id int identity(1,1),
name varchar(100),
DateAdded date,
InactivationDate date ) ;
insert into [dbo].[personstatus] values
('KRISS',CONVERT(VARCHAR(15), '2014-01-24', 120), CONVERT(VARCHAR(15), '2014-02-24', 120));
insert into [dbo].[personstatus] values
('KRISS',CONVERT(VARCHAR(15), '2014-05-24', 120), CONVERT(VARCHAR(15), '2014-06-21', 120))
insert into [dbo].[personstatus] values
('VDENTI',CONVERT(VARCHAR(15), '2014-01-01', 120),CONVERT(VARCHAR(15), '2014-01-31', 120))
insert into [dbo].[personstatus] values
('VDENTI',CONVERT(VARCHAR(15), '2014-02-01', 120), CONVERT(VARCHAR(15), '2014-02-24', 120))
insert into [dbo].[personstatus] values
('VDENTI',CONVERT(VARCHAR(15), '2014-03-01', 120) , CONVERT(VARCHAR(15), '2014-04-30', 120))
--select * from personstatus;
--The output I am looking for.
/*
1) FromDt = '2014-01-01' ToDt ='2014-01-30'
KRISS = 7
VDENTI = 7 days
2) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 1
VDENTI = 1 days
3) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 0
VDENTI = 1 days
4) FromDt = '2013-01-01' ToDt ='2014-12-31'
KRISS = 8+24+8+21 = 61 Days
VDENTI = 31+24+31+30 = 116 Days
*/
Thanks for the help i get from this forum.!!!
Vineet D
September 15, 2014 at 7:04 am
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;
September 15, 2014 at 8:45 am
Hi pietlinden,
I am greatful to see answer for case #4. But I am really finding ,how do I construct WHERE clause to pass the Fromdt and Todt Parameters
for case #1,#2,#3. The data in [DateAdded] and [Inactivationdate] is stored in range.
can you please extend above query with the parameters. ? Once again thanks for your help.
Vineet D
September 15, 2014 at 9:13 am
where DateAdded<=@ToDt and InactivationDate>@FromDt
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 15, 2014 at 9:34 am
--for case#1 and for the date range KRISS AND VDNTI is active for only 7 days but applying parameters to where clause result is giving 31 and 30
--e.g.
declare @FromDt date
,@ToDt date
set @FromDt = '2013-01-01'
set @ToDt ='2014-01-30'
SELECT name, SUM(DaysActive) AS TotalActiveDays
FROM
(
SELECT name, datediff(d,DateAdded, InactivationDate) AS DaysActive
FROM personstatus where ( DateAdded<=@ToDt and InactivationDate>@FromDt)) x
GROUP BY name;
Vineet D
September 15, 2014 at 9:41 am
Maybe I missed something... Could you explain how you determine what days they're active? The summary isn't hard - I guess I don't understand how you determine active though.
September 15, 2014 at 9:47 am
This might not be the best solution for performance because it will run on all rows. The tricky part is to get the complete list with values in zero for any period of time.
DECLARE @FromDt date = '20130101',
@ToDt date = '20140101';
WITH cteDays AS(
SELECT *, DATEDIFF(DD,
CASE WHEN @FromDt < DateAdded THEN DateAdded ELSE @FromDt END,
CASE WHEN @ToDt > InactivationDate THEN InactivationDate ELSE @ToDt END) + 1 numdays
FROM personstatus
)
SELECT name, SUM( CASE WHEN numdays > 0 THEN numdays ELSE 0 END) numdays
FROM cteDays
GROUP BY name;
September 15, 2014 at 9:53 am
You're not accounting for the overlapping days in the sum. Your datediff needs to be more restrictive:
declare @FromDt date
,@ToDt date
set @FromDt = '2013-01-01'
set @ToDt ='2014-01-30'
SELECT name, SUM(DaysActive) AS TotalActiveDays
FROM
(
SELECT name, datediff(d,case when @fromdt >dateadded then @fromdt else DateAdded end,
case when InactivationDate>@ToDt then @ToDt else InactivationDate end ) AS DaysActive
FROM personstatus where ( DateAdded<=@ToDt and InactivationDate>@FromDt)) x
GROUP BY name;
Also - you haven't described whether the dates are INCLUSIVE or EXCLUSIVE. In many cases the "from" is inclusive, but the "To" is often not.
In otherwords - were they active for the day they were inactivated or should we not count that day? If so - you need a "-1" after the datdeiff
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 15, 2014 at 9:55 am
And here you have a different version that will use only the rows needed. Ideally, you shouldn't have to create the PersonList set and you'll have a table with them that you can join to your personstatus table by something different than the name column.
I really hope that you have a different database design than the one showed in here.
DECLARE @FromDt date = '20130101',
@ToDt date = '20140101';
WITH cteDays AS(
SELECT *, DATEDIFF(DD,
CASE WHEN @FromDt < DateAdded THEN DateAdded ELSE @FromDt END,
CASE WHEN @ToDt > InactivationDate THEN InactivationDate ELSE @ToDt END) + 1 numdays
FROM personstatus
WHERE InactivationDate >= @FromDt
AND DateAdded <= @ToDt
),
PersonList AS(
SELECT DISTINCT name
FROM personstatus
)
SELECT p.name, SUM( CASE WHEN numdays > 0 THEN numdays ELSE 0 END) numdays
FROM PersonList p
LEFT
JOIN cteDays d ON p.name = d.name
GROUP BY p.name;
September 17, 2014 at 1:49 am
I glade and thanks to all members for giving solution and suggestion. This is working pretty nicely.
Vineet D
September 17, 2014 at 9:57 am
vineet_dubey1975 (9/17/2014)
I glade and thanks to all members for giving solution and suggestion. This is working pretty nicely.Vineet D
Thank you for the feedback.
Now, something really important. Do you understand how and why it works? Could you explain it to your rubber duck?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply