Grouping based on the common Time Frame

Question

Grouping based on the common Time Frame

naveen0905

SSC Journeyman

Points: 83
More actions
July 16, 2011 at 9:22 am

#254570

Hi Guys,
I have sample data like mentioned below.
Student IDattendancefrom_dateto_date
1 20 1/1/20102/1/2010
1 35 2/1/20102/15/2010
1 75 2/9/20102/15/2010
2 45 1/1/20102/1/2010
2 55 2/1/20102/15/2010
2 55 1/1/20102/15/2010
I have to make sure that at any common time frame attendance percentage should not be more than 100%.
Common Time Frame Means: for student ID 1 attendance percentage is 35 for duration "02/01/2010 - 02/15/2010" and 75% for duration "02/09/2010 - 02/15/2010". if we observe the total percentage for "02/09/2011 - 02/15/2010" is 75 + 35 = 110%. it shouldn't be like that.
similarly for student ID 2 for duration "02/01/2010 - 02/15/2010" the total percentage will be 110%.
I am not supposed to use any looping logic here. That is constraint give to me. 🙁
can anyone give me some idea how to handle these kind of scenarios.
Thanks in Advance.
Thanks,
Naveen.K

Viewing 6 posts - 1 through 6 (of 6 total)

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

naveen0905 (7/16/2011)
Hi Guys,
I have sample data like mentioned below.
Student IDattendancefrom_dateto_date
1 20 1/1/20102/1/2010
1 35 2/1/20102/15/2010
1 75 2/9/20102/15/2010
2 45 1/1/20102/1/2010
2 55 2/1/20102/15/2010
2 55 1/1/20102/15/2010
I have to make sure that at any common time frame attendance percentage should not be more than 100%.
Common Time Frame Means: for student ID 1 attendance percentage is 35 for duration "02/01/2010 - 02/15/2010" and 75% for duration "02/09/2010 - 02/15/2010". if we observe the total percentage for "02/09/2011 - 02/15/2010" is 75 + 35 = 110%. it shouldn't be like that.
similarly for student ID 2 for duration "02/01/2010 - 02/15/2010" the total percentage will be 110%.
I am not supposed to use any looping logic here. That is constraint give to me. 🙁
can anyone give me some idea how to handle these kind of scenarios.
Thanks in Advance.
Thanks,
Naveen.K

Hi Naveen. This question looks a little like a homework problem...which is fine as long as there is an attempt on your part to do the work. I will be happy to assist you, I only ask that you provide a few things to help get started.

Please provide:

1. your CREATE TABLE statement

2. some INSERT INTO statements to generate the sample data in your post

3. the query or queries you have tried so far (most important!)

4. the expected result

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

naveen0905 SSC Journeyman Points: 83 More actions · Answer 2

Hi,

Thanks for quick reply. I went to a remote location where I have very limited access to internet. Sorry the for the delay. Please find my sample code and data below.

create table #temp

(

StudentID int,

percentage float,

from_date datetime,

to_date datetime,

error varchar(200)

)

Insert into #temp(StudentID, percentage, from_date, to_date)

select 1,20,'1/1/2010','2/1/2010'

UNION ALL

select 1,35,'2/1/2010','2/15/2010'

UNION ALL

select 1,75,'2/9/2010','2/15/2010'

UNION ALL

select 2,45,'1/1/2010','2/1/2010'

UNION ALL

select 2,55,'2/2/2010','2/15/2010'

UNION ALL

select 2,55,'1/1/2010','2/15/2010'

UPDATE t3

SET error = isnull(error,'') + case when m.percentage > 100 then ' Percentage is more than 100%.' else '' end

--select case when m.percentage > 100 then ' Percentage is more than 100%.' else '' end

FROM #temp t3

INNER JOIN (

select t.StudentID, SUM(t1.percentage) percentage, t.from_date, t.to_date from #temp t

INNER JOIN #temp t1 on t.StudentID = t1.StudentID and not (t.from_date > t1.to_date or t.to_date < t1.from_date)

Group by t.StudentID, t.from_date, t.to_date) m on m.StudentID = t3.StudentID and m.from_date = t3.from_date and m.to_date = t3.to_date

select * from #temp

drop table #temp

Result what i am getting right now is :

1202010-01-01 00:00:00.0002010-02-01 00:00:00.000

1352010-02-01 00:00:00.0002010-02-15 00:00:00.000 Percentage is more than 100%.

1752010-02-09 00:00:00.0002010-02-15 00:00:00.000 Percentage is more than 100%.

2452010-01-01 00:00:00.0002010-02-01 00:00:00.000

2552010-02-02 00:00:00.0002010-02-15 00:00:00.000 Percentage is more than 100%.

2552010-01-01 00:00:00.0002010-02-15 00:00:00.000 Percentage is more than 100%.

I would like to know is there any better way to do it and I would like to have the error message like mentioned below.

"Percentage is more than 100% (have to mention for which date range it is more than 100%)"

Thanks,

Naveen.K

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

Check out the article by Itzik Ben-Gan on how to calculate "Concurrent Session".

When doing so, it's very easy to include the sum of percentages.

N 56°04'39.16"
E 12°55'05.25"

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

It took a bit of searching to find them so I figured I would share the links:

http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-1

http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-2

Note: The links in the articles to the "Listings" and "Web Listings" are broken but the content is there on the later pages of the article.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

naveen0905 SSC Journeyman Points: 83 More actions · Answer 5

Thanks Guys for the article & Links. It helped me. 🙂