LAG function in Oracle

I have a table (incident) that has column Create_date(DataType=Date). I want to get difference in Days OR Hours from Previous Record. Like the screenshot below. From Second Record Create_Date I want to minus First Create_Date and from Third Create Da

Snapshot of the row instance of the table with time intervals

I am not sure what to call this phenomenon. I have just titles it with "Snapshot of table row instance with time intervals". We have a situation where we need to create a new entity which is kind of JOIN of two tables but these two tables have h

Calculate cumulative totals for multiple dates

Please see bottom of question for test data.. I have data like below.. salesorder datee 1 2016-11-10 1 2016-11-10 2 2016-11-09 2 2016-11-09 Now i want to display my data like below datee Summ 2016-11-09 4 2016-11-10 6 So i have used below query ,whic

improve the query below row_number () over (partition by

Is there any way to improve query below, when I just check for one record it works faster(NAME='T 104') but when for all existing (113 records) records without NAME='T 104' it takes about 5 secs: the one below takes 73 msec: select name, PRODUCTION_D

Monthly balance Postgres

I'm using postgreSQL 9.2 I have a table with transactions that affect an account startbalance and I want to create a query that generates the monthly balances. It seems simple, but I'm stuck and the closest thing I can find might be this Calculate Mo

The custom sequence or the Windows function in Postgresql

I have a huge table which looks like this Group | Code | Value ------|------|------ G1 |0 |V1 G1 |2 |V2 G1 |2 |V3 G1 |1 |V4 G1 |2 |V5 G1 |2 |V6 G2 |0 |V7 G2 |1 |V8 G2 |1 |V9 G2 |2 |V10 G2 |2 |V11 G2 |2 |V12 G2 |2 |V13 I need to generate a table like

Get first and last row using ROW_NUMBER and PARTITION BY

Sample Input Name | Value | Timestamp -----|-------|----------------- One | 1 | 2016-01-01 02:00 Two | 3 | 2016-01-01 03:00 One | 2 | 2016-01-02 02:00 Two | 4 | 2016-01-03 04:00 Desired Output Name | Value | EarliestTimestamp | LatestTimestamp -----|

How & ldquo; reset & quot; SUM after reaching a threshold?

I wrote a query that creates two columns: the_day, and the amount_raised on that day. Here is what I have: And I would like to add a column that has a running sum of amount_raised: Ultimately, I would like the sum column to reset after it reaches 1 m

ORDER BY in a partition - SELECT keyword

Please see the DDL below: create table #names (name varchar(20), Gender char(1)) insert into #names VALUES ('Ian', 'M') insert into #names values ('Marie', 'F') insert into #names values ('andy', 'F') insert into #names values ('karen', 'F') and the

Find an intersection between date ranges in PostgreSQL

I have records with a two dates check_in and check_out, I want to know the ranges when more than one person was checked in at the same time. So if I have the following checkin / checkouts: Person A: 1PM - 6PM Person B: 3PM - 10PM Person C: 9PM - 11PM

Select by the largest sum, but without the sum in the result

I need to select the top score of all combined attempts by a player and I need to use a WITH clause. create table scorecard( id integer primary key, player_name varchar(20)); create table scores( id integer references scorecard, attempt integer, scor

Comparison of line values ​​in Oracle

I have Table1 with three columns: Key | Date | Price ---------------------- 1 | 26-May | 2 1 | 25-May | 2 1 | 24-May | 2 1 | 23 May | 3 1 | 22 May | 4 2 | 26-May | 2 2 | 25-May | 2 2 | 24-May | 2 2 | 23 May | 3 2 | 22 May | 4 I want to select the row

How to obtain correct summaries with the analysis?

I want to get summary numbers from the cust_detail table if a specific invoice_code appears in invoice_detail. In this example, I'd like to report cust_detail summaries only for batches 10 and 20 because they are the ones with invoice_code='9999'. Bu

select the top 10 for each number

Query #1 returns thousands of rows for each skid. I like to know if there is a faster way select only top 10 for each skid. query #2 is slow. Is there is any way I can optimize in query #1 to make it faster? query #1 select skid, date_run, price, ROW

SQL Server: Using the Windows function with an ordered set

With this sample dataset: ID POS QTY A B 32 1 50 1 2 28 2 200 1 2 12 3 300 2 2 16 4 400 1 2 98 5 500 3 1 56 6 600 2 2 33 7 700 2 2 How can i get SUM(QTY) by group. Group is based on diferent (A AND B) ORDERED BY POS !!! As: ID POS QTY A B SUM(QTY) 32

Select first & amp; last date in the window

I'm trying to select first & last date in window based on month & year of date supplied. Here is example data: F.rates | id | c_id | date | rate | --------------------------------- | 1 | 1 | 01-01-1991 | 1 | | 1 | 1 | 15-01-1991 | 0.5 | | 1 | 1 |

SQL recursion with row_numbers

I got a temporary view in PostgreSQL which offers the following data (simplified): temporary_view (sum, rw): |sum | rw| |1337 | 1| |42 | 2| |... | ...| Now I need to do a recursion on this until my condition is reached. For simplicity, let's say I wa

from each partition, select rows with N distinct values

I have a table with three columns x, y, z. I'd like to write a query that, within each PARTITION BY x, returns the rows containing the first n distinct values of y. Here's a sample for n = 2 -- the first 2 distinct values of y in the first partition

PostgreSQL Query for Calculating Baseball Points Rotisserie

I am trying to write a PostgreSQL query to calculate fantasy baseball points, as described here. What I have so far is available in this SQLfiddle, which calculates the points for each statistic correctly, except in the case of ties. Points for teams

Does hibernate support count (*) over ()

I'm trying to prevent having to create one separate query for count and one for the actual query. What I found is SesssionImpl::createQuery takes a considerable amount of time for a complex query and by combining count and the main query I can then e

While I'm trying to use Partition over Hive,

SELECT prc.user_key, percentile(P.price, 0.5) OVER (PARTITION BY P.user_key) as median_price FROM ( SELECT vfc.user_key, vfc.order_id, MIN(vddo.cc) price FROM td_b.mv_a vfc JOIN td_b.dim_deal_option vddo ON vfc.d_key = vddo.d_key WHERE vfc.action = '

Window function in PostgreSQL

I have a with my SELECT statement that I just can't figure out. The query is as follows: SELECT count(1), interaction_type_id FROM tibrptsassure.d_interaction_sub_type GROUP BY interaction_type_id HAVING count(interaction_type_id) > 1 ORDER BY count(

Percentage of values ​​for the top 3 of a character field

I have an unusual situation. Please consider the following code: IF OBJECT_ID('tempdb..#CharacterTest') IS NOT NULL DROP TABLE #CharacterTest CREATE TABLE #CharacterTest ( [ID] int IDENTITY(1, 1) NOT NULL, [CharField] varchar(50) NULL ) INSERT INTO #

Select a separate user group by period

I have a table with the following info |date | user_id | week_beg | month_beg| SQL to create table with test values: CREATE TABLE uniques ( date DATE, user_id INT, week_beg DATE, month_beg DATE ) INSERT INTO uniques VALUES ('2013-01-01', 1, '2012-12-

Postgres 9.1 - Get the next value

I have some data (sorted by "TimeStamp") that looks like this: Key | TimeStamp | Column3 | ColumnN --------------+-------------------------+-------------+-------------- 1 | 2012-01-01 08:00:23 | ... | ... 2 | 2012-01-01 08:01:07 | ... | ... 3 |

ROW_NUMBER () shows unexpected values

My table has values like (RowCount is generated by the query below): ID Date_trans Time_trans Price RowCount ------- ----------- ---------- ----- -------- 1699093 22-Feb-2011 09:30:00 58.07 1 1699094 22-Feb-2011 09:30:00 58.08 1 1699095 22-Feb-2011 0