I have a table named Employee_audit with following schema, emp_audit_id eid name salary 1 1 Daniel 1000 2 1 Dani 1000 3 1 Danny 3000 My goal is to write a SQL query which will return in following format, considering the first row also as changed value from null. columnName oldValue newValue name null Daniel s…
Tag: sql-server
Select data using closest date
I have two tables like below: table1 table2 I have to calculate TotalPrice (price x Quantity) from each ID for the date ‘2017-07-28’ but condition is that: if no price is available for a given date, the price closest to but before the date should be used. I have tried the below query but its not g…
SQL Server filling gaps in time series
I’m having trouble with a request in SQL. I have two tables, one representing a vector of dates and another timeseries of prices for different securities: Dates: DateId Date 1 2021-01-01 2 2021-01-02 3 2021-01-03 TimeSerie: SecurityId DateId Value 1 1 0.25 1 3 0.32 2 1 0.41 2 2 0.67 The timeserie may ha…
How to get the sum of values in a table interval?
The question is very simple :-). I’m a beginner. tables Result: A task….. There are two date variables. How to get the sum of values(qty) between dates(@startDate – @endDate). How to get the sum of values(qty) up to @startDate. How get the sum of values(qty) down to @endDate. If DocumentType is 1.…
Joining on the same table to get 2 different amount values
I want to get the total amount of a funding source (dim_6) against 2 different account codes (1301 AND 1300). Below is the query that I designed. The above query doesn’t return the correct sum of amount and it doesn’t match the value of amount if I try to run a simple SQL query against 1 account c…
SPROC with Outer-Apply returns too many rows
I have a complicated enough SPROC, which I modified to return a few more columns (from additional tables). What I wanted is to have this SPROC still to return the same amount of rows and the same data, but to fill in new columns (if data exists) as well, for those rows. My modification does fill new columns, …
Combination of dynamic pivot and static pivot in SQL Server
Dynamic pivot combined combined with static aggregates I have a table that looks something like this: I basically need to: Group by a number of fields (Place, State, Category in the example) Count per such group Sum MCount, Cost (and others, not in example) per group, these columns are static Pivot over Categ…
Convert foreign key constraint from SQL Server to Oracle
I want to convert the following script(SQL SERVER) to Oracle: I have tried to run the same script in Oracle: and I got this error: I believe that the “WITH CHECK ADD” is the problem! Answer See if this helps. A master table – its primary key column will be referenced from the tdistribucion t…
Return column name of max value in a row SQL Server
Initial Table: Output Table: I need to get the Job Name (column name) of the row with the max value in SQL Server. Is there a way other than else if to do this because ‘greatest’ function is not available in SQL? (Coz I have 50 Jobs at least) Answer You can use apply and window functions: That sai…
Difference between performing INSERT INTO vs Using MERGE INTO
I started working on a project that already has some scripts running and while I was looking some of the scripts that have already been done I encountered an scenario that looks something like this. My Question here is why they would use the merge into and force the mismatch just to perform an insert, they co…