0

I have 3 databases with tables on the server and I want to get the count of a document that is in the table (grouped together, pac and fac). The columns of the document table are document_key, pac, fac, arrival, The first db receives the document, the second records the document that were sent out and the third keeps count of documents that weren't processed (error). I also want to get the count that were processed within 48 hours and 30 days. The name of the table is document and its in postgresql. Here is what I have:

select 
   iw.pac, 
   iw.fac, 
   SUM(CASE WHEN iw.arrival::date BETWEEN current_date - 3 AND current_date - 1 THEN 1 ELSE 0 END) AS day2iw, 
   SUM(CASE WHEN iw.arrival::date BETWEEN current_date - 30 AND current_date - 1 THEN 1 ELSE 0 END) AS day30iw,
   SUM(CASE WHEN nw.arrival::date BETWEEN current_date - 3 AND current_date - 1 THEN 1 ELSE 0 END) AS day2nw, 
   SUM(CASE WHEN nw.arrival::date BETWEEN current_date - 30 AND current_date - 1 THEN 1 ELSE 0 END) AS day30nw,
   SUM(CASE WHEN ems.arrival::date BETWEEN current_date - 3 AND current_date - 1 THEN 1 ELSE 0 END) AS day2ems, 
   SUM(CASE WHEN ems.arrival::date BETWEEN current_date - 30 AND current_date - 1 THEN 1 ELSE 0 END) AS day30ems 
FROM db1.document AS iw
INNER JOIN db2.document AS nw
  ON iw.pac = nw.pac
INNER JOIN db3.document AS ems
  ON iw.pac = ems.pac
WHERE iw.pac <> '' AND iw.fac <> '' 
GROUP BY iw.pac, iw.fac
  AND iw.arrival::date BETWEEN current_date - 30 AND current_date - 1

Here is my desired result. The first lin is the header, R = received, P= processed ad E = error

(Pac) (Fac) (R -48 hrs) (R-30 Days) (P - 48hr)(P -30days) (E - 48hrs) (E-30days)

 LAB - ADM - 57    -     200    -    49   -    198   -     8     -     20
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
DiD
  • 77
  • 1
  • 11
  • 3
    Do you have a question? If you have a problem with your query, edit your question and include sample data, desired results, and describe the problem with the query you have written. – Gordon Linoff Apr 27 '15 at 14:55
  • Are you sure you mean three *"databases"*? Not *schemas*? (Which would be mostly irrelevant to the question.) You cannot even join tables from different databases in standard SQL. You would have to use dblink or foreign tables, but I think you are just confusing terms. Basic concepts: http://stackoverflow.com/questions/24918367/grant-privileges-for-a-particular-database-in-postgresql/24923877#24923877 And *always* declare your version of Postgres. – Erwin Brandstetter Apr 28 '15 at 02:22
  • Its 3 different databases. The pac and fac are 2 columns that in the document table and the document table is common to all three db. So When i want to get the count, it reads where pac = '' and fac = '' within a certain arrival time. Although i got a partial solution to this. I broke the query down to 3 and it ran for a much longer time. – DiD Apr 28 '15 at 14:00

0 Answers0