Completed

PostgreSQL: Write SQL statement

This project was successfully completed by lokeshk9936 for $10 USD / hour in a day.

Get free quotes for a project like this
Employer working
Project Budget
$8 - $15 USD / hour
Completed In
1 day
Total Bids
12
Project Description

I have lots of data in a table with the following structure:

CREATE TABLE receipts

(

id serial NOT NULL,

customer_id integer,

receipt_number serial NOT NULL,

image_name character varying(255),

entry_date date DEFAULT ('now'::text)::date,

text character varying(9999),

amount numeric(20,2) DEFAULT [url removed, login to view],

account integer,

vat_code character varying(10),

offset_account integer,

s3_path_to_original_file character varying(300),

received_date timestamp without time zone,

deleted boolean DEFAULT false,

typed integer DEFAULT 0,

checked boolean DEFAULT false,

reconciled boolean DEFAULT false,

accounted boolean DEFAULT false,

s3_path_to_image character varying(300),

CONSTRAINT receipts_pkey PRIMARY KEY (id)

)

I need to keep the data as it is, while converting to the following schema.

CREATE TABLE receipts (

id SERIAL PRIMARY KEY,

customer_id INT,

receipt_number SERIAL,

image_name CHARACTER VARYING(255),

entry_date DATE,

text VARCHAR(9999),

amount NUMERIC(20, 2) DEFAULT [url removed, login to view],

amount_original_currency NUMERIC(20, 2) DEFAULT [url removed, login to view],

currency currency_enum DEFAULT 'DKK', -- Use USD for dollar, DKK for danish kroner etc.

account INT,

vat_code VARCHAR(10), -- Used to record the VAT code of the company our customer has bought from or sold to.

offset_account INT,

s3_path_to_original_file VARCHAR(300),

s3_path_to_image VARCHAR(300),

received_date TIMESTAMP,

deleted BOOLEAN DEFAULT false,

typed SMALLINT DEFAULT 0, -- Number of typers that have typed the amount

checked BOOLEAN DEFAULT false,

reconciled BOOLEAN DEFAULT false,

receipt_type SMALLINT DEFAULT 0, -- Receipt 0 is unknown, 1 means entry_date is before bank transaction (invoice/bill/cash payment), 2 is after, 3 is immediately after or same date

expense BOOLEAN DEFAULT true, -- If it is not an expense, it is an income

accounted BOOLEAN DEFAULT false,

degrees SMALLINT DEFAULT 0, -- Used for storing the number of degrees to rotate the image.

email_conversation VARCHAR(9999) DEFAULT '' -- Used for store the email conversation when the user ask to customer.

);

The first one to deliver working SQL statements (probably involving ALTER TABLE) to achieve this gets 15$.

Completed by:
Skills Required

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online