Я использовал матрицу в SSRS с учебным годом в виде строк, месяцем в качестве данных и количеством (учеников) в качестве столбцов.
Относится к SQL Server 2019 и 2022 SSRS.
У меня есть запрос выбора:
Select schoolname, datename(month, date_01) as month, student name, schoolyear
from table 1 q
join
table 2
on
q.(column)=w.(column)
where schoolyear in ('2022-2023','2023-2024','2024-2025')
group by .......
order by .....
date_01 было в формате гггг/мм/дд, который я конвертировал в месяцы.
Имя школы я использовал в качестве параметра, который выбирается в качестве школы на основе изменения моих данных.
Показанные цифры представляют собой количество имен студентов в разные месяцы, когда они были зарегистрированы в эти месяцы.
Сейчас в школе ABC есть данные только с января по август, и после этого ни один ученик не был зарегистрирован. В основном это меняется для всех школ, поэтому фиксированной схемы нет.
Я не мог вспомнить, как это сделать. Для школ, имеющих данные за все месяцы, это нормально, но школам, имеющим данные только за несколько месяцев, трудно показать ноль за другие месяцы, поскольку по ним нет данных. Могу ли я что-нибудь сделать в сценарии SQL Server или SSRS?
В принципе, каждый месяц имеет разные данные. Эти данные, разделенные на 3 года и 12 месяцев, основаны на индивидуальных студентах. К сожалению, для школы ABC после августа не проводилась регистрация, поэтому после августа ничего не может быть показано. Теперь какая-то другая школа XYZ может иметь данные за все, кроме ноября. Мне просто нужно показать все мотыльки, когда какая-либо школа выбрана с нулевым значением или нулем для несуществующих данных.
Приемлемым способом было бы создать таблицу календаря, которая будет использоваться в качестве базовой таблицы, и соответствующим образом соединить ваши данные. Фрагмент ниже достигает того же самого.
DECLARE @StartYear INT = 2022
DECLARE @EndYear INT = 2024
DECLARE @MonthsOut INT = (@EndYear-@StartYear+1) * 12
;WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
R2(N) AS (SELECT 1 FROM R1 a, R1 b),
R3(N) AS (SELECT 1 FROM R2 a, R2 b),
Tally(Number) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R3)
,
Calendar AS
(
SELECT
CASE WHEN (Number % 12) = 0 THEN 12 ELSE (Number % 12) END AS [Month],
ROW_NUMBER() OVER (PARTITION BY Number % 12 ORDER BY Number) + @StartYear -1 AS StartYear,
ROW_NUMBER() OVER (PARTITION BY Number % 12 ORDER BY Number) + @StartYear AS EndYear,
Number AS [CalendarOrder]
FROM
Tally
WHERE
Number <= @MonthsOut
)
Select schoolname, datename(month, date_01) as month, student name, schoolyear
from
Calendar cal
left join
table 1 q on datepart(month, date_01) = cal.[Month] and datepart(year, date_01) cal.[startyear]
left join
table 2
on
q.(column)=w.(column)
where cal.schoolyear in ('2022-2023','2023-2024','2024-2025')
group by .......
order by .....,cal.CalendarOrder
Я получаю сообщение об ошибке от Tally: неверный синтаксис.
Если бы WHERE и FROM поменялись местами.
У меня плохо, первый запрос выполняется нормально, но второй из Tally вызывает недопустимый объект.
Обновлено с помощью календаря CTE. Порядок календаря предназначен только для визуализации и не требуется для получения результатов.
Все работало отлично; проблема все еще существует, чтобы показать ноль для несуществующих данных.
Это левое соединение, поэтому вы можете проверить отсутствие данных, данные имеют значение NULL и получить 0 вместо NULL --> СЛУЧАЙ, КОГДА StudentID ИМЕЕТ NULL, ТОГДА 0 ELSE 1 END AS CountFlag, а затем суммировать countflag в какой-то момент позже.
Привет, чтобы отображать нулевые данные как 0, они должны существовать. Представьте себе, что на столе лежат два шара: синий и черный, у каждого по одному. Я хочу отображать синий = 1, черный = 1 и зеленый = 0. Теперь этого зеленого цвета нигде нет в базе данных, но он все равно должен отображаться как 0. Здесь важно то, что имя месяца должно начинаться от Жана до Декабря, независимо от того, существуют данные или нет. Я знаю, это странно, что меня попросили сделать.
Вот почему данные должны быть основаны на календаре. Если вы объединяете все шары в месяце и нет шаров, скажем, для месяца 3, ball_id будет нулевым, но месяц 3 все равно будет там, потому что он находится в базовой таблице. Если ball_id имеет значение null, это означает, что мяча нет, поэтому вы можете получить нулевое значение или, если считать на более высоком уровне - COUNT(), нулевое значение не будет участвовать в подсчете, как если бы оно было 0.
Есть одно предостережение. Допустим, у вас есть класс A, класс B и класс C, и для класса A и B есть данные за 2024–2025 годы, а для класса C — нет. В этом случае вам придется добавить фиктивную запись для класса C (чтобы она отображалась в период 2024–2025 гг.). Для этого существуют разные методы, но судя по вашему сообщению, я не уверен, что это проблема.
Понял, попробую поискать еще. Работает ли эта штука, когда данные случайны? Я имею в виду, что данные за последние 2 года постоянны, но данные за текущий год сохраняются до сентября.
Означает, что данные разбросаны случайным образом по всему промежутку времени? Это не имеет значения, пока оно не отображается. Чтобы отобразить данные осмысленным образом, можно использовать простой ORDER BY в самом запросе или любой компонент пользовательского интерфейса, используемый для рендеринга данных, будет располагаться в том порядке, который наиболее важен для желаемого результата.
В параметре SSRS у меня 57 школ, поэтому для каждой выбранной школы данные будут отличаться.
Пока у школ есть данные об учащихся на временной шкале, они будут вносить свой вклад (или просто получат 0, если данных по школе не существует). Если у школы нет данных на временной шкале, и вы хотите, чтобы они отображались в метке для отображения, вам придется создать данные с нулевыми значениями или что-то в этом роде. Однако похоже, что вам нужен только 0 в течение месяца без активности, поэтому описанный выше механизм покроет все основания.
Вы имеете в виду предостережение?
Да, просто обратный случай вопроса, случай, когда вы хотите отобразить все школы, но данных по плоскости нет. Предостережение, потому что его не просили решить.
Большое спасибо, что нашли время и помогли мне. .
Нет проблем, рад, что смог помочь. если это ответит на ваш вопрос, пожалуйста, примите.
В каком месяце меняется год?