New answers tagged

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 ...


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