A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

New answers tagged

0

There's an END and ELSE after your set @DateofTransaction line, but there is no corresponding BEGIN and IF.


0

To anyone, that may come across this problem: The issue was nested dependencies. You need to run sp_refreshsqlmodule starting from the bottom of the dependency hierarchy. If you try to refresh the Stored Procedure, that uses this table type and passes it further, you will get this operand type clash error


0

You could also do it with a left join : select a.PartyId, a.OtherPartyId, a.relType, a.RelStatus from Bla a left join bla b on a.PartyId = b.OtherPartyIdand a.OtherPartyId=b.PartyId and a.RelType=b.RelTypeand a.RelStatus=b.RelStatus where b.RelTypeis null


0

This seems to at least approach a solution for you. You might need to add in some data type detection and conversions to make it work for you though. It also makes an assumption that the first column for the table is the ID column... DECLARE @TableName NVARCHAR(100) = 'dbo.MyTableName' DECLARE @SQLCommand NVARCHAR(4000) ;WITH CTE_Initial AS ( ...


12

is there any way to get that TOP N to not be so heavy? I think there's a little bit of a misunderstanding here about how execution plans work. That number is just the estimated cost, which is a model SQL Server uses to determine what the most efficient execution plan will be. It's not updated at runtime, so even if the operators used very little resources,...


6

Try creating the following indexes (I'm using what I think is something similar to your naming convention for index names): CREATE INDEX DomainTable$DomainIP$IDNum ON dbo.DomainTable (DomainIP, IDNum DESC); CREATE INDEX theDB$idx001 ON dbo.theDB (IDNum DESC) INCLUDE (theDate, [Status], Rate); The first index allows SQL Server to do a seek on DomainIP, ...


5

The most common version of the query to do this would be something like: INSERT INTO PartyRelationships ( PartyId, OtherPartyId, RelType, RelStatus ) SELECT pr.OtherPartyID, pr.PartyId, pr.RelType, pr.RelStatus FROM papr LEFT JOIN PartyRelationships pri ON pr.PartyId = pri.OtherPartyId AND pr.otherPartyId = pri....


6

Using EXCEPT could help you write this query. In short, if any rows of the first select do not have matches with the inverse + the correct RelType & RelStatus , return them. SELECT PartyId , OtherPartyId, RelType , RelStatus FROM dbo.Table EXCEPT SELECT OtherPartyId , PartyId, RelType , RelStatus FROM dbo.Table; DB<>Fiddle Remember that ...


0

If you suspect this to be as a result of 2 transactions occurring nearly simultaneously, then it would appear to perhaps be them overlapping and the first not having committed before the second queries the RPTs table. This will be down to your Transaction Isolation Levels for the queries. Top validate this you could include SET TRANSACTION ISOLATION LEVEL ...


-1

I know its an old question but looks like all the answers are complicated. So this one might be so simple. For SQL Server select case when right(rtrim(f1),1) = '.' then substring(rtrim(f1),1,len(rtrim(f1))-1) else f1 END AS f1 From Table1 For PGSQL and MYSQL select rtrim(Enames,'.') from table_name Hope it helps someone..


0

From your plans I see the problem in this statement: INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID]) select SI.[SurveyInstanceID] from [dbo].[SurveyInstances] SI inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID] inner JOIN #...


0

I managed to find the solution in the first answer to the question here: https://stackoverflow.com/questions/29676432/alphanumeric-sort


2

You need to parse the string to separate the alpha + numeric part. Assuming that it is always alpha first follow by numeric and not the other way round the 2 cross apply split the alpha and numeric part from the string select * from yourtable t cross apply ( select alpha = LEFT(your_col, PATINDEX('%[^0-9]%', your_col)) ...


1

This db<>fiddle shows a mock-up of two ways to solve this (with some highly simplified tables). First, you can create a function that calculates the days between the dates with the conditional check against weekends (and holidays in my example). The function simply takes the order and shipment dates as parameters, counts the number of weekends and ...


0

You don't need to split the data out into separate tables and re-join for this. Simply use OPENJSON to decompose the JSON into a table and use PIVOT to shift the grade point data into columns. The below example code can be seen in action here. Setup example data: CREATE TABLE DataTable ( Id INT, GroupId INT, Week VARCHAR(25), GroupData VARCHAR(MAX)...


2

Ok, here is my revised approach, using NTILE. It actually gives roughly the same as your proposed output. declare @ships table ( shipnr int, portnr varchar(50) ); insert into @ships (shipnr, portnr) values (1, 'A'), (2, 'A'), (3, 'A'), (4, 'A'), (5, 'A'), (6, 'B'), (7, 'B'), (8, 'B'), (9, 'B'), (10, 'C'), (11,'D'), (12, 'D'), (13, 'E'), (14, 'E'), (15, '...


1

Base on your input data, I will do something like this: select I.X.value('(key/text())[1]', 'varchar(50)') as ItemID, I.X.value('(value/text())[1]', 'varchar(50)') as Store from @XML.nodes('values/Nationality') as O(X) cross apply O.X.nodes('item') as I(X); ouptput ItemID Store UK def IE def


0

First, we need to create a working dataset to pre-summarize some values: WITH Dataset as ( SELECT sc.empID as empID, MAX(sc.ars) as ars, -- Avoid extra GROUP BY SUM(ISNULL(ic.lit,0)) as SumLit -- EDITED - fixed incorrect code copy. FROM salesconfig sc LEFT JOIN income ic ON sc.empID = ic.empID GROUP BY sc.empID ...


1

So, you are looking for the difference in Years between the two dates, correct? And you want to show one decimal place in the calculation, correct? Then a simple DATEDIFF() will not work for you. Try this instead: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1) This is also imprecise (because of leap years) but matches ...


0

Test Select case when sc.ars = 0 then 0 else SUM(sc.ars / NULLIF(SUM(ic.lit) OVER (PARTITION BY NULL),0)) end FROM salesconfig sc Left Join income ic On sc.empID = ic.empID


3

From comments: The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / ...


1

You can also group it in inner query. SELECT emp.ID, emp.fname, emp.lname, emp.address1 , emp.address2, emp.city, emp.state, emp.zip, emp.phone, s.Sales FROM ( SELECT emp_id, SUM(sales) AS Sales FROM empPerformance WHERE saledate BETWEEN @startdate AND @enddate GROUP BY emp_id ) s JOIN employeeData emp ON emp.Id = s.emp_id


0

declare @bk nvarchar(1000) set @bk = 'SIS5.0~person_id~696969' ;with hizizzle as ( select left(@bk, charindex('~', @bk)-1) as flippity, right(@bk, len(@bk) - charindex('~', @bk)) as floppity) select flippity, left(floppity, charindex('~',floppity)-1) as floppity, right(floppity, len(floppity) - charindex('~', floppity)) as flooo from hizizzle It was ...


0

If you know the number of items and want to access one or more by its index, you can do something like the following: DECLARE @bk nvarchar(1000) SET @bk = 'SIS5.0~person_id~696969' SELECT CAST(N'<x>' + REPLACE(@bk, '~', N'</x><x>') + N'</x>' AS XML).value('/x[1]', 'nvarchar(max)') [string1], CAST(N'<x>' + REPLACE(@bk, '~', N'&...


0

Found a work around : I have created ps1 file using the script below.... . . . $source = '' $destination = '' @(Get-ChildItem $source -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$destination\CMSOPEN_backup.bak") -force} . . . and then, created bat file which calls this ps1 file, went to sql agent ...


1

I don't believe it's possible, or useful to CREATE MASTER KEY in the master database in Azure SQL Database/Azure Synapse Analytics (formerly SQL Data Warehouse). And the error you're seeing is a bit misleading, as you get that error whenever you attempt an operation in master that is allowed in normal SQL Server, but prohibited in Azure SQL Database. The ...


0

What you're looking for is the SYSUTCDATETIME function, which is used like this: SELECT SYSUTCDATETIME(); It returns the current system date and time, adjusted to UTC time. This seems similar to what the postgres equivalent does (except it looks like current_timestamp includes timezone information, while SYSUTCDATETIME does not). If you need the time ...


Top 50 recent answers are included