#sql #sql-server #sql-server-2008 #ssrs-2008
#sql #sql-сервер #sql-server-2008 #ssrs-2008
Вопрос:
Я создаю набор данных, который будет отображаться в отчете SSRS.
У меня есть запрос в задании, который помещает количество в таблицу [dbo].[CountMetersDue]
на постоянной основе 1-го числа каждого месяца; значение меняется в течение месяца, поэтому вначале необходимо сделать снимок.
У меня настроен отчет, который использует пользовательское выражение для создания графика совокупного тренда. В основном принимает одно значение, делит на другое, чтобы получить процент. Поэтому у меня есть два запроса, которые необходимо объединить… Мне потребовалась целая вечность, чтобы разобраться во всем этом!
Мне просто нужна помощь с последней частью.
SELECT (SELECT [Count]
FROM [MXPTransferDev].[dbo].[CountMetersDue]
WHERE [MXPTransferDev].[dbo].[CountMetersDue].[DateTime] =
[MXPTransferDev].[dbo].[Readings].[dateRead]) AS [MetersDue],
COUNT(readingid) AS [TotalReadings],
CONVERT(DATE, dateread) AS [dateRead]
FROM [MXPTransferDev].[dbo].[Readings]
WHERE ( [MXPTransferDev].[dbo].[Readings].[dateRead] BETWEEN
'01-may-11' AND '31-may-11' )
AND ( webcontactid IS NOT NULL )
AND ( meter = 1 )
GROUP BY CONVERT(DATE, [MXPTransferDev].[dbo].[Readings].[dateRead])
CREATE TABLE [dbo].[CountMetersDue](
[Count] [int] NULL,
[DateTime] [datetime] NULL
) ON [USER]
GO
ALTER TABLE [dbo].[CountMetersDue]
ADD CONSTRAINT [DF_CountMetersDue_DateTime] DEFAULT (getdate()) FOR [DateTime]
GO
CREATE TABLE [dbo].[Readings](
[readingId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[dateRead] [datetime] NOT NULL,
[meter] [int] NOT NULL,
[webcontactid] [bigint] NULL,
Readings
readingId meter reading dateRead webcontactid
583089 4 3662 2011-05-25 15:00:33.040 479
583207 3 682 2011-05-25 15:00:33.027 479
583088 2 98064 2011-05-25 15:00:33.007 479
CountMetersDue
Count DateTime
2793 2011-12-01 00:00:00.000
1057 2011-05-01 14:08:20.437
610 2011-03-01 00:00:00.000
Комментарии:
1. Кстати, добавление двух пробелов в конец строки приводит к разрыву строки. Это немного проще для глаз, чем этот монолитный блок текста.
2. Можете ли вы опубликовать полный DDL для своих таблиц? Это упростит оказание помощи.
3. Я думаю, что дизайн вашей таблицы, возможно, потребуется изменить. У вас есть таблица,
[CountMetersDue]
в которой есть поле[Count]
. Не следует ли выводить количество требуемых счетчиков из запроса, а не считывать из таблицы? И почему вам нужно выбратьTOP 1
из таблицы? Сколько еще строк count в этой таблице? Как я уже говорил ранее, разместите DDL для таблиц, поскольку может быть другой способ получить нужные вам результаты.4. перепечатана последняя версия запроса. Я мог бы сопоставить имя столбца countmeters из-за соответствия другому столбцу date, и тогда было бы возможно объединить таблицы?
5. @jeff — Без DDL для таблиц трудно понять, как их можно объединить. Вы можете уменьшить количество полей, чтобы показывать только те, которые задействованы в этом запросе.
Ответ №1:
Вторая попытка ответить на ваш вопрос (вероятно, потребуется некоторое разъяснение от вас самих, прежде чем ответ будет правильным):
/* DDL: 2 tables [CountMetersDue] amp; [Readings]
[CountMetersDue]
([DateTime] datetime,
[Count] int)
[Readings]
([ReadingId] bigint,
[dateRead] datetime,
[webcontactid] bigint,
[meter] int)
[CountMetersDue] - contains 1 record on the first of every month, with count of the number of readings at that date
[Readings] - contains all the individual readings
ie:
[CountMetersDue]
01-Jan-2011 1000
01-Feb-2011 2357
01-Mar-2011 3000
[Readings]
1 01-Jan-2011 11 1
2 02-Jan-2011 12 1
3 03-Jan-2011 13 1
...
*/
SELECT
CONVERT(DATE, [dbo].[Readings].[dateRead]) AS dateRead,
COUNT([dbo].[Readings].[readingId]) AS TotalReadings,
[dbo].[CountMetersDue].[Count] AS MetersDue
FROM
[CountMetersDue] /* get all count meters due */
left join [Readings] /* get any corresponding Reading records
where the dateRead in the same month as
the CountMetersDue */
on DATEPART(year, Readings.dateRead) = DATEPART(year, [CountMetersDue].[DateTime]) /* reading in same year as CountMetersDue */
and DATEPART(month, Readings.dateRead) = DATEPART(month, [CountMetersDue].[DateTime]) /* reading in same month as CountMetersDue */
WHERE ([MXPTransferDev].[dbo].[Readings].[dateRead]) BETWEEN
@StartDate AND @EndDate
AND ( webcontactid IS NOT NULL )
AND ( meter = 1 )
GROUP BY
[dbo].[CountMetersDue].[Count],CONVERT(DATE, [dbo].[Readings].[dateRead])
Комментарии:
1. Спасибо за это, Эндрю, я проверю это … я предоставил некоторые образцы данных в моем исходном запросе…
2. @jeff не беспокойтесь, я просто внес небольшое исправление (скобка в неправильном месте). Спасибо за предоставление образца данных, можете ли вы также предоставить образец выходных данных?
3. Эндрю почти добрался … тестировал ваш запрос и редактировал некоторые фрагменты, поскольку из-за того, что все даты были разными, group by работала некорректно, поэтому пришлось использовать преобразование по дням… Сообщу вам, как это происходит во вторник, и покажу вам мой окончательный код! Спасибо за вашу помощь!
4. @jeff не беспокойтесь, когда у вас будет окончательный код, пожалуйста, отредактируйте ответ, чтобы помочь другим 🙂
5. Эндрю быстрый вопрос, прежде чем я отмечу это как ответ, какова была причина внешнего соединения слева?
Ответ №2:
Тогда это был бы запрос, который вы ищете?
Подзапросы, как они называются, можно включить, заключив их в круглые скобки ‘()’.
SELECT (SELECT [Count] FROM [xxxxx].[dbo].[CountMetersDue] AS tabA WHERE tabA.[datefield] = tabB.dateRead) AS [MetersDue], COUNT(readingId) AS [TotalReadings], CONVERT(DATE, dateRead) AS [dateRead]
FROM [xxxxx] AS tabB
WHERE (dateRead BETWEEN @StartDate AND @EndDate) AND (webcontactid IS NOT NULL) AND (meter = 1)
GROUP BY CONVERT(DATE, dateRead)
Комментарии:
1. извините, я не пояснил, что есть две разные таблицы… значение select top — это статическое значение, которое не изменяется. Я предполагаю, что мне нужно использовать соединение, возможно, перекрестное соединение?
2. Значит, предложенный запрос не работает? Я бы подумал, что мой запрос правильный, если ‘select top’ является статическим значением…
3. Если это помогло вам, вы могли бы принять ответ. Если этого не произошло, пожалуйста, прокомментируйте, какие ошибки у вас могут возникнуть, если вам угодно 🙂
4. привет, неверно истолкованные требования… нужны исторические записи в таблице select top, поэтому будет 1 запись за февраль, март, апрель и т.д. Мне нужно вернуть количество в таблицу select top, чтобы оно соответствовало дате в основном запросе…
5. нужно ли мне добавлять мои параметры date во вложенный запрос или я могу выполнить какое-то перекрестное объединение или что-то в этомроде? Я попытался удалить верхний бит select и порядок, надеясь, что он будет использовать параметры в предложении where, но, думаю, нет…