Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

How do I select [MSSQL]

rashad © (2004-09-22 11:36:00)
Hello Everybody.

I have got a very difficult situation, I cannot select data from database that exactly matches the given condition.

The point is:
I store this data in many tables of the database.
The database is created in SQL SERVER 2000

I load data using View named ViewTransactions
Fields of the result is:
transID, ref, quantity, TransDate, dateAdded, spareID, spareNum, TransType, ModuleName, IsOut.

I must load only those spareParts, which has Total Sales for the last N monthes, more than their balance.

To find TotalSale and Balance I have own algorytms,
- Total sales is total OUT transactions registered for the spare.
- Total balance is total IN transactions minus total OUT transactions.

How do I create query that returns to me that I need?

You can reply in Russian.

Thanks preliminary :)

Rule (2004-09-23 09:21:00) [1]
Мы тут карячемся на буржуйском языке, а автору помоему пофиг :)

ЮЮ (2004-09-23 03:37:00) [2]
>Total sales is total OUT transactions registered for the spare.

SELECT spareID, SUM(quantity) TotalOuts
FROM Table /* not from View - it's faster */
WHERE (IsOut = 1) /* AND <dates limitation> */
GROUP BY spareID

In same way for IN transactions we have:

SELECT spareID, SUM(quantity) TotalIns
FROM Table /* not from View - it's faster */
WHERE (IsOut = 0) /* AND <dates limitation> */
GROUP BY spareID

Now set relation between two results:

SELECT
ISNULL(Outs.spareID, Ins.spareID) spareID,
ISNULL(TotalOuts, 0) TotalOuts,
ISNULL(TotalIns, 0) TotalIns,
ISNULL(TotalOuts, 0) - ISNULL(TotalIns, 0) Balance
FROM
( SELECT spareID, SUM(quantity) TotalOuts
FROM Table /* not from View - it's faster */
WHERE (IsOut = 1) /* AND <dates limitation> */
GROUP BY spareID
) Outs
FULL JOIN
(
SELECT spareID, SUM(quantity) TotalIns
FROM Table /* not from View - it's faster */
WHERE (IsOut = 0) /* AND <dates limitation> */
GROUP BY spareID
) Ins
ON Outs.spareID = Ins.spareID
WHERE
/* sales > balance
sales > ins - sales
ins < 2 * sales
*/
ISNULL(TotalIns, 0) < 2 * ISNULL(TotalOuts, 0)

Do you undestand? :)

jack128 (2004-09-22 18:10:00) [3]
сергей1 (22.09.04 18:05) [13]
Читаем правила. По полного просветления (с) ИШ

сергей1 (2004-09-22 18:05:00) [4]
>2. To write in Russian that a lot the person could understand you.

everybody, who don't have a russian keyboard should do just like Rashad, i.e. write in english, because it is much easier to understand than translit, I hate translit. When I read such text, I often cannot undestand what is it about. All programmers, even they are russian, are able to read english text, because if you aren't, you shouldn't be a programmer. So please, ne pishite vot tak !

Rule (2004-09-22 15:38:00) [5]
rashad © (22.09.04 15:21) [10]

хех, коенчно неправильно(чегото не о том думал когда писал), надо сделать так :

select spareID from ViewTransactions a where
((select sum(quantity) from ViewTransactions b where a.spareID=b.spareID and isOut={true} and TransDate>:dt)>((select sum(quantity) from ViewTransactions с where a.spareID=с.spareID and isOut={false} and TransDate>:dt)-(select sum(quantity) from ViewTransactions d where a.spareID=d.spareID and isOut={true} and TransDate>:dt))) and TransDate>:dt

Карелин Артем (2004-09-22 15:27:00) [6]
U should write transID instead of ViewTransactions after dots.

rashad (2004-09-22 15:21:00) [7]
Спосибо Rule.
Thanks.

But I tried to use the query that you provided. But Enterprice Manager throws an exception saying:
Invalid Coulumn Name 'ViewTransactions'.

I remind that, ViewTransactions is just a View, not a Table or field.

Please, tell me more detailed :)
Thanks Preliminary.

Sandman25 (2004-09-22 12:09:00) [8]
select spareParts
from ViewTransactions
where TrandDate > dateAdd(тут по BOL сами найдете, что писать)
group by 1
having sum(
case IsOut
when Out
2*quantity
else
-quantity
end) > 0

Дело в том, что из sales > balance следует, что 2*Out > In

stone (2004-09-22 12:03:00) [9]
Hello, rashad.

For the decision of this problem you should:
1. To specify structure of the tables containing fields sales, balance and other.
2. To write in Russian that a lot the person could understand you.

Rule (2004-09-22 12:02:00) [10]
Rule © (22.09.04 12:00) [5]
небольшая ошибка, корректирую
select spareID from ViewTransactions a where
((select sum(quantity) from ViewTransactions b where a.ViewTransactions=b.ViewTransactions and isOut={true} and TransDate>:dt)>((select sum(quantity) from ViewTransactions с where a.ViewTransactions=с.ViewTransactions and isOut={false} and TransDate>:dt)-(select sum(quantity) from ViewTransactions d where a.ViewTransactions=d.ViewTransactions and isOut={true} and TransDate>:dt))) and TransDate>:dt

Rule (2004-09-22 12:01:00) [11]
хотя это только предположение, так как о лигеке полей я судил судя по названиям, которые не так уж и прозрачны

Rule (2004-09-22 12:00:00) [12]
но насколько я понял запрос будет выглядеть так

select spareID from ViewTransactions a where
((select sum(quantity) from ViewTransactions b where a.ViewTransactions=b.ViewTransactions and isOut={true} and TransDate>:dt)>((select sum(quantity) from ViewTransactions b where a.ViewTransactions=b.ViewTransactions and isOut={false} and TransDate>:dt)-(select sum(quantity) from ViewTransactions b where a.ViewTransactions=b.ViewTransactions and isOut={true} and TransDate>:dt))) and TransDate>:dt

где :dt можешь посчитать типа сейчас минус количество твоих N- месяцев

а {true} и {false} - это признак того OUT transaction у тебя или IN transaction соответственно

Плохиш (2004-09-22 11:59:00) [13]

> rashad © (22.09.04 11:36)

You must read books first

Rule (2004-09-22 11:51:00) [14]
чегото ты браток намутил такого, что я даже со своим знаниями английского не все понял, ты опиши значения полей а не их названия, потомучто логика не понятна

jack128 (2004-09-22 11:44:00) [15]
rashad © (22.09.04 11:36)
Транслит допускается, так что изволь уж перевести ;-)

Johnmen (2004-09-22 11:41:00) [16]
Нет. Лучше ты по-русски.

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »