All Questions
Tagged with recursive-query datetime
67 questions
1
vote
1
answer
41
views
Group by 15 minutes increments with a count, including counts of 0
I have the below query which groups my data in 15 minute increments but it does not include increments that don't have data in that 15 minute increment.
Current query:
SELECT
TO_CHAR(TRUNC(...
1
vote
1
answer
249
views
How to join generated datetimes with data in SQLite database?
I have a table with data from a sensor created like that:
CREATE TABLE IF NOT EXISTS "aqi" (
"time" datetime,
"pm25" real,
"pm10" real
);
When is sensor ...
- 107
1
vote
3
answers
2k
views
SQL Server : Using LAG() with calculated previous value
I have data like this in SQL Server 2016 database table:
PERIODE
PERIODE_FORECAST
VALUE
2021-08-01
2021-01-01
51384.673
2021-08-01
2021-02-01
44118.129
2021-08-01
2021-03-01
43164.446
2021-08-01
2021-...
- 11
0
votes
1
answer
373
views
How to select rows based on a rolling 30 day window SQL
My question involves how to identify an index discharge.
The index discharge is the earliest discharge. On that date, the 30 day window starts. Any admissions during that time period are considered ...
- 21
0
votes
2
answers
1k
views
SQL Server: Rewrite recursive CTE to substitute for option maxrecursion in a View
I ran into the issue that we cannot use underneath query inside a view, but only in a table. Unfortunately we deal with the situation that we don't have tables as an option for this project.
I am ...
- 309
0
votes
1
answer
140
views
SQL DATEADD Function Removing Week Ends [duplicate]
I need to add five days to the "2020-12-01" date. But here the scenario is while adding five days to "2020-12-01" date we should not include the Saturday (2020-12-05) and Sunday (...
- 89
1
vote
2
answers
973
views
Detect if a month is missing and insert them automatically with a select statement (MSSQL)
I am trying to write a select statement which detects if a month is not existent and automatically inserts that month with a value 0. It should insert all missing months from the first entry to the ...
- 69
-2
votes
3
answers
90
views
SQL with Date LOOP [closed]
I've been trying to write a query that returns the penultimate day of each month for 36 months, and this query should automatically return records for 36 months.
For example; such as 20201230 for ...
1
vote
1
answer
63
views
Adding the total number of distinct customers to a cte table in Microsoft SQL Server
I am using Microsoft SQL Server and am trying to achieve the following
Date
Distinct Customers last 30Days
2020-12-01
20000
2020-12-02
23000
What I am trying to get is that between 2020-11-01 and 2020-...
0
votes
1
answer
126
views
MySQL sum values diagonally
I have a table which looks like this:
| created_at | Current_Value | M1 | M2 | M3 | M4 |
| ---------- | ------------- | --- | --- | --- | --- |
| 01/08/2020 | 840 | 840 | 838 | 838 | 838 ...
- 433
-1
votes
1
answer
795
views
How to select sum of the data for every month in current year even if we do not have data [duplicate]
I have a SQL table:
CREATE TABLE test (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date NULL,
`distance` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO test (date, distance) VALUES ('...
- 1,273
-1
votes
1
answer
68
views
Get SQL data individually for days between now and 30 days ago
I'm building an analytics chart where I need to display the number of visits for each day, for the past 30 days. I've tried using the loop below, but it fails to pull certain data and drastically ...
- 11
2
votes
1
answer
368
views
SQLite group by all the days in selected date range even data not exsist
I have a table invoice with two columns, date and total I want get last 30 days total group by all days of the month (or else last 7 days groups by all days of the week)
date total
11/16 500
11/...
- 185
1
vote
1
answer
316
views
Generate date range from start date and end date SQL Azure
I need to generate a date range that comes from a table like this:
id | start_date | end_date
---+-------------+------------
1 | 2020-01-01 | 2020-01-03
2 | 2020-01-01 | 2020-01-03
The result ...
- 23
2
votes
1
answer
215
views
Using SQL to group consecutive items that share a common status (dummy data included)
Given a table that has sometimes repeated statuses within a group (in this case "vehicles"), I want to consolidate those statuses into a single row and aggregate status_seconds. The data ...
- 23