MS SQL server: CTE example

During one of my assignments I was asked to make a production report with the number of produced articles during a batch distributed evenly per hour between batch start and end.


This is how I solved it with a cursor and cte (common table expression)


--Variables
DECLARE
            @BatchID int,
            @startTime datetime,
            @stopTime datetime,
            @diffHours int,
            @Article int,
            @prodAmount int,
            @theRest int,
            @ProdLine int,
            @endDiff int,
            @amount int
 
            --Fetch the data
            DECLARE prod_Cursor CURSOR FOR
                                     SELECT BatchID, ProdLine, starttime, stoptime, article, prodAmount
                                                  FROM BatchLog
                                                  WHERE starttime=getDate() -- Or whatever statement you want
 
             OPEN prod_Cursor
 
            --Get the first row of data from the cursor
             FETCH NEXT FROM prod_Cursor INTO
                                     @BatchID, @ProdLine, @starttime, @stoptime, @article, @prodAmount
 
                         WHILE @@FETCH_STATUS = 0
                         BEGIN
                                    
                                     SET @diffHours=DATEDIFF(HH,@starttime,@stoptime)+1 -- how many time slots to ditribute into
                                     SET @amount=@prodAmount/@diffHours -- distribute amount of produced items
                                     SET @theRest=@prodAmount-(@amount*@diffHours) -- left over if share isn't even
                                    -- This is needed in order to get all started hours
                                     SET @endDiff=CASE
                                                  WHEN (DATEPART(MI,@startTime)>=DATEPART(MI,@stopTime)) THEN 0
                                                  WHEN (DATEPART(MI,@startTime)<DATEPART(MI,@stopTime)) THEN -1
                                     END
 
                                     -- Create CTE table and store values
                                     ;WITH cte AS(
                                                  SELECT @BatchID AS BatchID, @prodLine as prodLine, @startTime As StartTime, @stopTime as StopTime, CONVERT(varchar, @StartTime, 23) AS Datum, @amount+@theRest as amount, @article AS article
                                                  UNION ALL
                                                  SELECT @BatchID AS BatchID, @prodLine as prodLine, DATEADD(HH, 1, startTime), @stoptime as StopTime, CONVERT(varchar, DATEADD(HH, 1, startTime), 23) AS Datum, @amount as amount, @article AS article
                                                  FROM cte
                                                  WHERE startTime < DateAdd(HH,@endDiff,@stopTime) 
                                     )
 
                                    --Get the data from CTE
                                     SELECT BatchID, prodLine, Starttime, StopTime, Datum, DATEPART(HH,StartTime) As [perHour], amount, article
                                     FROM cte
                                                                          
                                     FETCH NEXT FROM prod_Cursor INTO
                                                  @BatchID, @prodLine, @starttime, @stoptime, @article, @prodAmount
 
                         END
                         CLOSE prod_Cursor
                         DEALLOCATE prod_Cursor
 
 

Comments

Post a Comment

Popular posts from this blog

Creating an old school amiga demo bitmap scroll in Python 3 and pygame 2

Image
 Back in the old days (1980:s) I did some demo coding on first the commodore 64 and later on an Amiga 500. I wouldn't say I was very good at it but I did manage to make some simple demos One feature that was a "must have" in all Amiga demos was a "sprite scroller". A sprite scroller displays a moving, e g scrolling, text on the screen. But not with the standard font, it must have a special and very cool looking font which was often made in a paint program like Deluxe paint. The bitmap image was then cropped to the characters and displayed in the scroll. Now, when I try to make my kids interested in coding, I was talking to them about text scrolls. It ended up with me trying to create a "sprite scroller" in Python. Here is a screenshot of the result If I recall correctly the way it was done on an Amiga was that I (or a friend) created a font bitmap containing all the characters needed for displaying the text message. The bitmap was then cropped into the...
Read more

Powershell: import complex PRICAT csv file

Image
Background A while ago I was asked to help with importing tyre data from several different manufacturers into a local PIM system. For exchanging data electronically the tyre industry have adopted the EDIFACT subset EANCOM PRICAT for the trade, particularly for the tyre trade by the term "EDIWheel". EDIWheel process begin with an electronic product and price catalogue (PRICAT) then orders (ORDERS), confirmation of orders (ORDRSP), delivery dispatch (DESADV) and electronic invoice (INVOIC). The EDI documents I had to process was PRICAT and invoice. PRICAT is basically a CSV file with some extra information on the first rows of the file. Delimiter in the file was ";". The extra rows in the csv file prevented me from simply reading the file with the built-in powershell function import-csv . EDIWheel PRICAT exists in two different versions, B2 (2008) or B4 (2016). In my case the format will be the older B2 format. Since the only script language available, due to the IT-...
Read more