Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Select the latest id from last week from each different contact withou...
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
--PostgreSQL 9.6 --'\\' is a delimiter select version() as postgresql_version; -- Select the latest id from last week from each different contact without repeating barcodes and sum the dif column from each contact ordered by user, contact, date desc CREATE TABLE "table1" ( "id" int NOT NULL, "user" text DEFAULT NULL, "contact" text DEFAULT NULL, "barcode" int DEFAULT NULL, "date" timestamp DEFAULT NULL, "in" int DEFAULT NULL, "out" int DEFAULT NULL, "dif" int DEFAULT NULL ); INSERT INTO "table1" ("id", "user", "contact", "barcode", "date", "in", "out", "dif") VALUES (1 ,'USER3' ,'Pepito Marquez' ,346234, '2017-05-30 05:00:0' , 100, 50, 50), (2 ,'USER2' ,'Guillermo Tole' ,987654, '2017-06-02 05:03:0' , 200, 150, 50), (3 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-05 05:37:0' , 150, 120, 30), (4 ,'USER2' ,'Jesus Zambrano' ,281460, '2017-06-05 23:35:0' , 120, 100, 20), (5 ,'USER2' ,'Guillermo Tole' ,987654, '2017-06-07 05:35:0' , 300, 210, 90), (6 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-08 05:37:0' , 300, 280, 20), (7 ,'USER2' ,'Jesus Zambrano' ,281460, '2017-06-09 05:37:0' , 190, 120, 70), (8 ,'USER2' ,'Jesus Zambrano' ,281460, '2017-06-13 05:26:0' , 230, 190, 40), (9 ,'USER2' ,'Guillermo Tole' ,987654, '2017-06-13 05:27:0' , 400, 380, 20), (10 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-13 05:37:0' , 450, 300, 150), (11 ,'USER2' ,'Jesus Zambrano' ,281460, '2017-06-15 05:36:0' , 310, 220, 90), (12 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-15 05:37:0' , 600, 360, 240), (13 ,'USER2' ,'Guillermo Tole' ,987654, '2017-06-16 05:27:0' , 500, 420, 80), (14 ,'USER2' ,'Guillermo Tole' ,987654, '2017-06-19 05:27:0' , 600, 550, 50), (15 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-19 05:37:0' , 750, 450, 300), (16 ,'USER2' ,'Jesus Zambrano' ,281460, '2017-06-20 09:37:0' , 490, 410, 80), (17 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-21 05:37:0' , 800, 550, 250), (18 ,'USER2' ,'Guillermo Tole' ,987654, '2017-06-23 05:27:0' , 700, 690, 10), (19 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-27 05:37:0' , 900, 690, 210), (20 ,'USER2' ,'Guillermo Tole' ,987654, '2017-06-28 05:27:0' , 800, 760, 40), -- 6699411391 Isadora (21 ,'USER2' ,'Jesus Zambrano' ,281460, '2017-06-29 05:37:0' , 600, 530, 70), -- 6691505961 Alan Medina (22 ,'USER3' ,'Pepito Marquez' ,346234, '2017-06-30 05:37:0' , 1050, 900, 150), (23 ,'USER3' ,'Pepito Marquez' ,346234, '2017-07-03 05:37:0' , 1200, 1000, 200), (24 ,'USER2' ,'Guillermo Tole' ,987654, '2017-07-04 05:27:0' , 900, 830, 70); select * from "table1"; -- Select latest last week data from every "barcode" column, and also from week before last, "barcode" column cannot repeat in the result and should be ordered by "user", "contact", "barcode". -- doesn't matter if lattest is higger or lower than before, just have to show lattest. -- this query do apart the results and keep only the week before last, but this shows repetitions of "barcode" and it also sum it select "user", "contact", "barcode", "date", "in", "out", sum("in" - "out") over (partition by "contact" order by "barcode") from "table1" where date_trunc('day', "date") <= '2017-06-20' ::date - ( interval '1 week')::interval and "date" > '2017-06-20'::date - ( interval '2 weeks')::interval order by "user", "contact", "date" desc; -- this query do apart the results and keep only the week before last, this doesn't show repetitions, but it does make the sum in background select distinct on ("barcode") "user", "contact", "barcode", "date", "in", "out", sum("in" - "out") over (partition by "contact" order by "barcode") from "table1" where date_trunc('day', "date") <= '2017-06-20' ::date - ( interval '1 week')::interval and "date" > '2017-06-20'::date - ( interval '2 weeks')::interval order by "barcode", "user", "contact", "date" desc ; -- even if group by is used, this doesn't show repetitions, but it does make the sum in background select distinct on ("barcode") "user", "contact", "barcode", "date", "in", "out", sum("in" - "out") over (partition by "contact" order by "barcode") from "table1" where date_trunc('day', "date") <= '2017-06-20' ::date - ( interval '1 week')::interval and "date" > '2017-06-20'::date - ( interval '2 weeks')::interval group by "barcode", "user", "contact", "barcode","date", "in", "out" order by "barcode" , "user", "contact", "date" desc ;
Absolute service time: 0,41 sec, absolute service time: 0,4 sec
edit mode
|
history
|
discussion
Error(s), warning(s):
42703: column "ID2" does not exist
postgresql_version
1
PostgreSQL 9.6.2, compiled by Visual C++ build 1800, 64-bit
id
user
contact
barcode
date
in
out
dif
1
1
USER3
Pepito Marquez
346234
30.05.2017 05:00:00
100
50
50
2
2
USER2
Guillermo Tole
987654
02.06.2017 05:03:00
200
150
50
3
3
USER3
Pepito Marquez
346234
05.06.2017 05:37:00
150
120
30
4
4
USER2
Jesus Zambrano
281460
05.06.2017 23:35:00
120
100
20
5
5
USER2
Guillermo Tole
987654
07.06.2017 05:35:00
300
210
90
6
6
USER3
Pepito Marquez
346234
08.06.2017 05:37:00
300
280
20
7
7
USER2
Jesus Zambrano
281460
09.06.2017 05:37:00
190
120
70
8
8
USER2
Jesus Zambrano
281460
13.06.2017 05:26:00
230
190
40
9
9
USER2
Guillermo Tole
987654
13.06.2017 05:27:00
400
380
20
10
10
USER3
Pepito Marquez
346234
13.06.2017 05:37:00
450
300
150
11
11
USER2
Jesus Zambrano
281460
15.06.2017 05:36:00
310
220
90
12
12
USER3
Pepito Marquez
346234
15.06.2017 05:37:00
600
360
240
13
13
USER2
Guillermo Tole
987654
16.06.2017 05:27:00
500
420
80
14
14
USER2
Guillermo Tole
987654
19.06.2017 05:27:00
600
550
50
15
15
USER3
Pepito Marquez
346234
19.06.2017 05:37:00
750
450
300
16
16
USER2
Jesus Zambrano
281460
20.06.2017 09:37:00
490
410
80
17
17
USER3
Pepito Marquez
346234
21.06.2017 05:37:00
800
550
250
18
18
USER2
Guillermo Tole
987654
23.06.2017 05:27:00
700
690
10
19
19
USER3
Pepito Marquez
346234
27.06.2017 05:37:00
900
690
210
20
20
USER2
Guillermo Tole
987654
28.06.2017 05:27:00
800
760
40
21
21
USER2
Jesus Zambrano
281460
29.06.2017 05:37:00
600
530
70
22
22
USER3
Pepito Marquez
346234
30.06.2017 05:37:00
1050
900
150
23
23
USER3
Pepito Marquez
346234
03.07.2017 05:37:00
1200
1000
200
24
24
USER2
Guillermo Tole
987654
04.07.2017 05:27:00
900
830
70