Select Statement - 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

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-20 : 08:22:42

Hi,

I have the following query whick works fine. However, I want to add some criteria which I dont know how to add it...

with cte as (
      select 
	patid, 
	eventdate, 
	consid,
        issueseq,
        prodcode,
	    staffid,
	    techcode,
	    RowNum = ROW_NUMBER() OVER (partition by patid, techcode ORDER BY  patid, techcode, eventdate)
        from dbo.therapy_patient_566_unident        
	) 
	select *
        into dbo.no_consultations
		from cte where  issueseq = 0 and eventdate between '07/12/2009' AND '07/12/2010' and RowNum > 1
		order by patid

The criteria is as follows,

For any 2 records when the patid, eventdate, consid and techcode are the same these records should be excluded.. So, the RowNum should not apply...

Any help please.. Thanks

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-06-20 : 09:01:22

Do u have any Primary key in your table?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-20 : 09:18:05

No, and dbo.therapy_patient_566_unident is a VIEW rather than a table... Sometimes I have 2 records with the same patid, eventdate, consid, and techcode and my query gives the first ROWNUM = 1 and the second ROWNUM = 2, then it keeps the second record.

I want to exclude both records when patid, eventdate, consid, and techcode are the same...Or the query to read it as 1 record and hence excluded as ROWNUM will not be greater than 1 anyway.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-20 : 09:23:13

If this is a valid example of input data
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160828

what is your expected output?

Jim

Everyday I learn something that somebody else already knew

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-20 : 09:41:51

ok Let me give an example;

For example for the following results;

patid, eventdate, consid, issueseq, prodcode, staffid, techcode, RowNum
1 20/06/11 2 0 45 5 100 1
2 21/05/10 17 0 100 8 87 1
2 22/05/10 19 0 178 8 199 2
3 15/08/10 4 0 79 9 188 1
3 15/08/10 4 0 98 9 188 2

Presently, the results from the query will be

patid, eventdate, consid, issueseq, prodcode, staffid, techcode, RowNum
2 22/05/10 19 0 178 8 199 2
3 15/08/10 4 0 98 9 188 2

I want the results to be

patid, eventdate, consid, issueseq, prodcode, staffid, techcode, RowNum
2 22/05/10 19 0 178 8 199 2

The patid = 3 record TO BE excluded because patid, eventdate, consid and techcode is the same for both records. Hence this is treated as 1 record and thus ROWNUM = 1 and therefore excluded.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources