Как я могу написать T-SQL, чтобы вернуть данные для всплывающих подсказок с исходным набором данных?
Я пишу SP в T-SQL, чтобы вернуть показатели "первого прохода" из тестовых данных, хранящихся в таблицах базы данных на SQL Server 2008 R2. У меня есть SP, написанный для возврата базовых данных для построения графиков в приложении, но я хотел бы добавить всплывающие подсказки, чтобы предоставить подробности за определенный период времени, когда пользователь наводит курсор на сегмент графика.
Примечание. Я не спрашиваю, как сделать часть пользовательского интерфейса, просто как получить данные. UI вещи, с которыми я буду иметь дело позже...
Вот упрощенная схема хранения исходных данных:
CREATE TABLE [dbo].[TestRecords](
[TestRecordID] [int] NOT NULL,
[HostName] [varchar](25) NOT NULL,
[UnitSerial] [varchar](20) NOT NULL,
[PassFailStatus] [bit] NOT NULL,
[AssyLineID] [int] NOT NULL,
[TestDateTime] [datetime] NOT NULL)
Идея состоит в том, чтобы вернуть общее количество построенных модулей в первый раз, деленное на общее количество построенных модулей - это число первого прохода. Мы хотим сделать это для любого количества сборочных линий (AssyLineID) за несколько произвольный период времени.
"Произвольный" в данном случае означает почасовой для данного дня или ежедневно в течение более длительного периода времени...
Результирующий набор данных представляет собой таблицу записей, подобную этой:
CREATE TABLE [dbo].[FpyValues](
[FpyValueID] [int] IDENTITY(1,1) NOT NULL,
[SessionID] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[AssyLineID] [int] NOT NULL,
[Fpy] [float] NOT NULL,
[TotalUnits] [int] NOT NULL,
[FailedUnits] [int] NOT NULL)
Пока все хорошо, но возвращаемое значение FPY не содержит много информации. Для интересных событий (относительно низкий или высокий FPY) команда по качеству хотела бы знать, какие типы единиц они строили и какие числа использовались для получения FPY - без ознакомления с еще одним отчетом. Я мог бы вернуться к базе данных, когда должна отображаться подсказка, но данные не будут такими же. Исходный набор данных учитывает единицу, которая вышла из строя в более ранний период времени, и не (ошибочно) считает ее хорошей единицей в текущем периоде времени.
Вот несколько упрощенная версия моего SP для получения почасовой FPY за данный день:
ALTER PROCEDURE [dbo].[GetHourlyFpy]
@ProdLineList VARCHAR(100),
@ReportDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Fpy FLOAT, @Total FLOAT, @Failed FLOAT
DECLARE @SessionID INT;
DECLARE @TempList TABLE
(
LineID INT
);
DECLARE @LineID VARCHAR(10);
DECLARE @LineName VARCHAR(16);
DECLARE @FailedUnits TABLE
(
UnitSerial VARCHAR(12)
);
DECLARE @Start INT, @End INT, @Current INT;
DECLARE @StartTime DATETIME, @EndTime DATETIME;
-- unpack incoming comma-separated list of Production Line IDs into temp table
-- get session ID to identify results for this session
-- get the start and end hour values (@Start and @End)
-- Get the Date part of the incoming DATETIME value (time = 00:00:00.000)
-- loop through all production lines, creating result records as we go
WHILE EXISTS(SELECT * FROM @TempList)
BEGIN
SELECT TOP 1 @LineID = LineID FROM @TempList;
-- clear the failed units table
DELETE FROM @FailedUnits;
-- set the start time for reporting
SET @StartTime = (SELECT DATEADD(Hh, @Start, @ReportDate));
-- loop through all 1-hour periods for the day
SET @Current = @Start;
WHILE @Current < @End
BEGIN
SET @EndTime = (SELECT DATEADD(Hh, 1, @StartTime));
SET @Total = (SELECT COUNT(DISTINCT tr.UnitSerial)
FROM TestRecords
WHERE @StartTime <= tr.TestDateTime
AND tr.TestDateTime < @EndTime
AND tr.AssyLineID = @LineID
AND (NOT EXISTS
(SELECT UnitSerial FROM @FailedUnits f WHERE tr.UnitSerial = f.UnitSerial)));
SET @Failed = (SELECT COUNT(DISTINCT tr.UnitSerial)
FROM TestRecords tr
WHERE @StartTime <= tr.TestDateTime
AND tr.TestDateTime < @EndTime
AND tr.PassFailStatus = 0
AND tr.AssyLineID = @LineID
AND (NOT EXISTS
(SELECT UnitSerial FROM @FailedUnits f WHERE tr.UnitSerial = f.UnitSerial)));
-- populate the failed units list as needed
INSERT INTO @FailedUnits
SELECT DISTINCT tr.UnitSerial
FROM dbo.TestRecords tr
LEFT OUTER JOIN
@FailedUnits f ON tr.UnitSerial = f.UnitSerial
WHERE @StartTime <= tr.TestDateTime
AND tr.TestDateTime < @EndTime
AND tr.PassFailStatus = 0
AND tr.AssyLineID = @LineID
AND f.UnitSerial IS NULL;
IF (0 = @Total)
SET @Fpy = 0;
ELSE
SET @Fpy = (@Total - @Failed) / @Total;
INSERT INTO dbo.FpyValues (SessionID, [DateTime], ProductionLine, Fpy, TotalUnits, FailedUnits)
VALUES(@SessionID, @StartTime, @LineID, @Fpy, @Total, @Failed);
SET @StartTime = (SELECT DATEADD(Hh, 1, @StartTime));
SET @Current = @Current + 1;
END
-- we're done with this production line
DELETE FROM @TempList WHERE LineID = @LineID;
END
RETURN @SessionID;
END
Мне нужен способ заполнить таблицу с деталями для каждой сборочной линии для каждого периода времени следующим образом:
CREATE TABLE [dbo].[FpyUnits](
[FpyUnitID] [int] IDENTITY(1,1) NOT NULL,
[FpyValueID] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[AssyLineID] [int] NOT NULL,
[UnitType] [varchar](25) NOT NULL,
[TotalUnits] [int] NOT NULL,
[FailedUnits] [int] NOT NULL)
Примечание. Мне нужно создать и сохранить основную / родительскую запись на диск до сохранения подробных записей, поэтому у меня есть значение внешнего ключа (FpyValueID).
Один из способов, который я могу себе представить, - это изменить способ вычисления исходных данных и данных SUM из подробных записей для расчета общих значений FPY. Я также вижу, где мне может понадобиться использовать директиву GROUP BY, чтобы получить подробные значения.
У кого-нибудь есть предложения о том, как построить SQL-запросы для извлечения этих данных без добавления дополнительных циклов? Это уже очень долго, поэтому я уйду отсюда. Если вам нужна дополнительная информация, пожалуйста, спросите...
Заранее спасибо за любые идеи / помощь, Дейв
2 ответа
Этот вопрос слишком длинный и содержит слишком много ненужной информации. Вместо того, чтобы просто удалить его, я предпочитаю опубликовать то, что я сделал, чтобы решить проблему, на тот случай, если у кого-то будет достаточно свободного времени, чтобы фактически прочитать и вопрос, и ответ...
Этот SP будет вызываться из службы WCF, а полученный набор данных возвращается клиенту из службы. Таким образом, нет необходимости делать все сразу. Я планирую использовать таблицу памяти, созданную на первом проходе за второй проход, которая создаст подробные записи. Идентификатор сеанса возвращается службе WCF, которая затем читает набор записей, возвращает данные клиенту и удаляет рабочие записи в базе данных.
Грубый, но эффективный. Если я придумаю более изящный способ сделать это, я вернусь и опубликую его.
Повеселись!!
Здесь я сделал снимок по вашему первому требованию:
вернуть общее количество построенных блоков в первый раз, деленное на общее количество построенных блоков - это номер первого прохода.
SELECT COUNT(TestRecordID) as UnitsPassed,CAST(CAST(COUNT(TestRecordID) AS DECIMAL(6,3)) / (SELECT COUNT(TestRecordID) FROM TestRecords) AS DECIMAL(6,3)) as FirstPassYield
FROM TestRecords
WHERE PassFailStatus = 1 AND testDateTime > '12/06/2011' AND testDateTime < 12/07/2011'
Там, вероятно, более эффективный способ работать на CASTs.