Tricky 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

General SQL Server Forums

New to SQL Server Programming

Tricky Query

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2010-11-30 : 07:51:12

Hi
i've been playing around with this on and off for months now but not had any luck.

What i am trying to do is a count on a dataset where a persons record may or may not appear in differnet years as follows.

08/09 Ref001 David Smith Current
09/10 Ref001 David Smith Current
10/11 Ref001 David Smith Left
08/09 Ref002 Elizabeth Jones Current
09/10 Ref002 Elizabeth Jones Current
10/11 Ref002 Elizabeth Jones Current

I am trying to do a count on RefNo but only count the latest record for each person. For example, in the above the correct answer would be 1 Current, One left, not 5 current, one left.

Can someone advise how I cna only count the latest record for each person please?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 07:58:01

Well...I didn't understand your requirements 100% so feel free to provide more explanations. I think this is pretty close though:

SELECT Status, COUNT(*)
FROM (
  SELECT *,
    RowNum = ROW_NUMBER() OVER (PARTITION BY RefNo ORDER BY YearCoolumn DESC)
  FROM myTable
  ) AS dt
WHERE dt.RowNum = 1
GROUP BY Status

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

chipembele
Posting Yak Master

106 Posts

Posted - 2010-11-30 : 08:26:08

Thanks for your reply. i'm not sure how to implement that into this but will have a go.


SELECT 
ExpectedEndYear, RefNo, FirstForename, Surname, StudyYear, Duration, DaysOncourse,ExpectedDaysOncourse,UserDefined10,StartDate,ExpectedEndDate, ActualEndDate, NVQAchievementDate, OutcomeID,           Outcome, [College Level], UserDefined4,Completion

FROM
(
SELECT     TOP (100) PERCENT ProSolution.dbo.StudentDetail.AcademicYearID, 
CASE WHEN ExpectedEndDate > '20080801' AND 
                      ExpectedEndDate < '20090801' THEN '08/09' WHEN ExpectedEndDate > '20090801' AND 
                      ExpectedEndDate < '20100801' THEN '09/10' WHEN ExpectedEndDate > '20100801' AND 
                      ExpectedEndDate < '20110801' THEN '10/11' WHEN ExpectedEndDate > '20110801' AND 
                      ExpectedEndDate < '20120801' THEN '11/12' WHEN ExpectedEndDate > '20120801' AND 
                      ExpectedEndDate < '20130801' THEN '12/13' WHEN ExpectedEndDate > '20130801' AND 
                      ExpectedEndDate < '20140801' THEN '13/14' WHEN ExpectedEndDate > '20140801' AND 
                      ExpectedEndDate < '20150801' THEN '14/15' END AS ExpectedEndYear, ProSolution.dbo.StudentDetail.RefNo, 
                      ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname, ProSolution.dbo.Offering.StudyYear, ProSolution.dbo.Offering.Duration, DATEDIFF(day, 
                      ProSolution.dbo.Enrolment.StartDate, ProSolution.dbo.Enrolment.ActualEndDate) AS DaysOncourse, 
DATEDIFF(day,ProSolution.dbo.Enrolment.StartDate, ProSolution.dbo.Enrolment.ExpectedEndDate) AS ExpectedDaysOncourse,
ProSolution.dbo.Offering.UserDefined10, 
                      ProSolution.dbo.Enrolment.StartDate, ProSolution.dbo.Enrolment.ExpectedEndDate, ProSolution.dbo.Enrolment.ActualEndDate, NVQAchievementDate, ProSolution.dbo.Outcome.OutcomeID, 
                      ProSolution.dbo.Outcome.Description AS Outcome, ProSolution.dbo.CollegeLevel.Name AS [College Level], ProSolution.dbo.Offering.UserDefined4, 
                      ProSolution.dbo.CompletionStatus.Description AS Completion
FROM         ProSolution.dbo.Offering INNER JOIN
                      ProSolution.dbo.Enrolment ON ProSolution.dbo.Offering.OfferingID = ProSolution.dbo.Enrolment.OfferingID INNER JOIN
                      ProSolution.dbo.CompletionStatus ON 
                      ProSolution.dbo.Enrolment.CompletionStatusID = ProSolution.dbo.CompletionStatus.CompletionStatusID INNER JOIN
                      ProSolution.dbo.Outcome ON ProSolution.dbo.Enrolment.OutcomeID = ProSolution.dbo.Outcome.OutcomeID INNER JOIN
                      ProSolution.dbo.CollegeLevel ON ProSolution.dbo.Offering.SID = ProSolution.dbo.CollegeLevel.SID INNER JOIN
                      ProSolution.dbo.StudentDetail ON ProSolution.dbo.Enrolment.StudentDetailID = ProSolution.dbo.StudentDetail.StudentDetailID
WHERE     (CASE WHEN ExpectedEndDate > '20080801' AND ExpectedEndDate < '20090801' THEN '08/09' WHEN ExpectedEndDate > '20090801' AND 
                      ExpectedEndDate < '20100801' THEN '09/10' WHEN ExpectedEndDate > '20100801' AND 
                      ExpectedEndDate < '20110801' THEN '10/11' WHEN ExpectedEndDate > '20110801' AND 
                      ExpectedEndDate < '20120801' THEN '11/12' WHEN ExpectedEndDate > '20120801' AND 
                      ExpectedEndDate < '20130801' THEN '12/13' WHEN ExpectedEndDate > '20130801' AND 
                      ExpectedEndDate < '20140801' THEN '13/14' WHEN ExpectedEndDate > '20140801' AND 
                      ExpectedEndDate < '20150801' THEN '14/15' END IS NOT NULL) AND (ProSolution.dbo.CompletionStatus.Description <> 'Transferred') AND 
                      (ProSolution.dbo.Offering.UserDefined4 = 'MAIN') 
AND (ProSolution.dbo.Offering.Duration = '2') AND 
((ProSolution.dbo.Offering.StudyYear = '1' AND 
                     ProSolution.dbo.CompletionStatus.Description = 'Withdrawn') OR ProSolution.dbo.Offering.StudyYear = '2')
AND 
                      (ProSolution.dbo.Enrolment.UserDefined2 = 'No' OR
                      ProSolution.dbo.Enrolment.UserDefined2 IS NULL OR
                      ProSolution.dbo.Enrolment.UserDefined2 = ''))STATS

GROUP BY RefNO,AcademicYearId,ExpectedEndYear, RefNo, FirstForename, Surname, StudyYear, Duration, DaysOncourse, 
ExpectedDaysOncourse,
UserDefined10, 
                      StartDate, ExpectedEndDate, ActualEndDate, NVQAchievementDate, OutcomeID, 
                      Outcome, [College Level], UserDefined4, 
                      Completion

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 08:55:09

Can you try this and see if it works? I spent a good 10 minutes reformatting the query to understand what's going on but I think this should work:

SELECT 
	ExpectedEndYear, RefNo, FirstForename, Surname, StudyYear, Duration, 
	DaysOncourse,ExpectedDaysOncourse,UserDefined10,StartDate,ExpectedEndDate, 
	ActualEndDate, NVQAchievementDate, OutcomeID,           
	Outcome, [College Level], UserDefined4,Completion,
	COUNT(*)
FROM(
	SELECT 
		ProSolution.dbo.StudentDetail.AcademicYearID, 
		ExpectedEndYear = CASE 
			WHEN ExpectedEndDate > '20080801' AND ExpectedEndDate < '20090801' THEN '08/09' 
			WHEN ExpectedEndDate > '20090801' AND ExpectedEndDate < '20100801' THEN '09/10' 
			WHEN ExpectedEndDate > '20100801' AND ExpectedEndDate < '20110801' THEN '10/11' 
			WHEN ExpectedEndDate > '20110801' AND ExpectedEndDate < '20120801' THEN '11/12' 
			WHEN ExpectedEndDate > '20120801' AND ExpectedEndDate < '20130801' THEN '12/13' 
			WHEN ExpectedEndDate > '20130801' AND ExpectedEndDate < '20140801' THEN '13/14' 
			WHEN ExpectedEndDate > '20140801' AND ExpectedEndDate < '20150801' THEN '14/15' 
			END, 
		ProSolution.dbo.StudentDetail.RefNo, 
		ProSolution.dbo.StudentDetail.FirstForename, 
		ProSolution.dbo.StudentDetail.Surname, 
		ProSolution.dbo.Offering.StudyYear, 
		ProSolution.dbo.Offering.Duration, 
		DaysOncourse = DATEDIFF(day, ProSolution.dbo.Enrolment.StartDate, ProSolution.dbo.Enrolment.ActualEndDate), 
		ExpectedDaysOncourse = DATEDIFF(day,ProSolution.dbo.Enrolment.StartDate, ProSolution.dbo.Enrolment.ExpectedEndDate),
		ProSolution.dbo.Offering.UserDefined10, 
		ProSolution.dbo.Enrolment.StartDate, 
		ProSolution.dbo.Enrolment.ExpectedEndDate, 
		ProSolution.dbo.Enrolment.ActualEndDate, 
		NVQAchievementDate, 
		ProSolution.dbo.Outcome.OutcomeID, 
		Outcome = ProSolution.dbo.Outcome.Description, 
		[College Level] = ProSolution.dbo.CollegeLevel.Name, 
		ProSolution.dbo.Offering.UserDefined4, 
		Completion = ProSolution.dbo.CompletionStatus.Description AS ,
		RowNum = ROW_NUMBER() OVER (PARTITION BY ProSolution.dbo.CompletionStatus.Description ORDER BY ExpectedEndDate DESC)
	FROM ProSolution.dbo.Offering 
		INNER JOINProSolution.dbo.Enrolment 
			ON ProSolution.dbo.Offering.OfferingID = ProSolution.dbo.Enrolment.OfferingID 
		INNER JOIN ProSolution.dbo.CompletionStatus 
			ON ProSolution.dbo.Enrolment.CompletionStatusID = ProSolution.dbo.CompletionStatus.CompletionStatusID 
		INNER JOIN ProSolution.dbo.Outcome 
			ON ProSolution.dbo.Enrolment.OutcomeID = ProSolution.dbo.Outcome.OutcomeID 
		INNER JOIN ProSolution.dbo.CollegeLevel 
			ON ProSolution.dbo.Offering.SID = ProSolution.dbo.CollegeLevel.SID 
		INNER JOIN ProSolution.dbo.StudentDetail 
			ON ProSolution.dbo.Enrolment.StudentDetailID = ProSolution.dbo.StudentDetail.StudentDetailID
	WHERE ExpectedEndDate BETWEEN '20080801' AND '20150801' 
		AND (ProSolution.dbo.CompletionStatus.Description <> 'Transferred') 
		AND (ProSolution.dbo.Offering.UserDefined4 = 'MAIN') 
		AND (ProSolution.dbo.Offering.Duration = '2') 
		AND (
			(ProSolution.dbo.Offering.StudyYear = '1' AND ProSolution.dbo.CompletionStatus.Description = 'Withdrawn') 
			OR 
			ProSolution.dbo.Offering.StudyYear = '2'
			)
		AND (
			ProSolution.dbo.Enrolment.UserDefined2 = 'No' OR
			ProSolution.dbo.Enrolment.UserDefined2 IS NULL OR
			ProSolution.dbo.Enrolment.UserDefined2 = ''
			)
	) AS STATS
WHERE stats.RowNum = 1
GROUP BY ExpectedEndYear, RefNo, FirstForename, Surname, StudyYear, Duration, 
	DaysOncourse,ExpectedDaysOncourse,UserDefined10,StartDate,ExpectedEndDate, 
	ActualEndDate, NVQAchievementDate, OutcomeID,           
	Outcome, [College Level], UserDefined4,Completion

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

chipembele
Posting Yak Master

106 Posts

Posted - 2010-11-30 : 09:22:06

Thanks

I'm only getting 5 rows returned. I sort of understand what its doing now but not fully.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 09:41:36

Hm, I think I messed up a little. Try this one and see if it looks any better:

SELECT 
	ExpectedEndYear, RefNo, FirstForename, Surname, StudyYear, Duration, 
	DaysOncourse,ExpectedDaysOncourse,UserDefined10,StartDate,ExpectedEndDate, 
	ActualEndDate, NVQAchievementDate, OutcomeID,           
	Outcome, [College Level], UserDefined4,Completion,
	COUNT(*)
FROM (
	SELECT 
		*,
		RowNum = ROW_NUMBER() OVER (PARTITION BY RefNo ORDER BY ExpectedEndYear DESC)
	FROM(
		SELECT 
			ProSolution.dbo.StudentDetail.AcademicYearID, 
			ExpectedEndYear = CASE 
				WHEN ExpectedEndDate > '20080801' AND ExpectedEndDate < '20090801' THEN '08/09' 
				WHEN ExpectedEndDate > '20090801' AND ExpectedEndDate < '20100801' THEN '09/10' 
				WHEN ExpectedEndDate > '20100801' AND ExpectedEndDate < '20110801' THEN '10/11' 
				WHEN ExpectedEndDate > '20110801' AND ExpectedEndDate < '20120801' THEN '11/12' 
				WHEN ExpectedEndDate > '20120801' AND ExpectedEndDate < '20130801' THEN '12/13' 
				WHEN ExpectedEndDate > '20130801' AND ExpectedEndDate < '20140801' THEN '13/14' 
				WHEN ExpectedEndDate > '20140801' AND ExpectedEndDate < '20150801' THEN '14/15' 
				END, 
			ProSolution.dbo.StudentDetail.RefNo, 
			ProSolution.dbo.StudentDetail.FirstForename, 
			ProSolution.dbo.StudentDetail.Surname, 
			ProSolution.dbo.Offering.StudyYear, 
			ProSolution.dbo.Offering.Duration, 
			DaysOncourse = DATEDIFF(day, ProSolution.dbo.Enrolment.StartDate, ProSolution.dbo.Enrolment.ActualEndDate), 
			ExpectedDaysOncourse = DATEDIFF(day,ProSolution.dbo.Enrolment.StartDate, ProSolution.dbo.Enrolment.ExpectedEndDate),
			ProSolution.dbo.Offering.UserDefined10, 
			ProSolution.dbo.Enrolment.StartDate, 
			ProSolution.dbo.Enrolment.ExpectedEndDate, 
			ProSolution.dbo.Enrolment.ActualEndDate, 
			NVQAchievementDate, 
			ProSolution.dbo.Outcome.OutcomeID, 
			Outcome = ProSolution.dbo.Outcome.Description, 
			[College Level] = ProSolution.dbo.CollegeLevel.Name, 
			ProSolution.dbo.Offering.UserDefined4, 
			Completion = ProSolution.dbo.CompletionStatus.Description AS
		FROM ProSolution.dbo.Offering 
			INNER JOINProSolution.dbo.Enrolment 
				ON ProSolution.dbo.Offering.OfferingID = ProSolution.dbo.Enrolment.OfferingID 
			INNER JOIN ProSolution.dbo.CompletionStatus 
				ON ProSolution.dbo.Enrolment.CompletionStatusID = ProSolution.dbo.CompletionStatus.CompletionStatusID 
			INNER JOIN ProSolution.dbo.Outcome 
				ON ProSolution.dbo.Enrolment.OutcomeID = ProSolution.dbo.Outcome.OutcomeID 
			INNER JOIN ProSolution.dbo.CollegeLevel 
				ON ProSolution.dbo.Offering.SID = ProSolution.dbo.CollegeLevel.SID 
			INNER JOIN ProSolution.dbo.StudentDetail 
				ON ProSolution.dbo.Enrolment.StudentDetailID = ProSolution.dbo.StudentDetail.StudentDetailID
		WHERE ExpectedEndDate BETWEEN '20080801' AND '20150801' 
			AND (ProSolution.dbo.CompletionStatus.Description <> 'Transferred') 
			AND (ProSolution.dbo.Offering.UserDefined4 = 'MAIN') 
			AND (ProSolution.dbo.Offering.Duration = '2') 
			AND (
				(ProSolution.dbo.Offering.StudyYear = '1' AND ProSolution.dbo.CompletionStatus.Description = 'Withdrawn') 
				OR 
				ProSolution.dbo.Offering.StudyYear = '2'
				)
			AND (
				ProSolution.dbo.Enrolment.UserDefined2 = 'No' OR
				ProSolution.dbo.Enrolment.UserDefined2 IS NULL OR
				ProSolution.dbo.Enrolment.UserDefined2 = ''
				)
		) AS STATS
	) AS dt		
WHERE dt.RowNum = 1
GROUP BY ExpectedEndYear, RefNo, FirstForename, Surname, StudyYear, Duration, 
	DaysOncourse,ExpectedDaysOncourse,UserDefined10,StartDate,ExpectedEndDate, 
	ActualEndDate, NVQAchievementDate, OutcomeID,           
	Outcome, [College Level], UserDefined4,Completion

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

chipembele
Posting Yak Master

106 Posts

Posted - 2010-12-01 : 04:29:50

Lumbago. Sorry it has taken me so long to reply. With the weather here in England being so bad (for us) I left work to try to get home so have only tried it this morning.

Initial tests show the results are just what I need and I am extremely grateful for your work on this. Thankyou very much.

I may be back to ask more question if that is ok.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-01 : 04:43:40

No worries...and while you're at it make sure you try to understand what it's actually doing as well. There is no point in this unless there is a learning experience involved :)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

chipembele
Posting Yak Master

106 Posts

Posted - 2010-12-01 : 04:51:24

Hi
My plan is that once its working I'll then disect it to work out how it works so I can utilise thism ethod in future.

I do have another question. What happens if the person has more than one course and needs to show these other courses also?

For example

08/09 Ref001 David Smith SHW3A Current
08/09 Ref001 David Smith DISR Current
09/10 Ref001 David Smith SHW3A Current
09/10 Ref001 David Smith DISR Current
08/09 Ref001 David Jones SHW3A Current
08/09 Ref001 David Jones DISR Current
09/10 Ref001 David Jones SHW3A Left
09/10 Ref001 David Jones DISR Current

On top of this I would then do a count on the course as follows (I know how to do the top level count but I just need to get to the correct base data)

SHW3a Current = 1 Left = 1
DISR Current = 2 Left = 0

i've highlighted the ones that would count in the list.

So really a person can appear twice in the base data but it will be on different courses.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-01 : 06:21:05

Then change this line

RowNum = ROW_NUMBER() OVER (PARTITION BY RefNo ORDER BY ExpectedEndYear DESC)

to this (I think)

RowNum = ROW_NUMBER() OVER (PARTITION BY RefNo, [DISRcolumnname] ORDER BY ExpectedEndYear DESC)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

chipembele
Posting Yak Master

106 Posts

Posted - 2010-12-01 : 06:35:39

quote:
Originally posted by Lumbago

Then change this line

RowNum = ROW_NUMBER() OVER (PARTITION BY RefNo ORDER BY ExpectedEndYear DESC)

to this (I think)

RowNum = ROW_NUMBER() OVER (PARTITION BY RefNo, [DISRcolumnname] ORDER BY ExpectedEndYear DESC)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Hi
not sure what you mean to do here. The SHW3a and DISR are courses that would come under the UserDefined10 column

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-01 : 06:53:04

So replace the red text that I marked out with UserDefined10 then...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

chipembele
Posting Yak Master

106 Posts

Posted - 2010-12-01 : 12:26:28

quote:
Originally posted by Lumbago

So replace the red text that I marked out with UserDefined10 then...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Thanks. will try this when I get back in the office.

chipembele
Posting Yak Master

106 Posts

Posted - 2010-12-06 : 06:32:26

Just to update, this looks to be working as I wanted. Many thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources