I would like a cumulative DR or CR balance for the following table: For the above table, a cumulative Balance column would be: Decreasing a DR below 0 makes it a CR Decreasing a CR below 0 makes it a DR Where balance is 0, value is 0 DR It could be calculated as CR’s being a negative value, but
Tag: window-functions
How can i find rows before a specific value?
I have the next row and what I want to do is to select all the rows before the type “shop”. I tried using case in the “where clause” but I didn’t get any result. How can I do it? |id|visitnumber|type …
SQL Function to Look at near-by Intervals of data
I am trying to determine if there is some sort of SQL function that would allow me to look at nearby intervals of data to aggregate them into other aggregate functions. My current data looks something like the following and occurs every 5 minutes with a single entry per day for a given time period per entity. EntityA 20200201 00:10:00
MAX() OVER PARTITION BY not working as intended
I’m having some issues when i try to obtain the MAX value of a field withing a set of records and i hope some of you can help me finding what am i doing wrong. I’m trying to get the ID of the item of the most expensive line, within an order. Given this query: I’m getting this results: Results
HIDE Column in Select query?
Hi I’m trying to hide the TotalRank Column that gets displayed during my results because I only needed it in this query to help sort items. I don’t need this information displayed. Answer You can use th following
Lag() function with WHERE clause
I have this sql function that works properly: SELECT out_hum , (out_hum – LAG(out_hum, 1) OVER (ORDER BY id)) As dif FROM excel_table But I want to select all the out_hum when the diferrence (dif) …
How to get the row index in SQLite3?
I have created a table that has a column of registration number as primary key. I have created row_id using row_number() that is ordered by the primary key. How can i search a registration number and get the row_id along with other information of that row? Answer If you have created the column row_id like: then use a CTE: Replace
How to pull a list of all visitor_ids that generated more than $500 combined in their first two sessions in the month of January 2020?
Tables: Sessions session_ts visitor_id vertical session_id Transactions session_ts session_id rev_bucket revenue Currently have the following query (using SQLite): I’m having trouble identifying the first two sessions that combine for $500. Open to any feedback and simplifying of query. Thanks! Answer You can use window functions and aggregation: The subquery joins the two tables, filters on the target month (note that
Compute a Decreasing Cumulative Sum in SQL Server
What I’m trying to achieve is a cumulative sum on a non-negative column where it decreases by 1 on every row, however the result must also be non-negative. For example, for the following table, summing over the “VALUE” column ordered by the “ID” column: I expect: Answer Your question is not very well described. My best interpretation is that you
Top-N By Decade for successive decades (in SQL Server)
I’m trying to get a ranked list of Top 5 (i.e. most common) document titles, grouped by decade, for each of the 6 most recent decades. The document titles are non-unique. There could be dozens or even hundreds of documents with the same title in any given calendar year. The following query is as far as I’ve been able to