New answers tagged t-sql
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 ...
answered Dec 28 '19 at 16:51
David Browne - Microsoft
19.4k1 gold badge18 silver badges49 bronze badges
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
