I am working on creating a SSRS report and I want to get the Average of the HourlyPayRate Column that I have(in my case statement). I am a self learner of SQL and was wondering how I can get the AVG …
Tag: sql-server-2016
How can I assign unique value to each duplicate value in a column in a table
I have data in my table as below Now I want to generate unique rank value to each duplicate value like 1,2,3,4 etc as below How to do this in SQL Server 2016?
Indexed View vs. Table
I had a long running query (relative to the data) that was hitting an indexed view. I thought an indexed view was physically stored data? I dumped everything into a table and ran the same query …
Daily snapshot table using cte loop
I need daily snapshot of how many employees are employed on any given day (defined as @date between Start_Date & End_Date), with one line per date. At one point, the below script would return all …
group by JSON Column that contains an array
I have the following table: that contains these data: How can I group by my array items in my JSON Column? and get this result:
Sql 2016 – How to get only modified column in system-versioned temporal table
How to get only modified column in system-versioned temporal table. I am using SQL 2016 system-versioned temporal table to track the changes. I have the history table populated with multiple rows which include modified and not modified columns for each version of row edit. Is there any way to get only modified column value from history row data. Appreciated your
Compare between JSON string using openjson in SQL Server 2016
I have two scenarios of inserting json string. I check the json string is already exists or not using the below code and then I insert into the table In the 2nd Scenario, my data is not getting inserted into table as the count is 1. But I want both the string in scenario 1 and 2 to be treated
Incorrect syntax near format in BULK INSERT?
I’m trying to figure out why the BULK INSERT command I’m using isn’t recognizing the FORMAT and FIELDQUOTE options used in the command. For some reason, I’m getting the error: Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ‘FORMAT’. Addition: FORMAT shows up in pink text in SSMS, FIELDQUOTE shows in black text, the others show in
Is there a LastIndexOf in SQL Server?
I am trying to parse out a value from a string that involves getting the last index of a string. Currently, I am doing a horrible hack that involves reversing a string: To me this code is nearly unreadable. I just upgraded to SQL Server 2016 and I hoping there is a better way. Is there? Answer If you want
LAG functions and NULLS
How can I tell the LAG function to get the last “not null” value? For example, see my table bellow where I have a few NULL values on column B and C. I’d like to fill the nulls with the last non-null value. I tried to do that by using the LAG function, like so: but that doesn’t quite work