Developing query to identify individual days and count them between dates

Question

Developing query to identify individual days and count them between dates

bruce.selzler

Valued Member

Points: 61
More actions
March 2, 2009 at 5:29 pm

#200839

Hello Team,
This is my first post here and I'm an SQL newbie so I hope this is appropriate.
I am using the following query to pull student data from a student information system into Filemaker Pro;
SELECT sch.name,tch.last_name,tch.first_name, students.student_number, students.last_name,students.first_name, cc.termid, cc.course_number, cc.section_number, cc.dateenrolled, cc.dateleft, cc.currentabsences
FROM ps.students students
JOIN ps.cc cc
ON students.id=cc.studentid
JOIN ps.TEACHERS tch
ON tch.ID=cc.teacherid
JOIN ps.Schools sch
ON sch.school_number=students.schoolid
WHERE termid >= '1800'
AND course_number='EDT'
OR course_number='PH'
This works fine, but I'm trying to figure out how this query could include a count of the number of M,T,W,TH,&F that occurred between "cc.dateenrolled" and "cc.dateleft".
Additionally the default "cc.dateleft" is the last day of school. So the query would have to evaluate whether the "cc.dateleft" is before the last day of school, or current date before counting the days between the dates.
Any ideas?
Let me know what you think.
- Sez
=====================
No matter where you go
There you are....

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

there is a real good artical called "calculating work days"[/url] by Jeff Moden here on SQL Server Central that explains the concept and also has a function at the end of the article; I think it will do exactly what you are after.

i couldn't explain it here better than the article does, so your best off reading that.

HTH!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 2

Hi Sez,

The solution willl be different if you are using Oracle. The solution "calculating work days"[/url] is meant for SQL Server.

Sincerely,

Maz

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

bruce.selzler Valued Member Points: 61 More actions · Answer 3

Maz,

Thanks for that, I'm glad I read this before getting too carried away. As I am using Oracle I'm guessing this throws a wrinkle into the problem.

Also, I was under the impressions that the "calculating work days" code counted and returned the total number of work days. I need the total number of Mondays, the total number of Tuesdays, The total number of Wednesdays, etc. And they need to be based on variable entry an leave dates.

Any ideas for someplace to look for help would be appreciated.

Thanks.

- Sez

=====================
No matter where you go
There you are....

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 4

Sez,

You need to develop a function which will take three parametrs to give out the number of days between two days and use it in your query. For example,

SELECT FnCountDaysBetweenTwoDates(cc.dateenrolled, cc.dateleft, 'MON'),FnCountDaysBetweenTwoDates(cc.dateenrolled, cc.dateleft, 'TUE'), FnCountDaysBetweenTwoDates(cc.dateenrolled, cc.dateleft, 'WED'), FnCountDaysBetweenTwoDates(cc.dateenrolled, cc.dateleft, 'THU'), FnCountDaysBetweenTwoDates(cc.dateenrolled, cc.dateleft, 'FRI'), FnCountDaysBetweenTwoDates(cc.dateenrolled, cc.dateleft, 'SAT'), FnCountDaysBetweenTwoDates(cc.dateenrolled, cc.dateleft, 'SUN'), ......

Though I am an amphibious with my roots in Oracle, I am swimming more in the TSQL these days and don't have a PL/SQL code readyliy available for this function. You can take hint from the arcticle "Calculating Business Days on Oracle" for this.

Sincerely,

Maz

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

bruce.selzler Valued Member Points: 61 More actions · Answer 5

Maz,

Thanks again. I'm going to try and wade through this. However at first glance this may be over my head at this point. I appreciate your help though.

Thanks.

- Sez

=====================
No matter where you go
There you are....

gints.plivna Ten Centuries Points: 1256 More actions · Answer 6

Suppose I have interval from 100 days back till today (sysdate).

The query then is as follows:

SQL> select to_char(sysdate - rownum, 'D'), count(*)

2 from dual

3 connect by level < (sysdate - (sysdate - 100))

4 group by to_char(sysdate - rownum, 'D');

T COUNT(*)

- ----------

1 14

3 15

6 14

7 14

5 14

2 14

4 14

Or with latvian names:

SQL> select to_char(sysdate - rownum, 'DAY') DAY, count(*)

2 from dual

3 connect by level < (sysdate - (sysdate - 100))

4 group by to_char(sysdate - rownum, 'DAY');

DAY COUNT(*)

-------------------------------------------- ----------

OTRDIENA 14

SESTDIENA 14

TREŠDIENA 15

PIEKTDIENA 14

PIRMDIENA 14

SVETDIENA 14

CETURTDIENA 14

This also perfectly shows that group by doesn't sort rows (just in case someone was sunk in such old myth).

Gints Plivna
http://www.gplivna.eu