Skip to content
Advertisement

Grouping rows over common data in a column and min max start/end date via cursor fetch method

I have a stored procedure with a couple of input parameters

Input parameters:

@UnitID = 13001,
@WOID = 731000037,732000049

Result set:

AuxTable is not the final intended table, it’s just being displayed for debugging/troubleshooting.

MsgLinkId MyRowId WO_ID SourceUnitName MaterialName BatchID UnitofMeasure Segment1 Segment2 Segment3 Segment4 Segment5 Amount StartDate EndDate
5 1 731000037 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 13391.7524414063 2022-01-01 14:22:25.773 2022-01-01 14:24:25.980
5 2 731000037 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 13391.7524414063 2022-01-01 14:22:25.773 2022-01-01 14:26:26.187
5 3 731000037 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 13391.7524414063 2022-01-01 14:22:25.773 2022-01-01 14:28:26.397
7 1 732000049 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 31993.4200439453 2022-01-02 17:12:43.550 2022-01-02 17:14:43.757
7 2 732000049 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 31993.4200439453 2022-01-02 17:12:43.550 2022-01-02 17:16:43.967
7 3 732000049 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 31993.4200439453 2022-01-02 17:12:43.550 2022-01-02 17:18:44.177
3 1 732000049 B1S01C04 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 57417.6939544678 2022-01-02 15:48:17.530 2022-01-02 15:50:17.737
3 2 732000049 B1S01C04 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 57417.6939544678 2022-01-02 15:48:17.530 2022-01-02 15:52:17.947
3 3 732000049 B1S01C04 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 57417.6939544678 2022-01-02 15:48:17.530 2022-01-02 15:54:18.157

FinalTable:

Unit_WO SourceUnitName MaterialName BatchID UnitofMeasure Segment1 Segment2 Segment3 Segment4 Segment5 Amount StartDate EndDate
731000037 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 13391.75 2022-01-01 14:22:25.773 2022-01-01 14:43:09.060
732000049 B1S01C03 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 31993.42 2022-01-02 17:12:43.550 2022-01-02 17:57:51.643
732000049 B1S01C04 RAW MILK 0 KGM B1S01W32 NONE NONE NONE NONE 57417.7 2022-01-02 15:48:17.530 2022-01-02 17:12:40.333

This is just a sample to understand the situation.

Question: how can I get “FinalTable”, without using the cursor fetch method?

I have tried using max(Amount), min(Start date), max(End date) and tried grouping over MsgLinkId,MyRowId but it didn’t work.

This is my stored procedure:

CREATE PROCEDURE dbo.********
    @UnitID nvarchar(50), 
    @WOID nvarchar(50), 
    @JobId nvarchar(50)= '', 
    @StartDate datetime = '', 
    @EndDate datetime = '' 
AS 
BEGIN 
    IF @EndDate = '' 
    BEGIN 
        SET @EndDate = @StartDate 
    END 

    SET @unitID = REPLACE(@unitId, 'All', '') 
    SET @WOID = REPLACE(@WOID, 'All', '') 
    SET @JobID = REPLACE(@JobId, 'All', '') 
    -- SET @startdate = REPLACE(@startdate, 'All', '')
    -- SET @enddate = REPLACE(@enddate, 'All', '')

    SELECT 
        T1.Msg_LinkId AS MsgLinkId, 
        ROW_NUMBER () OVER (PARTITION BY T1.Msg_LinkId 
                            ORDER BY T2.DT_Msg Asc) MyRowid, 
        T1.Unit_WO AS WO_ID, 
        T3.Name AS SourceUnitName, 
        T4.Name AS MaterialName, 
        T1.Data_DInt04 AS BatchId, 
        T5.description AS UnitofMeasure, 
        T6.Name AS Segment1, 
        T7.Name AS Segment2, 
        T8.Name AS Segment3, 
        T9.Name AS Segment4, 
        T10.Name AS Segment5, 
        SUM(T2.Data_Real01) OVER (PARTITION BY T2.Msg_LinkId) Amount, 
        T1.DT_Msg AS StartDate, 
        T2.DT_Msg AS EndDate 
    INTO
        #AuxTable
    FROM   
        QHandler_Messages T1 
    LEFT JOIN 
        QHandler_Messages T2 ON T1.Unit_WO = T2.Unit_WO 
                             AND T1.Msg_LinkId = T2.Msg_LinkId 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T3 ON T1.Data_DInt02 = T3.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vi_Materials T4 ON T1.Data_DInt03 = T4.Material_ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vw_uoms_all T5 ON T1.Data_DInt05 = T5.uom_id 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T6 ON T1.Data_DInt06 = T6.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T7 ON T1.Data_DInt07 = T7.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T8 ON T1.Data_DInt08 =   T8.ID 
    LEFT JOIN  
        LK_CUSTOMDB.dbo.ListUnit T9 ON T1.Data_DInt09 = T9.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T10 ON T1.Data_DInt10 = T10.ID 
    WHERE 
        (T1.unit_wo IN (SELECT CAST(splitdata AS nvarchar(max)) 
                        FROM dbo.ft_Admin_SplitString(@WOID, ',')) 
         OR @WOID = '') 
        AND (T1.unit_id IN (SELECT CAST(splitdata AS nvarchar(max)) 
                            FROM dbo.ft_Admin_SplitString(@UnitID, ',')) 
             OR @unitid = '') 
        AND T1.Msg_Identifier = 11 
        AND T2.Msg_Identifier = 12 
        AND (T1.Data_DInt01 IN (SELECT CAST(splitdata AS nvarchar(max)) 
                                FROM dbo.ft_Admin_SplitString(@JobID, ',')) 
             OR @jobid = '') 

    UNION ALL 

    SELECT 
        T1.Msg_LinkId as MsgLinkId, 
        ROW_NUMBER () OVER (PARTITION BY T1.Msg_LinkId 
                            ORDER BY T1.DT_Msg Asc) MyRowid, 
        T1.Unit_WO AS WO_ID, 
        T3.Name AS SourceUnitName, 
        T4.Name AS MaterialName, 
        T1.Data_DInt04 AS BatchId, 
        T5.description AS UnitofMeasure, 
        T6.Name AS Segment1, 
        T7.Name AS Segment2, 
        T8.Name AS Segment3, 
        T9.Name AS Segment4, 
        T10.Name AS Segment5, 
        SUM(T1.Data_Real01) OVER (PARTITION BY T1.Msg_LinkId)   Amount, 
        T1.DT_Msg AS StartDate, 
        T1.DT_Msg AS EndDate 
    FROM 
        QHandler_Messages T1 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T3 ON T1.Data_DInt02 = T3.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vi_Materials T4 ON T1.Data_DInt03 = T4.Material_ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.vw_uoms_all T5 ON T1.Data_DInt05 = T5.uom_id 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T6 ON T1.Data_DInt06 = T6.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T7 ON T1.Data_DInt07 = T7.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T8 ON T1.Data_DInt08 = T8.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T9 ON T1.Data_DInt09 = T9.ID 
    LEFT JOIN 
        LK_CUSTOMDB.dbo.ListUnit T10 ON T1.Data_DInt10 = T10.ID 
    WHERE 
        (T1.unit_wo IN (SELECT CAST(splitdata AS nvarchar(max)) 
                        FROM dbo.ft_Admin_SplitString(@WOID, ',')) 
         OR @WOID = '') 
        AND (T1.unit_id IN (SELECT CAST(splitdata AS nvarchar(max)) 
                            FROM dbo.ft_Admin_SplitString(@UnitID, ',')) 
             OR @unitid = '') 
        AND T1.Msg_Identifier = 13 
        AND (T1.Data_DInt01 IN (SELECT CAST(splitdata AS nvarchar(max)) 
                                FROM dbo.ft_Admin_SplitString(@JobID, ',')) 
             OR @jobid = '') 
        AND T1.DT_msg BETWEEN @StartDate 
        AND DATEADD(HOUR, 48, @EndDate) 

    SELECT * 
    FROM #AuxTable --- Result of Aux Table 

    DECLARE @Finaltable TABLE  
                        (
                            MsgLinkId int, 
                            Unit_WO bigint, 
                            SourceUnitName nvarchar (255), 
                            MaterialName nvarchar (255), 
                            BatchId bigint, 
                            UnitofMeasure nvarchar (255), 
                            Segment1 nvarchar (255), 
                            Segment2 nvarchar (255), 
                            Segment3 nvarchar (255), 
                            Segment4 nvarchar (255), 
                            Segment5 nvarchar (255), 
                            Amount real, 
                            StartDate Datetime, 
                            EndDate Datetime
                        ) 
    DECLARE @MsgLinkId int 
    DECLARE @MyRowId int DECLARE cursorAux cursor for 
select 
  MsgLinkId, 
  MyRowid 
from 
  #Auxtable
  open cursorAux FETCH NEXT 
FROM 
  cursorAux INTO @MsgLinkId, 
  @MyRowid WHILE @@FETCH_STATUS = 0 BEGIN If Exists (
    select 
      * 
    from 
      @Finaltable 
    where 
      MsgLinkId = @MsgLinkId
  ) Begin 
Update 
  @Finaltable 
Set 
  EndDate = (
    Select 
      EndDate 
    from 
      #Auxtable where MsgLinkId = @MsgLinkId and MyRowid = @MyRowid)
    Where 
      MsgLinkId = @MsgLinkId end ELSE Begin Insert into @Finaltable (
        MsgLinkId, Unit_WO, SourceUnitName, 
        MaterialName, BatchId, UnitofMeasure, 
        Segment1, Segment2, Segment3, Segment4, 
        Segment5, Amount, StartDate, EndDate
      ) 
    select 
      MsgLinkId, 
      WO_ID, 
      SourceUnitName, 
      MaterialName, 
      BatchId, 
      UnitofMeasure, 
      Segment1, 
      Segment2, 
      Segment3, 
      Segment4, 
      Segment5, 
      Amount, 
      StartDate, 
      EndDate 
    from 
      #Auxtable
    where 
      MsgLinkId = @MsgLinkId 
      and MyRowid = @MyRowid end FETCH NEXT 
    FROM 
      cursorAux INTO @MsgLinkId, 
      @MyRowid END CLOSE cursorAux;
DEALLOCATE cursorAux;
select 
  Unit_WO, 
  SourceUnitName, 
  MaterialName, 
  BatchId, 
  UnitofMeasure, 
  Segment1, 
  Segment2, 
  Segment3, 
  Segment4, 
  Segment5, 
  Amount, 
  StartDate, 
  EndDate 
from 
  @Finaltable END GO

Advertisement

Answer

Based on what the cursor appears to be doing, the below cte query should get your results. It appears the cursor is inserting the first MsgLinkId record (MyRowID = 1) and then updating the EndDate for any subsequent record. This query takes the first MsgLinkId record and joins to the last MsgLinkId record in order to take the EndDate, but retains all the other data from the first record.

;with cte as (
    SELECT MsgLinkId,
          MyRowId,
          row_number() over(partition by MsgLinkId order by MyRowId desc) as MyRowIdDesc, 
          WO_ID, 
          SourceUnitName, 
          MaterialName, 
          BatchId, 
          UnitofMeasure, 
          Segment1, 
          Segment2, 
          Segment3, 
          Segment4, 
          Segment5, 
          Amount, 
          StartDate, 
          EndDate
    FROM #AuxTable --- Result of Aux Table 
)
select
  row_first.Unit_WO, 
  row_first.SourceUnitName, 
  row_first.MaterialName, 
  row_first.BatchId, 
  row_first.UnitofMeasure, 
  row_first.Segment1, 
  row_first.Segment2, 
  row_first.Segment3, 
  row_first.Segment4, 
  row_first.Segment5, 
  row_first.Amount, 
  row_first.StartDate, 
  row_last.EndDate --On subsequent rows, only the EndDate appears to be updated
from
    cte row_first
    inner join
    cte row_last on row_first.MsgLinkId = row_last.MsgLinkId and row_last.MyRowIdDesc = 1
where
    row_first.MyRowId = 1 --The @Finaltable is first filled with the initial record
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement