Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filter duplicate students via T-SQL Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 6:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:40 PM
Points: 159, Visits: 454
/*

I need to filter for duplicate students:
---------------------------------------
For same student, if one or both instance/s of IDNo is null then do the following:

1.
if both instances of Tel3 are identical for same Student (Alison), if they are
use it to populate field Sort with the value.

2.
if both instances of Tel3 are not identical for same Student (Carmen), check if both instances of Tel2 are identical for same Student, if they are use it to populate field Sort with the value.

3.
if both instances of Tel3 and Tel2 are not identical for same Student (Amber), check if an instance of Tel3 & Tel2 are identical for same Student, if they are use it to populate field Sort with the value.

*/

SELECT 'Alison' as Student, NULL as IDNo, '1111' as Tel3, '1111' as Tel1, NULL as Sort UNION ALL
SELECT 'Alison' as Student, '80405088' as IDNo, '1111' as Tel3, '1111' as Tel1, NULL as Sort UNION ALL

SELECT 'Amber' as Student, NULL as IDNo, '5555' as Tel3, '6666' as Tel1, NULL as Sort UNION ALL
SELECT 'Amber' as Student, NULL as IDNo, '7777' as Tel3, '7777' as Tel1, NULL as Sort UNION ALL

SELECT 'Carmen' as Student, NULL as IDNo, '8888' as Tel3, '8888' as Tel1, NULL as Sort UNION ALL
SELECT 'Carmen' as Student, '20083085' as IDNo, '9999' as Tel3, '8888' as Tel1, NULL as Sort
Post #1444362
Posted Friday, April 19, 2013 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
duplicate post. direct replies here. http://www.sqlservercentral.com/Forums/Topic1444358-392-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1444381
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse