[T-SQL] How to Add a Date randomly

---

• How to Add a Date randomly
• Query help
• How to wrote procedure for these?
• split a string
• Can I avoid using cursors
• query
• Partial match & Update

How to Add a Date randomly Posted: 03 Jul 2013 08:25 PM PDT Hi All,In a table (sale.person) I have more than 9 lacs row. I have only three columns. I need to add a column by Orderdate (which I have done by Alter table). Next I want to fill up the rows with date which should be set as a Order. For example if the date of first row is 2013-07-04 then the date of the next row will be 2013-07-03 and so one.Is there any way so that I can do it easily?

Query help Posted: 03 Jul 2013 02:10 AM PDT Hello - I was handed a script to modify. I am an accidental DBA. I am confused on how to modify a query to return certain results. For example when the script returns a 5 i want it to display a 1, when it returns a 4 i want to display a 2 etc.. Any help would be great.Thanks!!

How to wrote procedure for these? Posted: 03 Jul 2013 06:58 PM PDT Hai friends, i ve two tables create table travel_request(request_id int identity primary key,user_id varchar(100) foreign key references users(user_id)purpose_travel varchar(100),total_amount varchar(10))create table onward_journey(onward_journey_id int identity,request_id int foreign key references travel_request(request_id),departuredate datetime,from varchar(100),to varchar(100),travel mode varchar(100))how to i insert these tables?and one more think is when i started enter travel purpose request_id ll genereted automatically depends upon that request_id onward_journey ll be there how to do that?

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

Can I avoid using cursors Posted: 03 Jul 2013 06:06 AM PDT I need to update/create records in a Notes table based upon changes to data in a separate table.I wrote a cursor to solve this problem it follows the following basic outlineIt loops through a table that list the type of 'test code' we need to look for to create notes as well as a template of the note to be created.Next it loops through all patient records that have this 'test code' the notes are patient specific and not order specific so I added this loop to cut down on the number of function calls to set a particular variable.Next it loops through all orders for the patient that has this 'test code' and calls the appropriate stored procedure to update or create the note.Is there a better way to do this without using nested cursors? It takes about 1hr for this query to run.code follows[code="sql"]DECLARE @OrderCode as varchar(50)DECLARE @DaysBetweenTest as intDECLARE @NoteText as varchar(max)DECLARE @TemplateNote as varchar(max)DECLARE @PatientId as varchar(50)DECLARE @LastPerformedDate as DATEDECLARE @OrderEventId as INTDECLARE @FirstPayableDate as DATEDECLARE @NoteId as INTDECLARE @FrequencyCheckNoteId as INT-- Loop through order codes in FrequencyCheck tableDECLARE FreqOCLoop CURSOR FOR SELECT OrderCode, DaysBetweenTest, NoteText from dbo.FrequencyCheckOPEN FreqOCLoopFETCH NEXT FROM FreqOCLoopINTO @OrderCode, @DaysBetweenTest, @TemplateNoteWHILE @@FETCH_STATUS = 0BEGIN-- Loop through patients with future events for order code DECLARE PatientLoop CURSOR FOR select distinct t3.PatientId from dbo.rvw_OrderEventOrderCodes as t1 inner join dbo.[Order] as t2 on t1.OrderId = t2.id inner join dbo.Patient as t3 on t2.PatientId = t3.id where t1.ScheduledDueDate > getdate() and t1.OrderCode=@OrderCode and t1.[Status]=2 OPEN PatientLoop FETCH NEXT FROM PatientLoop INTO @PatientId WHILE @@FETCH_STATUS = 0 BEGIN -- Initialize values SET @LastPerformedDate = NULL SET @FirstPayableDate = NULL SET @NoteText = NULL -- Find last performed date for this patient SET @LastPerformedDate = (SELECT dbo.TestLastPerformedDate(@OrderCode, @PatientId)) -- Calculate next valid date SET @FirstPayableDate = DATEADD(dd,@DaysBetweenTest,@LastPerformedDate) -- Set note value IF @LastPerformedDate IS NOT NULL BEGIN SET @NoteText = REPLACE(@TemplateNote,'<FirstPayableDate>',@FirstPayableDate) END ELSE SET @NoteText = 'No previous results for test code ' + @OrderCode -- Find all future OrderEvents for this patient and loop through them to add or update notes DECLARE EventLoop CURSOR FOR select distinct t1.OrderEventId from dbo.rvw_OrderEventOrderCodes as t1 inner join dbo.[Order] as t2 on t1.OrderId = t2.id inner join dbo.Patient as t3 on t2.PatientId = t3.id where t1.ScheduledDueDate > getdate() and t1.OrderCode=@OrderCode and t1.[Status]=2 and t3.PatientId = @PatientId OPEN EventLoop FETCH NEXT FROM EventLoop INTO @OrderEventId WHILE @@FETCH_STATUS = 0 BEGIN -- Check to see if we have already added a note for this order if (select count(id) from dbo.FrequencyCheckNotes where NoteOrderEventId=@OrderEventId)>0 BEGIN -- Check to see if the currently existing note is the same if @LastPerformedDate <> (select LastPerformedDate from dbo.FrequencyCheckNotes where NoteOrderEventId = @OrderEventId) BEGIN -- Set values SET @NoteId = (SELECT NoteId FROM dbo.FrequencyCheckNotes WHERE NoteOrderEventId = @OrderEventId) SET @FrequencyCheckNoteId = (SELECT id FROM dbo.FrequencyCheckNotes WHERE NoteOrderEventId = @OrderEventId) -- Update OrderEvent note EXEC [dbo].[UpdateFrequencyCheckNote] @NoteText = @NoteText, @NoteId = @NoteId, @LastPerformedDate = @LastPerformedDate, @FrequencyCheckNoteId = @FrequencyCheckNoteId END END ELSE -- Create new note EXEC [dbo].[CreateFrequencyCheckNote] @NoteText = @NoteText, @OrderEventId = @OrderEventId, @LastPerformedDate = @LastPerformedDate FETCH NEXT FROM EventLoop INTO @OrderEventId END CLOSE EventLoop DEALLOCATE EventLoop FETCH NEXT FROM PatientLoop INTO @PatientId END CLOSE PatientLoop DEALLOCATE PatientLoop FETCH NEXT FROM FreqOCLoop INTO @OrderCode, @DaysBetweenTest, @TemplateNoteENDCLOSE FreqOCLoopDEALLOCATE FreqOCLoop[/code]

query Posted: 03 Jul 2013 05:21 AM PDT what is the query to select nth highest salary of emp table

Partial match & Update Posted: 03 Jul 2013 04:55 AM PDT I need update the AppID in table1 by comparing Table2. the data looks like below. Here the problem is AppNames in the both tables are not exactly same but technically they are same with partial match.Table1AppID||||AppnameNull ||||CSO Services OperationsNull |||| CSPNull ||||AAC Claims SystemsTable 2AppID|||||Appname1 |||||Corporate Services Operations(CSO)2 |||||Credit Servicing Portal(CSP)3 |||||American Assuarance Company (AAC) Claims SystemsPlease let me know how to get rid of this task.thanks

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610