I need help to develop the proposed task: I need to count the current month active employees and then generate a “forecast” for the next six months, the forecast is just the last total active from the current month over the next six months, just changing the date value on the result. Expected Resu…
Tag: sql-server
Running assignment of values with break T-SQL
With the below table of data Customer Amount Billed Amount Paid Date 1 100 60 01/01/2000 1 100 40 01/02/2000 2 200 150 01/01/2000 2 200 30 01/02/2000 2 200 10 01/03/2000 2 200 15 01/04/2000 I would like to create the next two columns Customer Amount Billed Amount Paid Assigned Remainder Date 1 100 60 60 40 01…
Calculate SUM from one table and display columns from another
I have 2 tables, Employee, and Transaction Transaction tID cID carID eID tDate PickupDate ReturnDate Amount_Due Employee eID fName lName Job Manager Hired I need to calculate the commission (2.5%) and display that along with fName and lName. I’ve calculated the commission, I think I’ve done the jo…
Detect duplicate JSON nodes in JSON parameter before insert in SQL Server table
I want to detect duplicate nodes from JSON before insert them in SQL Server table. I have this stored procedure that takes a JSON parameter like this: I want to return exception if @SalaryItems has duplicate key How can I detect duplicate in this table in my code? Answer Use OPENJSON() without a with clause s…
Track state of time series event in SQL column
This seems really simple but I can’t figure it out, working in SQL Server. I have time series data and I want a column to track the state of ON/OFF events for each row, so when there’s an ON event then the Desired Output column will have a 1 for each subsequent event until there is an OFF event. P…
Find matching pattern
I have a table that contains multiple strings pattern (‘Q88 9WE’,’S9 D2P’,WC2R 3LS etc..). How do I extract and split the rows that meet only the pattern Where A is a character in the range A-Z and N is a digit 1-9 I have tried using the logic below and it doesn’t seem to work I&…
Cannot evaluate whether a SUM is null or even greater than 0
I have a simple query here: When I pass an IssueId that does match the criteria, the CASE works, but when I willingly pass an IssueId that I know will return no rows, 0 is not being returned. I get this in SQL studio: The idea here is to determine whether the sum is NULL, 0 or greater so a
Query nanoseconds from first entry
I have a data set keyed with a TestID and a datetime2 value where there are about 8000 entries per TestID, each is about 100 nanoseconds apart. I’d like to query all rows for a given data set (#40 in this partial example) and return the nanoseconds from the first record. Like this: I have a query to do …
Using multiple nested OR statements in WHERE clause makes query return incorrect results
I have a WHERE clause that has a nested OR statement, as seen here: The @NeedsFollowUp, @NeedsInitialVacc, and @NeedsTraining variables are all set by the variables above. When one or more of these are set to “1”, the query should return employee entries that match the criteria inside their relate…
Loop to create new columns
Say that I have a SQL Server table which has 4 columns MonthEnd, Gender, Weight and Age. Now, the MonthEnd go from 201901 through 201912. I have a process where a run a query which generates a variety of metrics, for instance sum(Weight) and average(Age) for each month. Right now, I set a where MonthEnd = 201…