Stack, PostgreSQL - rextester

Stack

--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'	,'Guillermo Tole'	,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'	,'Guillermo Tole'	,281460,	'2017-06-09 05:37:0'	,	 190,	 120,	 70),						
(8	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-12 05:26:0'	,	 230,	 190,	 40),						
(9	,'USER3'	,'Juan Rulfo'	    ,123456,	'2017-06-12 05:37:0'	,	 450,	 300,	150),						
(10	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-13 05:27:0'	,	 400,	 380,	 20),						
(11	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-14 05:37:0'	,	 450,	 300,	150),						
(12	,'USER3'	,'Juan Rulfo'	    ,123456,	'2017-06-15 05:37:0'	,	 450,	 300,	150),						
(13	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-15 05:36:0'	,	 310,	 220,	 90),						
(14	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-15 05:37:0'	,	 600,	 360,	240),						
(15	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-16 05:27:0'	,	 500,	 420,	 80),						
(16	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-19 05:27:0'	,	 600,	 550,	 50),						
(17	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-19 05:37:0'	,	 750,	 450,	300),						
(18	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-20 09:37:0'	,	 490,	 410,	 80),						
(19	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-21 05:37:0'	,	 800,	 550,	250),						
(20	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-23 05:27:0'	,	 700,	 690,	 10),						
(21	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-27 05:37:0'	,	 900,	 690,	210),						
(22	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-28 05:27:0'	,	 800,	 760,	 40),			-- 6699411391	Isadora
(23	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-29 05:37:0'	,	 600,	 530,	 70),		 	-- 6691505961   Alan Medina
(24	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-30 05:37:0'	,	1050,	 900,	150),			
(25	,'USER3'	,'Pepito Marquez'	,346234,	'2017-07-03 05:37:0'	,	1200,	1000,	200),						 
(26	,'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 'this query do apart the results and keep only the week before last, but this shows repetitions of barcode column and it also sum it' as first;
select "user", "contact", "barcode", "date", "in", "out","dif"
from "table1" 
    where date_trunc('day', "date") <= '2017-06-25' ::date - (  interval '1 week')::interval 
 and "date" >  '2017-06-25'::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 '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' as second;

select distinct on ("barcode") "user", "contact", "barcode", "date", "in", "out", "dif", sum("in" - "out") over (partition by "contact")
from "table1" 
    where date_trunc('day', "date") <= '2017-06-25' ::date - (  interval '1 week')::interval 
 and "date" >  '2017-06-25'::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 'even if group by is used, this doesn`t show repetitions, but it does make the sum in background and i only need to be summed the most recent from each barcode for every contact from ' as third;
select distinct on ("barcode") "user", "contact", "barcode", "date", "in", "out", "dif", sum("in" - "out") over (partition by "contact" )
from "table1" 
    where date_trunc('day', "date") <= '2017-06-25' ::date - (  interval '1 week')::interval 
 and "date" >  '2017-06-25'::date - (  interval '2 weeks')::interval 
 group by "barcode", "user", "contact", "barcode","date", "in", "out", "dif"
 order by "barcode" , "user", "contact", "date" desc ;
 
 
 
 
 --Taking first as example..
 
--   	user	contact	barcode	date						in	out	dif
-- 1	USER2	Guillermo Tole	987654	16.06.2017 05:27:00	500	420	80
-- 2	USER2	Guillermo Tole	281460	15.06.2017 05:36:00	310	220	90
-- 3	USER2	Guillermo Tole	987654	13.06.2017 05:27:00	400	380	20
-- 4	USER2	Guillermo Tole	281460	12.06.2017 05:26:00	230	190	40
-- 5	USER3	Juan Rulfo	    123456	15.06.2017 05:37:00	450	300	150
-- 6	USER3	Juan Rulfo	    123456	12.06.2017 05:37:00	450	300	150
-- 7	USER3	Pepito Marquez	346234	15.06.2017 05:37:00	600	360	240
-- 8	USER3	Pepito Marquez	346234	14.06.2017 05:37:00	450	300	150

-- This would be the expectation
-- (MOST RECENT in . out) SUM of all the barcodes showed
--   	user	contact	        barcode	date	            in	out	sum
-- 1	USER2	Guillermo Tole	987654	16.06.2017 05:27:00	500	420	170 (80 + 90)
-- 2	USER2	Guillermo Tole	281460	15.06.2017 05:36:00	310	220	170 (80 + 90)
-- 5	USER3	Juan Rulfo	    123456	15.06.2017 05:37:00	450	300	150
-- 7	USER3	Pepito Marquez	346234	15.06.2017 05:37:00	600	360	240


run \| edit \| history \| help	0