Tag: datetime

Using rank to enumerate the session

datetime google-bigquery sql window-functions

Hi can anyone help me for this. What im trying to do is to rank my session id but it seems not to work as it returns me just “1”. The third row should be 2, the row 5 should have 2 for rank and the row 6 should have 3 for rank and so on. I try to enumerate

SQL Flag Rows Occurring 2 Hours After Previous Flag

datetime recursive-query sql sql-server sql-server-2019

I have some messy RFID data due to over sensitive antenna’s. There is a physical process that tracks an RFID tag moving through different stations in a cycle. An item with an RFID tag can move through the cycle more than one time a day, but it is highly unlikely that it could start the cycle within a two hour

specific day of next month on a sql query

datetime select sql sql-server where-clause

I have a complex (complex because it gave me lots of work to complete!) and it returns results that fulfill the query till the day 30 of the current month. Here is the code: I have a job that runs this query on the third monday of every month. What i need is that the query returns values not only

Max and Avg debt days over a period of time

datetime sql sql-server sum unpivot

I have invoices pending payment, every invoice has two dates, first when the invoice is required to pay and the other when the invoice is paid. I want to know in a period of time the max debt and the …

Grouping shift data by 7-day windows in SQL Server 2012

datetime recursive-query sql sql-server sql-server-2012

What I want to do is to calculate the number of shifts and hours worked by each employee in any given 7-day period. In order to achieve this, I need to identify and group ‘islands’ of shifts. Note …

Finding daily registered users

count datetime distinct min sql

I have a table which includes the information of player_id, first_timestamp, last_timestamp, and date, etc. So I have initially 10 payers on 2020-07-08, and then 18 players on 2020-07-09, some of the players from previous day might appear on 2020-07-09 again. And I’m trying to find the new players registered on 2020-07-09 that did not appear on 2020-07-08, but I

How to exclude weekends and holidays dates and find an expected date in MySQL?

count datetime mysql recursive-query sql

I am working on a task where I have 2 tables. i.e, tickets and holidays. Now I also have the number of days to complete the tickets. Now I need to find the expected date by excluding holidays(specified in the holidays table) and weekends. Now I can able to find a date using ticket created date and days to complete

SQL: Convert bigint type to formatted date

datetime mysql sql string

I believe this is a simple question, but I’ve been searched around and got no satisfactory answer. Basically I have a Bigint object in MYSQL, I want to convert it to date format like 20201004, for example: I’ve tried But neither allow formatting, I hope to get the easiest and fastest conversion. Answer Assuming that your number is an epoch

Pivot table in SQL Server 2016

datetime pivot sql sql-server window-functions

I am having problem with pivoting a table in SQL Server 2016. Consider the following data/query WITH DATA1 AS ( SELECT 123 cid, ‘test’ cname, ‘2020-03-17’ dt, ‘BELLE’ fc, 3782703 mn union …

Rolling sum based on date (when dates are missing)

datetime sql sum tsql window-functions

You may be aware of rolling the results of an aggregate over a specific number of preceding rows. I.e.: how many hot dogs did I eat over the last 7 days SELECT HotDogCount, DateKey, …