Im not very strong on loops and recursive queries in T-SQL (might as well given that it's more of a set based language anyway) but I have still problem which I can't figure out without the use of recursive query and a function? I have 2 tables called Brands and Products. CREATE TABLE Brands (RowID INT, BrandName VARCHAR(50)) CREATE TA...
How we can use CTE in subquery in sql server? like .. select id (i want to use CTE here), name from table_name ...
Hi, I have spent the past couple of days working on this and am going around in circles. My question is based around the answer I accepted in this post: stackoverflow question I now have my data moved from a single 400 column table to a much more managable database structure with many thanks to Damir Sudarevic. My database looks li...
I am a newbie in SQL Server 2008 and just got introduced to HierarchyId's. I am learning from SQL Server 2008 - HIERARCHYID - PART I. So basically I am following the article line by line and while practicing in SSMS I found that for every ChildId some hexadecimal values are generated like 0x,0x58,0x5AC0 etc. My questions are What a...
I have this type of data TimeOFDay column is varchar. I want to change this time in 24 hour time, using SQL, and updating TwentyFourHourTime column. TwentyFourHourTime column is also varchar. How can I do this. Thanks. ...
I can't find a simple way to do this in T-SQL. I have for example a column (SortExport_CSV) that returns an integer '2' thru 90. If the stored number is a single digit, I need it to convert to a 2 digit string that begins with a 0. I have tried to use CAST but I get stuck on how to display the style in the preferred format (0#) Of cour...
I use the following to implement Random ordered results in Linq2SQL: partial class OffertaDataContext { [Function(Name = "NEWID", IsComposable = true)] public Guid Random() { throw new NotImplementedException(); } } In the following query: IEnumerable<Enquirys> visibleOnSite = En...
Is it possible that a multi-statement UDF return a User Defined Table Type, instead of a table that is defined within it's return param? So instead of: CREATE FUNCTION MyFunc ( @p1 int, @p2 char ) RETURNS @SomeVar TABLE ( c1 int ) AS I would like to do: CREATE FUNCTION MyFunc ( @p1 int, @p2 char ) RETURNS @SomeVar M...
I got a nested query which I'm trying to run; however I have to screen for numbers with a -1 I have the following: update invoices set type = 5 where tranno like dbo._fnStripLeadZeroes(( select invoice_number from [bob.jupiter.com].webap.dbo.billinglog)) + '-1' with invoice_number(varchar15) and tranno(varchar10) am i approachi...
Hi there, I have an table with 2 columns, a date column and an int column and I want to sum the int columns per month where a month start on the 15th So the first result would be from today to the 15 of next month (Jan), the next row will be from the 16Jan to the 15Feb and so on until there are no more dates in the first column Makes ...
I would like to be able to round a number to n significant figures in SQL. So: 123.456 rounded to 2sf would give 120 0.00123 rounded to 2sf would give 0.0012 I am aware of the ROUND() function, which rounds to n decimal places rather than significant figures. ...
Hi, I have a table that contains many rows of SQL commands that make up a single SQL statement (to which I am grateful for this answer, step 5 here) I have followed the example in this answer and now have a table of SQL - each row is a line of SQL that build a query. I can copy and paste the contents of this table into a new query win...
I want to optimize this query WITH CTE AS ( SELECT KladrItemName _KladrItemName , WordPositionKladrItem _WordPositionKladrItem , WordPositionAddressString _WordPositionAddressString , StartPosition _StartPosition , EndPosition _EndPosition , Metric _Metric , IsConstruction ...
I have a table with columns - id, x1, x2, x3,x4 if i have values 1,y,y,y,y 2,y,null,n,null 3,y,null,null,null then say, i want to calculate (1)/(number of y's) and display rows as 1,.25,.25,.25,.25 2,1,0,0,0 3,1,0,0,0 Is it possible to do this using sql query? ...
Is the following possible? I am unable to do so. Do I have to have a permanent table to create index? declare @Beatles table ( LastName varchar(20) , FirstName varchar(20) ) CREATE CLUSTERED INDEX Index_Name_Clstd ON @Beatles(LastName) ...
Hi All, I have a string Variable test=HARIA|123|MALE|STUDENT|HOUSEWIFE i am using | as delimited character. if i want to extract data from 2nd occurence of pipe till 3rd occurrence. i need to get 'MALE' from above string. any help appreciated ...
declare @top int set @top = 5 select top @top * from tablename Is it possible? Or any idea for such a logic (i don't want to use dynamic query)? ...
Please see data given below I want to keep one set of records and want to delete another duplicate set of records. You can see ForeignKey are same just Primary Key is different. Need to keep 2 records having lowest primary key among the set of 4 records. ...
Hi I have a table of q-grams (2 letter strings). I need to join another table to this on the CHAR(2) field. Would it be faster to convert these 2-grams into a number (e.g. aa=1, ab=2, ac=3) and convert the fields in both this table and the referencing table to an INT and join using an INT rather? Kind regards Peter ...
Hello I would like to show a list of all messages with the date of the latest message. I've acomplished the list. SELECT COUNT(*) AS NumberOfTimes, TrackRecord.message_identifier, MessagesInstalledApplications.messageKind FROM TrackRecord INNER JOIN MessagesInstalledApplications ON TrackRecord.message_identifier = MessagesInstalledAppli...