Home > Default > Sql monthly report

Sql monthly report

November 30Hits:0
Advertisement
Hello. I have a problem that it may be trivial for you but gave me a lot of headaches. I got 4 tables. This is the code for all of them:
CREATE TABLE customers
     name           VARCHAR2(40) NOT NULL PRIMARY KEY,
     address      VARCHAR2(40) NOT NULL,
     balance          NUMBER(10) NOT NULL
INSERT INTO customers VALUES ('Shaun Turtle', '4 Family Avenue', '-200');
INSERT INTO customers VALUES ('Samantha Salamander', '53 Lois Lane', '-50');
INSERT INTO customers VALUES ('Ruth Raptor', '21 Stony Street', '43');
CREATE TABLE orders
     order_no     NUMBER(8) NOT NULL PRIMARY KEY,
     order_date     DATE NOT NULL,
     cust          VARCHAR2(40) NOT NULL REFERENCES customers(name)
INSERT INTO orders VALUES ('624', '10-Mar-2011', 'Shaun Turtle');
INSERT INTO orders VALUES ('625', '20-Mar-2011', 'Ruth Raptor');
INSERT INTO orders VALUES ('626', '25-Mar-2011', 'Shaun Turtle');
CREATE TABLE includes
     order_no     NUMBER(8) NOT NULL REFERENCES orders(order_no),
     item          VARCHAR2(40) NOT NULL ,
     qty          NUMBER(10) NOT NULL,
     PRIMARY KEY(order_no,item)
INSERT INTO includes VALUES ('624', 'Cheddar', '3');
INSERT INTO includes VALUES ('624', 'Chardonnay', '6');
INSERT INTO includes VALUES ('625', 'Cheddar', '5');
INSERT INTO includes VALUES ('625', 'Escargot', '12');
INSERT INTO includes VALUES ('625', 'Rocket', '1');
INSERT INTO includes VALUES ('625', 'Walnuts', '1024');
INSERT INTO includes VALUES ('626', 'Walnuts', '2048');
CREATE TABLE supplies
     name          VARCHAR2(40) NOT NULL,
     item          VARCHAR2(40)NOT NULL,
     price          NUMERIC(8,2) NOT NULL,
     PRIMARY KEY(name, item)
INSERT INTO supplies VALUES ('Acme', 'Cheddar', '3.49');
INSERT INTO supplies VALUES ('Acme', 'Chardonnay', '5.19');
INSERT INTO supplies VALUES ('Acme', 'Walnuts', '0.06');
INSERT INTO supplies VALUES ('Acme', 'Escargot', '0.25');
INSERT INTO supplies VALUES ('Ajax', 'Cheddar', '3.90');
INSERT INTO supplies VALUES ('Ajax', 'Chardonnay', '5.09');
INSERT INTO supplies VALUES ('Ajax', 'Rocket', '0.69');
The task is: At the end of the month the organisation likes to produce a report which details each customer’s real balance taking into account, firstly their current balance, and then all orders made during that month. They always ensure that each item ordered comes from the cheapest supplier. The report must include the following information: customer’s name, address, old balance, new balance and number of items that have been ordered by that customer during that period (you do not need to count the number of orders for any one customer). Your task is to produce the report for March 2011, assuming that current balances shown are correct as of the end of February 2011. Note that the report should be written using the minimal number of SQL statements possible.
Can you help me with this please?

Answers

It appears like a school homework, the tables don't have PKs and there are not normalized
The problem is also classic, I suggest you to apply divide and conquer strategy, you need to calculate the balance at the end of the last month, and the current month operations... so, you have 2 very different queries. Write one of the queries, then write the second, and finally try to put both togheter using "union all"
Also, you can found usefull to use an imaginary column like row_type with values 0 (for the balance) and 1 (for the last operations), and order the final query by row_type and date
I hope this help
Alfonso

Read other 5 answers

Tags:

Related Articles

  • Sql monthly reportNovember 30

    Hello. I have a problem that it may be trivial for you but gave me a lot of headaches. I got 4 tables. This is the code for all of them: CREATE TABLE customers      name           VARCHAR2(40) NOT NULL PRIMARY KEY,      address      VARCHAR2(40) NOT

  • Monthly Reports ScheduleNovember 30

    Hi All, I have few monthly reports which I want to schedule on month end so I have created three subscriptions, 28th of February, 30th of April, June, Sep, Nov and 31st on remaining of the months. Yesterday, on 28th May it executed all the reports fr

  • CUIC monthly reports errorNovember 30

                      HI,                                       I was pulling a stock monthly report and some of the moths are duplicated , but with different data. What could cause this to happen. Thank you for any help.Genrally your CUIC reports are pu

  • How can i make sql formated reportNovember 30

    sql formated report.. empno month sal 10080 500 1000 2000 10020 100 50 will you please give the way...Hi, for make a formated report you will must configure the SQL Plus enviroment, you can review the next link for set the values according at your re

  • Open source user friendly SQL Qyery+Report BuilderOctober 11

    hi guys Please help me find any link from where i can download OpenSource user friendly SQL Qyery+Report Builder thanksOne would hope it was! it is not possible to give everything that the OP may request. The closest match, now that is workable.Read

  • QSUF (Query string url filter) and SQL Server reporting services report viewer parametersOctober 11

    Hi, this is my issue: I have a SQL Server reporting services web part on a page with a report with 1 parameter, lets say it's a client list Then i have a QSUF that will be used to filter the clients list through the URL However, once i connect the fi

  • Dsml and SQL 2005 reporting services?October 11

    I usually post over in the IDM forum so be nice to the newbie here :) I have a Sun Directory server that I've enabled DSML on. On that side everything is good. However, ideally I want to pull this data into a MS SQL 2005 reporting services report. Th

  • Unable to insert sql server reporting services report viewer webpartOctober 11

    Hello, We are trying to add a SQL Server Reporting Services Report Viewer and when we click add we get the following error message The file you imported is not valid. Verify that the file is a Web Part description file (*.webpart or *.dwp) and that i

  • How do I post to SQL Server Reporting servicesOctober 11

    Hi I have been using MS forums for almost 20 years but in the current incarnation I find it utterly imnpossible to post to anywhere useful. I have a serious issue I need to resolve with SQL Server reporting services but cannot find how to get this me

  • Monthly report listing out ALL open POOctober 11

    Hi, As our management requested, we need a monthly report listing out ALL open PO for CDG, APR   Someone tell me to input cost centre but it is time consuming to input it as we have over 100 cost centers.  Do you know if I can input SPG/profit center

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.686 s.