I have machine output data in the form: and I need to generate a new table by removing some rows: The machine sometimes collects multiple VALUEs for each run, but I only need the last one (it’s cumulative). However, I may have multiple runs of the same ID per shift, and it’s not impossible to have…
Tag: sql-server
Bi-temporal SQL table querying
I’m trying to model a SQL table to store salary changes for employees. One approach is using a bi-temporal table as follows: Here an employee was hired on 10/1/2015 with salary of 100,000. Then on 2/15/2016 he had a performance review and his boss said, “we’re bumping your salary to 110,000 …
IIF or case when in window function
I have some timestamps in a table and I want to create a dummy variable (0 or 1) that tests if the row above is equal to the current row, after the timestamps are sorted. I need to do this in different partitions. Is there a window function that can do this in SQL Server? So I know my partition
Query that merges latest reference in multiple tables into shared rows
I have the following schema which is pretty simple and straight forward: I want to write an optimized query that returns me a list of all phones with their latest message and latest picture taken. In this case with the latest “CreatedAt” for both fields: Example expected data-set: Right now I̵…
DateAdd function in Oracle SQL
How can I convert DATEADD(week,-4,GETDATE())in the where clause ( in Oracle SQL) Trying to get data for prior 4 weeks Answer In Oracle, you would use: Or, if you prefer:
How to filter record in a table with different condition
How to find the record in a table which as only ‘Return’ as Reason? I have table which has many records As per the data in the table, it returns only records with policy number 5555, because only that policy number has only ‘Return’; all other policy numbers have both ‘ReturnR…
Join and flatten table output
I am currently trying to join 3 tables. The main table is company, if there is 3 companies and 2 roles exists in the role table I want the output to be like this: Company1, Role 1, …
Trying to convert SQL getdate into varchar with no in-between characters
I have a list of auto-generated tables in MS-SQLServer that differ only in the date listed at the end of the name. The challenge is that I want to create a script that always references a table ‘x …
How to query multiple conditions for one column in SQL?
I have three tables, one is book, the other one is category and last one book_category which has relation between book and category. In my situation I have books and their categories. For example one book can have multiple categories. How can query the books have both categories. For example, one book have no…
SQL many joins many to many
I have 5 tables: Table Reports Many to many table ReportsIpRel Table of InfoProviders Many to many table QueriesIpRel And table of Queries The SELECT I am trying to achieve is the following: I tried many left/inner joins like this: but all for nothing. I have to use where condition: WHERE report_Id = ‘2…