August 26, 2017 at 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)
August 26, 2017 at 4:25 pm
JJR333 - Saturday, August 26, 2017 6:16 AMHi,
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 7DATA TABLE
Date Data
---------- --------
2017-01-02 700
2017-01-03 700
2017-01-04 etc2017-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
Change is inevitable... Change for the better is not.
August 27, 2017 at 5:53 am
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)
August 27, 2017 at 6:46 am
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
August 27, 2017 at 8:19 am
Yes, 2008R2
August 27, 2017 at 10:56 am
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
Change is inevitable... Change for the better is not.
August 27, 2017 at 12:06 pm
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
Change is inevitable... Change for the better is not.
August 28, 2017 at 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
August 28, 2017 at 5:16 am
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
August 28, 2017 at 7:40 am
JJR333 - Monday, August 28, 2017 4:17 AMThank 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 INTSET @IDHUIS = 'XX'
SET @IDAFD = 'A03'
SET @JAAR = 2017
SET @WEEKBEGIN = 1
SET @WEEKEND = 52SELECT dt.IsoYear,
dt.IsoWkNr as IsoWeek,
SUM(ISNULL(ca.UrenMeerzorg,0)/7) as HrsMeerzorg
FROM dbo.dimTime dtOUTER 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
) caWHERE 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
Change is inevitable... Change for the better is not.
August 28, 2017 at 10:05 am
Okay, in a bit of a panic here to come up with a soluton. :pinch:
Will study your answer more thoroughly.
Thanks,
J.
August 28, 2017 at 11:52 am
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
;
August 28, 2017 at 3:06 pm
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
August 8, 2021 at 12:39 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply