SQL code simplifying with ISNULL or COALESCE assistance
Instead of using ISNULL or COALESCE you can combine WHERE conditions to single query: CREATE PROCEDURE [GetCount] @Session NVARCHAR(7), @Activity BIT = true AS SELECT COUNT([user].[ActivityID]) AS [Count] FROM [ActivityListing] AS [act] INNER JOIN [ActivityListingDescriptions] AS [actDesc] ON [act].[ActivityID] = [actDesc].[ActivityID] WHERE [act].[ListingSession] = @Session AND ((@Activity = 1 AND...
Combine two rows based on common ID
sql,tsql,merge,sql-server-2012
You can do this by aggregation: select ID, max(ColumnA) ColumnA, max(ColumnB) ColumnB from TableName group by ID ...
Dynamic Where Condition Based on Parameter
Try Select * From Customer Where (CustomerType is null AND @customertype ='') OR CustomerType IN (@Type) You need change your "IN" part , however. Edit: Select * From Customer Where (@customertype ='') OR CustomerType IN (...) To handle all those input in one stored procedure: CREATE PROCEDURE FIND_CUSTOMER_BY_TYPE @TYPE VARCHAR(50)...
Exception “Procedure or function expects parameter, which was not supplied.” thrown even after supplying the parameter
c#,.net,sql-server,stored-procedures,sql-server-2012
sqlparameter reads zero as an object. Be careful to used zeroes, read this post link Must convert zero to integral type first . Your code should look like this. new SqlParameter("@IsPublic", Convert.ToInt32(0)); ...
Inner join with one row of another table
sql,sql-server,sql-server-2012,inner-join
You can use CROSS APPLY to run the right-hand subquery once for each left-hand row: SELECT e.id, edh.dept,edh.date FROM Employee e cross apply ( select top 1 eh.empid, eh.dept, eh.date from EmpDeptHistory eh where eh.empid = e.id order by eh.date desc) as edh ...
How to display data from a specific week?
c#,sql,sql-server,sql-server-2012
Your query is perfectly fine. The problem is the difference of "Week number calculation" in your c# method and the SQL server Datepart function. Your function gives week = 21 where as Datepart gives week = 22. So there is no matching result is your query. Try following to calculate...
How to extract *Area Code* from a phone number in SQL [closed]
You can create a function where you can pass your phone no and it will return you the area code. CREATE FUNCTION [dbo].[f_GetAreaCode] ( @phoneNumber as nvarchar(200) ) RETURNS nvarchar(50) AS BEGIN -- Declare the return variable here DECLARE @areaCodeAbbr as nvarchar(50) -- Add the T-SQL statements to compute the...
How to create “cell blocks” in SQL Server 2012
sql,sql-server,sql-server-2012,ssms
Not sure though but if you are trying to repeat the - character some x number of times then you can use REPLICATE ( string_expression ,integer_expression ) function like below; which will repeat - character 20 times. select replicate('-',20) EDIT: I think you are looking for a built-in text formatting...
Parsing Dates in SQL
sql,sql-server-2012,sql-server-2014
SQL Server 2012 onwards, you can use TRY_PARSE: -- If the culture argument isn't provided, the language of current session is used. SELECT TRY_PARSE('Thu, 4 Jul 2013 09:18:24' AS datetime2) AS 'datetime2'; TRY_PARSE: Returns the result of the expression, translated to the requested data type, or null if the cast...
Verify data integrity for varbinary column
sql,sql-server,image,sql-server-2012
This worked for me: How to export image field to file? The short version without the cursor looks like this: DECLARE @ImageData VARBINARY(max) DECLARE @FullPathToOutputFile NVARCHAR(2048); SELECT @ImageData = pic FROM Employees WHERE id=5 SET @FullPathToOutputFile = 'C:\51.jpg' DECLARE @ObjectToken INT EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT; EXEC sp_OASetProperty @ObjectToken, 'Type',...
how to select a column value as number or vice versa
One way to do it would be using case select case when col = 'yes' then 1 else 0 end as new_col_value from your_table ...
SQL Insert Date Mystery on 2012 from 2005
sql-server,sql-server-2005,sql-server-2012
You can run DBCC USEROPTIONS on the two different SQL box and chek the dateformat. To set that value use SET DATEFORMAT ymd or any other combination like mdy, dmy, etc...
SQL Server 2012 & Polybase - 'Hadoop Connectivity' configuration option missing
sql-server,hadoop,sql-server-2012
Are you sure Polybase is installed and enabled? You should have installed it during the SQL Server installation process and enable the according services....
How to get the value for the primary key, which is filled by a default sequence in ms sql?
You can utilize the OUTPUT Clause. That enables you to output values from a DELETE, INSERT, UPDATE or MERGE statement. You use it like this: INSERT INTO dbo.Sites(Name) OUTPUT INSERTED.SiteId VALUES('test') ...
“Invalid use of a side-effecting operator 'OPEN SYMMETRIC KEY' within a function.” error while opening a symmetric key
sql-server,sql-server-2008,security,sql-server-2012,encryption-symmetric
There are several things you can do inside a procedure but can't do inside a function. Based on Ben Cull's blog, you can get around this limitation by creating a procedure that handles opening the keys and call that before using the function. The procedure: CREATE PROCEDURE OpenKeys AS BEGIN...
MSSQL Automatic Merge Database
sql,sql-server,sql-server-2012
Assuming you can create a linked server on server A that connects to server B (Here's a guide) Then create a trigger on your table, for example table1: CREATE TRIGGER trigger1 ON table1 AFTER INSERT AS INSERT INTO ServerB.databaseB.dbo.table1 select * from inserted More on triggers here. For part 2,...
Unable to execute the create statement generate by cursor
You really should not use a cursor for that. Not only is it possible to do without a cursor, it's simpler: DECLARE @CREATE VARCHAR (max), @TYPE int = 5 SET @CREATE = 'CRATE TABLE TABLE_TYPE_' + CAST(@TYPE as varchar) + '(' SELECT @CREATE = @CREATE + REPLACE( REPLACE( REPLACE([Name], 'WX-',...
dbcontext.entity.select not available in Visual Studio 2013
c#,asp.net,sql-server-2012,linq-to-entities,entity-framework-6
You should add namespase to your file: using System.Linq; ...
Split string and number columns
sql,sql-server,sql-server-2012
I'd use a recursive CTE to do it. WITH SplitCTE AS ( SELECT itemid, LEFT(ClassID,CHARINDEX(',',ClassID)-1) AS ClassID ,RIGHT(ClassID,LEN(ClassID)-CHARINDEX(',',ClassID)) AS remaining FROM table1 WHERE ClassID IS NOT NULL AND CHARINDEX(',',ClassID)>0 UNION ALL SELECT itemid, LEFT(remaining,CHARINDEX(',',remaining)-1) ,RIGHT(remaining,LEN(remaining)-CHARINDEX(',',remaining)) FROM SplitCTE WHERE remaining IS NOT NULL AND CHARINDEX(',',remaining)>0 UNION ALL SELECT itemid,remaining,null FROM SplitCTE...
Combine columns from three different tables into a single column
The term you would want to Google would be: UNION and CONCAT. Note: CONCAT is not supported in prior versions to SQL Server 2012. To get your expected output, I would do this: select concat(cast(col1 as varchar(10)),col2,col3) as FileOutput from table1 UNION select concat(cast(col1 as varchar(10)),col2) as FileOutput from table2...
Insert into Change Log based on Trigger After Update
sql-server,triggers,sql-server-2012,sql-server-triggers
You have a couple of problems in your code: Triggers must handle a set of updates (not a single update) Your trigger does an UPDATE not an INSERT to your ChangeLog Your code should look more like: CREATE TRIGGER PayRate_UPDATE ON Employees AFTER UPDATE AS BEGIN DECLARE @DateChanged SMALLDATETIME SELECT...
Finding records in main table that match records in another table in SQL Server
sql,sql-server,sql-server-2012
Ow, my brain hurts... Replace all of the question marks (3 of them) with the trip ID of the trip where you want to check for similar trips. select distinct s.tripIDFK as tripId from Legs l left join Slices s on l.sliceIDFK = s.sliceId where s.tripIDFK != ? and not...
Calculate total time between two times in sql
I Reversed the condition and it worked, thanks, following is the sql ROUND(SUM(DATEDIFF(SECOND, LogInTime, IIF(LogOffTime IS NULL,GetDate(),LogOffTime) )) / 3600.0, 1) as SystemHours ...
Inserting Dataset + additional columns into a table
sql-server,tsql,sql-server-2012,dataset
You're gonna want to make your union a subquery. INSERT INTO TBL1 SELECT A, B, C, 'USER', GETDATE() FROM ( SELECT COL1 AS A, COL2 AS B, COL3 AS C UNION ALL SELECT COL1 AS A, COL2 AS B, COL3 AS C ) r ...
How to search a column name in all tables in a database in SQL Server 2012? [duplicate]
sql,sql-server,sql-server-2012
Try the below Select distinct object_name(object_id), name from sys.columns where name like '%tax%' or select table_name, Column_name from Information_Schema.Columns where Column_Name like '%Tax%' ...
Where to declare a SET in SQL?
It makes no sense to "set" a variable in a where clause - maybe you mean in the SELECT? SELECT S.subID, C.cKey, @regType = ??? FROM Comp AS C JOIN JData AS J ON J.pKey = C.primary JOIN Sub AS S ON J.fKey = S.pKey WHERE ( ... ) Note...
Data streams in case of Merge
sql-server,ssis,sql-server-2012,sql-azure,sql-server-2014
The answer to your questions a, b and c (if you're using SSIS transformation components in SSIS) is essentially “yes, all new data and existing data required for transformation will flow into SSIS instance, and the resulting merged data will flow out of SSIS instance to the target server”. More...
slq join get sum of amount from first table and sum of cost from second table when second table has more rows then first table by grouping with hours
sql,sql-server,sql-server-2012
one approach is to use a derived table: SELECT CAST([Date] AS DATE) AS [Date], DATEPART(HOUR,i.[Date]) AS HourOfDay, COUNT(i.InvoiceId) AS NumberOfInvoices, SUM(i.Amount) AS Amount, SUM(it.Cost) AS Cost FROM invoice i INNER JOIN (SELECT InvoiceId, SUM(Cost) AS Cost FROM invoiceitem GROUP BY InvoiceId) it ON i.InvoiceId = it.InvoiceId GROUP BY [Date],DATEPART(HOUR,i.[Date]) or...
SSRS Subscription: newly defined fails, old one works
email,sql-server-2012,subscriptions,reporting-services-2012
I deleted subscriptions and recreated them from scratch. And no errors, all works fine. Funny part is that I entered email addresses manually, in contrast with previous attempts on copy&paste them from elsewhere. Still weird....
SQL varchar variable inserts question mark
there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...
Implement more conditions on upsert (user defined table types) in SQL Server?
sql-server,stored-procedures,sql-server-2012,user-defined-types
Myself got an answer. Thanks everyone. This is the latest updated working stored procedure. ALTER PROCEDURE [dbo].[WP_InsertUpdateVendorItemPrices] @inputTable InsertUpdateVendorPrices READONLY AS BEGIN SET NOCOUNT ON; MERGE INTO VendorItemPricing tableVendor USING @inputTable tableTemp ON tableVendor.ItemPartNumber = tableTemp.ItemPartNumber and tableVendor.VendorName = tableTemp.VendorName WHEN MATCHED THEN UPDATE SET tableVendor.VendorPrice = CASE WHEN tableVendor.IsLocked...
Convert SSIS (Visual Studio 2013) to work on SSIS 2012
visual-studio-2013,ssis,sql-server-2012
The short and long answer, unfortunately, is no. If you want to develop SSIS/SSDT for SQL Server 2012, you must use the Visual Studio environment that came with 2012. Here's the long version of the background info. To build SSDT packages for SQL Server 2012, you actually use Visual Studio...
How can I resolve a collation conflict
The problem is probably not on the date (and definitely if we assume that you really are using native types). That leaves the where clause. So, use the COLLATE operator: UPDATE NewFishTrackerProfessional.dbo.LandingHeaders lh SET lh.LandingDate1 = (SELECT CONVERT(DATE, l.LandingDate) FROM FishTracker.dbo.Landings l WHERE lh.TicketNumber COLLATE Latin1_General_CI_AS = ls.TicketNo COLLATE Latin1_General_CI_AS...
How do I Specify Computed Columns in a Table which are based on another Column in SQL Server?
sql,sql-server,sql-server-2012
Only replace MyTable below with the name of your table. The drawback of this method is that it becomes really expensive if working with many rows (e.g. 40 s for 10000). For such a case, you can use a view. (Thanks to @Amit for insisting on the UDF. Sorry, the...
Determine overlapping times between record sets
sql,sql-server,sql-server-2012
You can use inequalities in the join condition to get overlaps. Then use case to get the earliest and latest time: Select (case when p.StartTime > s.StartTime then p.StartTime else s.StartTime end) as StartTime, (case when p.EndTime < s.EndTime then p.EndTime else s.EndTime end) as EndTime from Patient p Inner...
SQL server Delete script order
sql,sql-server,sql-server-2012
Below is an example that inserts deleted entity transactions into a table variable, which is subsequently used to delete the Transaction rows. DECLARE @DeletedMyEntityTransaction table ( TransactionID int ); DELETE dbo.MyEntityTransaction OUTPUT deleted.TransactionID INTO @DeletedMyEntityTransaction WHERE MyEntityID = @MyEntityID; DELETE dbo.[Transaction] WHERE TransactionID IN ( SELECT TransactionID FROM @DeletedMyEntityTransaction );...
Fastest way to add a grouping column which divides the result per 4 rows
sql,sql-server,tsql,sql-server-2012
Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...
create single query to fetch data from tables as seperate rows
mysql,sql-server,sql-server-2012
Maybe this solution helps you even if i don't like it. This returns exactly what you want (UPDATED). SELECT p.product_id, p.product_name, '' as option_id, '' as option_name, '' as choiceId, '' as choice_name FROM `product` p UNION ALL SELECT pr.product_id as product_id, '' as product_name, o.option_id, o.option_name, '' as choiceId,...
Format Numbers in Multiple columns of SSRS Report
reporting-services,sql-server-2012,ssrs-2008
This is really, really stupid; you have to have spaces before and after the ampersand, and if you don't then you tend to get that error message. I don't know why, it's daft as hell but I bet that's it. Catches me out constantly. Try: =FormatNumber(Fields!HoursWorked.Value & Fields!ContactAttempted.Value & Fields!UnableToContact.Value,2)...
how to sort alphanumeric data in sql..?
sql,sql-server,sql-server-2008,sql-server-2012
Try this: SELECT Sizes FROM Items ORDER BY CAST(LEFT(Sizes, PATINDEX('%[a-z]%', Sizes)-1) as numeric(9, 2)) that's assuming your data will always be number followed by at least one alphabetic char. sql fiddle (thanks to matt!)...
SQL Server union not sorting correctly
You need to order & use top 1 in outer query select top 1 from ( select scrr.name, sess.end_time from tbl_session sess inner join tbl_scripts scrr on sess.script_id = scrr.script_id where end_time >= '5-May-2015 14:58:00' and end_time < '06-May-2015 14:58:00' and scrr.script_type in (1,3,4) and sess.operator_id = 95 UNION select...
Compare only DATE from 2 datetime columns - sql
Another way: Select * from Admissions Where DATEDIFF(dd,AdmitDateTime,DepartDateTime)=0 ...
SQL Server Versions - 2012 vs 2014 [closed]
sql-server,oracle,sql-server-2008,sql-server-2008-r2,sql-server-2012
I wonder if we get 2014 version which is 2 generations ahead of the 2008 version, would that cause any problem? Jumping those two version won't cause any problems. I would like to find out if the serial number for 2014 can be applied to 2012. You should check...
Change column values based on another column in same table
sql,sql-server,sql-server-2012
update t set t.amount = t2.amount from your_table t join ( select id, min(amount) as amont from your_table where code = 101 group by id ) t2 on t1.id = t2.id where t.code <> 101 ...
Cartesian coordinate- Select coordinates which fall within an area
sql,sql-server,google-maps,sql-server-2012
Here's how I'd do it. Let's assume that you have a zone table with one row per zone. I would define it like this: create table dbo.Zones ( ZoneID int identity not null, constraint [PK_Zone] primary key clustered (ZoneID), ZoneGeometry geometry ); You'd populate it like so: insert into dbo.Zones...
T-SQL Insert when not in table and when max timestamp Issue
sql-server,tsql,sql-server-2012,max,sql-insert
You could use a CTE with just one column that is the maxtime from tb2. Then join on the CTE and reference it when checking for if its the max or not. WITH aggregateTime (maxTime) AS ( SELECT MAX(STime) AS maxTime FROM tb2 ) INSERT INTO tb1 (id, EMPNO, Street1)...
Return records even if they don't exist in the joined tables
sql,sql-server,sql-server-2012
You need first to cross join and the left join on MemberGroups table. Try this: select g.GroupID, g.GroupTitle, m.MemberID, m.MemberName, case when mg.MembersGroupID is null then 'No' else 'Yes' end as IsPartOfGroup from GROUPS g cross join MEMBERS m left join MEMBER_GROUPS mg on g.GroupID = mg.GroupID and m.MemberID =...
How to go backwards through the previous 12 months?
sql,sql-server,sql-server-2012
DECLARE @date DATETIME = GETDATE() WHILE @date > DATEADD(year,-1,GETDATE()) BEGIN --do marvelous things SET @date = DATEADD(MONTH,-1,@date) END ...
SQL Server : Recursive Advanced Query on two conditional logics Not showing hierarchy relationship
sql,sql-server,tsql,sql-server-2012,common-table-expression
You are on the right track :) Some fixes: You are inserting zero, not null, as ParentId into products 8 and 10 - this is why they are never picked up in your initial #TmpMasterProduct query - you'll need to change these back to NULL e.g. INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut)...
Loop in SQL Server without a Cursor
sql,sql-server,tsql,sql-server-2012
This should reproduce the logic of the cursor in a more efficient way WITH T AS (SELECT *, IIF(FIRST_VALUE([ACTION]) OVER (PARTITION BY WORKER ORDER BY [SEQUENCE] ROWS UNBOUNDED PRECEDING) = '1-HR', 0, 1) + COUNT(CASE WHEN [ACTION] = '1-HR' THEN 1 END) OVER (PARTITION BY WORKER ORDER BY [SEQUENCE] ROWS...
How to use max function with select top in sql
sql,sql-server,sql-server-2012
You can use CTE query. Example: WITH CTEQuery (AccountId) AS ( SELECT TOP 10 AccountId FROM TempAccount ) SELECT MAX(AccountId) FROM CTEQuery ...
How to use IF ELSE in SQL statements
sql,if-statement,sql-server-2012
sorry syntax for if..elseif..else in sql server is IF Begin .... END ELSE IF Begin ...... END Else ... END In Your program if (@Product ='1') Begin if (@CardType=1) Begin if (@[email protected]) > 0) Begin …….. end else begin …………. end end Else if (@CardType=2) begin if (@[email protected]) > 0)...
Need help to transpose rows to columns
sql,sql-server,sql-server-2012,pivot
You can do it with the following query: SELECT col, [CAD], [EUR], [GBP], [USD] FROM ( SELECT SCCY, col, val FROM mytable CROSS APPLY (SELECT 'current', AccountedPremiumCurrent UNION ALL SELECT 'previous', AccountedPremiumPrevious) x(col, val) ) src PIVOT ( MAX(val) FOR SCCY IN ([CAD], [EUR], [GBP], [USD]) ) pvt PIVOT on...
Deleting non matching records from a table prior to linking
The first major step is to backup your database :) And give a try this query, DELETE FROM LandingDetails WHERE LandingId NOT IN (SELECT LandingId FROM LandingHeaders) ...
how to sum the time grouped by individual day in Sql-server
mysql,sql,sql-server,sql-server-2008,sql-server-2012
Can you try this Select play, cast(starttime as date) as date, SUM(datediff(MINUTE, endtime, starttime)) as TimePerDay from cte where starttime >= '2015-05-30 17:11:34.000' group by play, cast(starttime as date) union SELECT 'hockey', DATEADD(DAY,number+1,(select min(starttime) from cte)) as date, 0 as TimePerDay FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number+1,(select min(starttime)...
Photos as Varbinary(MAX) in SQL Server 2012 causes error 502753 in Access 2010
sql,ms-access,sql-server-2012,ms-access-2010,varbinarymax
It looks like you bulk-inserted the raw binary image data directly from the BMP file into the VARBINARY column using T-SQL. Therefore, those images don't have the "OLE wrapper" that is added when an image is inserted via a Bound Object Frame on an Access form. See the my answer...
Not getting the correct SUM values in SQL Server 2012 when using a PIVOT
sql-server,sql-server-2012,pivot,distinct,aggregate-functions
The following query should give you what you want: SELECT Store, TotalSold, [John] AS WastedByJohn, [Jim] AS WastedByJim, [Alex] AS WastedByAlex FROM (SELECT Store, Employee, Waste, SUM(Sold) OVER (PARTITION BY Store) AS TotalSold FROM #Foo) src PIVOT (SUM(Waste) FOR Employee IN ([John], [Jim], [Alex]) ) PVT To understand why you...
One Table - Two Fields - Same value - T/SQL
sql-server,tsql,sql-server-2012
The problem is that you're comparing both of the lines to themselves. I assume you would like to find just the first row, but also the second line (t1) will match to the first line (t2) and that's the second result you get. Not sure what you're doing, but you'll...
Rename table via sp_rename or ALTER SCHEMA
sql,sql-server,sql-server-2012
Based on your edited answer the quickest way to do that is: If you have to include default value to the column ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} [WITH VALUES] and then drop the old column from the table. ALTER TABLE {TABLENAME} DROP COLUMN...
Cannot insert duplicate key row in object 'dbo.ta_Kullanici' with unique index 'IX_ta_Kullanici'.\r\nThe statement has been terminated
c#,entity-framework,sql-server-2012
Have a look at the table in question dbo.ta_Kullanici and see what column the index named IX_ta_Kullanici is on. The index in question is a unique index, meaning that it will not allow any duplicate values for the column(s) Remember that this index could be on any column in the...
Retrieve the Return Result with Chronological Order Based on Parameter
tsql,stored-procedures,sql-server-2012
Without an explicit ORDER BY Statement, SQL Server will determine the order using a variety of means e.g. collation\indexes\order of insert etc. This is arbitrary and will change over time! No Seatbelt - Expecting Order without ORDER BY If you want to guarantee the order of your output, you need...
SQL statement to find values close to each other
sql,sql-server,sql-server-2012
I might use a correlated subquery: DECLARE @tbl TABLE (val DECIMAL (9,2)) INSERT INTO @tbl VALUES (1),(1.25),(2),(4),(4.5),(4.75),(5) SELECT * FROM @tbl a WHERE EXISTS(SELECT 1 FROM @tbl b WHERE b.val <> a.val AND b.val BETWEEN a.val-.25 AND a.val+.25) You could also work an ABS into this which might be more...
Invalid column name 'CalanderDate'. when using a with and a union
I think I've found the problem: As I suspected, it's caused by your mixture of join styles: This on clause is invalid on FREQ.Task = INC.Task AND FREQ.Contract = INC.Contract AND CalanderDate = INC.[EstimatedCompletionStamp] since you are using an implicit join on calander. I think that this should work: SELECT...
Declare table dependency in stored procedure when using T-SQL
sql-server,tsql,sql-server-2012
You can get dependencies to show up for the stored procedure for sections of code that never execute. For example, if you wanted to "declare" a dependency on a table named TestTable, you could use CREATE PROC MyStoredProc AS DECLARE @SQL VarChar(4000) SET @SQL = 'SELECT * FROM TestTable' EXEC...
Update table using random of multiple values in other table
sql,sql-server,tsql,random,sql-server-2012
This is how to do it: UPDATE d SET Prefix = ca.Prefix FROM #Data d CROSS APPLY(SELECT TOP 1 Prefix FROM #Prefix p WHERE d.DataID = d.DataID AND p.Code = d.Code ORDER BY NEWID()) ca Notice d.DataID = d.DataID. This is here to force Sql Server engine to reevaluate subquery...
Group Count in T/SQL
sql,sql-server,sql-server-2012
You can try qith cross apply: SELECT ..., ca.NoUniqueGRPed FROM #TempTab t1 CROSS APPLY(SELECT COUNT(DISTINCT GRP) AS NoUniqueGRPed FROM #TempTab t2 WHERE t1.Value = t2.Value)ca ...
Merge two SQL query results into one result
sql,sql-server,sql-server-2008,sql-server-2012
Use sub-queries inside your select statement: SELECT (select count(*) from VendorItemPricing where VendorName = 'Apple') as AppleSupports, (select count(*) from VendorItemPricing where VendorName = 'HP') AS HpSupports ...
Improve read XML string in sql server
c#,sql-server,xml,tsql,sql-server-2012
You can use XML types and related XML methods .node / .value to achieve this. DECLARE @DeviceIDs XML = N'<Devices><ID value="13" /><ID value="39" /></Devices>' SELECT c.value('@value','int') as DeviceID FROM @DeviceIDs.nodes('Devices/ID') as t(c) ...
SQL Server connection string with “$” in password
powershell,sql-server-2012,connection-string
try this: $CONN_STR = 'Server=SERVER;Database=mydb;User ID=myuser;Password=abc1$4def;' Use single quotes instead that way it wont think you are trying to reference a variable...
INSERT single quoted value for all column types in SQL Server
sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
SQL Server will implicitly convert data types according to data type precedence rules as documented in the Books Online https://msdn.microsoft.com/en-us/library/ms190309.aspx. Integer has a higher precedence so string literals will be converted to integer in your examples. The downside is a little more work for SQL Server to do but the...
Subquery returned more than 1 value. This is not permitted when the subquery follows =,.. or when the subquery is used as an expression
sql,sql-server,tsql,stored-procedures,sql-server-2012
Replacing this (SELECT [reservationno] FROM roombookingdetails) from first insert with this (SELECT Isnull(Max(reservationno) + 1, 1) FROM roombookingguestdetails) and this (SELECT [reservationno] FROM roombookingdetails) from second insert with this (SELECT Isnull(Max(reservationno) + 1, 1) FROM (RoomBookingOccupancy) solved my problem...
TSQL Table Value Function Parameter different behavior if parameter a variable
sql-server,tsql,sql-server-2012
The default length of VARCHAR when declared without a length is 1 or sometimes 30. In your scenario, it defaults to 1. That's why you're only seeing 1 row with t as a result. You should declare @invarchar as VARCHAR(MAX), or simply add a length. DECLARE @invarchar as `VARCHAR(MAX)` See...
Sql query to select 3rd element from data? [duplicate]
sql,sql-server,sql-server-2012
Sql server 2012 supports offset and fetch, so your query should look like this: SELECT * FROM Products WHERE Products.Category ='Fruits' ORDER BY Products.Category -- or whatever column you need the sort on OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY; ...
SQL - specifying a condition in COUNT / returning a value from the same field more than once in one SELECT
sql,sql-server,sql-server-2012
You can, you just need to use a SUM and a CASE statement. Something like this should do it: SELECT u.USER_NAME As [User] , COUNT(ad.ACTIVITY_ID) As [Total Activities Late] , SUM(CASE WHEN ad.DAYS_LATE BETWEEN 1 and 7 THEN 1 ELSE 0 END) As [Upto One Week Late] FROM USERS u...
Dotnetnuke migration from SQL 2005 to SQL 2012
sql-server-2005,sql-server-2012,dotnetnuke,database-migration
I found out a solution. I restored my original version of database and finaly I passed every first and last upgrade of every major version. It worked well then.
Zeroth Row for a OLTP table
sql,sql-server,sql-server-2008,sql-server-2012
Having zeroth row is related to business rule or specifications. For example, you have a data warehouse where you are loading data for customer and by mistake your customer haven't provided any country name then you might need to provide him a default value (which could be anything, in your...
i want to remove un-necessary joins from my following query to increse the efficiency.
sql,sql-server,sql-server-2008,sql-server-2012
At present there is nothing wrong with your current query although you may not find it aesthetically pleasing. If it is running slow you could try adding indexes or these are some alternatives though I'm not saying they are better: Using Cross Apply: select s.customerid , cust.FullName as CustomerName, s.managerID,...
check multiple schedules for missing dates
Using your list of agents you can cross join the agent table to the @SchedWeek table. Then use that to left join to your AgtScheduleDetail table. Similar to WITH cteAgentSchedule AS ( SELECT DateCode, [agentName], [agentId] FROM @schedweek, [Agents] -- combine tables ) SELECT a.DateCode, a.[agentName], (CASE WHEN asd.[agentId] IS...
Using a Table of Column Definitions to Create an Insert Query
At the risk of sounding like Clippy... it looks like you're trying to import a flat file. Is your RAW_DATA coming from a flat file somewhere? If so you might look into using bulk insert: Use a Format File to Bulk Import Data If you are just asking how can...
ASp.Net Identity Role manager
asp.net,vb.net,webforms,sql-server-2012,asp.net-identity
I found a solution here is my code Imports Microsoft.AspNet.Identity Imports Microsoft.AspNet.Identity.EntityFramework Imports Microsoft.AspNet.Identity.Owin Imports Microsoft.Owin.Security Imports Owin Partial Class AssignRoles Inherits System.Web.UI.Page Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim UserManager = New UserManager(Of IdentityUser)(New UserStore(Of IdentityUser)(New IdentityDbContext())) UserManager.AddToRole(DropDownList5.SelectedValue, DropDownList6.SelectedValue) Label1.Text =...
SQL Server 2012 - Dynamic SQL
sql-server,sql-server-2012,dynamic-sql
The datatype of @TableName being VARCHAR(25) is incorrect (or at least a poor choice). Most objects (Tables, Views, Stored Procedures, Functions, etc) have a datatype of sysname which is an alias for NVARCHAR(128). So no, the first DECLARE uses a datatype that is not only not long enough, but...
SQL Transaction Stuck open
You can just execute ROLLBACK TRAN from the same query window. The error didn't abort the transaction because you started the transaction in a separate batch before the GO. If you don't have the same connection available you have to find and kill the transaction. See close/kill transaction....
Symmetric Key Decryption returning values with space between characters
c#,asp.net,sql-server,sql-server-2012,encryption-symmetric
It appears that there are a couple of things at play here: decryptbykey takes a varbinary, not an nvarchar. I'd guess that there is some unwanted conversion going on there. You're converting the result of the decryption to varchar but returning from your function as nvarchar. Again unwanted conversion. ...
SQL update statement:Subquery returned more than 1 value
Your error lies here: if @price < (select Price from dbo.Movies where MovieType = @movietype) You cannot compare a single value (@price) to multiple values, you need to make sure the subquery returns only one. Also, you could just as well rewrite the command like this: UPDATE mo SET mo.Price...
How to use OFFSET and Fetch without Order by in SQL Server
sql-server,sql-server-2012,sql-order-by,fetch,offset
By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...
Can not able to take back up get error Msg 3201, Level 16, State 1, Line 1
sql-server,sql-server-2012,dynamic-sql
A file name with : is invalid, change them to ;. @SQL = '.....' + REPLACE(@BackupPath, ':', ';') + '....' ...
Determine if T-SQL select statement returns any rows
You can just assign @ShowQuotingTool to the count query. This is based on the fact that any count > 0 will set the bit variable @ShowQuotingTool to true and count = 0 will set it to false Declare @ShowQuotingTool as bit SELECT @ShowQuotingTool = (select count(*) from tblAgentsLicensedState where AgentCode...
TSQL filtering by character match
sql,sql-server,tsql,sql-server-2012
SQL Fiddle WITH Cte(login, gw) AS( SELECT login, gw1 FROM TestData WHERE gw1 > 0 UNION ALL SELECT login, gw2 FROM TestData WHERE gw2 > 0 UNION ALL SELECT login, gw3 FROM TestData WHERE gw3 > 0 UNION ALL SELECT login, gw4 FROM TestData WHERE gw4 > 0 UNION ALL...
SQL Query using FOR XML PATH that works right
Similar to @MartianCodeHound I'd use STUFF SELECT t1.eventId, STUFF((SELECT ',' + t3.stringValue FROM TABLE2 t2 JOIN TABLE3 t3 ON t2.valueId = t3.valueId WHERE t2.eventId = t1.eventId ORDER BY t3.stringValue FOR XML PATH('')), 1, 1, '') AS stringValue FROM TABLE1 t1 Here's a test for you DECLARE @TABLE1 TABLE (eventId INT)...
SQL - Group by Elements of Comma Delineation
Check this Post (Function Definition by Romil) for splitting strings into a table: How to split string and insert values into table in SQL Server Use it this way : select b.Item, Count(*) from IEG.vServiceIEG a cross apply ( Select * from dbo.Split (a.resource_list,',') ) b Group by b.Item order...
SQL Server 2012 How to change the data type of a column from bit to datefield?
datetime,sql-server-2012,bit,data-type-conversion,sqldatatypes
you get this error because DF____Person__onvac__59062A42 sql object Depends on onvacation column. You can Find Dependency of Person table by Right Click-->View Dependancy remove that dependent object and try to alter column...
Make rows to columns in SQL Server for each record
You can use ROW_NUMBER() to give each phone number a rank within its customer ID, then use this to PIVOT the data: SELECT CustID, PhoneNumber1 = pvt.[1], PhoneNumber2 = pvt.[2], PhoneNumber3 = pvt.[3], PhoneNumber4 = pvt.[4] FROM ( SELECT CustID, PhoneNumber, RowNum = ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY Phonenumber)...
Updating every row in a table with information gleaned from other tables
Unless I'm missing something in your question it sounds like you want to do a simple joined update which can be done as a set based operation, so there is no need to use loops. If this is the case then this query should be what you want: UPDATE a...
How to reference SQL snippet multiple times in a query?
sql-server,tsql,sql-server-2012
You can construct a CTE that contains a single row with all of your variables, and then reference that in the query: WITH Consts as ( SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19010101', getdate()), '19000101') as FirstDayLastYear, DATEADD(YEAR, DATEDIFF(YEAR, '19010101', getdate()), '19001231') as LastDayLastYear ) select c.FirstDayLastYear, Case when orderDate > c.FirstDayLastYear then...
How regular expression OR operator is evaluated
c#,.net,sql-server,regex,sql-server-2012
If you are interested in what happens when you use | alternation operator, the answer is easy: the regex engine processes the expression from left to right. Taking the pattern you have as an example, ^.{8}|.{12}$|.{4} starts inspecting the input string from the left, and checks for ^.{8} - first...
How to insert records as break times in a select in a Stored Procedure
sql,sql-server,tsql,sql-server-2012
Here is one solution: DECLARE @t TABLE ( StartTime DATETIME , EndTime DATETIME, EmpID int ) INSERT INTO @t VALUES ( '1/06/2015 1:00', '1/06/2015 1:30', 1 ), ( '1/06/2015 2:00', '1/06/2015 3:00', 1 ), ( '1/06/2015 3:20', '1/06/2015 4:00', 1 ), ( '1/06/2015 4:00', NULL, 1 ), ( '1/06/2015 1:00',...
Change date format in SQL Server 2012
sql,sql-server,date,datetime,sql-server-2012
You could probably do something like this if each columns are formatted differently: select convert(varchar,cast('06-02-2015' as date) ,105) col1 ,convert(varchar,cast('02-06-2015' as date) ,110) col2 ,convert(varchar,cast('2015-Jun-02' as date),105) col3 Resultset: |col1 || col2 || col3 | |02-06-2015 || 02-06-2015 || 02-06-2015| ...
SQL Search Query with Dynamic search condition
sql,sql-server,sql-server-2012
I prefer to use CTEs to make the query text more readable. I assume that the table Tx_Product has a primary key ID. At first we convert @SearchString to a table. Then count number of rows in it to get the total number of keywords. You want to find those...