New answers tagged t-sql
0
Try the following.
select * from tblPunches
where
(select count(1) from tblPunches p2 where p2.userID = tblPunches.userID and p2.punchType = 'IN')
<> (select count(1) from tblPunches p3 where p3.userID = tblPunches.userID and p2.punchType = 'OUT')
Seems to me - if there is an unequal number of "IN" and "OUT" punches for a given ...
1
WITH T AS
(
SELECT
RANK() OVER (ORDER BY dateReceived ) Rank,
p.data
FROM parameterData p where parameterID=@parameterID
and dateReceived between @fromDate and @toDate
and recipe = @recipe
and isActive=1
)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS GroupID,
(Rank - 1) / @groupSize as GroupSize,
AVG(data) as average,...
5
To clarify on @McNets comment: The error isn't produced by SQL Server itself. Somebody created a trigger on the table, a trigger that fires on UPDATE. This trigger apparently does a check for number of rows and if above a certain threshold, it generates this error and does a rollback.
You should try to find who is responsible for the trigger and take it from ...
1
The optional WITH ROLLUP clause can be added to your GROUP BY in order to get the grand total row.
GROUP BY employeeName WITH ROLLUP
To get "TOTAL" as the label, you can use the GROUPING function.
Select
CASE WHEN GROUPING(employeename) = 1 THEN 'TOTAL' ELSE employeeName END
The final query looks like this:
Select
CASE WHEN GROUPING(...
1
When you set a database to single_user, you don't have any control over which session gets to connect, unless you're already connected to the database.
So simply connect before setting single_user to eliminate the race condition:
USE AdventureWorks2017
ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE
0
If you wrap
select
max(x.members_present),
max(x.patient_category)
from
(
<your query goes here>
) as x
group by
x.clientvisit_id,
x.client_id
around your current query you'll collapse those rows into one, removing the blanks.
4
You turned on IMPLICIT_TRANSACTION mode, which will start a new transaction on any statement that writes to the database. And that transaction must be committed or rolled back explicitly, even if the transaction is started inside a stored procedure.
EG
use tempdb
go
drop table if exists t
create table t(id int)
go
create or alter procedure foo
as
begin
...
answered Jun 25 at 14:11
David Browne - Microsoft
24.8k2 gold badges25 silver badges59 bronze badges
10
This is a bug with SQL Server inline functions in 2019 RTM.
DECLARE @Salary MONEY = 100440;
SELECT dbo.ufn_GetSalaryLevel(@Salary)
The expression in the constant scan (with CONVERT_IMPLICIT changed to CONVERT to make it runnable) is
SELECT CONVERT(VARCHAR(10), CASE
WHEN CASE
WHEN CASE
...
1
Unless I have misunderstood, the desired output is the username, game, total number of items bought, and total price paid for the items. This can be obtained by counting the items and summing the price, like so:
SELECT u.[Username]
, g.[Name]
, COUNT(i.[Name]) AS [Items Count]
, SUM(i.Price) AS [Items Price]
FROM UsersGames ...
1
You can use the over () clause, which allows you to return the aggregate value in each row of the results without using group by. You create another subquery using over (), and then compare the value of each row with the minimum value:
SELECT * FROM (
SELECT DepartmentID, AverageSalaries, MIN(AverageSalaries) OVER () AS MinSalary
FROM (SELECT ...
-1
Sorry did not understand the original question, this should return the desired result:
SELECT TOP (1) WITH TIES
DepartmentID,
AverageSalaries
FROM
(
SELECT
DepartmentID,
AVG(Salary) AS AverageSalaries
FROM
Employees
GROUP BY
DepartmentID
) AS [Initial]
ORDER BY
AverageSalaries
DepartmentID ...
0
Your requirement is clear for current sample data.
But there can be so many scenario.
Another approach, same sample data
use tempdb
if object_id('tempdb..radhe','U') is not null
drop table radhe
create table radhe(
[Film Id] int not null
,[Scene Id] int not null
,[Value Id]int not null
)
insert into radhe ([Film Id],[Scene Id],[Value Id])
select
...
0
The "emptyfile" option actually migrates all data from the specified file to other files in the same filegroup.
In other words, EMPTYFILE migrates data from a specified file to other files in the same filegroup.
My guess is that your "file2" is the only remaining file in this filegroup and that's probably why you cannot empty it.
Make sure nothing is ...
1
If it's saying the file is not empty, it's not empty. You need to figure out what's there and either rebuild it on another filegroup manually, or drop it. EMPTYFILE only works on the last file if there are no objects at all, and of course this is not possible on the PRIMARY filegroup.
My first guess is there's a table, index, or partition scheme still ...
0
You MERGE is probably failing due to permissions issues to access the [gtin] tabale. , and then proceeding to the UPDATE statement.
These won't solve access issues, but you won't get funky results:
Add TRY and CATCH blocks as appropriate for your needs (inside the cursor to skip just the offending item, or outside to fail the whole thing along with a ...
2
Use of EXCEPT is the best option, as noted by Brian, but this can be using directly in the MERGE as follows:
WHEN MATCHED AND EXISTS(
SELECT src.*
EXCEPT
SELECT tgt.*
)
Here is a full working example:
DROP TABLE IF EXISTS #Test
CREATE TABLE #TEST (
A INT PRIMARY KEY,
B INT,
C INT,
)
INSERT INTO #Test
VALUES (1, 1, 1)
...
1
DELTE the UPDATE from your trigger
CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
AFTER INSERT, DELETE
AS
BEGIN
When you delete the UPDATE trigger Case 4 and 3 wouldn't fire the trigger again.
So only when you INSERT or DELETE, the trigger would run, and update Cust or insert into AuditEntries
With this AFTER UPDATE TRiGGER you would create a ...
Top 50 recent answers are included
