Complicated Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Complicated Query

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-11-03 : 10:09:16

Ive come onto dead end with this query, ill provide the query graphically and see if anyone can help as im lost?

student_id, week_no, subject_no, absent
001, 1, 1, y
001, 2, 1, y
001, 3, 1, y
002, 1, 1, y
003, 1, 1, y

Ok, I need to write a query that only picks out the student that was absent 3 weeks in a row. Thing is the weeks can then be 6, 7, 8 etc any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 10:19:27

try this out

SELECT t.student_id
FROM Table t
CROSS APPLY (SELECT TOP 2 week_no
             FROM Table
             WHERE student_id = t.student_id
             AND week_no <t.week_no
             AND absent='y'
             ORDER BY week_no DESC) t1
OUTER APPLY (SELECT  COUNT(week_no) AS PresentCount
             FROM Table
             WHERE student_id = t.student_id
             AND week_no >=t1.week_no
             AND week_no<=t.week_no
             AND absent='n')t2
WHERE t.absent='y'
AND ISNULL(t2.PresentCount,0)=0
GROUP BY t.student_id

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-11-03 : 10:59:20

I got 'Outer Apply SQL construct or statement is not supported'

quote:

Originally posted by visakh16

try this out

SELECT t.student_id
FROM Table t
CROSS APPLY (SELECT TOP 2 week_no
             FROM Table
             WHERE student_id = t.student_id
             AND week_no <t.week_no
             AND absent='y'
             ORDER BY week_no DESC) t1
OUTER APPLY (SELECT  COUNT(week_no) AS PresentCount
             FROM Table
             WHERE student_id = t.student_id
             AND week_no >=t1.week_no
             AND week_no<=t.week_no
             AND absent='n')t2
WHERE t.absent='y'
AND ISNULL(t2.PresentCount,0)=0
GROUP BY t.student_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:09:29

are you using sql 2005? if yes, whats the compatibility level of db?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-11-03 : 11:12:39

Yes I am using 2005, sorry how would I find that out?

quote:
Originally posted by visakh16

are you using sql 2005? if yes, whats the compatibility level of db?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:20:06

run this and post back result

sp_dbcmptlevel 'yourdatabasenamehere'

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-11-03 : 11:23:06

The current compatibility level is 90.

quote:
Originally posted by visakh16

run this and post back result

sp_dbcmptlevel 'yourdatabasenamehere'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:26:56

then you should be able to use OUTER APPLY. Which client tool are you using?is it SQL mgmnt studio?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-11-03 : 11:57:36

No im using visual studio to do the reports.

quote:
Originally posted by visakh16

then you should be able to use OUTER APPLY. Which client tool are you using?is it SQL mgmnt studio?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 12:03:31

in that case make a procedure from query posted. execute the procedure in sql management studio and give procedure name in reports after selecting command type as stored procedure.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-11-04 : 05:10:05

Hey,

This is my query below:

SELECT student_id
FROM dbo.StudAtt T
CROSS APPLY (SELECT TOP 2 week_no
FROM dbo.StudAtt
WHERE student_id = t.student_id
AND week_no < t.week_no
AND (acad_period = '08/09')
AND (CASE WHEN (SUM(Actual)) > 0 AND (SUM(Meets) > 0) THEN ((SUM(Actual) * 1.0) / (SUM(Meets) * 1.0) * 100)
ELSE '0' END = 0)
ORDER BY week_no DESC) t1
OUTER APPLY (SELECT COUNT(week_no) AS PresentCount
FROM dbo.StudAtt
WHERE student_id = t.student_id
AND week_no >= t1.week_no
AND week_no <= t.week_no
AND (acad_period = '08/09')
AND (CASE WHEN (SUM(Actual)) > 0 AND (SUM(Meets) > 0) THEN ((SUM(Actual) * 1.0) / (SUM(Meets) * 1.0) * 100)
ELSE '0' END > 0)) t2
WHERE (t.acad_period = '08/09') AND (CASE WHEN (SUM(t.Actual)) > 0 AND (SUM(t.Meets) > 0) THEN ((SUM(t.Actual) * 1.0) / (SUM(t.Meets) * 1.0) * 100)
ELSE '0' END = 0)
AND ISNULL(t2.PresentCount, 0) = 0
GROUP BY t.student_id, t.acad_period
ORDER BY student_id

But I get the following error, 'An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.'

But im not grouping at all, does that make sense?

quote:
Originally posted by visakh16

in that case make a procedure from query posted. execute the procedure in sql management studio and give procedure name in reports after selecting command type as stored procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 11:41:33

did you try creating a procedure as i suggested? also in above attempt you are using SUM() in WHERE which causes the error. SUM() etc can be used only when you group by using a HAVING clause as sugested.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 11:45:56

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
studentID CHAR(3),
weekNo TINYINT,
subjectNo TINYINT,
[absent] CHAR(1),
PRIMARY KEY CLUSTERED
(
studentID,
weekNo
),
seq INT
)

INSERT @Sample
(
studentID,
weekNo,
subjectNo,
[absent]
)
SELECT '001', 7, 1, 'y' UNION ALL
SELECT '001', 6, 1, 'y' UNION ALL
SELECT '001', 8, 1, 'y' UNION ALL
SELECT '002', 3, 1, 'y' UNION ALL
SELECT '002', 2, 1, 'n' UNION ALL
SELECT '002', 1, 1, 'y' UNION ALL
SELECT '003', 4, 1, 'y'

-- Initialize sequencing
UPDATE @Sample
SET seq = NULL

DECLARE @studentID CHAR(3),
@weekNo TINYINT,
@seq INT

SELECT @studentID = studentID,
@weekNo = weekNo - 1,
@seq = 1
FROM @Sample
ORDER BY studentID,
weekNo

UPDATE @Sample
SET @seq = seq = CASE
WHEN @studentID = studentID AND @weekNo + 1 = weekNo THEN @seq + 1
ELSE 1
END,
@studentID = studentID,
@weekNo = weekNo
WHERE [absent] = 'y'

SELECT DISTINCT
studentID
FROM @Sample
WHERE seq >= 3[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 11:49:53

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
studentID CHAR(3),
weekNo TINYINT,
subjectNo TINYINT,
[absent] CHAR(1)
)

INSERT @Sample
(
studentID,
weekNo,
subjectNo,
[absent]
)
SELECT '001', 7, 1, 'y' UNION ALL
SELECT '001', 6, 1, 'y' UNION ALL
SELECT '001', 8, 1, 'y' UNION ALL
SELECT '002', 3, 1, 'y' UNION ALL
SELECT '002', 2, 1, 'n' UNION ALL
SELECT '002', 1, 1, 'y' UNION ALL
SELECT '003', 4, 1, 'y'

SELECT DISTINCT s1.studentID
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.studentID = s1.studentID
AND s2.weekNo = s1.weekNo + 1
AND s2.[absent] = 'y'
INNER JOIN @Sample AS s3 ON s3.studentID = s2.studentID
AND s3.weekNo = s2.weekNo + 1
AND s3.[absent] = 'y'
WHERE s1.[absent] = 'y'[/code]

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-11-07 : 08:58:58

Hi, ive turned your query into mine. and im getting error.

Msg 8117, Level 16, State 1, Line 22
Operand data type char is invalid for sum operator.

Ive changed it even more, because i know thats to do with the grouping and used a having etc and stil get the same error, any advice?

DECLARE @Sample TABLE
(
student_id CHAR(15),
week_no TINYINT,
Actual CHAR(1)
)

INSERT @Sample
(
student_id,
week_no,
Actual
)

SELECT dbo.stmbiogr.student_id, dbo.StudAtt.week_no, SUM(StudAtt.Actual) AS Actual
FROM dbo.stmbiogr INNER JOIN
dbo.StudAtt ON dbo.stmbiogr.student_id = dbo.StudAtt.student_id
WHERE (dbo.StudAtt.acad_period = '08/09')
GROUP BY dbo.stmbiogr.student_id, dbo.StudAtt.week_no
HAVING (SUM(StudAtt.Actual) = 0)

SELECT DISTINCT s1.student_id
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.student_id = s1.student_id
AND s2.week_no = s1.week_no + 1
AND (SUM(s2.Actual) = 0)
INNER JOIN @Sample AS s3 ON s3.student_id = s2.student_id
AND s3.week_no = s2.week_no + 1
AND (SUM(s3.Actual) = 0)
WHERE (SUM(s1.Actual) = 0)

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		studentID CHAR(3),
		weekNo TINYINT,
		subjectNo TINYINT,
		[absent] CHAR(1)
	)

INSERT	@Sample
	(
		studentID,
		weekNo,
		subjectNo,
		[absent]
	)
SELECT	'001', 7, 1, 'y' UNION ALL
SELECT	'001', 6, 1, 'y' UNION ALL
SELECT	'001', 8, 1, 'y' UNION ALL
SELECT	'002', 3, 1, 'y' UNION ALL
SELECT	'002', 2, 1, 'n' UNION ALL
SELECT	'002', 1, 1, 'y' UNION ALL
SELECT	'003', 4, 1, 'y'

SELECT DISTINCT	s1.studentID
FROM		@Sample AS s1
INNER JOIN	@Sample AS s2 ON s2.studentID = s1.studentID
			AND s2.weekNo = s1.weekNo + 1
			AND s2.[absent] = 'y'
INNER JOIN	@Sample AS s3 ON s3.studentID = s2.studentID
			AND s3.weekNo = s2.weekNo + 1
			AND s3.[absent] = 'y'
WHERE		s1.[absent] = 'y'

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 09:21:39

try like this

DECLARE @Sample TABLE
(
student_id CHAR(15),
week_no TINYINT,
Actual CHAR(1)
)

INSERT @Sample
(
student_id,
week_no,
Actual
)

SELECT dbo.stmbiogr.student_id, dbo.StudAtt.week_no,StudAtt.Actual AS Actual
FROM dbo.stmbiogr INNER JOIN
dbo.StudAtt ON dbo.stmbiogr.student_id = dbo.StudAtt.student_id
WHERE (dbo.StudAtt.acad_period = '08/09')

SELECT  s1.student_id
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.student_id = s1.student_id
AND s2.week_no = s1.week_no + 1
AND s2.Actual='y'
INNER JOIN @Sample AS s3 ON s3.student_id = s2.student_id
AND s3.week_no = s2.week_no + 1
AND s3.Actual = 'y'
WHERE s1.Actual = 'y'
GROUP BY s1.student_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 09:34:40

Please lock this topic since exactly same discussion is found over here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113791

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources