Hi, Why does the following query work, when there is no column named Agentid in the "NewAccounts"-CTE? WITH NewAccounts AS ( SELECT 3 as Newaccountid ), MovedAUM AS ( SELECT 1 AS Agentid, 2 as Col2 ) SELECT * FROM MovedAUM WHERE agentid IN (SELECT Agentid FROM NewAccounts) The following, modified query returns the error mess...
How can I alter Update Stored Procedure so if developer want to set only Size then he/she does not requireed to pass TimeStamp. Moreover, then what will be execute code for this procedure? Scenario: Update TimeStamp = getdate() field whose SizeID = 1 AND Size =Large Note: This field Parameters and condition parameters must be dynamic ...
Hi All, I am planning on using a recursive table in my data model so that I can support an undetermined number of children that can live under a parent. My example is a Table of Contents where I don't know how deep my subsections will be under a chapter. The issue I am stumbling over is what techniques do folks use to populate there ...
I am used to oracle and now been thrown T-SQL, I am doing a course shortly but can you help out until then. I have a list that I need to group in minutes. Sample of rowdate data SELECT ROWDATE,count(rowdate) FROM [mydb].[dbo].[mytable] GROUP BY ROWDATE order by 1 2010-08-16 15:01:18.110 1 2010-08-16 15:01:18.203 1 2010-08-16 15:01:18...
One of my users wants to get data into Excel from SQL 2008 query/stored proc. I never actually did it before. I tried a sample using ADO and got data but user reasonably asked - where are the column names? How do I connect a spreadsheet to an SQL resultset and get it with column names? ...
In my stored procedure, I make a temp_tbl and want to add several columns in a cursor or while loop. All works fine the cursor (the creation of a temp_bl but I can´t add the column when the column string is in a varchar variable. WHILE @@FETCH_STATUS = 0 BEGIN SET @webadressenrow = 'Webadresse_'+CAST(@counter as nchar(10)) A...
I'm sure this is a common request but I wouldn't know how to ask for it formally. I encountered this a long time ago when I was in the Army. A soldier has multiple physical fitness tests but the primary test that counts in the most recent. The soldier also has multiple marksmanship qualifications but only the most recent qualification...
Anyone has written script to automate the restoration process when databases are backed up via SQLSafe? My backups run in this format: SQLSafe Backup (full - weekly) SQLSafe Backup (diffs - daily) SQLSafe Backup (log - every 15 minutes). I wanted to find out of anyone has restore script automation already written for this. Thanks in a...
Hello! I have sql stored procedure which pass and return some values: CREATE PROCEDURE [dbo].[GetMoney] @userId int, @month DateTime AS BEGIN .... Select @something END I want to call GetMoney procedure for each user in users table and use results in sql query. How can i do it? ...
I have Ticker, Dt, [Open], Volume for the input table Dt is yyyy-mm-dd I want to return every row where ticker = 'IBM' and also returns the Ticker, Dt, [Open], Volume fields and then include a max column for only say for the previous x days. Let's say 3 days for the sake of not posting too much data. But I don't know how to get the ...
This is my main table structure: The expertise column is a many to many relationship with another table that has a list of available languages. I wanted to have a flattened structure to display all the languages that a person expertise's, so I wrote a stored procedure that stringifies those multiple expertises to fit for each user. ...
Hello guys/girls, I need to be able to convert a string to a hierarchyid in c#.net - I cannot use stored procedures. When I pass in the path (string) the query fails as the path is stored like this '/' instead of / Can I convert it to another type? SqlCommand command = new SqlCommand("INSERT Structure (Path,Description,ParentID) " + ...
Hi, I have the following in a stored procedure: DECLARE @new_column_name varchar(9) DECLARE @table_name varchar(16) DECLARE @SQLString nvarchar(2000) SET @new_column_name = N'name' SET @table_name = N'tbl_test_table' SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @table_name + '] WHERE [' + @new_column_name + '] = ''' + @de...
Following is the table and script of this table. DECLARE @temp TABLE (PPId INT, SVPId INT, Minimum INT, Maximum INT) INSERT INTO @temp VALUES(1,1,8,20) INSERT INTO @temp VALUES(2,1,21,100) Minimum & Maximum are passed in as parameter. I want to find all rows that fall in the given range. E.g.; If @minimum = 9 and @maximum = 15 t...
What I'm trying to do is create a table that has 2 fields, one being the status and one being a type of equipment. I then need a list of how long these particular types of equipment have been being repaired. There can be multiples of each equipment, so I would like something like this: Equipment Type | Status | 0-7 days | 8-15 days ...
I have one big query for performance related in SQL Server 2005. I have data like this id parentId 1 null 2 1 3 1 4 2 5 4 6 3 I want the order for the records by downline with parentId and id wise like id Order 1 1 2 2 4 3 5 4 3 5 4 6 I don't want to use loop, because loop is creating the problem if high ...
Tomalak posted an excellent response to an existing SO question: http://stackoverflow.com/questions/593898/sql-datediff-advanced-usage/594087#594087 It almost worked for me but I needed to compute the business hours difference between two dates, excluding weekends, even if less than a week had elapsed. My solution adds a while loop (w...
I have a db columns with values like 100.23.24.1, 100.23.24.2, 100.23.24.3 etc. I have to find the last number after the last dot. In this case I want 3. Then I want to increment the last number and generate new entry as 100.23.24.4. How can I do this. Can someone please help? I cannot look for the 100.23.24. pattern because this may ch...
Simple question here. Context: A Transact-SQL table with an int primary key, and a name that also must be unqiue (even though it's not a primary key). Let's say: TableID INT, TableName NVARCHAR(50) I'm adding a new rows to this able through a stored procedure (and, thus, specifying TableName with a parameter). Question: What's the b...
Hey, all. I am trying to figure out if there's a way to identify a "version" of a SP that gets called the most. I have an SP that gets called with a bunch of different parameters. I know that the SP is causing some issues and trying to pin point the problem. Besides capturing calls to the SP and manually sifting through the results, is ...