May 15, 2011 at 8:15 pm
Hi :
The following query is supposed to return the details of students who have been absent from school for three days or more. I tested it by entering absent details for one of the students, however the query still returns no records.
your help is much appreciated
declare @d datetime --input parameter for the procedure
set @d='2011-05-16'
declare @StartDate datetime --first day of a period, has to be calculated and exclude Sundays/Saturdays
declare @EndDate datetime --last day of a period that is neing checked
set @EndDate =@d --or could be calculated.More likely taht this would be set @EndDate=dateadd(d,-1,@d)
set @StartDate= case DATEPART(Dw,@EndDate) when 2 then DATEADD(d,-4,@EndDate)--@EndDate is Monday
when 3 then DATEADD(d,-4,@EndDate) --@EndDate is Tuesday
else DATEADD(d,-2,@enddate)
end
select s.stud_code, s.given_name,surname, x.Num_Absences
from Student s
inner join
(
select stud_code,count(*) Num_Absences
from StudAbsent
where Absent_date between @StartDate and @EndDate
and cmpy_code='10'
group by stud_code
having count(*) > 2
) x on s.stud_code = x.stud_code;
May 15, 2011 at 10:22 pm
Hi again 🙂
I could see a few possible problems here:
-Absent student does not exist in table Students (so inner join eliminates it)
(However, that would mean that you do not have FK constraint between Students and absent_student which is not good)
-There is no absent students with cmpy_code=10
-DATEFIRST is not Sunday.
You could check this if you execute SELECT @@DATEFIRST in the same session before the rest of the code
If it returns 7 it means that Sunday is the days first, if not, the code should be changed to match whatever day first is
Could you please check all of the above and let us know your findings?
May 15, 2011 at 10:27 pm
saghbash (5/15/2011)
your help is much appreciated
For these types of problems, I recommend you follow the instructions in the article at the first link in my signature line below. You'll get better help quicker.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2011 at 12:40 am
Hi again and again 🙂
Thanks ModestyBlaise84, I combined your sample query with toddasd's sample query and the result was positive. I have the needed data. but I just need last thing. When I try to filter result by Year level the query fails. please refer to code below
select s.stud_code, s.given_name,surname,s.year_grp,s.form_cls,x.Num_Absences
from Student s
where year_grp > 6 ( It does not like this line)
I also tried s.year_grp > 6
inner join
(
select stud_code,count(*) Num_Absences
from StudAbsent
where Absent_date between @StartDate and @EndDate
and absent_Type='AFS'
group by stud_code
having count(*) >= 3
) x on s.stud_code = x.stud_code;
When I try to run the query I get the following error:
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'inner'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near 'x'.
I am trying to create two queries one for secondary and one for primary for follow up. please advise
Thanks once more
May 16, 2011 at 12:53 am
Your question is a bit confusing...
WHERE should be at the end.
If this is not what you had, please move it to the end (after join between s and x) and give it another try.
May 16, 2011 at 1:17 am
Work on the inner query
select stud_code,count(*) Num_Absences
from StudAbsent
where Absent_date between @StartDate and @EndDate
and cmpy_code='10'
group by stud_code
having count(*) > 2
until you are certain that the result set returned is correct, then join to the student table.
Follow Jeff's advice and read the article - if you post sample data, you will get a tested result rather than guesswork.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 16, 2011 at 1:51 am
I fully agree with ChrisM@home. The easiest way to wark on this is to break it down to smaller queries instead of looking at the entire query. Also please provide table scripts and some sample data. So we may work on it.
Note: You may hide/omit/change critical data.
May 16, 2011 at 2:16 am
DECLARE @Student AS TABLE (stud_code INT IDENTITY, given_name VARCHAR(30), surname VARCHAR(30), year_group INT)
DECLARE @StudAbsent AS TABLE (stud_code INT, absent_date DATETIME)
INSERT @Student (given_name, surname, year_group)
SELECT 'Bob', 'Bobson', 7
UNION ALL SELECT 'Tim', 'Bobson', 7
UNION ALL SELECT 'Tim', 'Bobson', 8
INSERT @StudAbsent (stud_code, absent_date)
SELECT 1, '2011-05-12'
UNION ALL SELECT 3, '2011-05-12'
--====Your code====--
declare @d datetime --input parameter for the procedure
set @d='2011-05-16'
declare @StartDate datetime --first day of a period, has to be calculated and exclude Sundays/Saturdays
declare @EndDate datetime --last day of a period that is neing checked
set @EndDate =@d --or could be calculated.More likely taht this would be set @EndDate=dateadd(d,-1,@d)
set @StartDate= case DATEPART(Dw,@EndDate) when 2 then DATEADD(d,-4,@EndDate)--@EndDate is Monday
when 3 then DATEADD(d,-4,@EndDate) --@EndDate is Tuesday
else DATEADD(d,-2,@enddate) end
--====Sample Query====--
SELECT s.stud_code, s.given_name, s.surname, ab.absences
FROM @Student s
LEFT OUTER JOIN (SELECT stud_code, COUNT(*) AS absences
FROM @StudAbsent
WHERE absent_date BETWEEN @StartDate AND @EndDate
GROUP BY stud_code) ab ON ab.stud_code = s.stud_code
WHERE ab.absences > 0 AND s.year_group = 7
May 16, 2011 at 3:30 am
select s.stud_code, s.given_name,surname,s.year_grp,s.form_cls,x.Num_Absences
from Student s
inner join
(
select stud_code,count(*) Num_Absences
from StudAbsent
where Absent_date between @StartDate and @EndDate
and absent_Type='AFS'
group by stud_code
having count(*) >= 3
) x on s.stud_code = x.stud_code
where year_grp > 6 ( It does not like this line)
I also tried s.year_grp > 6;
this is the correct syntax try this out.
May 16, 2011 at 4:26 am
Hi Guys,
I'm not a big fan of using sub-queries unless it is absolutely necessary to do so. I've amended the last part of skcadavre's solution (I hope you dont mind!!! :-)), I'm not a big fan of re-inventing the wheel. Please see the last part of the query.
create table Student (stud_code INT IDENTITY, given_name VARCHAR(30), surname VARCHAR(30), year_group INT)
create table StudAbsent (stud_code INT, absent_date DATETIME)
INSERT Student (given_name, surname, year_group)
SELECT 'Bob', 'Bobson', 7
UNION ALL SELECT 'Tim', 'Bobson', 7
UNION ALL SELECT 'Tim', 'Bobson', 8
INSERT StudAbsent (stud_code, absent_date)
SELECT 1, '2011-05-16'
UNION ALL SELECT 3, '2011-05-11'
--====Your code====--
declare @d datetime --input parameter for the procedure
set @d='2011-05-16'
declare @StartDate datetime --first day of a period, has to be calculated and exclude Sundays/Saturdays
declare @EndDate datetime --last day of a period that is neing checked
set @EndDate = @d --or could be calculated.More likely taht this would be set @EndDate=dateadd(d,-1,@d)
set @StartDate =
case DATEPART(Dw, @EndDate)
when 2 then DATEADD(d,-4, @EndDate) --@EndDate is Monday
when 3 then DATEADD(d,-4, @EndDate) --@EndDate is Tuesday
else DATEADD(d,-2,@enddate) end
--====Sample Query====--
select s.stud_code, s.given_name, surname, COUNT(sa.stud_code) Num_Absences
from Student s inner join StudAbsent sa
on s.stud_code = sa.stud_code
group by s.stud_code, s.given_name, surname
having count(sa.stud_code) > 2
May 16, 2011 at 5:03 am
Mr Quillz (5/16/2011)
Hi Guys,I'm not a big fan of using sub-queries unless it is absolutely necessary to do so. I've amended the last part of skcadavre's solution (I hope you dont mind!!! :-)), I'm not a big fan of re-inventing the wheel. Please see the last part of the query.
Any performance change? Or just personal preference?
Going to check during lunch, but what happens when we have say: -
10 year groups of students each with 2000 students in
1,000,000 absences for each year group
May 16, 2011 at 10:47 am
Mr Quillz (5/16/2011)
Hi Guys,I'm not a big fan of using sub-queries unless it is absolutely necessary to do so.
This is not a sub-query, it's a derived table and is a great way of breaking up logic into groups in large queries. 😎
I agree with you about real sub-queries, though. 😀
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 16, 2011 at 6:24 pm
Thanks Guys the following query works as needed. 🙂
declare @d datetime --input parameter for the procedure
set @d=(SELECT DATEADD(day, DATEDIFF(day, 0, getdate()), 0)) -- get current date
declare @StartDate datetime --first day of a period, has to be calculated and exclude Sundays/Saturdays
declare @EndDate datetime --last day of a period that is neing checked
set @EndDate =@d --or could be calculated.More likely taht this would be set @EndDate=dateadd(d,-1,@d)
set @StartDate= case DATEPART(Dw,@EndDate) when 2 then DATEADD(d,-4,@EndDate)--@EndDate is Monday
when 3 then DATEADD(d,-4,@EndDate) --@EndDate is Tuesday
else DATEADD(d,-2,@enddate)
end
select s.stud_code, s.given_name,surname,year_grp,form_cls,x.Num_Absences
from Student s
inner join
(
select stud_code,count(*) Num_Absences
from StudAbsent
where Absent_date between @StartDate and @EndDate
and absent_Type='AFS'
--and cmpy_code='10'
group by stud_code
having count(*) >= 3
) x on s.stud_code = x.stud_code where s.year_grp > 6
June 7, 2012 at 4:50 pm
Posted to the wrong thread, therefore deleted 🙂
June 8, 2012 at 12:23 pm
toddasd (5/16/2011)
Mr Quillz (5/16/2011)
Hi Guys,I'm not a big fan of using sub-queries unless it is absolutely necessary to do so.
This is not a sub-query, it's a derived table and is a great way of breaking up logic into groups in large queries. 😎
I agree with you about real sub-queries, though. 😀
To be sure, the type of "sub-query" you're talking about is frequently referred to as a "Correlated Sub-Query". Because they cannot operate by themselves (they depend on an outer query), they can be a real bugger to troubleshoot. Still, they have their uses and they can be perform small miracles in a very high performance fashion especially when used with CROSS APPLY.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply