I have a table in an MSSQL database that lists when students start a programme. The academic year runs from August to July. Where a student starts on 01 Sep 2019 I want to show that they started in …
Tag: date
MySQL get previous day data
I have the following table What I try to get is writing the day before data on the following day I can get yesterday but don’t know how to do it for all rows. Thanks, Answer You can use next approach. In this query we use join same table to itself by date with 1 day shift. DB Fiddle
Last Row In Group Based on Timestamp Column For a Subset of Group Ids – Postgres
Given a postgres table of the following form: group id | timestamp | value ———+——————————-+—— 1 | 2020-04-15 15:04:44.020288+00 | 8.0 2 …
Converting month name to month number in SQL Server
I have a column (says Period_name ) that accepts varchar value. Inside the column, I have inputs likeJan-19, Feb-19, etc. I need an SQL instruction whenever left of Period_name comes like Jan, Feb then it should convert into corresponding month number. For example Input Output The SQL query condition is as fo…
SQL how can we get monthly trend from 2 separated columns start_date and end_date?
Imagine we have the following data: The question here is to get the 2020 monthly trend of active paid subscriptions. For each subscriber (ID), we can only count the months that they are active. So for S1, we can only count S1 active in Jan 2020 and March 2020, not Feb 2020. During the interview, I wrote a fun…
Comapring DATE_ADD with NOW not working on MySQL
I’ve try to compare an DateTime in MySQL. The table is to check locked users. For sample, an User will be locked for 1 Day at the DateTime 2020-04-09 14:51:32: until is the datetime time_created with added days from locked_time. Here is the Result of entries: The calculation (time_created with added loc…
How to get the time difference in hours between 2 dates where dates are stored in 12 hour format in Oracle?
I have two dates : Date1: 20-Mar-2020 07:35:00 PM and Date2: 20-Mar-2020 02:42:00 PM I have written the query : SELECT ROUND(minutes / 60, 2) || ‘ Hours ‘ FROM ( SELECT (Date1 – to_date(…
Mysql query per date and per group
I have an SQL query which counts user uptake (registrations) by day for a particular group of users. It also does a cumulative (running total) This query is using the audit table purely to get me a list of dates reliable even if there are no users created on every day, i know the audit table with have a recor…
select * from [table] where min(date) > certain date
i have a table with customers. every customers has several orders with the paid price and day he/she ordered an item. i want only customers their first order was after a certain date orders table …
Calculate time from begin date in SQL
I’m trying to tally up the number of unique ID accounts that are active for more than a year as well as include how long each one is active. The problem with the code I have is it’s not including …