Tuesday, July 23, 2013

[T-SQL] update script

[T-SQL] update script

update script

Posted: 23 Jul 2013 12:41 AM PDT

hii need to write script like thisBEGIN TRY -------------------------------------------------------------------- -- Add New columns into product -------------------------------------------------------------------- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' [product]]') AND type in (N'U')) BEGIN ---------------------------------------------------------------- -- Add New columns into product -------------------------------------------------------------------- BEGIN TRANSACTION ALTER TABLE product ADD Type varchar(25) NULL ALTER TABLE product ADD ToDisplay bit NULL --BEGIN TRANSACTION INSERT INTO product VALUES (6,'Not Started',NULL,NULL,0,'Clinical',0) INSERT INTO product VALUES (7,'In Progress',NULL,NULL,0,'Clinical',1) INSERT INTO product VALUES (8,'Complete - Resolved',NULL,NULL,0,'Clinical',1) INSERT INTO product VALUES (9,'Complete - Unresolved',NULL,NULL,0,'Clinical',1) INSERT INTO product VALUES (10,'Closed - Member/Client Declined.',NULL,NULL,0,'Clinical',1) ------------------------------------------------------------------- ------------- Update values for Type and Display ------------------------------------------------------------------------- UPDATE product SET Type = 'HA',ToDisplay =1 where ID IN(1,2,3,4,5) ALTER TABLE product ALTER COLUMN TYPE VARCHAR(25) NOT NULL alter table product alter column ToDisplay bit not null commit trnasaction END ELSE BEGIN PRINT ' product table does not exists' ENDEND TRY---------------------------------------------------------------------- Error Handling for all logic--------------------------------------------------------------------BEGIN CATCH END CATCH giving me error invalid column name 'type' invalid column name 'Todisplay'is there any way i can do all this in 1 script?

SP vs Identical T-SQL - different results

Posted: 22 Jul 2013 09:11 PM PDT

Guys,I've got an SP which takes two date parameters (a max and min), if I execute the SP I get X rows, if I script the SP to a new window, remove the SP stuff, declare and set the date parameters identically to when running the SP I get a totally different results set!Looking at the two results sets they do both have data over the same date range so it isn't an odd date formatting issue or something (besides, I'm using the same format in both cases).I've tried to re-compile the SP, recreate with a different name, even run on a test database on a separate server and I still get the same results. I passed this to a colleague for a sanity check, he hard coded hte dates into the SP - running the SP (now no params) and the code still produces different results.By different I'm talking 90 odd rows vs over 1000 rows.Is there any way to see if the sys objects has got confused or something? - As a side note, it's a proc used for an SSRS report that's been in use for many months (stretching into years) without an issue, and there haven't been any changes to it.Any help much appreciated - I'm going crazy here!

search values in a temp table with like operator

Posted: 22 Jul 2013 01:57 AM PDT

Hi, I have a SQL proc with a search-parameter (given as coma separated values) which should return all data of a table in which one of the search criteria is valid to any field of the table. At the moment I compare with '=' but I have to change this into like operator. No idea how I can handle this. Help would be highly appreciated!Thanks!!!Sue[code="sql"]create table tblclient( ID int identity(1,1) primary key, Firstname varchar(50), Lastname varchar(50), birthdate smalldatetime)goinsert into tblclientvalues('John','Singer','01.04.1980'),('Mary','Smith','21.06.1975'),('Marylou','Singersmith','11.03.1987'),('Carl','Smith','11.03.1987')gocreate proc pSearch @searchparam varchar(400) -- values will be entered like this - seperated by comma: John, Mary, 23.10.1980as set @searchparam = @searchparam + ',' declare @value varchar(100) set @value = '' -- create temp table to enter search values separated in rows create table #search(value varchar(100)) -- insert each value into #search while charindex(',',@searchparam)> 0 begin set @value = left(@searchparam, charindex(',',@searchparam)-1) set @searchparam = ltrim(right(@searchparam,len(@searchparam) - charindex(',',@searchparam))) insert into #search values(@value) end -- return all clients which referes to either one of the search criterias with like operator select id, firstname, lastname, birthdate from tblclient where firstname in(select value from #search) or lastname in(select value from #search) or convert(varchar(15),birthdate,104) in(select value from #search) goexec pSearch 'Mary' -- should return mary and marylouexec pSearch 'Mary, Smit' -- should return mary, marylou and carlexec pSearch 'Mary, 11.03.1987' -- should return mary,marylou and carl[/code]

Convert given Date to various formats

Posted: 22 Jul 2013 05:12 PM PDT

I have declare Start Date in below format.Declare @StartDate date = '2013-01-01'I want get output for below 2 col's as follows DateKey(int) = 01012013 Date(date) = '01-01-2013'INT, Date are datatypes here

TSQL Business Rule Implementation Between Two Tables

Posted: 22 Jul 2013 05:17 AM PDT

Hello,I am writing a report in SSRS and at first thought I would implement these biz rules at the report level but finding it inferior to the task. So now I can either implement as custom code (vb.net) in SSRS or at TSQL level which would be my preference.So the majority of the business rule I have already satisfied. It is this last step that has me stumped. To make it simple I have provided a sample table that represents the data I need to work with.[code="other"]DECLARE @TeamTable TABLE( TeamID VARCHAR(3), AssignedTask INT)INSERT INTO @TeamTable VALUES ('AAA', 12)INSERT INTO @TeamTable VALUES ('BBB', 45)INSERT INTO @TeamTable VALUES ('CCC', 67)INSERT INTO @TeamTable VALUES ('DDD', 11)INSERT INTO @TeamTable VALUES ('EEE', 12)INSERT INTO @TeamTable VALUES ('FFF', 10)INSERT INTO @TeamTable VALUES ('GGG', 11)INSERT INTO @TeamTable VALUES ('HHH', 6)INSERT INTO @TeamTable VALUES ('III', 3)INSERT INTO @TeamTable VALUES ('JJJ', 11)INSERT INTO @TeamTable VALUES ('KKK', 0)INSERT INTO @TeamTable VALUES ('LLL', 4)INSERT INTO @TeamTable VALUES ('MMM', 12)INSERT INTO @TeamTable VALUES ('NNN', 1)INSERT INTO @TeamTable VALUES ('OOO', 0)INSERT INTO @TeamTable VALUES ('PPP', 12)INSERT INTO @TeamTable VALUES ('QQQ', 12)INSERT INTO @TeamTable VALUES ('RRR', 0)[/code]This query get's the data in a manner that accurately represents my dataset:[code="other"]SELECT TT.TeamID, TT.AssignedTask, RANK() OVER (ORDER BY TT.AssignedTask DESC) AS 'Rank'FROM @TeamTable TTWHERE AssignedTask > 0[/code]Now here is the trick. I have to assign a weighted value to each team based upon the number of teams that have 1 or more task assigned and I don't know what to do next. So in my sample data you'll see that teams KKK, OOO, RRR have zero so 15 is our top point value and I get that using:[code="other"]DECLARE @RankMax TINYINTSET @RankMax = @@ROWCOUNTSELECT @RankMax[/code] So team CCC gets 15 points because they are top dog and it scales down.....with a twist. BBB gets 14.AAA, EEE, MMM, PPP, QQQ all tied. So the next set of points are added then averaged and everyone gets the average score.So 5 teams tied thus 13,12,11,10,9 are added together to equal 55 divided by 5 means each team gets 11 points.Then we move down to JJJ, GGG, DDD another tie. 8 + 7 + 6=21 and 21/3 = 7The rest go down to zero. So the final recordset would look like this:TeamID | AssignTask | Rank |CCC 67 15BBB 45 14AAA 12 11EEE 12 11MMM 12 11PPP 12 11QQQ 12 11JJJ 11 7GGG 11 7DDD 11 7FFF 10 5HHH 6 4LLL 4 3III 3 2NNN 1 1Not looking for anyone to do my homework just a pointer or two in how to approach the problem would be much appreciated.Thank You

Job shows communication link failure when VPN connects

Posted: 22 Jul 2013 10:34 PM PDT

Hi friends, I am running a job in SQL server which shows communication link failure when the machine connects to VPNCan you explain why this happens as the SQL server is on the same machine Thanks,Anish

Basic about clustered index !

Posted: 22 Jul 2013 02:44 PM PDT

Is the same space used by Clustered index and the column on which the primary key is declared?

Finding unequal column values with multiple column comparison

Posted: 22 Jul 2013 05:10 PM PDT

Hello All,I need your help in writing a query for below scenario,Lets assume there are two tables,Table A has 4 coulmns and Table B has 4 coulms[u][b]Table A[/b] Definition and Value[/u]Col1 Col2 Col3 Col41 2 3 A1 2 3 B1 2 3 C[u][b]Table B[/b] Definition and Value[/u]Col1 Col2 Col3 Col41 2 3 B1 2 3 AIf I write a inner join on Table A and B to compare like a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 != b.col4It is not fetching the odd record in Table A which is having value 'C' in Col4, though it will fetch all the records in Table A.Ideally I should get all the records where all the three columns (col1, col2, col3) in Table A and B are same and though Col4 is different (regardless of order of the record).Is it possible to write a TSQL -Query ?Thanks in advanceKJ

split a string

Posted: 03 Jul 2013 06:19 AM PDT

If I have a street address that I would like to split street from Apt number, how could I do that?for example now I have fullstreet ='100 NW 25 St APT# 303'Now I would like to split fullstreet into two columns Street = '100 NW 25 St ' and Apartment = #303what is the syntax?Thanks much
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog