I am using SQL Server 2019 and looking for a way to show ONLY the latest value for each AOC based on its latest FW_Version. Here is my query I have so far but this shows everything: What I would like to show ONLY items marked in yellow… How can I make this happen? Here is the list of my
Tag: tsql
Find value that can be 0 or can be greater than 0 but must have at least one record equal 0
I need to find records that equal 0 and have other records that are greater than 0. The result should have at least one record that equals 0 and must also have at least one record that is greater than 0. It is easier to explain it with visuals: It should not look like: I have tried this: Sorry if
Inserting rows based on conditions
Table Name: CaseHistory I’m currently working with a dataset that aims to offer insights on customer support cases on a weekly basis. In the example above, you can see the progression of case 123376 from Week 1 till Week 5, amongst other cases. What I would like to be able to do is insert a row for a case for
How do I generate random number with a range X times in SQL SERVER
I want to generate a random number X times with a range. I’ve tried to create a function: CREATE FUNCTION Random_Number (@Times INT, @Upper BIGINT,@Lower BIGINT, @randomvalue numeric(18,10)) RETURNS …
How can I determined which users with a specific RoleID that’s not been active within a time interval? And which home department do they belong to?
This script below will tell me how many non active users with a certain RoleID there’s been within a timeframe. When I run the script below. It will also tell me which home department each user …
Query for Exam Score calculation
Problem Statement: I have an EXAM table which has following fields and sample data I want to write a SQL query to output the following fields My SQL Query: Can I achieve my result by writing a better SQL query? Answer You could indeed join the table with several aggregate subqueries that compute the relevant values at student and exam
TSQL – Union records based on partial uniqueness
Sample Data Currently inserted data: Goal: To insert record into the table variable (or cte/temp table) based on uniqueness ONLY ON [Id] field. “UNION” would not do that because, at minimum, [Group] is going to make duplicate records based on just [Id]… unique. Oh, and [Group] value basically indicates the level of importance, 1 = these records should be inserted
SET .. WHERE .. within batch throwing an error in SSMS v18.4
I am revisiting MS SQL after a few years, so apologies for a basic question! I am performing some address data cleansing, to expand abbreviations like DR into ‘DRIVE’ RD into ‘ROAD’ etc to help with identifying duplicate addresses. I am exploring ways to optimise my query by taking my existing block of 10 sequential SET .. WHERE .. statements
How to select two values in one row?
Seems extremely basic, but I can’t find an elegant solution to it. I have two SQL tables, say one contains the users, and the other one, the cities. A third table contains the matches between the two …
Group by values that are each multiple of number
This is the table t. I want to group it every time the TotalQty >= 5n (let n = group). i.e. once the TotalQty >= 5n I want to sum together the qty from n-1 to n. With this query I can get pretty close but I doesn’t consider the the previous “valid” TotalQty + 5 — How do I