December 27, 2011 at 9:13 am
Hi, id like to know if anyone find some solution that doesnt involves use Cursors or Loops..
My scenario:
- A academy got some courses that run all year along tblCourses ==> there are a table with all classes (id - courseid- day - hour - profesorid), let's call tblClasses.
- An studen can pay for getting classes ==> table tblAlumnsCourses that make relation between courses and Alumns and there's a table that get id from tblClasses and make relation with alumnid, lets call tblClassesReceived, so when alumn get into the course all the rows in tblClassesReceived are generated, so alumn can show his calendar, etc....
- Everyday professor confirm in our system the asistance of every alumn to classes
- So, everything is right but there's a case where an alumn just pay for some hours (not for all course), in this scenario, alumn can go whenever he wants, then professor check in our system the asistances, if alumn has assisted then hours the left are = total hours boughts - sum(hours assisted) all this info about hours bought and assisted is stored and updated in table tblAlumnsCourses.
In this scenario all the rows in tblClassesReceived are generated too, because alumn can assist to class whenever he wants.
So, now, we'd like to get a report that just get classes that left from tblClassesReceived, for an alumn that has bought hours >> .
For example:
A course of Yoga, classes are Friday from 7 to 9, from October to May.
An alumn has bought 12 hours = 6 classes 1st January,
so we'd like to get just classes he can assist from January = 4 classes in January and 2 in February
is It possible without Cursor?
Thanks in advance
December 27, 2011 at 9:17 am
Yes. Just translate your Courses Purchased into hours in all cases, whether they buy hours directly or courses.
This sounds like homework or a test. Is it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 27, 2011 at 9:31 am
Hi, thanks for your answer.
No, it's not a test nor homework, it's just a doubt about how could i get some data.
I think that my poor english doesnt make me explain in a right way.
Forget about classes, courses....
Just think about a table with 2 columns : datetime DateCol - int NumberCol.
Then the table got hundreds of rows and column NumberCol got always value = 2.
How could i get first X rows when sum (NumberCol) <= Y
For example, Y = 10 then i know i got to get 5 rows, and id like to get first 5 rows (ordered by DateCol ASC)
Thanks in advance.
December 27, 2011 at 9:41 am
mrecuerop (12/27/2011)
Hi, thanks for your answer.No, it's not a test nor homework, it's just a doubt about how could i get some data.
I think that my poor english doesnt make me explain in a right way.
Forget about classes, courses....
Just think about a table with 2 columns : datetime DateCol - int NumberCol.
Then the table got hundreds of rows and column NumberCol got always value = 2.
How could i get first X rows when sum (NumberCol) <= Y
For example, Y = 10 then i know i got to get 5 rows, and id like to get first 5 rows (ordered by DateCol ASC)
Thanks in advance.
Like using "select top 5 blah-blah-blah order by DateCol"?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 27, 2011 at 9:42 am
You could use the Top () operator to do that.
declare @Rows int;
set @Rows = 5;
select top (@Rows) MyColumn
from dbo.MyTable
order by MyOtherColumn;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 27, 2011 at 10:19 am
Thanks to you for yours responses.
Well its an starting, select TOP (N) but the key data is N.
N never will be the same value, so statement that im looking for is for getting N.
And N got to be the number of rows that are necesaries for
sum(NumberCol) <= Y (Y could be a variable or parameter f.i.)
So for example, as i said before all NumberCol got a value = 2 then with Y = 10 then i need 5 rows
with Y = 20 then i need 10 rows
December 28, 2011 at 6:32 am
Is "Y" something that can be calculated from an input value, or does it vary based on values in the column, or something else? I'm not getting a clear picture of how you're calculating Y.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 8:57 am
Hi, thanks for reply.
Well finally i have found the answer in another forum.
Problem is that i have not explained clearly here.
Im gonna post my requeriment and the 2 solutions i got, maybe it can be helpfull for another people.
My Requeriments
id like to know how could i get some rows that some sum of some columns is equal or less than a number and ordered by datetime ASC.
I know i could do it with a cursor but id prefer to do it with a select statement if its possible
Let's see.
I got this table:
ID STAR DATETIME DURATION (HOURS)
627 2011-08-03 17:00:00.000 2
628 2011-08-05 17:00:00.000 2
629 2011-08-08 17:00:00.000 2
630 2011-08-10 17:00:00.000 2
631 2011-08-12 17:00:00.000 2
1º) Well, imagine we are at 2011-08-01, and id like to get all the rows from table 'till sum(duration) <= 4.
Result must get rows with ID 627 and 628
2º) If we were at 2011-08-07 and id like to get all the rows from table 'till sum(duration) <= 4. then rows would be with ID 629, 630
3º) If we were at 2011-08-11 and id like to get all the rows from table 'till sum(duration) <= 4. then rows would be with ID 631
4º) If we were at 2011-08-27 and id like to get all the rows from table 'till sum(duration) <= 4. then no rows would be retrieved
Key factor is that value that is compared with sum(duration) (<= 4) is dinamyc, is not always the same.
Solution 1
Probably easiest approach (from the perspective of programming efforts) would be to use a recursive CTE, like this:
DECLARE @x INT; SET @x = 4;
DECLARE @startDate DATETIME; SET @startDate = '20110827';
;WITH cte AS
(
SELECT TOP 1 id, [DURATION (HOURS)] AS cumulativeHours
FROM YourTable
WHERE [star datetime] >= @startDate
ORDER BY [star datetime]
UNION all
SELECT y.id, c.cumulativeHours+y.[DURATION (HOURS)]
FROM YourTable y INNER JOIN cte c ON c.id+1 = y.ID
WHERE c.cumulativeHours+y.[DURATION (HOURS)] <= @x
)
SELECT id FROM cte;
The performance may not be great - would be a problem if you have lot of data in the table and each calculation requires the summation of a large number of rows.
Solution 2 (i have added some modif)
DECLARE @startdate datetime,@target int
SELECT @startdate = '2011-08-07', --put your date value here
@target=4
SELECT t.ID
FROM table t
CROSS APPLY (SELECT SUM(Duration) AS RunTime
FROM table
WHERE DATETIME <= t.DATETIME and
DATETIME >= @startdate
)t1
WHERE t.DATETIME >= @startdate
AND RunTime <=@target
For my goal, second SQL is better.
Thanks for your help.
December 28, 2011 at 9:10 am
Both of those solutions will work on very small datasets. If your database will ever have a larger number of rows, either of those will choke performance-wise.
Only use them if you know your data will never be used for anything more than a few hundred to maybe a thousand rows, tops. Honestly, that means don't use them in a production database, in every case I know of.
Those are both what are called "triangular joins".
There are better ways to do running totals, which is what it looks like you're doing here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply