SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 eliminating unwanted row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 04/05/2013 :  10:28:08  Show Profile  Reply with Quote
Hi,

im trying to eliminate some rows of data from the query below wherever the InductionChecklistRecieved is Y, but anything before this i want to elminate for each learner.

The difficulty im having is a learner may be in my results set 4 times with the same NI Number, but all the other data will be different. So if the first 2 records have N in InductionChecklistRecieved, then the 3rd has Y and the 4th has N. i need to to exclude rows 1 and 2 from the from my result, but keep row 3 and 4.

i've tried playing around with the row number over partition function, but had no luck as of yet.

i hope this makes sense, maybe if i could post a picture of my result, it would make better sense.



select w.NI,
       w.Learner_ID,
       w.Datereferral,
       ROW_NUMBER() OVER(PARTITION BY w.ni ORDER BY w.Datereferral ASC) as ReferralPosition,
       case when h.Learner_ID IS null then 'N' else 'Y' end as InductionChecklistReceived

from wpr w
      left join HISTORY H (nolock)
      on w.Learner_ID = h.Learner_ID
      and h.Category_ID = 215

where ClientGroup = 26


Ketan M Mistry

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/05/2013 :  12:12:53  Show Profile  Reply with Quote
Can you post sample data (in a consumable format) and expected ouptut? That'll make it much easier for us to help you. One you are showing us real data and two, you'll get working code. Here are two links on how to post your question including DDL, DML and Expected output so we can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - Lamprey on 04/05/2013 12:13:20
Go to Top of Page

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 04/08/2013 :  06:55:56  Show Profile  Reply with Quote
Thanks for your response Lamprey

Below is the code

From the resulting query what im trying to eliminate are lines 1 and 2 (learner_id 44302 and 47316). but im not too sure how i would do this. The first four learners are the same people but have been referred 4 times, only thing in the system that is same for this learner is name, dob and NI number, everything else will be different.

All i would like to capture is where there is a Y for inductionchecklist for a Ni number, that and anything after that, however if someone hasn't got a Y ever i need to have this in the results too

Hope its makes sense - if i could post a spreadsheet of example results as i can't figure how to attach data. Apologies if this is something easy.


CREATE TABLE [dbo].[WPR](
	[Learner_ID] [int] NOT NULL,
	[Firstname] [char](30) NULL,
	[Surname] [char](30) NULL,
	[NI] [char](9) NULL,
	[DOB] [smalldatetime] NULL,
	[Datereferral] [smalldatetime] NULL,
) 

CREATE TABLE [dbo].[HISTORY](
	[HistoryID] [int] NOT NULL,
	[Learner_ID] [int] NULL,
	[DateEntered] [datetime] NOT NULL,
	[Category_ID] [int] NULL
) 

INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (22161,'JEFF','SMITH','KK991155K','1979-12-28 00:00','2012-08-07 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (42457,	'JO','BLOGGS','PP059991Z','1968-09-28 00:00','2012-06-01 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (47962,	'JOHN','SMITH','TT229940Z',	'1966-06-03 00:00','2012-08-16 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (44302,	'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-06-27 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (47316,	'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-08-07 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (50359,	'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-09-19 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (53672,	'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-10-31 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (59305,	'DEAN','BENT','ZZ999999Z','1974-02-01 00:00','2013-01-15 00:00')
INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (60773,	'DEAN','BENT','ZZ999999Z','1974-02-01 00:00','2013-02-01 00:00')

INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered,Category_ID) VALUES (1,47962,	'2012-08-16 00:00',215)
INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered,Category_ID) VALUES (2,50359,	'2012-09-19 00:00',215)

select w.NI,
       w.Learner_ID,
       w.Datereferral,
       ROW_NUMBER() OVER(PARTITION BY w.ni ORDER BY w.Datereferral ASC) as ReferralPosition,
       case when h.Learner_ID IS null then 'N' else 'Y' end as InductionChecklistReceived

from wpr w
      left join HISTORY H (nolock)
      on w.Learner_ID = h.Learner_ID
      and h.Category_ID = 215
Order by w.NI




Ketan M Mistry
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/08/2013 :  13:16:49  Show Profile  Reply with Quote
Here is a quick hack that appears to get the data that you want.
;with cte as
(
	select
		w.NI,
		w.Learner_ID,
		w.Datereferral,
		ROW_NUMBER() OVER(PARTITION BY w.ni ORDER BY w.Datereferral ASC) as ReferralPosition,
		case when h.Learner_ID IS null then 'N' else 'Y' end as InductionChecklistReceived
	from 
		wpr w
	left join 
		HISTORY H (nolock)
		on w.Learner_ID = h.Learner_ID
		and h.Category_ID = 215
)


select 
	w.*
from
	cte as w
left outer join
	(
		select *
		from cte
		where InductionChecklistReceived = 'Y'
	) as t	
	on w.ni = t.ni
WHERE
	(
		t.NI IS NOT NULL
		AND w.Datereferral >= t.Datereferral
	)
	OR
		t.NI IS NULL
Order by 
	w.NI
Go to Top of Page

ketanmmistry
Starting Member

United Kingdom
8 Posts

Posted - 04/10/2013 :  12:22:13  Show Profile  Reply with Quote
Lamprey,

Apologies for late response, many thanks for looking at this for me.

This does the trick, i was trying to do it within the main query.



Ketan M Mistry
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.47 seconds. Powered By: Snitz Forums 2000