Hoe kan ik T-SQL schrijven om gegevens voor tooltips terug te brengen met de originele dataset?

Ik schrijf een SP in T-SQL om de 'first pass yield'-metrieken terug te halen uit testgegevens die zijn opgeslagen in databasetabellen op SQL Server 2008 R2. Ik heb de SP geschreven om de basisgegevens voor grafische weergave in de toepassing te retourneren, maar ik wil tooltips toevoegen om details te bieden voor een bepaalde periode wanneer de gebruiker over het segment van de grafiek zweeft.

Opmerking - Ik vraag niet hoe ik de UI-partij hier moet doen, alleen hoe ik de gegevens krijg. UI-dingen waar ik later mee te maken heb ...

Hier is een vereenvoudigd schema voor het opslaan van de originele gegevens:

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)

Het idee is om het totale aantal eenheden dat de eerste keer correct is gebouwd, terug te delen, gedeeld door het totale aantal gebouwde eenheden - dit is het rendementsnummer voor de eerste keer. We willen dat doen voor een onbeperkt aantal assemblagelijnen (AssyLineID) gedurende een enigszins arbitraire tijdsperiode.

'Arbitrair' betekent in dit geval per uur voor een bepaalde dag of dagelijks gedurende een langere periode ...

De resulterende dataset is een tabel met records zoals deze:

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) 

Tot zover goed, maar de teruggestuurde FPY-waarde bevat niet veel informatie. Voor interessante evenementen (relatief lage of hoge FPY) zou het kwaliteitsteam willen weten welke soorten eenheden ze aan het bouwen waren en welke nummers werden gebruikt om de FPY te krijgen - zonder nog een ander rapport te raadplegen. Ik zou terug kunnen gaan naar de database wanneer de tooltip moet worden weergegeven, maar de gegevens zullen niet hetzelfde zijn. De oorspronkelijke gegevensset houdt rekening met een eenheid die in een eerdere tijdsperiode faalde en deze niet (ten onrechte) als een goede eenheid in de huidige tijdsperiode telt.

Hier is een enigszins vereenvoudigde versie van mijn SP voor het verkrijgen van elk uur FPY voor een bepaalde dag:

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

Ik heb een manier nodig om een ​​tabel met details voor elke assemblagelijn in te vullen voor elke periode als volgt:

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)

Opmerking Ik moet de master/parent-record op schijf maken en opslaan voordat ik de detailrecords bewaar, dus ik heb de waarde voor de foreign key (FpyValueID).

Een manier die ik me kan voorstellen is om te veranderen hoe ik de oorspronkelijke gegevens en SUM-gegevens uit de detailrecords bereken om de algemene FPY-waarden te berekenen. Ik kan ook zien waar ik misschien de GROUP BY-instructie moet gebruiken om de detailwaarden te krijgen.

Heeft iemand suggesties voor het construeren van de SQL-query's om deze gegevens terug te halen zonder meer looping toe te voegen? Dit is echt al lang, dus ik stop hier. Als je meer info nodig hebt, vraag het dan ...

Bij voorbaat dank voor eventuele ideeën/hulp, Dave

0
Ik zal proberen dit terug te brengen tot een eenvoudigere dataset, dus er is niet zoveel ruis. Er zit echt veel spul in dat de echte vraag afleidt. Ik werd op een zijspoor gezet door een ander gloeiend project, maar daar kom ik snel weer op terug.
toegevoegd de auteur DaveN59, de bron
Ik heb je vraag in mijn favorieten gehad om terug te komen en deze te beantwoorden, maar elke keer als ik dat doe, zie ik hoe groot het is en kan ik moeilijk door het probleem graven. Is er een manier om het in te korten?
toegevoegd de auteur Stephen Turner, de bron

2 antwoord

Deze vraag is te lang en bevat te veel onnodige informatie. In plaats van het gewoon te verwijderen, kies ik ervoor om te posten wat ik heb gedaan om het probleem op te lossen, voor het geval iemand voldoende tijd over heeft om de vraag en het antwoord daadwerkelijk te lezen ...

Deze SP wordt aangeroepen vanuit een WCF-service en de resulterende gegevensset wordt vanuit de service teruggestuurd naar de client. Het is dus niet nodig om alles tegelijk te doen. Wat ik van plan ben te doen is de geheugentabel te gebruiken die in de eerste pass is gemaakt in een tweede pass die de detailrecords zal maken. De sessie-ID wordt teruggestuurd naar de WCF-service, die vervolgens de recordset leest, de gegevens terugstuurt naar de client en de werkrecords in de database verwijdert.

Ruw maar effectief. Als ik een slimmere manier bedenk om dit te doen, kom ik terug en post het.

Veel plezier!!

0
toegevoegd

Hier nam ik een shot op je eerste vereiste:

retourneer het totale aantal eenheden dat de eerste keer correct is gebouwd, gedeeld door het totale aantal gebouwde eenheden: dit is het eerste rendementsnummer.

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'

Er is waarschijnlijk een meer efficiënte manier om de CASTs te bewerken.

0
toegevoegd
Bedankt, maar ik had dat gedeelte al uitgevonden in de geposte SQL-code. Er zijn verschillende andere vereisten voor dat deel dat ik niet in de post heb opgenomen, omdat ik het al aan het werk had. Wat ik echt nodig heb, zijn enkele ideeën over hoe je het achtergronddetail samen met de FPY-waarden kunt krijgen ... Ik vind het wel leuk om DECIMAL-typen te gebruiken in plaats van FLOAT.
toegevoegd de auteur DaveN59, de bron