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

Откуда: Гималай
Сообщений: 2101
Приветствую всех, приветствую лучший форум по SQL!!!
Поздравляю всех с прошедшими праздниками ;)
Нужен совет коллеги, есть следующий запрос:
SELECT
	CAST(CONVERT(varchar,PayDT,104) AS datetime) AS DT,
	SUM(PaySum) AS Total,
	COUNT(*) AS Amount 
FROM
	TblDocPayments WITH (NOLOCK)
WHERE
	PayDT>='20090901 00:00:00' AND PayDT<='20090916 23:59:59' AND Canceled=0 
GROUP BY
	CAST(CONVERT(varchar,PayDT,104) AS datetime) WITH ROLLUP
ORDER BY
	GROUPING(CAST(CONVERT(varchar,PayDT,104) AS datetime))

В результате возвращается итоговая строка, но в столбце DT этой строки значение NULL
Как сделать так чтобы была строка 'TOTAL'

Сделал так:
SELECT
	DT = CASE WHEN GROUPING(CAST(CONVERT(varchar,PayDT,104) AS datetime)) =1 THEN 'TOTAL'
	ELSE CAST(CONVERT(varchar,PayDT,104) AS datetime)
	END,
	SUM(PaySum) AS Total,
	COUNT(*) AS Amount 
FROM
	TblDocPayments WITH (NOLOCK)
WHERE
	PayDT>='20090901 00:00:00' AND PayDT<='20090916 23:59:59' AND Canceled=0 
GROUP BY
	CAST(CONVERT(varchar,PayDT,104) AS datetime) WITH ROLLUP
ORDER BY
	GROUPING(CAST(CONVERT(varchar,PayDT,104) AS datetime))

Но выходит следующая ошибка:
автор
Ошибка преобразования datetime из символьной строки.

Если же преобразовать дату в строку выйдет, но сортировка при выборке данных за несколько месяцев идет неправильная, как можно решить проблему?
Спасибо всем за внимание
16 сен 09, 13:32    [7667098]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
Скрипт создания и заполнения таблицы?
16 сен 09, 13:38    [7667151]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Объясните, что это такое?!
CAST(CONVERT(varchar,PayDT,104) AS datetime))
Почему из даты в строку - 104-й формат, а обратно - какой бог на душу положит?
Надо отбросить время?
DATEADD(DAY, DATEDIFF(DAY,0,PayDT),0)
Или в Вашем варианте замените 104 на 112
16 сен 09, 13:42    [7667179]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
CREATE TABLE [dbo].[TblDocPayments](
	[PayDT] [datetime] NOT NULL CONSTRAINT [DF_TblDocPayments_PayDT]  DEFAULT (getdate()),
	[PaySum] [money] NOT NULL CONSTRAINT [DF_TblDocPayments_PaySum]  DEFAULT ((0)),
	[Canceled] [smallint] NOT NULL CONSTRAINT [DF_TblDocPayments_Canceled]  DEFAULT ((0))
 CONSTRAINT [PK_TblDocPayments] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO TblDocPayments (PayDT, PaySum, Canceled)
VALUES ('20090914 00:00:00', 100, 0)
INSERT INTO TblDocPayments (PayDT, PaySum, Canceled)
VALUES ('20090915 00:00:00', 100, 0)
INSERT INTO TblDocPayments (PayDT, PaySum, Canceled)
VALUES ('20090915 15:00:00', 100, 0)
INSERT INTO TblDocPayments (PayDT, PaySum, Canceled)
VALUES ('20090916 00:00:00', 100, 0)
INSERT INTO TblDocPayments (PayDT, PaySum, Canceled)
VALUES ('20090916 12:00:00', 100, 0)
INSERT INTO TblDocPayments (PayDT, PaySum, Canceled)
VALUES ('20090916 16:30:00', 100, 0)
16 сен 09, 13:53    [7667264]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iap
Объясните, что это такое?!
CAST(CONVERT(varchar,PayDT,104) AS datetime))
Почему из даты в строку - 104-й формат, а обратно - какой бог на душу положит?
Надо отбросить время?
DATEADD(DAY, DATEDIFF(DAY,0,PayDT),0)
Или в Вашем варианте замените 104 на 112

:-) понимаю что это "варварский" метод, делал для того чтобы взять только дату
не подскажете вариант "отбрасывания" времени?
16 сен 09, 13:54    [7667269]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
orunbek
iap
Объясните, что это такое?!
CAST(CONVERT(varchar,PayDT,104) AS datetime))
Почему из даты в строку - 104-й формат, а обратно - какой бог на душу положит?
Надо отбросить время?
DATEADD(DAY, DATEDIFF(DAY,0,PayDT),0)
Или в Вашем варианте замените 104 на 112

:-) понимаю что это "варварский" метод, делал для того чтобы взять только дату
не подскажете вариант "отбрасывания" времени?
А Вы разве не процитировали два способа, которые я для Вас написал?
16 сен 09, 13:58    [7667296]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
2iap:
Спасибо теперь протестил, работает отлично
А вот насчет замены NULL на строку не подскажете?
16 сен 09, 14:44    [7667709]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek,

isnull?
16 сен 09, 14:46    [7667730]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
SELECT
	ISNULL(cast(cast(PayDT AS date) as varchar(20)), 'ALL'),
	SUM(PaySum) AS Total,
	COUNT(*) AS Amount 
FROM
	TblDocPayments WITH (NOLOCK)
WHERE
	PayDT>='20090901' AND PayDT<='20090916' AND Canceled=0 
GROUP BY
	cast(PayDT AS date) WITH ROLLUP
ORDER BY
	GROUPING(cast(PayDT AS date))
-------------------------------------
Jedem Das Seine
16 сен 09, 14:53    [7667824]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek,

isnull?


SELECT
	ISNULL(DATEADD(day,DATEDIFF(day,0,PayDT),0),'TEST') AS DT,
	SUM(PaySum) AS Total,
	COUNT(*) AS Amount 
FROM
	TblDocPayments WITH (NOLOCK)
WHERE
	PayDT>='20090901 00:00:00' AND PayDT<='20090916 23:59:59' AND Canceled=0 
GROUP BY
	DATEADD(day,DATEDIFF(day,0,PayDT),0) WITH ROLLUP
ORDER BY
	GROUPING(DATEADD(day,DATEDIFF(day,0,PayDT),0)), DT
автор
ошибка преобразования datetime из символьной строк
16 сен 09, 14:54    [7667837]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek,

ISNULL(cast(DATEADD(day,DATEDIFF(day,0,PayDT),0) as varchar),'TEST')
16 сен 09, 14:58    [7667873]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek,

ISNULL(cast(DATEADD(day,DATEDIFF(day,0,PayDT),0) as varchar),'TEST')

Спасибо
Ваш код отлично работает, но проблема в формате даты.
В формате dd.mm.yyyy не получается вывести с правильной сортировкой данных за несколько месяцев
Т.е. через код:
SELECT
	ISNULL(CONVERT(varchar,DATEADD(day,DATEDIFF(day,0,PayDT),0),104),'Total') AS DT,
	SUM(PaySum) AS Total,
	COUNT(*) AS Amount 
FROM
	TblDocPayments WITH (NOLOCK)
WHERE
	PayDT>='20090801 00:00:00' AND PayDT<='20090916 23:59:59' AND Canceled=0 
GROUP BY
	DATEADD(day,DATEDIFF(day,0,PayDT),0) WITH ROLLUP
ORDER BY
	GROUPING(DATEADD(day,DATEDIFF(day,0,PayDT),0)), DT
16 сен 09, 19:15    [7669852]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek,

простите, а какая связь между моим кодом и порядком сортировки?? мой код в предложении SELECT, а не ORDER BY!
А какой собственно порядок вам нужен?
16 сен 09, 21:26    [7670189]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek,

простите, а какая связь между моим кодом и порядком сортировки?? мой код в предложении SELECT, а не ORDER BY!
А какой собственно порядок вам нужен?

Во первых скажу, что возможно нужно выбрать данные за несколько месяцев, и что даты должны выводиться в следующем в виде:
DT Total Amount
01.07.2009 5 1
05.07.2009 1500 5
20.08.2009 6 1
28.08.2009 1000 2
01.09.2009 100 5
02.09.2009 200 3
03.09.2009 50 1
10.09.2009 200 4

когда данные вывожу через ваш код
выходят данные в следующем виде (грубо говоря):
DT Total Amount
01 jul 2009 5 1
05 jul 2009 1500 5
20 jul 2009 6 1
28 jul 2009 1000 2
...

т.е. не формат dd.mm.yyyy а dd.MMMM.yyyy - короткое наименование месяца
Спасибо Вам за внимание
17 сен 09, 11:47    [7671968]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek,

используйте вместо cast convert с явным указанием формата.
17 сен 09, 12:33    [7672370]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
CONVERT(varchar,PayDT,104)
так к примеру?
17 сен 09, 12:55    [7672523]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek
CONVERT(varchar,PayDT,104)
так к примеру?

к примеру так. Определитесь - какой именно формат вам нужен, и вперед.
17 сен 09, 13:08    [7672664]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek
CONVERT(varchar,PayDT,104)
так к примеру?

к примеру так. Определитесь - какой именно формат вам нужен, и вперед.

в этом случае данные выходят в таком виде:
DT Total Amount
01.07.2009 100 1
01.08.2009 200 3
02.07.2009 500 4
... ... ...

и т.д., т.е. сортировка не та
17 сен 09, 14:32    [7673428]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
orunbek,
ну так в оред бай - напишите такой же конверт
17 сен 09, 14:43    [7673542]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Maxx
orunbek,
ну так в оред бай - напишите такой же конверт


вот так:
SELECT
	ISNULL(CONVERT(varchar,DATEADD(day,DATEDIFF(day,0,PayDT),0),104),'Total') AS DT,
	SUM(PaySum) AS Total,
	COUNT(*) AS Amount 
FROM
	TblDocPayments WITH (NOLOCK)
WHERE
	PayDT>='20090801 00:00:00' AND PayDT<='20090916 23:59:59' AND Canceled=0 
GROUP BY
	DATEADD(day,DATEDIFF(day,0,PayDT),0) WITH ROLLUP
ORDER BY
	GROUPING(DATEADD(day,DATEDIFF(day,0,PayDT),0)), DT

?
17 сен 09, 14:50    [7673616]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
тип того,у меня получаеться правильній результат
-------------------------------------
Jedem Das Seine
17 сен 09, 14:58    [7673711]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek,

вы так и не сказали - какую все-таки хотите сортировку. сейчас у вас сортируется странно, по GROUPING, т.е. сначала записи по датам, потом итог, но при этом по датам сортировка не задана никакая.
17 сен 09, 15:36    [7674109]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek,

не, вру, бес попутал, есть сортировка по дате, только она не по дате, а по строке, которая вычисляется. хотите именно по дате - сделайте так:

ORDER BY
	GROUPING(DATEADD(day,DATEDIFF(day,0,PayDT),0)), DATEADD(day,DATEDIFF(day,0,PayDT),0)
17 сен 09, 15:49    [7674209]     Ответить | Цитировать Сообщить модератору
 Re: Итого запроса через ROLLUP  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek,

не, вру, бес попутал, есть сортировка по дате, только она не по дате, а по строке, которая вычисляется. хотите именно по дате - сделайте так:

ORDER BY
	GROUPING(DATEADD(day,DATEDIFF(day,0,PayDT),0)), DATEADD(day,DATEDIFF(day,0,PayDT),0)

Спасибо!!! В самый раз!!!
Всем спасибо, тема закрыта
18 сен 09, 15:45    [7679898]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить