Friday, October 11, 2013

[T-SQL] How do delete multiple CHAR(9) at end of string

[T-SQL] How do delete multiple CHAR(9) at end of string

How do delete multiple CHAR(9) at end of string

Posted: 10 Oct 2013 05:36 AM PDT

I have a situation where at the end of a remarks string, there is anywhere from 1 -9 "char(9)" at the end of the string.Is there any tried and true function or code that can easily without looping, get rid of those unwanted characters and clean up my remarks code???HELP....and thank you!!! :w00t:

Unable to alter view

Posted: 10 Oct 2013 10:54 AM PDT

I have a view that I cannot make changes to.I just open the view in a query window make a change and then say run and it never comes back.I can make other views and make changes to other views - but for some reason I can't do it for this view.Anything that would cause it not to work.I can't just open it and run it without making changes.I can select using the view fine. Comes back right away.Thanks,Tom

Merging one database to another

Posted: 10 Oct 2013 02:07 AM PDT

I am not sure exactly the best way to accomplish this task so am looking for some adviceWe use Dynamics GP. At the moment this is in an OLTP environment. It also contains about 10 years of historical data. We also need to run a number of reports but this impacts the OLTP environment.My intent is to create a duplicate copy of the GP database on a separate SQL server. I can use this db for reporting requirements. This DB can contain all the data and then I can purge everything older than three years from the primarly OLTP db. Want I then need to do is, after the nightly GP post, take a copy of todays updated information and merge it into the db on the report server. This way the report DB is always, at worst , one day behind the OLTP db which is just fine.And this (sorry for the long winded explanation) is where my question comes in. I can see that I can use the merge command to do what I want to do but if any of you are familiar with GP , then you will know how many tables will need to be merged. Is Merge still the best way to accomplosh this task , simply iterating through each table, or is there are better method to perform this data merge for the entire DB ?Thanks

Sql script to use a column from one table and populate another table

Posted: 10 Oct 2013 09:33 AM PDT

I have two tables Product table and Rate Table.[code="other"]ProductProductId Name [/code][code="other"]RateLevelId Cost ProductId[/code]Each Product has 7 Levels and cost for each level is 100, 200.... 700.I now need a script to take all the product Ids and Populate the Rate table , so that my end output would look like this :[code="other"]RateLevelId Cost ProductId1 100 1 2 200 1 3 300 1 4 400 1 5 500 1 6 600 1 7 700 1 1 100 2 [/code]and so onCurrently I insert the first 7 rows manually and then run the below query for every product id[code="other"]INSERT INTO dbo.Rate (LevelID, Cost, ProductId) SELECT LevelID, Cost, ProductId FROM dbo.Rate WHERE ProductId = 1[/code]Can you direct me on how to fully automate my work ?

Newly Available/No Longer Available analysis

Posted: 10 Oct 2013 03:34 AM PDT

Thanks if you can help.I have a data set that contains a current record of available units for that day.[code="sql"]DECLARE @AvailableUnits TABLE(UnitDate DATE,UnitID INT)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ('10/1/2013',1)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/1/2013',2)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/1/2013',3)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/2/2013',1)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/2/2013',2)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/2/2013',3)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/2/2013',4)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/3/2013',2)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/3/2013',3)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/3/2013',4)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/4/2013',2)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/4/2013',3)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/4/2013',4)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/5/2013',2)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/5/2013',3)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/5/2013',4)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/6/2013',2)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/6/2013',3)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/6/2013',4)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/6/2013',5)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/7/2013',2)INSERT @AvailableUnits (UnitDate,UnitID) VALUES ( '10/7/2013',5)SELECT * FROM @AvailableUnits[/code]I'd like to compare each days data against the previous and identify any units that are newly available or no longer available. My expected results are:2013-10-02,4,'Newly Available Unit'2013-10-03,1,'Unit No Longer Available'2013-10-06,5,'Newly Available Unit'2013-10-07,3,'Unit No Longer Available'2013-10-07,4,'Unit No Longer Available'Any day in the series could have results with the exception of the first day. If the data is unchanged compared to the previous day I don't want to return results.

t-sql 2008 r2 performance

Posted: 10 Oct 2013 04:46 AM PDT

In t-sql 2008 r2, can you tell me what performs better from the following two options and why:1. If parameter value = '1' goto parm1 else of parameter value = '2' goto parm2 goto final parm1: <do something1> goto final parm2: <do something2> final:2. If parameter value = '1' <do something1> else of parameter value = '2' <do something2>
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog