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

Откуда:
Сообщений: 10
Добрый день,

Есть проблема, бьюсь уже 2-й день
Есть отчет в SCCM, который по запросу начальства необходимо выводить в EXCEL, но не в стандартном виде. С самим экспортом проблем нет, но есть проблема с самим запросом.

Пример.
Есть 2 столбца в самой базе:
PROD.ProductName ProductVersion
µTorrent 2.0.1
µTorrent 2.1.3
µTorrent 3.0.3
µTorrent 3.1.2
µTorrent 2.0.1

Необходимо посчитать сколько приложений µTorrent установлено по версиям (сами версии должны быть вида, например, такого 2.0.1 = 2 или 2.1.3 = 2 и т.д.), т.е вывод должен быть таким
PROD.ProductName ProductVersion Count
µTorrent 2 3
µTorrent 3 2


На чём остановился сам:

SELECT PROD.ProductName,
case
when charindex('.', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1) AS INT
when charindex(',', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1) AS INT
end as ProductVersion,
Count(distinct PROD.ResourceID) as Count
FROM v_GS_SoftwareProduct PROD
Group By PROD.ProductName, ProductVersion
Order by PROD.ProductName

Вывод

PROD.ProductName ProductVersion Count
µTorrent 2 2
µTorrent 2 1
µTorrent 3 1
µTorrent 3 1

Иными словами первая и последняя строка сложились по параметру 2.0.1, а вод в как надо не хотят. Почему так произошло, я понимаю. Но вот как сделать правильно :)
Спасибо заранее за помощь!
24 авг 12, 10:17    [13058584]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
Flexo84
Member

Откуда:
Сообщений: 10
Да, забыл пометить 2 строки:
substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1)
и
substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1)
нужны из-за корявости снятия данных самим SCCM/
и ошибку забыл стереть AS INT в строчках выше - осталось от запроса CAST
24 авг 12, 10:20    [13058596]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
SHok_by
Member

Откуда: Minsk
Сообщений: 51
А то что приложения дублируются это нормально?
24 авг 12, 10:29    [13058632]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
SHok_by
Member

Откуда: Minsk
Сообщений: 51
Ну попробуй так:
SELECT LEFT(val,CHARINDEX('.',Val)-1) AS [ProductName], COUNT(*) AS [Count] FROM PROD.ProductName ProductVersion
GROUP BY LEFT(val,CHARINDEX('.',Val)-1)
24 авг 12, 10:31    [13058641]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
Flexo84
Member

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

Они не дублируются в одном случае точка, в другом запятая. Базе лежат значения, например, 2.0.1 и тут же 2,5,5
24 авг 12, 10:44    [13058697]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
qwerty112
Guest
Flexo84
Group By PROD.ProductName, ProductVersion
...
Иными словами первая и последняя строка сложились по параметру 2.0.1, а вод в как надо не хотят. Почему так произошло, я понимаю. Но вот как сделать правильно :)
Спасибо заранее за помощь!

произошло потому, что есть GROUP BY по ProductVersion
вместо него, должен быть весь твой case ... end
24 авг 12, 10:52    [13058745]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
Flexo84
Member

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

Спасибо! Заработало. Я рядом был. Что-то такое думал сделал.
Я правда немного допилил. получилось вот что:

SELECT PROD.ProductName,
case
when
charindex('.', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1)
when
charindex(',', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1)

end as ProductVersion,

Count(case
when
charindex('.', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1)
when
charindex(',', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1)

end) as Count
FROM v_GS_SoftwareProduct PROD
Group By PROD.ProductName, (case
when
charindex('.', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1)
when
charindex(',', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1)

end)
Order by PROD.ProductName

Можно ли как-то этого монстра упростить. Например вводом переменной или т.п.? Или и так хорошо?
24 авг 12, 11:45    [13059058]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
qwerty112
Guest
Flexo84,

нинадо это
Count(case
when 
charindex('.', PROD.ProductVersion, 1) > 1
then 
substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1)
when
charindex(',', PROD.ProductVersion, 1) > 1
then
substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1)

end) as Count

просто напиши count(PROD.ProductVersion) или даже count(*)
24 авг 12, 12:04    [13059182]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
Flexo84
Member

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

угу, спасибо работает)
24 авг 12, 12:19    [13059267]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
Flexo84
Member

Откуда:
Сообщений: 10
Flexo84
qwerty112,

угу, спасибо работает)


а именно Count(PROD.ProductVersion) as Count
24 авг 12, 12:19    [13059271]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
Flexo84
Member

Откуда:
Сообщений: 10
Может кому пригодится код.
Задача.
Вывести в EXCEL (работает и в самом SCCM) список программного обеспечения, установленного на ПК в компании, так, чтобы выводились значения, отвечающие требованиям ПО - Версия - Количество.

Например.
База:
µTorrent 2.0.1
µTorrent 2.1.3
µTorrent 3.0.3
µTorrent 3.1.2
µTorrent 2.0.1

Вывод:

ПО Версия Количество
µTorrent 2 3
µTorrent 3 2

Сам код:

SELECT PROD.ProductName,
case
when
charindex('.', PROD.ProductVersion, 1) > 1
then
LTRIM(substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1))
when
charindex(',', PROD.ProductVersion, 1) > 1
then
LTRIM(substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1))

end as ProductVersion,

Count(PROD.ProductVersion) as Count

FROM v_GS_SoftwareProduct PROD
Group By PROD.ProductName,
(case
when
charindex('.', PROD.ProductVersion, 1) > 1
then
LTRIM(substring(PROD.ProductVersion, 1, charindex('.', PROD.ProductVersion, 1) - 1))
when
charindex(',', PROD.ProductVersion, 1) > 1
then
LTRIM(substring(PROD.ProductVersion, 1, charindex(',', PROD.ProductVersion, 1) - 1))

end)
Order by PROD.ProductName
24 авг 12, 12:37    [13059359]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
Flexo84
Member

Откуда:
Сообщений: 10
Flexo84
Может кому пригодится код.

Функция LTRIM нужна, чтобы обрезать пробелы в базе. Например, есть версия 2.0.1, а есть _2.0.1 ( _ - пробел ).
24 авг 12, 12:39    [13059370]     Ответить | Цитировать Сообщить модератору
 Re: Формирование SQL запроса в EXCEl  [new]
astatsa
Member

Откуда: Краснодар
Сообщений: 83
А так не проще:
select SUBSTRING(PROD.ProductName, 1, CHARINDEX('.', PROD.ProductName, 1) - 1), 
         count(*)
  from v_GS_SoftwareProduct PROD
group by SUBSTRING(PROD.ProductName, 1, CHARINDEX('.', PROD.ProductName, 1) - 1)
24 авг 12, 20:57    [13062308]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить