I have this SQL statement: It returns a table like: But I am looking to have something like this: How do I change my SQL statement to accomplish this? Answer The solution to this problem ended up being to create a temp table for the main query. Then pulling the appropriate data out of the temp table utilizing…
Tag: sql-server
How can i get a gap from a history table for repeating groups over time
I have a historical table with data like as bellow : The EXP_DT is null mean that the row is active. I want to return a group of data each time Status changes The expected result like as bellow : Thanks for help ! Answer This is classic gaps-and-islands problem. We can solve it by using LAG to check for
Prevent Duplicate Entries in SQL Application
I have an application that’s running on two different machines. The use of the application is pretty simple, we scan a product and it associates the product_id and creates a Unique_ID that’s auto-incremental. Ex: U00001 then the next is U00002 My problem is while both the machines are running, som…
SQL Select Distinct Top5 with Order By and Where
I have a table where Employees can check assets and each of their checks is recorded in a table. Now I want to extract the 5 last checked Asset_Ids for a given employee. He might have checked the same asset more than once, each check is recorded in a table -EmployeeLog- Basically my table has 3 columns : Empl…
Why can’t I sum values for a given period with intervals present in another table?
I’m using a software. I can not change the model. The software is a film rental manager. In the database, I have some clients and differents type of rental and different type of client (free, vip, vip+, …) A client can rent a movie for one day, one week, buy a movie, etc. He starts as a free clien…
How to find the sum from value of sql column
I have two tables with following data. studenttbl: feetbl: I need to find below data, but I am not getting balance value of seatno 1006: I am using this SQL query: Please help me with this. Answer This issue is due to INNER JOIN versus LEFT JOIN. When you INNER JOIN youll only get matches between two tables. …
What is the most efficient way to optimize a filter based on parameters on a SQL Server stored procedure?
Right now I have something like: … and so on, so in the end I’ll have I think this is going to work, but it doesn’t seems efficient to me. Is there a way to optimize this process and filter information with multiple parameters, with the option of some of them being null? Answer The most effi…
Split String into rows but keep associated data
I’m not real familiar with Split_String and my searches aren’t turning up anything that I can figure out for my case. What I need to do is split a field into rows each time its delimited but then for each new row created, copy the associated columns with that field to those new rows. How data will…
Count total day difference in a Pivot SQL
I have data in two tables with dates PAL date and HUB date. I’m looking to show the number of day difference in a table with 6 columns: Client 1day 2days 3days 4days 5days Client will display the clients grouped, 1 Day will display the count of how many datediff of 1. 2 Days will display the count of ho…
Reset ROW_NUMBER() when previous partition ID changes
I am trying to reset the ROW_NUMBER() value when there is a change in one of the IDs when sequenced in datetime order. For example in the table below, when Location_ID changes, I want to reset the RowNum to 1. Here’s the statement I am using. I get RowNumX, but want RowNumY. EventDate Scan_Timestamp Ass…