May 27, 2012 at 9:44 pm
We are running an SQL 2005 server with student’s database. The business manager wants to be alerted when five or more students exit the school in any given week. So I wanted to create a stored procedure to query the database daily and email the list should the business manager's condition be met.
We have a students table with the following fields
Cmpy_code
Givent_Name
Surname
Year_grp
Form_cls
dol ( Date of leaving)
Therefore I created the following query
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 Being checked
set @StartDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))
set @EndDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6))
select s.stud_code, s.given_name,s.surname,s.year_grp,form_cls,s.dol
from Student s
inner join
(select stud_code,count(*)
from Student as c
where dol between @StartDate and @EndDate
and cmpy_code='01'
and s.stud_code = c.stud_code
having s.dol = >' 5'
group by stud_code )
The query should first
- Check if the date of leaving falls in the current week
- Then count number of students who left in the current period
- If number = > 5 then creat list and email it to Business Manager (I know how to do this once this query is working)
- If number is less than 5 do nothing
When I run the query I receive the following error:
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '>'.
I know this could be a silly mistake somewhere but I cannot spot it. Please help
Regards
May 27, 2012 at 9:55 pm
Change the line
having s.dol = >' 5'
to
having s.dol >= ' 5'
May 27, 2012 at 10:44 pm
The HAVING keyword must come after the GROUP BY clause.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2012 at 11:09 pm
I think there's more issues here than just the syntax error:
1. DOL is a datetime (presumably), so why the test on DOL <= '5'?
2. Where is the ON following the derived table you're doing the INNER JOIN on?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 28, 2012 at 12:34 am
Many thanks guys. I reviewd my code following your comments and now it is like this
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 Being checked
set @StartDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))
set @EndDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6))
select s.stud_code, s.given_name,s.surname,s.year_grp,form_cls,s.dol
from Student s
inner join
(select stud_code,count(*)
from Student as c
where dol between @StartDate and @EndDate
and cmpy_code='01'
and s.stud_code = c.stud_code
group by stud_code
having count(*) > = ' 5' )
s on s.stud_code = c.stud_code
and now I am getting the following errors
sg 4104, Level 16, State 1, Line 12
The multi-part identifier "s.stud_code" could not be bound.
Msg 8155, Level 16, State 2, Line 12
No column was specified for column 2 of 's'.
Msg 1011, Level 16, State 1, Line 12
The correlation name 's' is specified multiple times in a FROM clause.
Thanks once more time
May 28, 2012 at 12:56 am
A couple of issues. You have two tables aliased as S, the master table in the first from and the sub query, hence the MSG 101. Secondly the outer query has no knowlege of the 'c' alias as it is within the sub-query. And the not bound error realtes back to the two data sources (table and sub-query) having the same alias. Also all columns returned in a sub-query must have a lable, count(*) , hence the 8155 error
So you probably need to summarise the sub query to allow the >= 5 filter with another sub query.
May 28, 2012 at 1:03 am
I believe your query will run as follows:
select s.stud_code, s.given_name,s.surname,s.year_grp,form_cls,s.dol
from Students s
inner join
(select stud_code, x=count(*)
from Students as c
where dol between @StartDate and @EndDate
and cmpy_code='01'
group by stud_code
having count(*) > = 5 )
c on s.stud_code = c.stud_code
However I think you should also consider the following two alternatives below, using a temporary table @Students, instead of Students. Both do a single table scan (yours does 2) so will probably be more efficient in the long run.
DECLARE @Students TABLE
(stud_code INT, Cmpy_code VARCHAR(10),
Given_Name VARCHAR(20), Surname VARCHAR(20),
Year_grp INT, Form_cls INT, DOL DATETIME)
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 Being checked
set @StartDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))
set @EndDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6))
SELECT s.stud_code, s.given_name,s.surname,s.year_grp,form_cls,s.dol
FROM @Students s
CROSS APPLY (
SELECT c=COUNT(*) OVER (PARTITION BY (SELECT NULL))) x
WHERE dol between @StartDate and @EndDate and cmpy_code='01' and x.c >= 5
;WITH Studs AS (
SELECT stud_code, given_name,surname,year_grp,form_cls,dol, cmpy_code
,COUNT(*) OVER (PARTITION BY (SELECT NULL)) As c
FROM @Students s)
SELECT stud_code, given_name,surname,year_grp,form_cls,dol
FROM Studs
WHERE dol between @StartDate and @EndDate and cmpy_code='01' and c >= 5
Couldn't test them with real data since you provided none, but I think they should work.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 28, 2012 at 1:27 am
-- Table definition
CREATE TABLE dbo.Students
(
StudentID integer NOT NULL,
GivenName nvarchar(20) NOT NULL,
Surname nvarchar(50) NOT NULL,
LeavingDate datetime NOT NULL,
CONSTRAINT [PK dbo.Students StudentID]
PRIMARY KEY (StudentID)
);
-- Sample data
INSERT dbo.Students
(StudentID, GivenName, Surname, LeavingDate)
SELECT 1, N'Aaron', N'Aardvark', '2012-05-28' UNION ALL
SELECT 2, N'Barry', N'Beetle', '2012-05-29' UNION ALL
SELECT 3, N'Chris', N'Centipede', '2012-05-27' UNION ALL
SELECT 4, N'Danny', N'Deer', '2012-05-30' UNION ALL
SELECT 5, N'Ernie', N'Elephant', '2012-06-01' UNION ALL
SELECT 6, N'Frank', N'Fox', '2012-05-30' UNION ALL
SELECT 7, N'Gary', N'Goat', '9999-12-31' UNION ALL
SELECT 8, N'Harry', N'Heffalump', '9999-12-31';
-- Monday is the first day of the week
SET DATEFIRST 1;
-- Date range variables
DECLARE
@ThisWeekMonday datetime,
@ThisWeekSaturday datetime;
-- Important to use a base date that is a Monday
SET @ThisWeekMonday =
DATEADD(WEEK, DATEDIFF(WEEK, '2011-08-01', GETDATE()), '2011-08-01');
-- Saturday generally occurs 5 days after Monday
SET @ThisWeekSaturday =
DATEADD(DAY, 5, @ThisWeekMonday);
-- One way to get the results needed
WITH LeaversThisWeek AS
(
SELECT
s.GivenName,
s.Surname,
s.LeavingDate,
LeaverCount = COUNT_BIG(*) OVER ()
FROM dbo.Students AS s
WHERE
s.LeavingDate >= @ThisWeekMonday
AND s.LeavingDate < @ThisWeekSaturday
)
SELECT
ltw.GivenName,
ltw.Surname,
ltw.LeavingDate
FROM LeaversThisWeek AS ltw
WHERE
ltw.LeaverCount >= 5;
GO
-- Tidy up
DROP TABLE dbo.Students;
June 7, 2012 at 5:00 pm
Dear All thanks for the help and guidance. Just to advise you of what I ended up doing. As some of the proposed solution were too complicated. For me anyway :-). So I changed the query as follows:
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 Being checked
set @StartDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))
set @EndDate = (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6))
IF (select count(stud_code)as Exiting_Students From student where dol between @StartDate and @EndDate)> = 3
Begin
select *
From student
where dol between @StartDate and @EndDate
End
The query is working fine and as intended. But I am looking for improvement especially avoid repating the criteria of where dol between @StartDate and @EndDate
Thanks once more, much appreciated.
June 7, 2012 at 6:37 pm
saghbash (6/7/2012)
Dear All thanks for the help and guidance. Just to advise you of what I ended up doing. As some of the proposed solution were too complicated. For me anyway :-).
Things are always complicated until they're easy.
If you decompose the query suggestions and work through them one piece at a time, reading MS BOL where you need clarification on something, my statement above will eventually come true.
Just a suggestion.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply