Lookup values similar to VLookUp in Excel

  • Hi,
    I am struggling to write a procedure which looks up a value in a lookup table.
    See desired results below.
    Any suggestions?
    Cheers,
    Julian


    LOOKUP TABLE
    Date       ID         HRS
    ---------- ---------- --------------
    2017-01-02 180        700
    2017-02-01 180        7

    DATA TABLE
    Date       Data
    ---------- --------
    2017-01-02 700
    2017-01-03 700
    2017-01-04 etc

    2017-02-01 7
    2017-02-02 7
    2017-02-03 7
    2017-02-04 7
    2017-02-05 etc

    (35 row(s) affected)

  • JJR333 - Saturday, August 26, 2017 6:16 AM

    Hi,
    I am struggling to write a procedure which looks up a value in a lookup table.
    See desired results below.
    Any suggestions?
    Cheers,
    Julian


    LOOKUP TABLE
    Date       ID         HRS
    ---------- ---------- --------------
    2017-01-02 180        700
    2017-02-01 180        7

    DATA TABLE
    Date       Data
    ---------- --------
    2017-01-02 700
    2017-01-03 700
    2017-01-04 etc

    2017-02-01 7
    2017-02-02 7
    2017-02-03 7
    2017-02-04 7
    2017-02-05 etc

    (35 row(s) affected)

    With the data given in your post, what output are you looking for? 

    Also, I think you're looking for an INNER JOIN.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi Jeff,

    For each row in the Date table, eg. for IDClient 180, I want to return the corresponding HrsPlan to be found in the LookupTable.
    So for January 2 and on 700 should be returned.
    Per February 1, 7 should be returned.
    This is similar to the Excel VLookup function.
    Plan is to "Left Outer Join" the Date table with the lookup table, so as to get a sequential table per day for the year (or week or month)
    Also want to be able SUM the HrsPlan per (all) IDLocation and/or IDDept, Year, Week, etc.
    My previous post is my first attempt at a stored procedure to achieve this.

    Thanks for your help,
    Julian

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[LookupTable]') AND type in (N'U'))
    DROP TABLE LOOKUPTABLE
    BEGIN
    CREATE TABLE [LookupTable](
        [Date] [date] NOT NULL,
        [IDClient] [varchar](10) NOT NULL,
        [IDLocation] [varchar](4) NULL,
        [IDDept] [varchar](4) NULL,
        [HrsPlan] [float] NULL,
        [Active] [bit] NULL
    )
    END
    GO
    INSERT [LookupTable] ([Date], [IDClient], [IDLocation], [IDDept], [HrsPlan], [Active]) VALUES (CAST(0x2E3C0B00 AS Date), N'190', N'XX', N'A01', 7, 0)
    INSERT [LookupTable] ([Date], [IDClient], [IDLocation], [IDDept], [HrsPlan], [Active]) VALUES (CAST(0x4A3C0B00 AS Date), N'180', N'XX', N'A01', 700, 1)
    INSERT [LookupTable] ([Date], [IDClient], [IDLocation], [IDDept], [HrsPlan], [Active]) VALUES (CAST(0x683C0B00 AS Date), N'180', N'XX', N'A01', 7, 1)
    INSERT [LookupTable] ([Date], [IDClient], [IDLocation], [IDDept], [HrsPlan], [Active]) VALUES (CAST(0x843C0B00 AS Date), N'180', N'XX', N'A01', 0, 1)
    INSERT [LookupTable] ([Date], [IDClient], [IDLocation], [IDDept], [HrsPlan], [Active]) VALUES (CAST(0xA53C0B00 AS Date), N'111', N'XX', N'A03', 210, 0)
    INSERT [LookupTable] ([Date], [IDClient], [IDLocation], [IDDept], [HrsPlan], [Active]) VALUES (CAST(0x1C3D0B00 AS Date), N'190', N'XX', N'A02', 70, 0)
    INSERT [LookupTable] ([Date], [IDClient], [IDLocation], [IDDept], [HrsPlan], [Active]) VALUES (CAST(0x5B3D0B00 AS Date), N'200', N'SB', N'A02', 140, 0)
    GO
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DateTableResults]') AND type in (N'U'))
    DROP TABLE DATETABLERESULTS
    BEGIN
    CREATE TABLE [DateTableResults](
        [Date] [date] NOT NULL,
        [IsoYear] [smallint] NOT NULL,
        [IsoWkNr] [smallint] NOT NULL,
        [HrsPlan] [float] NULL
    ) ON [PRIMARY]
    END
    GO
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x4A3C0B00 AS Date), 2017, 1,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x4B3C0B00 AS Date), 2017, 1,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x4C3C0B00 AS Date), 2017, 1,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x4D3C0B00 AS Date), 2017, 1,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x4E3C0B00 AS Date), 2017, 1,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x4F3C0B00 AS Date), 2017, 1,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x503C0B00 AS Date), 2017, 1,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x513C0B00 AS Date), 2017, 2,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x523C0B00 AS Date), 2017, 2,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x533C0B00 AS Date), 2017, 2,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x543C0B00 AS Date), 2017, 2,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x553C0B00 AS Date), 2017, 2,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x563C0B00 AS Date), 2017, 2,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x573C0B00 AS Date), 2017, 2,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x583C0B00 AS Date), 2017, 3,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x593C0B00 AS Date), 2017, 3,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x5A3C0B00 AS Date), 2017, 3,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x5B3C0B00 AS Date), 2017, 3,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x5C3C0B00 AS Date), 2017, 3,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x5D3C0B00 AS Date), 2017, 3,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x5E3C0B00 AS Date), 2017, 3,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x5F3C0B00 AS Date), 2017, 4,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x603C0B00 AS Date), 2017, 4,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x613C0B00 AS Date), 2017, 4,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x623C0B00 AS Date), 2017, 4,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x633C0B00 AS Date), 2017, 4,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x643C0B00 AS Date), 2017, 4,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x653C0B00 AS Date), 2017, 4,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x663C0B00 AS Date), 2017, 5,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x673C0B00 AS Date), 2017, 5,700)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x683C0B00 AS Date), 2017, 5,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x693C0B00 AS Date), 2017, 5,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x6A3C0B00 AS Date), 2017, 5,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x6B3C0B00 AS Date), 2017, 5,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x6C3C0B00 AS Date), 2017, 5,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x6D3C0B00 AS Date), 2017, 6,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x6E3C0B00 AS Date), 2017, 6,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x6F3C0B00 AS Date), 2017, 6,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x703C0B00 AS Date), 2017, 6,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x713C0B00 AS Date), 2017, 6,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x723C0B00 AS Date), 2017, 6,7)
    INSERT [DateTableResults] ([Date], [IsoYear], [IsoWkNr],[HrsPlan]) VALUES (CAST(0x733C0B00 AS Date), 2017, 6,7)
    GO
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DateTable]') AND type in (N'U'))
    DROP TABLE DATATABLE
    BEGIN
    CREATE TABLE [DateTable](
        [Date] [date] NOT NULL,
        [IsoYear] [smallint] NOT NULL,
        [IsoWkNr] [smallint] NOT NULL
    ) ON [PRIMARY]
    END
    GO
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x4A3C0B00 AS Date), 2017, 1)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x4B3C0B00 AS Date), 2017, 1)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x4C3C0B00 AS Date), 2017, 1)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x4D3C0B00 AS Date), 2017, 1)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x4E3C0B00 AS Date), 2017, 1)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x4F3C0B00 AS Date), 2017, 1)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x503C0B00 AS Date), 2017, 1)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x513C0B00 AS Date), 2017, 2)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x523C0B00 AS Date), 2017, 2)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x533C0B00 AS Date), 2017, 2)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x543C0B00 AS Date), 2017, 2)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x553C0B00 AS Date), 2017, 2)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x563C0B00 AS Date), 2017, 2)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x573C0B00 AS Date), 2017, 2)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x583C0B00 AS Date), 2017, 3)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x593C0B00 AS Date), 2017, 3)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x5A3C0B00 AS Date), 2017, 3)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x5B3C0B00 AS Date), 2017, 3)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x5C3C0B00 AS Date), 2017, 3)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x5D3C0B00 AS Date), 2017, 3)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x5E3C0B00 AS Date), 2017, 3)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x5F3C0B00 AS Date), 2017, 4)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x603C0B00 AS Date), 2017, 4)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x613C0B00 AS Date), 2017, 4)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x623C0B00 AS Date), 2017, 4)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x633C0B00 AS Date), 2017, 4)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x643C0B00 AS Date), 2017, 4)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x653C0B00 AS Date), 2017, 4)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x663C0B00 AS Date), 2017, 5)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x673C0B00 AS Date), 2017, 5)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x683C0B00 AS Date), 2017, 5)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x693C0B00 AS Date), 2017, 5)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x6A3C0B00 AS Date), 2017, 5)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x6B3C0B00 AS Date), 2017, 5)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x6C3C0B00 AS Date), 2017, 5)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x6D3C0B00 AS Date), 2017, 6)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x6E3C0B00 AS Date), 2017, 6)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x6F3C0B00 AS Date), 2017, 6)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x703C0B00 AS Date), 2017, 6)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x713C0B00 AS Date), 2017, 6)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x723C0B00 AS Date), 2017, 6)
    INSERT [DateTable] ([Date], [IsoYear], [IsoWkNr]) VALUES (CAST(0x733C0B00 AS Date), 2017, 6)

  • you have posted in sql 2008 forum...please confirm this is the version you are using.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Yes, 2008R2

  • Ok... understood on the VLookup thing.  I forgot that it's a "range" calculation behind the scenes (it's been almost 2 decades since I've used one) that returns the row with the highest value (a date, in this case) that's NOT greater than the input value. One of the nuances here is that if you go to lookup a date that is prior to the lowest date in the DateLookup table, you should (in this case) return a 0.

    Unless I'm just missing it (serious brain fog this morning), the way the DateLookup table is laid out, this will always and forever require either a very expensive "Triangular Join" or 1 Clustered Index Seek for every row in the Date Table, which is also horribly expensive. 

    So my question becomes, can the DateLookup table be renamed and a specially calculated table, which would contain one extra row per IDClient and one extra column to include a non-inclusive "EndDate" column, which would be maintained auto-magically by a trigger on the original but renamed table?  If so, we can not only solve the short term problem but we can seriously deal with large scale in the future.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I meant my other question, not "previous post".
    As a backup I have been looking at your suggestion (Columns "DateBegin" and "DateEnd").
    I will have a shot at it first, and come back later.

    Thanks for your help,
    Cheers,
    Julian

  • Maybe I can save you some time...

    The following code works just fine but it has than nasty ol' Triangular Join in it.


      ALTER TABLE dbo.LookupTable
        ADD CONSTRAINT PK_LookupTable PRIMARY KEY CLUSTERED(Date,IDClient)
    ;

     SELECT  dt.Date
            ,dt.IsoYear
            ,dt.IsoWkNr
            ,ca.HrsPlan
       FROM dbo.DateTable dt
      OUTER APPLY
            (
             SELECT TOP 1
                    lu.HrsPlan
               FROM dbo.LookupTable lu
              WHERE lu.IDClient = 180
                AND lu.Active   = 1
                AND lu.Date    <= dt.Date
              ORDER BY lu.Date DESC
            ) ca
       ORDER BY Date
    ;

    If your lookup table has all of the goodies, including a "leader" row for each IDClient to cover dates prior to the first date in the original lookup table, things become a breeze...


       WITH
    cteEnumerateLU AS
    (--==== This adds a row number used for the upcoming offset calculation that
         -- will allow us to calculate the non-inclusive EndDate for each lookup
     SELECT  RN = ROW_NUMBER() OVER (PARTITION BY IDClient ORDER BY Date)
            ,IDClient
            ,Date
            ,HrsPlan
       FROM dbo.LookupTable
      WHERE Active   = 1
    )
     SELECT --lo.*, hi.*,
             IDCLient  = ISNULL(lo.IDClient,hi.IDClient)
            ,Date      = ISNULL(lo.Date,'0001')
            ,EndDate   = ISNULL(hi.Date,'9999') --Date of next row
            ,HrsPlan   = ISNULL(lo.HrsPlan,hi.HrsPlan)
       INTO #DateLookup
       FROM cteEnumerateLU lo
       FULL JOIN cteEnumerateLU hi
         ON lo.RN+1 = hi.RN
        AND lo.IDClient = hi.IDClient
    ;

    And this is what the lookup code becomes...


     SELECT dt.*, lu.HrsPlan
       FROM dbo.DateTable dt
       JOIN #DateLookup lu
         ON dt.Date >= lu.Date
        AND dt.Date  < lu.EndDate
      WHERE IDClient = 180
    ;

    That #DateLookup table could become a permanent table that would be recalculated whenever someone updated the original lookup table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thank you Jeff, great!
    Cheers,
    Julian

    (https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-apply-basics/)


    DECLARE @IDHUIS VARCHAR(4)
    DECLARE @IDAFD VARCHAR(4)
    DECLARE @JAAR INT
    DECLARE @WEEKBEGIN INT
    DECLARE @WEEKEND INT

    SET @IDHUIS = 'XX'
    SET @IDAFD = 'A03'
    SET @JAAR = 2017
    SET @WEEKBEGIN = 1
    SET @WEEKEND = 52

    SELECT dt.IsoYear,
            dt.IsoWkNr as IsoWeek,
            SUM(ISNULL(ca.UrenMeerzorg,0)/7) as HrsMeerzorg
      
    FROM    dbo.dimTime dt

            OUTER APPLY
                (
                    SELECT TOP 1 M.UrenMeerzorg
             
                    FROM dbo.Meerzorg M JOIN HuisAfdeling H ON
                            M.IDHuis = H.IDHuis AND
                            M.IDAfd = H.IDAfd
                            
                    WHERE M.Active = 1
                    AND    (M.IDHuis = @IDHUIS or @IDHUIS IS NULL)
                    AND (H.IDAfd = @IDAFD or @IDAFD IS NULL)        
                    AND H.MeeTellen <> 'NEE'
                    AND M.[DatumData] <= dt.[Date]
              
                    ORDER BY M.[DatumData] DESC
                ) ca

    WHERE   IsoYear = @JAAR
            AND (IsoWkNr >= @WEEKBEGIN OR @WEEKBEGIN IS NULL)
            AND (IsoWkNr <= @WEEKEND OR @WEEKEND IS NULL)

    GROUP BY IsoYear, IsoWkNr
    ORDER BY IsoWkNr

  • Please note that the code pattern used here has a severe performance problem.
    See https://www.simple-talk.com/content/article.aspx?article=2280 for explanations and options to fix

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • JJR333 - Monday, August 28, 2017 4:17 AM

    Thank you Jeff, great!
    Cheers,
    Julian

    (https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-apply-basics/)


    DECLARE @IDHUIS VARCHAR(4)
    DECLARE @IDAFD VARCHAR(4)
    DECLARE @JAAR INT
    DECLARE @WEEKBEGIN INT
    DECLARE @WEEKEND INT

    SET @IDHUIS = 'XX'
    SET @IDAFD = 'A03'
    SET @JAAR = 2017
    SET @WEEKBEGIN = 1
    SET @WEEKEND = 52

    SELECT dt.IsoYear,
            dt.IsoWkNr as IsoWeek,
            SUM(ISNULL(ca.UrenMeerzorg,0)/7) as HrsMeerzorg
      
    FROM    dbo.dimTime dt

            OUTER APPLY
                (
                    SELECT TOP 1 M.UrenMeerzorg
             
                    FROM dbo.Meerzorg M JOIN HuisAfdeling H ON
                            M.IDHuis = H.IDHuis AND
                            M.IDAfd = H.IDAfd
                            
                    WHERE M.Active = 1
                    AND    (M.IDHuis = @IDHUIS or @IDHUIS IS NULL)
                    AND (H.IDAfd = @IDAFD or @IDAFD IS NULL)        
                    AND H.MeeTellen <> 'NEE'
                    AND M.[DatumData] <= dt.[Date]
              
                    ORDER BY M.[DatumData] DESC
                ) ca

    WHERE   IsoYear = @JAAR
            AND (IsoWkNr >= @WEEKBEGIN OR @WEEKBEGIN IS NULL)
            AND (IsoWkNr <= @WEEKEND OR @WEEKEND IS NULL)

    GROUP BY IsoYear, IsoWkNr
    ORDER BY IsoWkNr

    Heh... the code in my first example (which you appear to have modeled the code above from)  was to demonstrate the horrible performance and resource usage of a Triangular Join in hopes of selling you on the modified table solution that follows that.  The ORs that you've added smack of a typical "Catch All" query (a bad, thing the way it is written), which Gail wrote about in the link she provided.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Okay, in a bit of a panic here to come up with a soluton. :pinch:
    Will study your answer more thoroughly.
    Thanks,
    J.

  • This is starting to look better!
    Want to use as a storedprocedure with (optional) parameters for various reports.
    IDHuis (location), IDAfd (department within location)
    Any thoughts?

    Thanks for your input and guidance Jeff! 🙂
    Cheers,
    Julian





    USE XXX;
    GO

    DROP TABLE #DATELOOKUP;
    GO

    WITH
    cteEnumerateLU AS
    (--==== This adds a row number used for the upcoming offset calculation that
      -- will allow us to calculate the non-inclusive EndDate for each lookup
    SELECT RN = ROW_NUMBER() OVER (PARTITION BY IDBewoner ORDER BY Datumdata)
       ,IDHuis, IDAfd, IDBewoner, DatumData, UrenMeerzorg
     FROM dbo.Meerzorg
    WHERE Active = 1
    )
    SELECT --lo.*, hi.*,
        IDCLient        = ISNULL(lo.IDBewoner,HI.IDBewoner )
       ,IDHuis         = ISNULL(lo.IDHuis,HI.IDHuis )
       ,IDAfd          = ISNULL(lo.IDAfd,HI.IDAfd )
       ,BeginDate      = ISNULL(lo.DatumData,'0001')
       ,EndDate        = ISNULL(hi.DatumData,'9999') --Date of next row
       ,HrsMeerzorg    = ISNULL(lo.UrenMeerzorg,0)
      
     INTO #DateLookup
     
     FROM cteEnumerateLU lo
                FULL JOIN cteEnumerateLU hi
                    ON lo.RN+1 = hi.RN
                 AND lo.IDBewoner = hi.IDBewoner
     
    --/*------
      select *
      from #DateLookup
      order by IDCLient, BeginDate, enddate
    --*/-----

        SELECT        dt.IsoYear, dt.IsoWkNr ,
                    SUM(lu.HrsMeerzorg)/7 AS HrsMeerzorg
                    
        FROM        dbo.dimTime dt
                        JOIN #DateLookup lu
                            ON    dt.Date >= lu.BeginDate
                                AND dt.Date < lu.EndDate
                                
        WHERE        DT.IsoYear >= 2016
                    AND dt.IsoWkNr between 1 and 52
                    --AND lu.IDHuis = 'WH'
                    --AND lu.IDAfd = 'A01'

        GROUP BY dt.isoyear, dt.IsoWkNr
        ORDER BY dt.isoyear, dt.IsoWkNr

    ;

  • JJR333 - Monday, August 28, 2017 11:52 AM


    Any thoughts?

    Yes. Read the article I linked.
    Any time you have optional parameters, or very large differences in row count between executions (eg doing things like ,BeginDate  = ISNULL(lo.DatumData,'0001') ,EndDate   = ISNULL(hi.DatumData,'9999') ) you are likely to have erratic performance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply