Friday, July 26, 2013

[T-SQL] How to write a codition based on min rank

[T-SQL] How to write a codition based on min rank

How to write a codition based on min rank

Posted: 25 Jul 2013 05:27 AM PDT

Hello All, could you please assist me how to get data based on min rankDDLCreate table TableA1 (PatID int, blockName varchar(20), RankID int)insert into TableA1 values (123, 'Cancer', 5)insert into TableA1 values (235, 'Hearts', 6)insert into TableA1 values (345, 'Child' ,1)insert into TableA1 values (123, 'OutPatient', 3)insert into TableA1 values (567, 'OutPatient', 4)insert into TableA1 values (789, 'Inbound' , 7)insert into TableA1 values (567, 'OutPatient', 3)insert into TableA1 values (678, 'Cancer', 5)insert into TableA1 values (789, 'Hearts', 6)insert into TableA1 values (789, 'KidneySpl', 9)insert into TableA1 values (345, 'OutPatient', 3)select * from TableA1 order by 1Create table TableB1 (PatID int, ModelId int )insert into TableB1 values (123,114346)insert into TableB1 values (235,226554)insert into TableB1 values (345,336544)insert into TableB1 values (567,446789)insert into TableB1 values (678,558987)insert into TableB1 values (789,667998)select * from TableB1 order by 1--Joiningselect a.*, b.ModelID from TableA1 A inner join TableB1 Bon a.PatID = b.PatID order by 1,3Required outcome is based on the RankID . So if for the same PatID that appears more times in TableA1, the ModelID (from TableB) goes to the record with highest rankID.expected Results will bePatID blockName RankID ModelID123 OutPatient 3 114346123 Cancer 5 235 Hearts 6 226554345 Child 1 336544345 OutPatient 3 567 OutPatient 3 446789567 OutPatient 4 678 Cancer 5 558987789 Hearts 6 667998789 Inbound 7 789 KidneySpl 9 Thank you in advancedhani

creating procedure for Insertion?

Posted: 22 May 2013 05:15 PM PDT

Hai Friends , I m creating one web application in that input fileds are departuredate,from_place,To_place,travel mode.My condition for insertion when i choose to enter departuredate(input date) is always greater than already appeared date on database date.create table journey(departuredate datetime,from_place varchar(50),to_place varchar(50),travel mode nvarchar(50))insert into journey values ('20-05-2013','cdsfs','dhf','Train')insert into journey values ('21-05-2013','cds','dh','Car')insert into journey values ('22-05-2013','cfs','df','Bus')My procedurecode:create procedure jack(@departuredate datetime,@from_place varchar(50),@to_place varchar(50),@travelmode nvarchar(10))as begin if exists('select departuredate from journey where departuredate<@departuredate')print 'Must choose greater date of departuredate'endelsebegininsert into journey (departuredate,from_place,to_place,travel mode) values(@depaturedate,@from_place,@to_place ,@travelmode,)endend/these query shows exceuted successfully,but it was not woking any body suggest me

When NULL IS NOT NULL

Posted: 25 Jul 2013 01:22 PM PDT

To all my SQL friends out there. While trying to construct a 1M row test harness I ran across an interesting enigma shrouded in uncertainty. Eventually I was able to construct the test data I needed but the results below could use a bit of expert examination to explain them.3 queries - any takers?[code="sql"]-- This query may return no rows (if it does just run it -- again until you get some) but when it does return something-- you gotta ask yourself "why NULL IS NOT NULL?"WITH Tally (n) AS ( SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)SELECT n, [Type], value=(ABS(CHECKSUM(NEWID()))%10000)/100.FROM Tally aOUTER APPLY ( SELECT CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'a' END UNION ALL SELECT CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'b' END ) c ([Type])WHERE [Type] IS NOT NULLORDER BY n;-- This query always returns rows and I'm scratching my head-- asking myself "why NULL IS NOT NULL?"WITH Tally (n) AS ( SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)SELECT m, [Type], value=(ABS(CHECKSUM(NEWID()))%10000)/100.FROM Tally aOUTER APPLY ( SELECT n,CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'a' END UNION ALL SELECT n,CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'b' END ) c (m,[Type])WHERE [Type] IS NOT NULL;-- This query returns an interesting error and now I'm really befuddledWITH Tally (n) AS ( SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)SELECT m, [Type], value=(ABS(CHECKSUM(NEWID()))%10000)/100.FROM Tally aOUTER APPLY ( SELECT n,CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'a' END UNION ALL SELECT n,CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'b' END ) c (m,[Type])WHERE [Type] IS NOT NULLORDER BY m;[/code]When you change UNION ALL to UNION in the first query, you also get interesting results when you run it multiple times.[b]Edit:[/b] Note that OUTER APPLY or CROSS APPLY seems to make no difference.

Rectrict Delete from all tables in a database.

Posted: 25 Jul 2013 08:37 PM PDT

Hi Can anyone solve my problemI want to restrict of all users including admin rights users that they could not delete from any tables in a particular sql server database.Is there any way to do this using trigger.

Linked Server Update Query Structure Affects Performance

Posted: 25 Jul 2013 10:03 PM PDT

A friend just came to me with this one and I don't have an answer...anyone?[quote]I was trying to update a table on a remote server.update myTable set net_fare=-net_fare, tax=-tax where booking_ref = 'ABC123' and hist_tran_no = 25319042 This was taking around 25 seconds to run, despite creating an index on the remote side on hist_tran_no and booking_ref. The execution plan showed that it was running a very expensive Remote Scan which was eating up all of the time. After a few attempts, I tried this:update h set net_fare=-net_fare, tax=-taxfrom myTable h where booking_ref = 'ABC123' and hist_tran_no = 25319042 To all appearances, this is exactly the same update, but using a table alias - I didn't expect any real difference in how this ran, it was just a change of the structure of the query so I could try other stuff. What this did though, was to make the entire query a Remote Query, executing in 0 seconds. So, it's a weird one, unless either of you have any clever explanations of why this happens…[/quote]Does anyone have any information/resources on why this change to the format of the query would allow it to be remoted?Thanks

Need Help Urgently

Posted: 25 Jul 2013 03:36 PM PDT

I Have A Product Name FIX_10_MTH_AM_CT_NC_DB_LCI Have a Validation Like All Components Of The ProductName Must Be The Codes From the Tables A,B,C,D,E,F,G,H,I,JLike THat . Fix Must be A Code From A10 must be a value From B .................If All the components are from the tables then i Should return 1 else return 0.Please Help ME...........

What does this parameter do? ('* All')

Posted: 25 Jul 2013 05:34 AM PDT

Hey guys - It's become my job to decipher dozens (prob close to 100) of SPs written by a dozen different developers. Of course nothing is documented and that's where I come in. One SP has these couple of lines.....IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'TableA')DROP TABLE TableASelect Distinct FieldNameA Into TableAfrom TableB[b]Insert Into TableA(FieldNameA)Values ('* All')[/b]OK. So it seems Table A is dropped, recreated with fresh daily data and then repopulated with Distinct values from Table B.But what does the bolded text do??

relative position of field in a recordset

Posted: 25 Jul 2013 02:58 AM PDT

I need to know the value in a field at a variable offset, or relative position, in a cursor. (I know, a cursor.)In VBA I would open a recordset and use rst.Fields(i), where i is the variable offset value.I need to run an UPDATE against a temp table based on the value in the field:UPDATE tmptankbooking SET field(i) = @LocationAbbrI'd rather not be doing this in a cursor, but I'm not experienced enough to think of an alternative.Thanks.

Need help to build query

Posted: 25 Jul 2013 01:58 AM PDT

[code="plain"]create table sample ( a date,b date, c date ,d date )insert into sample values (null,GETDATE(),GETDATE(),GETDATE())insert into sample values (null,GETDATE(),GETDATE(),null)insert into sample values (GETDATE(),GETDATE(),null,GETDATE())insert into sample values (GETDATE(),GETDATE()+1,GETDATE(),null)select * from sample[/code] output :a b c dNULL 2013-07-25 2013-07-25 2013-07-25NULL 2013-07-25 2013-07-25 NULL2013-07-25 2013-07-25 NULL 2013-07-252013-07-25 2013-07-26 2013-07-25 NULLLogic : all not null column should be equal. If any row not falls in that condition needs to be hi-lighted.requested output is a b c d2013-07-25 2013-07-26 2013-07-25 NULL
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog