How do I select

← →
rashad © (2004-09-22 11:36) [0]

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 :)

← →
Johnmen © (2004-09-22 11:41) [1]

Нет. Лучше ты по-русски.

← →
jack128 © (2004-09-22 11:44) [2]

rashad © (22.09.04 11:36)
Транслит допускается, так что изволь уж перевести ;-)

← →
Rule © (2004-09-22 11:51) [3]

чегото ты браток намутил такого, что я даже со своим знаниями английского не все понял, ты опиши значения полей а не их названия, потомучто логика не понятна

← →
Плохиш © (2004-09-22 11:59) [4]

> rashad © (22.09.04 11:36)

You must read books first

← →
Rule © (2004-09-22 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 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 соответственно

← →
Rule © (2004-09-22 12:01) [6]

хотя это только предположение, так как о лигеке полей я судил судя по названиям, которые не так уж и прозрачны

← →
Rule © (2004-09-22 12:02) [7]

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

← →
stone © (2004-09-22 12:03) [8]

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.

← →
Sandman25 © (2004-09-22 12:09) [9]

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

← →
rashad © (2004-09-22 15:21) [10]

Спосибо 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.

← →
Карелин Артем © (2004-09-22 15:27) [11]

U should write transID instead of ViewTransactions after dots.

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

← →
сергей1 (2004-09-22 18:05) [13]

>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 !

сергей1 (22.09.04 18:05) [13]
Читаем правила. По полного просветления (с) ИШ

>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? :)

Мы тут карячемся на буржуйском языке, а автору помоему пофиг :)

How do I select Найти похожие ветки