Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вычисление суммы за предыдущую дату  [new]
jkterinq
Member

Откуда:
Сообщений: 7
Помогите решить проблему.
Не получается правильно составить запрос.
Необходимо посчитать остаток товара на складе.

Есть код, но при запуске возникает ошибка в 5-ой строке: Invalid object name 'Q1'. Invalid object name 'Q2'. Если заменить на FROM nb.TEreport, тогда код запускается, но данные считает неправильно (за исключением первой строки 1 янв.)

Как правильно задать FROM?

SELECT Q1.Dat, Q1.Goods AS Q1, Q2.Goods AS Q2, Q1.Goods - Q2.Goods  AS Store_1,
(CASE WHEN Q1.Dat = '2013.01.01' THEN Q1.Goods - Q2.Goods + 330016
ELSE (Q1.Goods - Q2.Goods) + (SELECT SUM(Q1.Goods - Q2.Goods) + 330016 
                              FROM Q1, Q2
                              WHERE Q1.Dat >= '2013.01.01' ) 
                              END ) AS Store_2 
FROM
    (SELECT TOP 100 PERCENT CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) AS Dat, 
    SUM(nb.TEreport.goods) AS Goods
    FROM nb.TEreport INNER JOIN nb.TEprovider ON nb.TEreport.id_provider = nb.TEprovider.id
    WHERE (LEFT(nb.TEprovider.name, 9) != 'СО СКЛАДА') 
    AND (CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) <= CONVERT(VARCHAR, GETDATE(), 102))
    AND CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) >= '2013.01.01'
    GROUP BY CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102)
    ORDER BY Dat ) Q1 --Приход
INNER JOIN
    (SELECT TOP 100 PERCENT CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) AS Dat, 
    SUM(nb.TEreport.goods) AS Goods
    FROM nb.TEreport INNER JOIN nb.TEway ON nb.TEreport.id_way = nb.TEway.id
    WHERE (SUBSTRING(nb.TEway.name, 5, 8) != 'на склад') 
    AND (CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) <= CONVERT(VARCHAR, GETDATE(), 102))
    AND CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) >= '2013.01.01'
    GROUP BY CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102)
    ORDER BY Dat ) Q2 --Расход
ON Q1.Dat = Q2.Dat
ORDER BY Q1.Dat



Q1 - Приход
Q2 - Расход
Store_1 - Разница между приходом и расходом
Store_2 - Остаток на складе (В данном случае работает только 1 января, т.е. 330016 + 12650 = 342666, 330016 - число по умолчанию. А чтоб посчитать 2 января, надо 10857 + 342666 = 353523, 3 января = 3777 + 353523 и т.д.)
Что у меня есть сейчас (см. рис):

К сообщению приложен файл. Размер - 42Kb
11 мар 13, 14:34    [14035563]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
может быть поможет тынц
11 мар 13, 14:40    [14035609]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Чтобы не было синтаксических ошибок:
; with Q1 as     (SELECT TOP 100 PERCENT CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) AS Dat, 
    SUM(nb.TEreport.goods) AS Goods
    FROM nb.TEreport INNER JOIN nb.TEprovider ON nb.TEreport.id_provider = nb.TEprovider.id
    WHERE (LEFT(nb.TEprovider.name, 9) != 'СО СКЛАДА') 
    AND (CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) <= CONVERT(VARCHAR, GETDATE(), 102))
    AND CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) >= '2013.01.01'
    GROUP BY CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102)
    ORDER BY Dat )
, Q2 as (SELECT TOP 100 PERCENT CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) AS Dat, 
    SUM(nb.TEreport.goods) AS Goods
    FROM nb.TEreport INNER JOIN nb.TEway ON nb.TEreport.id_way = nb.TEway.id
    WHERE (SUBSTRING(nb.TEway.name, 5, 8) != 'на склад') 
    AND (CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) <= CONVERT(VARCHAR, GETDATE(), 102))
    AND CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102) >= '2013.01.01'
    GROUP BY CONVERT(VARCHAR, DATEADD(s, nb.TEreport.data, 25568), 102)
    ORDER BY Dat )    
SELECT Q1.Dat, Q1.Goods AS Q1, Q2.Goods AS Q2, Q1.Goods - Q2.Goods  AS Store_1,
(CASE WHEN Q1.Dat = '2013.01.01' THEN Q1.Goods - Q2.Goods + 330016
ELSE (Q1.Goods - Q2.Goods) + (SELECT SUM(Q1.Goods - Q2.Goods) + 330016 
                              FROM Q1, Q2
                              WHERE Q1.Dat >= '2013.01.01' ) 
                              END ) AS Store_2 
FROM        Q1 --Приход
INNER JOIN  Q2 --Расход
ON Q1.Dat = Q2.Dat
ORDER BY Q1.Dat
11 мар 13, 14:41    [14035621]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
jkterinq
Member

Откуда:
Сообщений: 7
Гавриленко Сергей Алексеевич, ругается на WITH и на ORDER
11 мар 13, 14:51    [14035713]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
jkterinq
Гавриленко Сергей Алексеевич, ругается на WITH и на ORDER
Какая же у Вас версия сервера? Секрет?
Какой смысл в TOP 100 PERCENT?
Строковое представление дат зависит от разных настроек - надо бы точки убрать.
И не надо тогда конвертировать в строку перед сравнением.
11 мар 13, 14:58    [14035767]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
jkterinq
Member

Откуда:
Сообщений: 7
iap,
Сервер 2008
Без топ 100 не работает, выдает The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Дата хранится в секундах, а сгруппировать нужно именно по дате.
11 мар 13, 15:05    [14035812]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Без слез не взглянешь.
1. Убрать все конверты в varchar,
2. Убрать всякие TOP,
3. По возможности вынести GETDATE() и '2013.01.01' в переменные, на которые натравить DATEADD(s, <переменная>, 25568) и уже после этого ставить условие на колонки nb.TEreport.data >= <переменная>
4. 25568 и 342666 - вынести в константу, ибо грозит тем что "ой, вот тут забыли поменять"
5. LEFT(nb.TEprovider.name, 9) != 'СО СКЛАДА' -это просто 5 баллов. Конечно можно оставить, но это грустно...
11 мар 13, 15:07    [14035835]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
jkterinq
Member

Откуда:
Сообщений: 7
ambarka_max,
спасибо за комментарии, я еще в процессе обучения sql. Но даже если учесть ваши советы, то текущую проблему это вряд ли решит
11 мар 13, 15:17    [14035918]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Отично решит и избавит от дальнейших. Удаляетете не просто TOP а "TOP 100 PERCENT" и соответсвующий "ORDER BY..."
Далее советую вместо INNER JOIN применить FULL OUTER JOIN , т.к. иначе если будут дни с приходом но без расхода, или наоборот - в результате вы их не увидете.
11 мар 13, 15:25    [14035981]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
jkterinq
Member

Откуда:
Сообщений: 7
ambarka_max,
Спасибо, TOP убрала, заменила INNER JOIN. С переменными сложнее, потому что ограниченный доступ к БД. Вопрос по прежнему остается открытым.
11 мар 13, 15:31    [14036028]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
jkterinq
ambarka_max,
Спасибо, TOP убрала, заменила INNER JOIN. С переменными сложнее, потому что ограниченный доступ к БД. Вопрос по прежнему остается открытым.
По-Вашему, переменные - это что?!
11 мар 13, 16:04    [14036254]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
jkterinq
Member

Откуда:
Сообщений: 7
iap, знаю только, как работать с переменными при создании функций, но не в запросах
11 мар 13, 16:09    [14036284]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
jkterinq
iap, знаю только, как работать с переменными при создании функций, но не в запросах

там всё очень сложно
11 мар 13, 16:49    [14036555]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
jkterinq
Member

Откуда:
Сообщений: 7
Зайцев Фёдор,
Действительно :) Но код подкорректировать я смогу позже, сейчас проблема в другом
11 мар 13, 17:01    [14036633]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммы за предыдущую дату  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
;with 
TEway as (select * from (values(1,'ХХХ со склада ХХХ'),(2,'ХХХ на склад ХХХ'))t(id, name)),
TEreport as (select * from (values (0, 1356912000- 1*86400, 330016, 1),
   (11, 1356912000+ 0*86400+40000, 20000, 1),(12,1356912000+ 0*86400+50000,28046,2),(13,1356912000+ 0*86400+60000,20696,1),
   (21, 1356912000+ 1*86400+40000, 45742, 1),(22,1356912000+ 1*86400+50000,14885,2),(23,1356912000+ 1*86400+60000,20000,2),
   (31, 1356912000+ 2*86400+40000, 41827, 1),(32,1356912000+ 2*86400+50000,38050,2),(44,1356912000+ 3*86400+70000,18625,2),
   (41, 1356912000+ 3*86400+40000, 22336, 1),(42,1356912000+ 3*86400+50000,22336,1),(43,1356912000+ 3*86400+60000,20000,2)
   )t(id, data,goods, id_way)),
--;with 
CTE as (
   SELECT cast(DATEADD(s, TEreport.data, 25568)as DATE) Dat, Q1, Q2
   FROM TEreport 
   JOIN TEway ON TEreport.id_way = TEway.id
   cross apply (select 
      case when SUBSTRING(TEway.name, 5, 8) !='на склад' then goods else 0 end Q1,
      case when SUBSTRING(TEway.name, 5, 8) = 'на склад' then goods else 0 end Q2
      ) c
   )
select Dat, SUM(Q1) Q1, SUM(Q2) Q2, 
   (select SUM(Q1-Q2)prev from CTE where Dat < c.Dat)+SUM(Q1)-SUM(Q2) Qrez
from CTE c
WHERE Dat between '20130101' AND GETDATE()
GROUP BY Dat
11 мар 13, 21:02    [14037395]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить