sql-ex.ru #30 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql-ex.ru #30

Author

Topic

lcblank
Starting Member

10 Posts

Posted - 2015-05-05 : 19:07:31

So, below is the problem:
"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.

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."

So, they want the total income and outcome grouped by date and point.
I created a very sloppy code that doesnt work. I am not quite sure how to accomplish this.

It is past salvaging but here is the code:

select  point, date, sum(out) as outcome, sum(inc) as income 
from 
   (select 
      case when outcome.date is not null then outcome.date else income.date 
      end as date,
      case when outcome.point is not null then outcome.point else 
      income.point end as point,
      inc, out
   from outcome full join income on outcome.date=income.date and 
   income.point=outcome.point) as a
group by date, point

lcblank
Starting Member

10 Posts

Posted - 2015-05-05 : 19:20:02

So, I simplified this to

SELECT Income.point, Income.date, SUM(out), SUM(inc)
FROM Income LEFT JOIN 
 Outcome ON Income.point = Outcome.point AND
 Income.date = Outcome.date
GROUP BY Income.point, Income.date
UNION
SELECT Outcome.point, Outcome.date, SUM(out), SUM(inc)
FROM Outcome LEFT JOIN 
 Income ON Income.point = Outcome.point AND
 Income.date = Outcome.date
GROUP BY Outcome.point, Outcome.date

.
The issue here is that there is no primary key so entries are being repeated with several matches.

[url]http://www.sql-tutorial.ru/en/book_exercise_30.html[/url]

How would I fix this?

lcblank
Starting Member

10 Posts

Posted - 2015-05-05 : 19:35:05

[code]With a as (SELECT Income.point, Income.date, SUM(inc) as sinc
FROM Income
GROUP BY Income.point, Income.date),
b as
(SELECT Outcome.point, Outcome.date, SUM(out) as sout
FROM Outcome
GROUP BY Outcome.point, Outcome.date)
select a.point, a.date, sout, sinc from a left join b on a.date=b.date and a.point=b.point
union
select b.point, b.date, sout, sinc
from b left join a on a.date=b.date and a.point=b.point[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources