I am trying to convert a SQL statement to Linq/Entity Framework, and am having a difficult time. Below is the SQL. The FULL JOIN and the GROUP BY seem to be what I’m struggling most with. I’ve reviewed this SO answer and I understand how to execute a FULL JOIN on its own, but can’t figure ou…
Tag: sql-server
How to count the number of occurences for a specific value from a range of columns using T-SQL?
I have a table (t1) as shown below that contains single-character values in all columns named v%: I need to count the number of occurrences for “O” and “E” and display them in 2 additional columns. How can I do this? Expected Output: Answer I would use apply and aggregation:
How to group by and select corresponding row values?
To start I am using SSMS 2018. I am trying to create a Common Table Expression to use in some code later. I need to get down to a single row per applicable quarter per person, I’ve tried using Havings/…
Why am I getting “Error converting data type varchar to int.”
I am creating a mock database for a fictional gym. I have 3 relevant tables: Program, Workouts and Exercises (see below). Each Program contains one or many Workouts and each Workout contains one or many Exercises. I am trying to create a stored procedure to add rows to these three tables. I keep getting the e…
Self Join to calcuate one day before a given date
I have this table Employee_Hire which has Hire date of an employee (ID) in a Department. Now I want to write a Select query which adds one more column (Last_day) to show last day he worked in that …
Value from 2nd Row of column copied to 1st row of next column
The table looks like below. I am trying to make 1 new column say SuprerFrez whose value is depends on column FREQHZ. For every UVRID group 2nd value FREQHZ will be 1st value of SuprerFrez and for last FREQHZ, SuprerFrez value will be zero. Expected output with 1 new column whose value depends upon FREQHZ colu…
Change date time format from YYY-MM-DD HH:MM:SS to YYYY.MM.DD HH:MM:SS in SQL Server
I have a table with a column RequestDate with following format 2019-12-01 00:00:00:000. I want to see results like this: 2019.12.01 00:00:00:000. I used this command From the above query I am seeing results as 2019.12.01, but I am missing time (00:00:00:000) – how can I keep along with time. I want fina…
In SQL Server: Create Pivot Table with each Date as a own column
I have a table containing 4 different identifiers, a date and a numeric column like: from which I want to create a kind of pivot table like the following: This should be created for past 10 days or so, so it CRUCIAL I don’t have to set the dates manually. I tried something with PIVOT but that didn’…
SQL Function to Look at near-by Intervals of data
I am trying to determine if there is some sort of SQL function that would allow me to look at nearby intervals of data to aggregate them into other aggregate functions. My current data looks something like the following and occurs every 5 minutes with a single entry per day for a given time period per entity.…
Sql Syntax to tag rows in a table where this specific criteria appears at least once or more
I am using SQL Server 2014 and I have a table (t1) which contains a list of 6 numbers in more than 25,000 rows. Extract of t1 is given below: Id F1 F2 F3 F4 F5 F6 1 5 11 15 …