I am not so familiar with SQL. I have a table in my SQL Server database called Product CREATE TABLE [dbo].[Product] ( [Age] INT NULL, [Name] NVARCHAR (50) NULL, [Id] …
Tag: sql-server-2016
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…
TSQL – Parent Child (1 to zero/many) Grouping/Aggregation
Code (Sample Data Staging): Description: @Emp is the sample Employee table (Unique Employee records). EId = Employee Id FN = First Name LN = Last Name @EmpPhCont is the sample Employee Phone Contact table (Each Emp from @Emp table can have zero, one, or multiple phone numbers here – unique by Emp/Type).…
TSQL – Conditionally “Cross Join” Records
Code (staging sample data): Detail: @LookupTab is basically a filter that will have either 0, 1, or both values (111 – Option 1 and 112 – Option 2). @DataTab is the actual data in the table (can be huge). The Val field in this table can either be 111 (Option 1), 112 (Option 2), or 223 (Both Option…
T-SQL – Count unique characters in a variable
Goal: To count # of distinct characters in a variable the fastest way possible. DECLARE @String1 NVARCHAR(4000) = N’1A^’ ; –> output = 3 DECLARE @String2 NVARCHAR(4000) = N’11’ ; –> output = …
Compare two tables for a matching value from the respective columns and identify records missing
I have two tables and data like below: I want to compare two table’s columns “Type and MatchType” and identify the Ids of first table where Type is missing in MatchType. “Type” and “MatchType” are …
Correct json formatting in ms sql server
I have the following SQL table I wrote this code to format result as JSON, but can’t get it in the desired format. If i name all product columns as name then sql returns an error Use different names and aliases for each column in SELECT list. Sql code: Current output: Desired output: Answer You may try …
Aggregation Of Overlapping Events Problem
I have a list of 1 to N Shifts Each shift can have N events with their own start and end date I want to Aggregate and salami slice these into a common aggregate event, that changes whenever the shifts …
How to look for missing rows within a group?
Consider the data below. I am trying to find situations where in a specific RequestID, there is an Attempt: 0, but Attempt: 2 is missing. I’ve tried looking for Attempt: 0 with a WHERE predicate …
Getting count depending on subquery
In my SELECT statement below, I’m looking to get a COUNT of connections from a table, where connections are defined as 1 connection per unique transactionID. Thus, in my COUNT, I want to check to make …