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 2008 Forums
 Transact-SQL (2008)
 eliminating unwanted row

Author  Topic 

ketanmmistry
Starting Member

8 Posts

Posted - 2013-04-05 : 10:28:08
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-05 : 12:12:53
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
Go to Top of Page

ketanmmistry
Starting Member

8 Posts

Posted - 2013-04-08 : 06:55:56
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-08 : 13:16:49
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

8 Posts

Posted - 2013-04-10 : 12:22:13
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
   

- Advertisement -