I have a table with multiple calendars inside it and I need to get the previous business day for each row in the table. I’ve been trying to use the LAG function to achieve this but I’m unable to get the correct return for every item. I’ve ended up splitting it into two queries. When the day is a business
Tag: date
How do I join a sparse table and fill rows between in SQL Server
How can I apply weights from a one table to another [Port] where the weight table has sparse dates? [Port] table [Weights] table So, I want to use the weights as if I had a full table like this below. i.e. change to new weight on first day it appears in [Weights] table: Answer You can use apply: outer apply
Need to group records by consecutive date SQL
I’m building a query in order to build consecutive dates which is create table #consecutivedates ( sgid nvarchar(max), metric nvarchar(max), targetvalue nvarchar(max), startdate …
How to add a column of total number of rows in BigQuery
I have a table in BigQuery where I save a DATESTAMP and temperature sensor value. I usually filter the query by dates. My goal is to add a column in this query where it puts the total number of rows. For example, if I run this query I have this result: And when I run this query I have this
What is the best way in SQL to combine sequential events based on matching end time to start time?
That database I work in records events based on a part ID and the times in which it is active. The issue I came across is these events are truncated to fit within a single day. If the active time for a part carries over to the next day, the event will be split by the number of days it
DATEDIFF vs (w1.date = w2.date +1) difference? MySQL syntax
I was working on a SQL database question using MySQL. The goal is to find all IDs that satisfy today is warmer than yesterday. I’ll show you my original code, which passed 2 out of 3 test cases and then a revised code which satisfies all 3. What is the functional difference between these two? Is it a MySQL thing,
PostgreSQL count of each status per day
I have the following table: I also have a table with every calendar day from 2019-11-01 to 2019-12-31. I need to find out how many occurrences of each status exist per calendar day for the time span listed above. If a status is Opened on 2019-12-14 and Pending on 2019-12-17, I need to count that it was Opened for every
Create A shift summary, with a shift spanning over 2 days
I have a data set like the sample below. I need to query and summarize the number of transactions per shift. The problem is that the shift cycle does not align with the normal hours of a day. Eg. The shift for day 2020-01-01 will start at 06:00 AM on 2020-01-01 and end at 06:00 AM on 2020-01-02 Below are
Overlap the intervals using mysql
+——+————+————+ | id | start_date | end_date | +——+————+————+ | 1 | 2019-01-01 | 2019-01-12 | | 1 | 2019-01-10 | 2019-01-27 | | …
Selecting all entries in a table before this month
I’ve been trying different solutions but none seem to return the same values as I get from excel. I want to return all the items from a table where the entries in a column are before the current month value. So, if this month is January 2020, return the values before January 2020. Ideally it’s going in a function where