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 ca…
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 …
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 basicall…
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 …
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