Friday, April 19, 2013

[T-SQL] Filter duplicate students via T-SQL

[T-SQL] Filter duplicate students via T-SQL

Filter duplicate students via T-SQL

Posted: 18 Apr 2013 11:56 PM PDT

I need to filter for duplicate students:For same student, if one instance of IDNo is null then check if both instances of Tel3 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel3 are not identical for same Student, check if both instances of Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel2 are not identical for same Student, check if an instance of Tel3 & Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.View example below:-------------------Student IDNo Tel3 Tel2 SortKyle 91686 721377200 721377200Kyle NULL 721377200 721377200 Bronwyn 94123 775498898 777642110Bronwyn NULL 777642110 777642110 Mark NULL 772170700 772476573Mark NULL 772476573 772476573

filter duplicate students via T-SQL

Posted: 18 Apr 2013 11:52 PM PDT

I need to filter for duplicate students:For same student, if one instance of IDNo is null then check if both instances of Tel3 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel3 are not identical for same Student, check if both instances of Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel2 are not identical for same Student, check if an instance of Tel3 & Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.View example below:-------------------Student IDNo Tel3 Tel2 SortKyle 91686 721377200 721377200Kyle NULL 721377200 721377200 Bronwyn 94123 775498898 777642110Bronwyn NULL 777642110 777642110 Mark NULL 772170700 772476573Mark NULL 772476573 772476573

Filter duplicate students via T-SQL

Posted: 18 Apr 2013 11:53 PM PDT

I need to filter for duplicate students:For same student, if one instance of IDNo is null then check if both instances of Tel3 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel3 are not identical for same Student, check if both instances of Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel2 are not identical for same Student, check if an instance of Tel3 & Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.View example below:-------------------Student IDNo Tel3 Tel2 SortKyle 91686 721377200 721377200Kyle NULL 721377200 721377200 Bronwyn 94123 775498898 777642110Bronwyn NULL 777642110 777642110 Mark NULL 772170700 772476573Mark NULL 772476573 772476573

filter duplicate students via T-SQL

Posted: 18 Apr 2013 11:52 PM PDT

I need to filter for duplicate students:For same student, if one instance of IDNo is null then check if both instances of Tel3 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel3 are not identical for same Student, check if both instances of Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.if both instances of Tel2 are not identical for same Student, check if an instance of Tel3 & Tel2 are identical for same Student, if they are - use it to populate field Sort with the value.View example below:-------------------Student IDNo Tel3 Tel2 SortKyle 91686 721377200 721377200Kyle NULL 721377200 721377200 Bronwyn 94123 775498898 777642110Bronwyn NULL 777642110 777642110 Mark NULL 772170700 772476573Mark NULL 772476573 772476573

How to stop SSMS from adding database name to scripts

Posted: 18 Apr 2013 07:31 AM PDT

Is there an option to prevent SSMS from adding the Database Name to scripts it generates?For example, if I do a right click on a table in the Object Explorer then select "Script Table as"INSERT To", it will create the insert statement with the database on it. That has caused me headaches when I create it and use it in my stored procedure and don't notice it. I would never want to do that.I have the same issue with Stored Procedures. When you do a modify, it always puts a "USE Database" at the top. I need to get rid of that one to for the same reason.Thanks,Tom

How to get zero values for a row

Posted: 18 Apr 2013 08:31 PM PDT

I have done some and tried to use the ISNULL command but this is not working.My query is this:[code="sql"]select count (*) as 'Total', datename (mm, date_time) as 'Month Name',MONTH (date_time) as 'Month' ,Year (date_time) as 'Year'from opencallleft join updatedbon updatedb.callref = opencall.callrefwhere (updatetxt like 'Call assigned to the WIBS group%')and year(date_time) in ('2013')and priority not in ('UNIX')and probcode like ('CL%')group by datename (mm, date_time), Year (date_time), MONTH (date_time)order by Year, MONTH [/code]I did try using the ISNULL command as I said, but this did not return zero rows.It's been a while since I wrote reports, I think my mind has gone blank. Any help is appreciated!

how to convert the below subquery to joins

Posted: 18 Apr 2013 07:41 PM PDT

how to convert the below subquery into join ..SELECT CtryId, WrkflwId, Dt FROM stepHistory H WHERE EXISTS (SELECT * FROM (SELECT WrkflwId, CtryId , max(DtTm) DtTm FROM stepHistory GROUP BY WrkflwId, ctryId ) S WHERE S.WrkflwId = H.WrkflwId AND S.DtTm = H.DtTm AND H.CtryId = S.CtryId) AND SteTyId = 2 ----I have use the above query as inner join because i have to run the above query into an datawarehous applicance which wont support subqueryplease help----------------------------------------------------------------------------------------------------------------------------------------------------------------

XML Question (?)

Posted: 18 Apr 2013 11:51 AM PDT

Hi,Let's say I have the following table with just 1 record:SELECT Field1, Field2, Field3 FROM tblreturns 11, 22, 33What would be a query to return something like: <CustomInformation> <ColumnValue name="Field1">11</ColumnValue> <ColumnValue name="Field2">22</ColumnValue> <ColumnValue name="Field3">33</ColumnValue> </CustomInformation>?Thanks!

Please Help ...Sorry if posted it twice/ in wrong forum topic

Posted: 18 Apr 2013 08:57 AM PDT

Have a Table with the CSV Values in the columns as belowID Name text 1 SID,DOB 123,12/01/1990 2 City,State,Zip NewYork,NewYork,01234 3 SID,DOB 456,12/21/1990 What i need is to get is 2 tables in this scenario as out put with the corresponding values , These table should be generated Dynamically as the values in the source table varies every week.ID SID DOB 1 123 12/01/1990 3 456 12/21/1990ID City State Zip2 NewYork NewYork 01234Is there any way of achieving it using a Cursor/Procedure or any other method in SQL server?

passing parm from view to multistatement table valued function

Posted: 18 Apr 2013 08:54 AM PDT

Does anyone know of a sneaky/devious way to pass a parameter (value) from a view to the input parameter of a multi-statement table valued function? I am trying to substitute a function for a table referenced in the view to return a single row.I am limited to using a view by third party software and can't do any pre-processing. I simply have a pop up window that accepts an input parameter of search argument(s) that matches existing column value(s) for a column that exists in the view. Most replies to this question are "you can't get there from here". Anybody have another route I can follow. Maybe a way to convince the view to be a little less static?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog