FAQ Database Discussion Community


MSSQL Create and Conditionally Increment a Column

sql-server,tsql
I am getting some data (into a #temp table) that has some logical ordering. When I'm pulling the data I'd like to add a new sequence/counter that increments only when certain conditions are met within the other fields. Ideally something like this: DECLARE @counter int = 0; SELECT Item, Date,...

Getting SCOPE_IDENTITY() after insert with select

sql-server,tsql,scope-identity
Im having an issue getting the scope identity from an insert statement. I've done it numerous times before, but always with defined values: INSERT INTO t_table (field1, Field2) VALUES (field1Val, field2Val); SET @id = SCOPE_IDENTITY() How does one do so when you are getting the values for the insert by...

selecting latest result from a table using SQL query

sql,sql-server,tsql
I have the following table: From a select statement what I want is the latest unique rows (in green) for each policy. Some cause the policy information will be from the day before (all policies will not be published on the same day) . In this scenario ACB1 has changed...

Retrieving an XML node value with TSQL?

sql-server,xml,tsql,soap
What am I not getting here? I can't get any return except NULL... DECLARE @xml xml SELECT @xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <SOAP-ENV:Body> <webregdataResponse> <result>0</result> <regData /> <errorFlag>99</errorFlag> <errorResult>Not Processed</errorResult> </webregdataResponse> </SOAP-ENV:Body>...

How do I resolve the “Enter Parameter value” error in MS-Access

sql,excel,vba,tsql,ms-access
This code and another is producing the logical error in Ms-Access where it asks for a parameter equal to an objects name. In this case it wants [DSRT_ERS].[ID]. INSERT INTO DSRT_ERS SELECT * FROM DSRT_TEMP WHERE [DSRT_ERS].[ID] <>[DSRT_TEMP].[ID]; If you look at the DB's documentation you notice it is spelled...

How to check a comma delimited integer values by “IF” condition T-SQL?

sql,sql-server,tsql
A simple table contains the one column with integer vales.The Figure is given below. I am using COALESCE to construct the 'numbers' by comma. So, now there is a problem when i check a above constructed value in IF Condition like below. It shows an error for cannot convert the...

Improve my SQL Select statement to select students who have not fully completed a section

sql,sql-server,linq,tsql
My SQL skills are quite limited. I'm in my second year of computer science at a technical college. I'm building a windows forms application that will allow the BAS director at my college to keep track of students and their progress throughout the courses. I have complete control over the...

Creating a view with a column not in the base table

tsql
I have a table with 4 columns. I am being asked to create a view that performs a calculation and then puts the results in a column not in the table. Here it is: Create a view called v_count that shows the number of students working on each assignment. The...

How to reference SQL snippet multiple times in a query?

sql-server,tsql,sql-server-2012
I need to convert some stored procs to views and the stored procs have a lot of DECLARE statements which create constants that get referenced later in the query. For example SELECT @FIRSTDAYLASTYEAR = DATEADD(YEAR, DATEDIFF(YEAR, '1901-01-01', DATEADD(YEAR, -1, getdate())), '1901-01-01') I need to refer to @FIRSTDAYLASTYEAR in a single...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

c++,sql-server,qt,tsql,odbc
I get this error when I try to call stored procedure in QT using QODBC: QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error Amount of parameters is correct, syntax looks alright to me. The procedure executes fine in Management Studio. What might be the...

Suppress tempdb message when outputting result set

sql-server,tsql,sqlcmd
Using SQLCMD, I am running a script to output to STDOUT then gziping the output. When I look at the output file, I see this warning message: Database name 'tempdb' ignored, referencing object in tempdb. In my script, I have a check at the start of the script to drop...

Joining tables and LEFT JOIN as new Columns in tsql

sql,sql-server,tsql,table,row
In this sample I have two tables containing data for my Customer and Products that a office supply shop has. which is seen below: The Above tables primarily functions to supply the required data for the third table which is the Orders table Using the orders table, I can get...

TSQL XML Comma delimited list

sql-server,tsql
I am trying to get a comma delimited field. The structure looks very similar to the one below. I'd like to get a comma delimited list for all nodes below. DECLARE @Test XML = ' <Order id="orderId"> <Products> <Product>1</Product> <Product>2</Product> <Product>3</Product> <Product>4</Product> <Product>5</Product> </Products> <Address street="1234 City World" zip="12345" city="City"...

SQL Subquery column equals operation

sql,sql-server,tsql
I am trying to do a SQL query for user with certain permission enabled flag. I know, I can do this: select u.ID, u.Name, (select p.Value from Permissions p where p.UserID = u.ID AND p.Key = 'CanEdit') as IsPermissionEnabled from Users u But it's not exactly what I need, can...

Looking to optimize multiple JOINs into one dynamic JOIN - Possible?

sql,tsql
So I currently have a query that pulls in buyer and seller names into a single column value. I am utilizing a CASE statement for if one of the buyer or seller values is NULL, and also since the user can delete the buyer or seller and it retains its...

Getting error “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.” for comparing sales accounts

sql-server,tsql,sap
I am getting the following error when trying to compare 2 columns to another 2 columns based on a sales employee. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. My code is this: select T1.CardCode 'BP Code', T1.CardName 'BP Name',...

Which is more efficient for Selecting records that 'happened' - from 00:00:00 to 23:59:59 of a date in a another record, in the same JOIN

sql,sql-server,sql-server-2008,tsql
I would like to fetch data for records for a given date/ 1 day interval. This means from 00:00:00 to 23:59:59 of that day. The date value comes from another table in a join statement... i.e, it's not a parameter. Suppose I have a Transactions table with a DateOccured (Datetime)...

Merging two or more records in SQL query result with same dates

sql,sql-server,database,tsql
I have a SQL query which basically looks like this: select t1.Date,t1.Earnings from table1 as t1 So the query result is as following: 5.5.2015 20.0 5.5.2015 16.0 5.5.2015 24.0 6.6.2015 15.0 6.6.2015 15.0 My question is: is there any way to merge these 3 dates into one(as they are same...

How to join result of a Group by Query to another table

sql,tsql
I have the following Group By SQL: SELECT s.Login_Name, COUNT(s.s1CIDNumber) FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name I want to know how I can join this result set to another table(tblUsers) to add user_id to the result set (tblSurvey and tblUsers have 1-1 relationship on Login_Name) I tried the following: Select...

updated record is inserting into the history table not the old record

sql,sql-server,tsql
i have two tables Test and TestHistory CREATE TABLE [dbo].[TEST]( [ID] [int] NULL, [Name] [varchar](10) NULL, [Status] [char](1) NULL, [CreatedDate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Test_History]( [ID] [int] NULL, [Name] [varchar](10) NULL, [Status] [char](1) NULL, [CreatedDate] [datetime] NULL ) ON [PRIMARY] GO INSERT INTO TEST ([ID],[Name],[Status],[CreatedDate])values (1,'Mohan','A',GETDATE())...

SQL Like/Contains on BIGINT Column

sql,sql-server,tsql
I have a BIGINT column that I want to do a partial match on. e.g. @search = 1 should return all records where the first number is 1 (1, 11, 100 etc). Basically the same as a varchar LIKE. I have tried: DECLARE @search VARCHAR SET @search = '1' and...

Update recently added column in script

sql,tsql
So I have this script that is supposed to add column, update it from values from other column, delete that other column, and then rename the new column. Here is what I have: ALTER TABLE Certification.ProductEducationCreditTypeTemp ADD NumberOfCredit decimal(18,4) DEFAULT(0) NOT NULL UPDATE Certification.ProductEducationCreditTypeTemp SET NumberOfCredit = convert(decimal(18,4), NumberOfCredits) ALTER...

SQL bypass REPLACE by CASE statement

sql-server,tsql,replace,case,sql-server-2014
I have data where some records contain blank rows (=no space, no NULL). E.g.: LOCALE en-es en-uk uk-uk When I want to select that blank row, it's easy with: SELECT LOCALE FROM ABC WHERE LOCALE = '' But when I try to replace it as follows, it does not work,...

SQL - what's the best way to look up which table a column belongs to?

sql-server,tsql
In a SQL Server environment, while maintaining a SQL query that involves multiple tables, often I'll need to figure out what what table a particular column belongs to. Unfortunately, the notation in the query is not TableName.ColumnName, but just ColumnName. Is there any way to look up what table the...

What is the execute command to use here

sql,sql-server,sql-server-2008,tsql
protected void Page_Load(object sender, EventArgs e) { string sqlConnectionString = @"Data Source=phriz-webapp01;Initial Catalog=PFTracking;Integrated Security=True"; string script = "if not exists(select * from sys.servers where name=N'CNCTC-WEB01')begin exec sp_addlinkedserver @server='CNCTC-WEB01'exec sp_addlinkedsrvlogin 'CNCTC-WEB01','false',null,'svc_Phils','[email protected]' end INSERT INTO [PFTracking].[dbo].[TempTable] SELECT c.[pf_id],a.[RequestDate],c.[pf_carrierUsed],b.[PiecePrice] * b.[PartQuantity] as [Amount]...

SQL Join Views - Duplicate Field

sql-server,tsql,join,view,alias
I am trying to join two views, I have tried to alias the cAuditNumber field under StkSalesUpdated view to AuditNumber1, but I still get the following message: Msg 4506, Level 16, State 1, Procedure KFF_Sales_Data_Updated, Line 3 Column names in each view or function must be unique. Column name 'cAuditNumber'...

SQL Server TSQL - How to achieve “NOT IN” statements within a CASE

sql-server,tsql,stored-procedures
I have the following working code: INSERT INTO #resultado SELECT 1, v.idReqVoucher, v.dtSolicitacao, v.idFuncionario_solicitante, v.idFuncionario_beneficiario, v.idStatus, NULL as valor FROM reqVoucher v WHERE v.idReqVoucher = CASE WHEN @idRequisicao = 0 THEN v.idReqVoucher ELSE @idRequisicao END AND v.idStatus = CASE WHEN @status = 0 THEN v.idStatus ELSE @status END AND v.dtSolicitacao...

Combining two SELECT querys

sql,tsql
I am using Transact-SQL (Microsoft SQL Server 2012). I trying to combine the following two SELECT statements so that the rows from z will be added right next to the union of x and y. But I had no luck yet. The two SELECT statements are: SELECT x.a, x.b, x.c...

How To Get the Sum of a Column within a given Date range in a Table and Update a particular Cell in The Table with The Sum in TSQL?

sql-server,database,tsql,stored-procedures
I have a Table named PersonalLedgerForTheiftFundAndShareClone I want to get The Sum Of TInterest ( TheiftFundInterest ) within The Date Range Of 2014-03-31 00:00:00.000 To 2015-03-31 00:00:00.000 For each Employee and Then Update The TInterest with The Sum Where Date is 2015-03-31 00:00:00.000 For that Specific Employee. I Have Written...

XPath query: parsing multiple paths using the same query (Cross Apply / .nodes() )

sql-server,xml,tsql,xpath
I have a rather big and structured XML receipt which one I want to parse into a relational database. There are some equal structures on different levels, so it'd be very good to parse them using the same SQL statement. Like: DECLARE @XMLPath varchar(127) SET @XMLPath = 'atag/btag/item' INSERT INTO...

Simple Pivot Table with Month and text data Dynamic SQL

sql,sql-server,tsql,pivot
How do you create a simple Pivot Table in Dynamic SQL with the following data: ID Month AssignmentMonth Designation 1 5 May Joe Blow 2 5 May Available 3 5 May Available 4 5 May Available 5 6 June Carry over 6 6 June Christopher Freeberg 7 6 June Ringo...

SQL Server : Recursive Advanced Query on two conditional logics Not showing hierarchy relationship

sql,sql-server,tsql,sql-server-2012,common-table-expression
Recently I got a challenge to resolve in SQL Server 2012. This is the background of the problem. We are maintaining a self referencing entity (hierarchy) in our product entity. Each product is having parent and child relationship. Product is having special grouping called master products which derive based on...

scrypt T-SQL Function

sql-server,function,tsql,scrypt
Does anyone have a tested function that implements the scrypt algorithm in TSQL? I've searched here and the interwebs at large and to my amazement have not found a CREATE FUNCTION statement I can copy and paste. Others doing this search, be warned, that Google will "help" you by thinking...

Find intervals when flag was active

sql,sql-server,tsql,sql-server-2008-r2
I need to find out from a table below when flag inserted was active (inserted = 1). I need to show interval date_from to date_to when inserted was active. Here is the table: no dcr action deleted inserted --------------- ----------------------- ------ ----------- ----------- 191005040000806 2014-04-17 10:39:59.790 U 1 0 191005040000806...

How to use field value of joined table for join rather than foreign key

sql-server,tsql,join
I have a new database schema that I need to query and I am having trouble getting the data I need without using a primary key in the JOIN. I didn't design the schema but I have to work with it. I tried creating a SQLFiddle for this but it...

How to access another database located on a different server in a SP? [duplicate]

sql-server,sql-server-2008,tsql,stored-procedures
This question already has an answer here: Selecting data from two different servers in SQL Server 7 answers I am working on a stored procedure that has been written a few years ago. The stored procedure accesses another database in the join query as shown below, select * from...

How contact Column table in T-SQL?

tsql
This is my table user I want a contact column in one record I am usinng this query but it dont work, seprate colume with ',' please help me. id CourseName 1 ali 2 saeed 3 amir i want this result CourseName ali,saeed,amir DECLARE @coursename nvarchar(200) SET NOCOUNT ON; DECLARE...

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures
I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...

sql query to join and concat values

sql,sql-server,sql-server-2008,tsql,sql-server-2005
sql server: 2012 These are 3 tables. First table is the user's (consultants) table The second one is the prices table, each user from the first column has a price The last table define in what area (city) the consultants offer services Consultants Prices Area uid | Name uid |...

How can I execute/call a MySQL stored procedure using SQL Server 2008 R2

tsql,sql-server-2008-r2
How can I execute/call a MySQL stored procedure using SQL Server linked server? Here is the SQL I have so far. select * from openquery(linked_server_name, 'call sproc_name()') ...

How to display character values for numerical data for many columns

sql,tsql
This is a construction question. Usually when you have data in an statistic program format (R, SPSS) you have a label for each value (called a value-label). Let us say we have tree questions that are answered by three persons Q1|Q2|Q3 Person1||2 |1 |1 Person2||3 |1 |2 Person3||3 |2 |3...

SQL Server Stored procedure declaring variable in different way

sql-server,tsql,stored-procedures
I'm just started using MSSQL and need to understand. While I was reading few sample code that I might use in near future found CREATE PROCEDURE [dbo].[SP_Set_LMS_BY_ERW] @UPA nvarchar(30) = NULL Question is what's the difference between @UPA above Declare @count nvarchar(30)? Thank you...

How do I convert this tSQL statement to LINQ using group by in a sub query

c#,sql-server,linq,tsql
I have the following MSSQL query I am trying to convert to LINQ. I am using entity framework with the following syntax to get at the data. var rv = (from i in DC.TableA select i).ToList(); This is the sql I want to write a C# LINQ query for but...

SQL Server query with case in select statement with multiple INNER JOINS

sql-server,tsql,select,case,inner-join
I have a query with returns a set of data about projects. The issue however is that some of the projects don't have a valid currency id. So i'm trying to build a query that will, if the currency id is 0, use the id 140 instead. This is what...

Improving work with SQL DataTime

sql,sql-server,database,tsql
I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

SQL Update Trigger for writing entire row to another table

sql-server,tsql,triggers
I posted a question yesterday and this is related. I have a table called People_Master CREATE TABLE [dbo].[People_Master]( [ID] [int] IDENTITY(900001,1) NOT NULL, [Email] [nvarchar](50) NULL, [FirstName] [nvarchar](50) NULL, [uac] [int] NULL, [department] [nvarchar](50) NULL, CONSTRAINT [PK_People_Master] PRIMARY KEY CLUSTERED ( [ID] ASC ) And I have created another table...

Adding Collation to a SQL Server CTE statement

sql-server,tsql,collation
I am trying to determine how to add COLLATION to this operation.... WITH CTE (srtTxt, DuplicateCount) AS ( SELECT srtTxt, ROW_NUMBER() OVER(PARTITION BY srtTxt ORDER BY ID) AS DuplicateCount FROM dbo.tblLang **WHERE DuplicateCount > 1** ) DELETE FROM CTE WHERE DuplicateCount > 1 GO This is the setting I am...

T-SQL: Combine rows to one row

sql,sql-server,database,tsql
I have the following table: number word ====== ==== 1 AAA 2 BBB 2 CCCC 4 CCCC 4 BBB 4 AAA Now I want to create a new table, where a "number" only occurs in one row. The corresponding values in "word" should be converted to comma sepeareted string. The...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

combine single rows into column in sql server

sql-server,tsql
ID NAME INPUTS 2 ABCD First Name 2 ABCD Last Name 3 1234 First Name 3 1234 Last Name 6 QWERTY First Name 6 QWERTY Last Name Above is what the Select statement returns. I would like the select statement to return one row per ID combining the last column...

Split comma separated string table row into separate rows using TSQL

sql,sql-server-2008,tsql
Say I have a query that returns the following ID SomeValue 1 a,b,c,d 2 e,f,g Id like to return this as follows: ID SomeValue 1 a 1 b 1 c 1 d 2 e 2 f 2 g I already have a UDF calls Split that will accept a string...

XML Query with a variable not working

sql,sql-server,xml,tsql
declare @xml xml = '<ChangeSet DBSchemaVersion="2.0.0.88" DBSyncVersion="15" ScopeId="1"> <C op="I" PK="44"> <User UserId="44" Role="3" UserName="Dummy1" /> </C> <C op="I" PK="45"> <User UserId="45" Role="3" UserName="Dummy2" /> </C> <C op="I" PK="46"> <User UserId="46" Role="3" UserName="Dummy3" /> </C> </ChangeSet>' -- this works fine SELECT [SyncTable].Col.query('User') FROM @xml.nodes('/ChangeSet/C[@op=''I'']') as [SyncTable](Col) FOR XML AUTO --...

How to get Duplicate records from single table depends on column value?

sql-server,tsql,sql-server-2008-r2
I got table like this: declare @t1 table(Pat_Ref int,Fname VARCHAR(20), Sname VARCHAR(20),minor VARCHAR(1),SourceSys VARCHAR(40),regdt datetime) insert into @t1 values (111,'John', 'Wayne','N','ick','2015-06-09 21:31:09.253') ,(111,'John', 'Wayne','N','ick','2014-05-09 21:31:09.253') ,(111,'John', 'Wayne',null,'hpk','2015-04-09 21:31:09.253') ,(112,'Jill', 'Smith','N','ick','2015-01-08 21:31:09.253') ,(112,'Jill', 'Smith',null,'hpk','2015-05-01 21:31:09.253') ,(113,'Bill', 'Peyton','N','ick','2015-06-09 21:31:09.253') ,(114,'Gill',...

How to filter a SELECT result with IF condition?

sql,sql-server,tsql
I have a SQL query that returns all employees. It gives me an error because it expects after the WITH( ), it expects a SELECT statement. But if I would like to filter the result a second time, how do I work around using IF-ELSE IF? Note: My SQL might...

many rows into a single column with SQL

sql,sql-server,tsql
I have an one to many table, and if there is rows that have same reference id(Paragraph ID) I want to concatenate so LoginName value have many in same row. This query does what I want it to do but there is a problem, It replaces first char. the STUFF...

Print the return value of user-defined function

function,tsql,return-value
I simply want to do this something like this for testing the function: PRINT fnctGeneric([argument]) I've searched around but can't find anything. Tried https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=SQL+Print+function+return+value -- https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=SQL+Print+return+value+from+user-defined+function --...

Pivot sample with dynamic columns

sql,sql-server,tsql,pivot
I've seen many pivot examples but I didn't find any that applies to my case. I hope someone can give me a hand. I have the these two tables Entities Performance +----------+--------+----------+ | idEntiry | idWeek | idResult | +----------+--------+----------+ | 1 | 1 | 1 | | 2 |...

Calculating the duration between two dates in sql

sql,sql-server,sql-server-2008,tsql
I'm having some difficulty with an SQL query for reporting machine efficiency from a CNC monitoring system. The monitoring software records the duration the machines are in different states like machining, stopped, out of work setup etc. I want to total up the duration of these states for each machine...

SQL stored procedure: increment months from a starting date to an end date

sql-server,tsql,date,stored-procedures,cursor
I'm trying to create a stored procedure in SQL Server that basically increments the months from a given start date up to a given end date and updates them into a predefined table. Problem: Somewhat like this: Exec MonthRunner @Start ='2014-01-01', @End = '2014-06-01' Should give me a table like...

What is difference between join syntax in T-SQL [duplicate]

sql,sql-server,performance,tsql
This question already has an answer here: ANSI vs. non-ANSI SQL JOIN syntax 6 answers This is my SQL query: SELECT last_name, department_name FROM employees e, departments d WHERE e.department_id = d.department_id; And: SELECT last_name, department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; What is...

Show different fields on one when, despite multiple IDs

sql,sql-server,sql-server-2008,tsql
This is how the table looks: CountryId CountryName MemberType 143 AUT 1 171 AUT 2 202 AUT 3 206 BE 1 And this is the result I get when i run my query: CountryName IsType1 IsType2 AUT Y N AUT N Y AUT N N BE Y N My problem...

SQL query sum unique records

sql,sql-server,tsql,sum
I'm trying to sum all sales of one period of time of a selling vehicle. The problem is that every product sold is one row whit amount and price and a total of the bill and the bill number. So I have 2 options: multiply ever sold product amount whit...

How to remove duplicate ID rows. While removing, use the rows that has NULL value in another column

sql,sql-server,tsql
While removing duplicate rows with same ID value, how to remove the rows that has null value in one particular column. Note: there are other non-duplicate rows (below e.g., 12) that has NULL value and should still get selected in the result set. Input table: Id | sale_date | price...

Want to count Distinct values in column 1 based on column 2 values

sql,sql-server,tsql
Here is my sample data for the select statement banner_id subject_code N00012301 MATH N00012963 ENGL N00012963 MATH N00013406 ENGL N00013406 ENGL N00013406 MATH N00013998 ENGL N00016217 MATH N00017367 MATH N00017367 ENGL N00017833 MATH N00018132 MATH N00019251 ENGL N00019251 ENGL N00019312 MATH N00019312 ENGL N00019312 ENGL N00020261 ENGL i want count...

Combining result rows

sql,sql-server,tsql
I'm trying to get the result below to be displayed group by Article. Would this be possible? So that in the end I will only have 2 lines. The data from TYPE I & W will be combined. The image to my query as below The query as below SELECT...

How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql
I have two tables, one an abbreviated calendar: create table dbo.calendar ( [date] datetime ) insert into dbo.calendar values ('20150101 00:00:00 AM') -- 1/1/15 insert into dbo.calendar values ('20150102 00:00:00 AM') -- 1/2/15 insert into dbo.calendar values ('20150103 00:00:00 AM') -- 1/3/15 insert into dbo.calendar values ('20150104 00:00:00 AM') --...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...

SQL Query using FOR XML PATH that works right

sql,tsql,sql-server-2012
I have read the many posts regarding collapsing multiple table columns into a single column using FOR XML PATH, but I am not a database person and cannot make other posts' suggestions work. I expect my problem is easy to solve for an experienced SQL DB person. I have MS...

TSQL update value with subquery

sql-server,tsql,sql-update,compare
I have 2 tables and want to compare them and modify tableA (set NameMod = 1) if it has different rows. To compare tables I use: select Id, Name from tableB except select Id, Name from tableA And then I want to modify tableA: update tableA Set NameMod = 1...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy
I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...

Using a case statement to set the values of declared variables

sql,sql-server,database,tsql,case
I wanted to know if its possible to set the values of declared variables by using a case statement. example: CASE WHEN fieldValue ='stringValue1' THEN SET @DeclaredVar1 = 100 END ...

SQL-Server Verify SHA2_512 hash procedure

sql-server,tsql,encryption,cryptography,sha512
Consider this method declare @pswd nvarchar(max); set @pswd = '2YKRCqHv'; Select orig = a.Hash, hashA = 0x0200 + a.Salt + Hashbytes('SHA2_512', cast('2YKRCqHv' as varbinary(max)) + a.Salt), hashB = 0x0200 + a.Salt + Hashbytes('SHA2_512', cast(@pswd as varbinary(max)) + a.Salt) from DB a where a.Hash = 0x0200 + a.Salt+ Hashbytes('SHA2_512', cast('2YKRCqHv' as...

Number of records per day without timestamp

sql-server,tsql
I am trying to query the number of quotes we receive per day compared to last 14 days. The first query I tried was: SELECT CreatedDateTime, count(CreatedDateTime) FROM dbo.Quotes WHERE CreatedDateTime >= dateadd(day, datediff(day,0,GetDate())- 14,0) GROUP BY CreatedDateTime ORDER BY CreatedDateTime DESC However I think the timestamp on the data...

Value in each column of table

sql,sql-server,tsql
May i get help on this, not sure how to achieve this. I have a table with 11 columns. column 2 to column 10 is consists of specific attribute and attribute_value. for eg: Diameter--> DVALUE Width-- > WVALUE Resultset should consists of as Result 1| Result2| Result 3... Result 1,...

Does SQL Azure support REPLACE T-SQL function with cyrillic characters?

sql-server,tsql,azure,sql-azure,management-studio-express
I am trying command like USE [aaa] GO SELECT [Id] ,REPLACE([BlaBlaField], 'xyz','') FROM [dbo].[aaa] GO through SQL Management Studio on SQL EXPRESS and SQL Azure Web Edition DB. In SQL Express it works, but on Azure it does not. I searched on the web at 'Azure SQL Database General Guidelines...

SQL loop through check

sql,sql-server,tsql,case
I'm having a slight issue creating an SQL query. I have a table with 4 columns 'Enquiry Date' , 'Taken Date' , 'Indication Date' , 'Cancelled Date' the table is called tbl_Sales. What i would like to do is create a bit a t-sql that says look at the 4...

Retrieve the Return Result with Chronological Order Based on Parameter

tsql,stored-procedures,sql-server-2012
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1...

SQL Procedure: Generating random password and storing it in database

sql-server,tsql,stored-procedures
What I want to do is to create a random password and save the hashed password to db. I am using the "random password create procedure" from this site. And here is how I save the hashed version of it to the datbase. declare @salt varbinary(4) = CRYPT_GEN_RANDOM(4); declare @hash...

Increment each field in a column by 1 - SQL Server 2008

sql,sql-server,tsql
I need to increment each field in a column by 1 starting from 2866. I've built the following query, but all it does is set each field to 2867 as opposed to 2868, 2869 etc... DECLARE @a int SET @a = 2866 UPDATE view_kantech_matched SET image_id = @a + 1...

SQL Server : coalesce, the part of string is missing

sql-server,tsql,coalesce
I have this code: declare @results varchar(500) select @results = coalesce(@results+', ', '') + convert(varchar(12),k.t1) from ( select '('+cast(count(distinct(g.RoomID)) as varchar) + ') '+ rt.ClassName as t1 from db_pms.Guests g left join db_pms.RoomTypes rt on rt.RoomTypeID=g.RoomTypeID where g.GroupID = 47 and g.Status >= 0 group by g.RoomTypeID, rt.ClassName ) k...

Unpivot dynamic table columns into key value rows

sql-server,tsql,data-migration,unpivot,cross-apply
The problem that I need to resolve is data transfer from one table with many dynamic fields into other structured key value table. The first table comes from a data export from another system, and has the following structure ( it can have any column name and data): [UserID],[FirstName],[LastName],[Email],[How was...

TSQL, join tables base on non-primary key and Left table remain the same

tsql
How should I query to get my expected result? Table A +----+-------+-------------+------------+ + ID + Model + Description + Material + +----+-------+-------------+------------+ + 1 + M1 + CHASIS + AC001 + + 2 + M1 + SCREW + AS001 + + 3 + M1 + SEAL + ASE01 + +...

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

organize sql heirarchy

sql,tsql,hierarchy
I have the following table: Id Son RowOrder Technology 1 8 NULL fa 8 0 NULL fa 9 15 NULL gr 15 0 NULL gr I would like to create an sql query that will do an "order by" by the following order: technology, "father" (a record that has a...

Select a specific date for the current year

sql,tsql,sql-server-2008-r2
I have a query that needs to select {current year}-05-31 00:00:000. Is there a way I can do it without just concatenating the year on to that string? Here's my current query: SELECT DATEADD(dd, 30, DATEADD(mm, 4, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))) ...

Finding a gap in dates

sql,sql-server,sql-server-2008,tsql,stored-procedures
I have a table with 3 columns: Date smalldatetime not null Val1 decimal not null Val2 decimal not null And a lot of data in this table. Not all calendar dates are presented in the table. How can I find all places where the dates gap is more than x...

Date Diff in calculated field

sql,sql-server,tsql
I am trying to create a computed column to display the number of days between now and a date in another field. I have tried the following: DATEDIFF(d, @Now(), [NextServiceDate]) But I get the following error: 'Assets' table - Error validating the formula for column 'DaysTillService'. I have tried without...

TSQL return distinct rows based on the preferred value of a column

sql,sql-server,tsql
When I run the following SQL against a SQL Server database: SELECT t1.id, t1.name, t2.status FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id = t2.id I get the following results id name status ------------------- 1 bob active 1 bob draft 2 jim active 3 ted draft I'm trying...

One Table - Two Fields - Same value - T/SQL

sql-server,tsql,sql-server-2012
Source: CREATE TABLE #TempTab (ID INT, Value INT, Value1 varchar(10), Value2 varchar(10), value_ INT, Value1_ varchar(10), Value2_ varchar(10), Value3_ varchar(10), Limit INT) INSERT INTO #TempTab SELECT 1, 1,'One','One',1,'One','One','Yes',2 UNION ALL SELECT 1, 1,'One','One',1,'One','Two','No',2 Current query try: SELECT t1.ID, CA.Value3_ FROM #TempTab t1 CROSS APPLY( SELECT Value, Value3_ FROM #TempTab t2...

TSQL Table Value Function Parameter different behavior if parameter a variable

sql-server,tsql,sql-server-2012
I have a table value function in TSQL that works well if I pass a string but not if I pass a varchar. For example, this works: SELECT * from [dbo].[SplitString]('ta;aa;qq', ';') This does not work: declare @invarchar as varchar set @invarchar = 'ta;aa;qq' SELECT * from [dbo].[SplitString](@invarchar, ';') If...

Merge Into with no rows

sql-server,tsql,merge
Is there a way to use the instruction: MERGE INTO MySchema.MyTable AS Target USING (VALUES ........ ) with nothing instead of the dots? Usually you have there something like a list of (firstValue, SecondValue,...,LastValue), one for each row you want to merge but I'd like to be able to write...

How to assign variable if Cursor returns Bit of 0

tsql,cursor
I have written a cursor to search through a table looking at one bit value. If all values are 1, I send an email. But if one value is 0 in any row, I don't send the email. The issue that I am having comes in my If statement. In...

Building totals based on condition in another row

tsql,data,pivot
Really struggling to understand the best way of doing this... I have a table of data **StudentID AssessmentCode ResultGroup Result** 46933 12ENG IBLevel HL 46933 12ENG Mark 6 46933 12ECO IBLevel HL 46933 12ECO Mark 5 46933 12GEO IBLevel SL 46933 12GEO Mark 6 46933 12LAN IBLevel HL 46933 12LAN...

Create a new column in my SQL View - Column must be two other columns combined

sql,sql-server,tsql,concatenation
I am trying to create a new view, but I also want this view to have a new column. The new column must be StkCode and Description_1 combined. For example: If my StkCode is DX11122 and Description_1 is Vanilla Powder; I want a field that will read as follows: "Vanilla...

Convert row to columns SQL dynamically [duplicate]

sql,sql-server,sql-server-2008,tsql
This question already has an answer here: Pivot Dynamic Columns, no Aggregation 1 answer Simple way to transpose columns and rows in Sql? 2 answers I have a table Dev with the data below YYYMMDD Atest BTest CTest 20150525 100 200 300 20150526 110 210 310 20150527 120 220...

SQL - Group by Elements of Comma Delineation

sql,tsql,sql-server-2012
How can I group by a comma delineated list within a row? Situation: I have a view that shows me information on support tickets. Each ticket is assigned to an indefinite number of resources. It might have one name in the resource list, it might have 5. I would like...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql
My table looks like this: CREATE TABLE MyTable ( TableID INT IDENTITY NOT NULL, ForeignID INT NOT NULL, Value sql_variant NOT NULL, CodeOne VARCHAR(4) NOT NULL, CodeTwo VARCHAR(4) NOT NULL ) I'm trying to do a insert with the following code: INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo) VALUES ( 1,...

Compare multi values against multi values, maintaining performance

c#,performance,linq,tsql,database-performance
I have two forms of records or data. One being Inventory Record and the other a Product. Both the Inventory Record and Product Record have Code values stored in separate tables. Each record has a value record and is foreign keyed to the main table. I am trying NOT to...