Need solution for ex-30 in sql-ex.ru

Question

 Here  is the question

Under the assumption that the income (inc) and expenses (out) of the money at
each outlet (point) are registered any number of times a day, get a result set
with fields: outlet, date, expense, income.
Note that a single record must
correspond to each outlet at each date.
Use Income and Outcome tables.

I am getting exact result but on testing it is failing . Can any one see into it whats the issue?

Thanks In Advance

Gunjan

Answer 1

Gunjan,

Could you please post more information about the table structures ? That would help us provide with a more detailed solution. Based on the limited information you have provided , it seems you have one table for income and another for outcome, and each with multiples rows for each outlet.

to get the result set you are looking for, you need to join the income and outcome tables on the outlet, sum the amount across the transactions and group by date and outlet. your query will look something like this ;

SELECT
a.outlet,
a.date,
/** note, use an appropriate date/time function here to strip out the time from a datetime datatype column **/
sum(a.amount) as income_amount,
sum(b.amount) as outgo_amount
FROM
income as a
INNER JOIN outgo as b 
ON a.outlet = b.outlet
GROUP BY
a.outlet,
a.date 
/** note, use an same date/time function here in group by as you have used in the select for the date/time data type column **/

This is just a basic sample query for your understanding. depending on your specific scenario, you may need to use left, right or full outer join, instead of an inner join. Here is a link to an msdn blog on how to write joins in TSQL and how they work. http://blogs.msdn.com/b/spike/archive/2008/07/22/the-simplification-of-join-simple-example-of-how-t-sql-join-work.aspx 

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

Answer 2

Sanil,

I am giving the information which you have asked for. I am giving you select statement of all table containing all columns. Hope it will make you clear about table structure.

SELECT TOP 1000 [point]
,[date],[out] FROM [inc_out].[dbo].[Outcome_o]

SELECT TOP 1000 [code]
 ,[point]
 ,[date]
 ,[out] FROM [inc_out].[dbo].[Outcome]

SELECT TOP 1000 [point]
 ,[date]
 ,[inc]
  FROM [inc_out].[dbo].[Income_o]

SELECT TOP 1000 [code]
 ,[point]
 ,[date]
 ,[inc]
  FROM [inc_out].[dbo].[Income]

Answer 3

It helps, but not significantly, since i am not sure what the columns "point", "code" , etc mean. Also, what is the difference between the outcome_o table and the outcome table ? Same question for the 2 income tables ? So i am making a few assumptions while refining my previous solution here ;

• Using the "outcome" and the "income" tables in my query ( you can use the "_o" tables as well if you so choose)
• Assuming that the column "point" represents a cash register (you can have many of those in 1 outlet) and the column "code" represents the outlet code, i am joining the 2 tables on the column "code". The idea is to join the tables based on whatever column represents the outlet, because you want to match the in & out transactions for each outlet on each day.
• Also assuming that the date column has ONLY date and the time part of the datetime datatype has been stripped out. If that is not the case, use an appropriate date/time function to achieve this. See this thread for this purpose : http://social.msdn.microsoft.com/Forums/en/transactsql/thread/62dbe447-5e06-463c-b14e-e01f9c813c0f 
• I am assuming that the "inc" and "out" columns are storing the transaction amounts. The datatype used is either decimal or money. 
• I am using an INNER JOIN in my query , assuming that each outlet has a row for each day in both income & outcome tables. If that is not the case,please use the appropriate OUTER JOIN

This is what the query will be, based on details you gave and the assumptions i have filled in ;

SELECT
a.code as outlet,
a.date,
sum(a.inc) as income_amount,
sum(b.out) as outgo_amount
FROM
income as a
INNER JOIN outcomeas b 
ON code= code
GROUP BY
a.code,
a.date ;

Hope this helps !

 

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

Answer 4

Here is the description to understand tables:

The firm has a few outlets that receive items for recycling. Each of the outlets
receives funds to be paid to deliverers. Information on received funds is
registered in a table:
Income_o(point, date, inc)
The primary key is
(point, date), thus receiption of money (inc) takes place not more than once a
day (date column does not include time component of the date). Information on
payments to deliverers is registered in the table:
Outcome_o(point, date,
out)
In this table the primary key (point, date) also ensures bookkeeping of
the funds distribution at each point not more than once a day.
In case
incomes and expenses may occur more than once a day, another database schema is
used. Corresponding tables include code column as primary key:
Income(code,
point, date, inc)
Outcome(code, point, date, out)
In this schema date
column does not also include the day time.

Answer 5

So, are you saying that the income_o & outcome_o tables have 1 row per per outlet per day ? If yes, that makes the scenario all the more easier. You don't even have to use sum and group by - just join these 2 tables on the columns point & date in a simple select statement. Since you seem to doing this form a studying perspective, as your thread heading suggests , you definitely want to give it a try and write this simple sql statement yourself, based on these tips. Good Luck and happy learning.

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.