[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

How can I improve this SQL solution such that foreign key criteria can be applied?

From a previous question I was given :

SELECT     *
FROM         (SELECT     TOP 1 Datecolumn
                       FROM          mytable
                       WHERE      (Datecolumn< @StartDate)
                       ORDER BY Datecolumn DESC) x
UNION
SELECT     Datecolumn
FROM        mytable
WHERE     Datecolumn BETWEEN @StartDate AND @EndDate
UNION
SELECT *
FROM          (SELECT TOP 1 Datecolumn
                       FROM   mytable
                       WHERE     Datecolumn > @EndDate
                       ORDER BY Datecolumn
                       )
ORDER BY DateColumn

The purpose of this was to find records between two dates and return one record from either side of those dates.

Now mytable has a key which I want to also group by based on a foreign key join so above will become something like :
SELECT     *
FROM         (SELECT     TOP 1 Datecolumn,ForeignKeyID
                       FROM          mytable
                       WHERE      (Datecolumn< @StartDate) and ForeignKeyID = @ForeignKeyID
                       ORDER BY Datecolumn DESC) x
UNION
SELECT     Datecolumn,ForeignKeyID
FROM        mytable
WHERE     Datecolumn BETWEEN @StartDate AND @EndDate and ForeignKeyID = @ForeignKeyID
UNION
SELECT *
FROM          (SELECT TOP 1 Datecolumn,ForeignKeyID
                       FROM   mytable
                       WHERE     Datecolumn > @EndDate and ForeignKeyID = @ForeignKeyID
                       ORDER BY Datecolumn
                       )
ORDER BY DateColumn

Except that instead of providing @ForeignKeyID via a stored procedure I want to join on a list of ForeignKeys from another selection and for all the results to return together although as above with just the dates between for each ForeignKey and one either side.

At the moment I am using a stored procedure but I wanted to get a group of results in one hit rather than round tripping each stored procedure.

Thanks in advance!
0
dgloveruk
Asked:
dgloveruk
  • 9
  • 5
  • 5
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post an example of the rows that you are getting now and those ones that you want to achieve?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first of all, you should apply UNION ALL and not UNION to avoid the sql tries to perform a implicit DISTINCT, which you don't need

then, to apply a JOIN, you simply put "all" into a "inline view" like this:
SELECT  sq.*, lt.*
FROM ( SELECT Datecolumn,ForeignKeyID
     FROM         (SELECT     TOP 1 Datecolumn,ForeignKeyID
                       FROM          mytable
                       WHERE      (Datecolumn< @StartDate) and ForeignKeyID = @ForeignKeyID
                       ORDER BY Datecolumn DESC) x
    UNION ALL
SELECT     Datecolumn,ForeignKeyID
FROM        mytable
WHERE     Datecolumn BETWEEN @StartDate AND @EndDate and ForeignKeyID = @ForeignKeyID
UNION ALL
SELECT *
FROM          (SELECT TOP 1 Datecolumn,ForeignKeyID
                       FROM   mytable
                       WHERE     Datecolumn > @EndDate and ForeignKeyID = @ForeignKeyID
                       ORDER BY Datecolumn
                       )
 ) SQ
JOIN other_table lt
   on lt.ID = SQ.ForeignKeyID
 ...
ORDER BY SQ.DateColumn

Open in new window

0
 
dgloverukAuthor Commented:
Thanks for replying Guy,
I've tried to do what you said but encountered issues.
How does the @ForeignKeyID parameter which is currently part of the criteria get set via that join?
If I take out the @ForeignKeyID criteria and try to use the results to join I don't get sets of dates that are correct since Select TOP 1 doesn't return the correct record without @ForeignKeyID present to filter out.
Have I not understood your suggestion?
Regards,
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I will need to see some sample data of the tables and the expected results to clarify.
0
 
dgloverukAuthor Commented:
No problem, hopefully this will help to demonstrate this :exampleDatePeriodSelection.pngI have attached that XLS file too.
Regards,
0
 
dgloverukAuthor Commented:
0
 
dgloverukAuthor Commented:
To clarify, the requestedlist table is my selection which contains the periodsetIDs which I wish to return with the @startdate and @enddate parameters.
Regards,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you need to run your query for each period?
And the @ForeignKeyID it's the PeriodSetID?
0
 
dgloverukAuthor Commented:
My actual stored procedure currently is (it is a little different from first post, in that it uses start and end dates as opposed to a single date)

SELECT     *
FROM         (SELECT     TOP 1 *
                       FROM          dbo.KPI
                       WHERE     KPIStartDate <= @StartDate and ConsultantID = @ConsultantID
                       ORDER BY KPIStartDate DESC) x
UNION ALL
SELECT     *
FROM         dbo.KPI
WHERE     (KPIStartDate > @StartDate and KPIEndDate < @EndDate) and ConsultantID = @ConsultantID
union ALL
SELECT     *
FROM           (SELECT     TOP 1 *
                       FROM          dbo.KPI
                       WHERE     KPIStartDate <= @EndDate and ConsultantID = @ConsultantID
                       ORDER BY KPIStartDate DESC)
0
 
dgloverukAuthor Commented:
Sorry to confuse you guys, I was trying to provide abstract examples but ended up with a mixture!
Vitor, the above example @PeriodSetID is actually @ConsultantID
The query runs in three parts as shown in example (contrary to original post)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your query should work EXCEPT the ORDER BY is wrong in one of the places:
SELECT     *
FROM         (SELECT     TOP 1 *
                       FROM          dbo.KPI
                       WHERE     KPIStartDate <= @StartDate and ConsultantID = @ConsultantID
                       ORDER BY KPIStartDate DESC ) x
UNION ALL
SELECT     *
FROM         dbo.KPI
WHERE     (KPIStartDate > @StartDate and KPIEndDate < @EndDate) and ConsultantID = @ConsultantID
union ALL
SELECT     *
FROM           (SELECT     TOP 1 *
                       FROM          dbo.KPI
                       WHERE     KPIStartDate <= @EndDate and ConsultantID = @ConsultantID
                       ORDER BY KPIStartDate ASC) 

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think the problem is with the ORDER BY but with the field in the last subselect (KPIEndDate  instead of KPIStartDate):

SELECT     *
 FROM         (SELECT     TOP 1 *
                        FROM          dbo.KPI
                        WHERE     KPIStartDate <= @StartDate and ConsultantID = @ConsultantID
                        ORDER BY KPIStartDate DESC) x
 UNION ALL
 SELECT     *
 FROM         dbo.KPI
 WHERE     (KPIStartDate > @StartDate and KPIEndDate < @EndDate) and ConsultantID = @ConsultantID
 union ALL
 SELECT     *
 FROM           (SELECT     TOP 1 *
                        FROM          dbo.KPI
                        WHERE     KPIEndDate > @EndDate and ConsultantID = @ConsultantID
                        ORDER BY KPIEndDate DESC) 

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, tested with more data and the last ORDER BY should be ASC.
Means that from the original code in the last subselect you need to change KPIStartDate to KPIEndDate , <= to > and DESC to ASC:

SELECT     *
 FROM         (SELECT     TOP 1 *
                        FROM          dbo.KPI
                        WHERE     KPIStartDate <= @StartDate and ConsultantID = @ConsultantID
                        ORDER BY KPIStartDate DESC) x
 UNION ALL
 SELECT     *
 FROM         dbo.KPI
 WHERE     (KPIStartDate > @StartDate and KPIEndDate < @EndDate) and ConsultantID = @ConsultantID
 union ALL
 SELECT     *
 FROM           (SELECT     TOP 1 *
                        FROM          dbo.KPI
                        WHERE     KPIEndDate > @EndDate and ConsultantID = @ConsultantID
                        ORDER BY KPIEndDate ASC) 

Open in new window

0
 
dgloverukAuthor Commented:
Ok thank you for spotting that! Given that change, I how can I operate this procedure to be more like a select where the @ConsultantID is given from a list of table entries as alluded to in the XLS example?
Regards,
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
using such a function  as I describe in this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

however, for the TOP 1 queries, you will need to change the approach, for example like described in this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

I try to put the code here:
SELECT     ConsultantID , KPIStartDate , KPIEndDate 
 FROM         (SELECT      ConsultantID , KPIStartDate , KPIEndDate 
                           , row_number() over ( partition by ConsultantID                          ORDER BY KPIStartDate DESC) rn
                        FROM          dbo.KPI
                        WHERE     KPIStartDate <= @StartDate 
                            and ConsultantID in ( select value from dbo.ParmsToList( @ConsultantID, ',')) 
                  ) x
  where X.rn = 1
 UNION ALL
 SELECT     ConsultantID , KPIStartDate , KPIEndDate 
 FROM         dbo.KPI
 WHERE     (KPIStartDate > @StartDate and KPIEndDate < @EndDate) 
         and ConsultantID in ( select value from dbo.ParmsToList( @ConsultantID, ',')) 
 union ALL
 SELECT     ConsultantID , KPIStartDate , KPIEndDate 
 FROM           (SELECT     ConsultantID , KPIStartDate , KPIEndDate 
                           , row_number() over ( partition by ConsultantID                          ORDER BY KPIStartDate ASC) rn
                        FROM          dbo.KPI
                        WHERE     KPIEndDate > @EndDate 
                      and ConsultantID in ( select value from dbo.ParmsToList( @ConsultantID, ',')) 
                     )  x
   WHERE x.rn = 1              

Open in new window

0
 
dgloverukAuthor Commented:
Thanks for your suggestion Guy, I am not sure it is quite what I was looking for...
I don't really want to pass a list of parameter @ConsultantIDs.  The consultantID list I need can be generated by the SQL server from a select query.  Perhaps you understood this and ParmsToList is appropriate.
Instead I have an idea how to do it which is essentially to Select my ConsultantIDs and then loop through the results with a cursor firing off the stored procedure to put each result into a temporary table and then returning its entire contents at the end of the cursor.

My main objective in all of this is that whilst the stored procedure above is not cpu intensive and the KPI table is never going to get very large (a few thousand records), I didn't want lots of client/server conversations to pull each KPI.

Cursors and temporary tables might have relatively high overhead but I am guessing (and it is guessing!) that running this stored procedure 80 times in a cursor and returning the table will be faster than calling a stored procedure 80 times from the client.

Comments always welcome!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in regards to this:
The consultantID list I need can be generated by the SQL server from a select query.  

then just replace:
and ConsultantID in ( select value from dbo.ParmsToList( @ConsultantID, ','))
by:
and ConsultantID in (  <your select query you refer to> )

and you are done
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If want you want it's do return all consultants that matches the date periods then you may try this solution:
;WITH 
FirstDate_CTE AS
(SELECT  ConsultantID, MAX(PeriodID) PeriodID
FROM dbo.KPI
WHERE  KPIStartDate <= @StartDate
GROUP BY ConsultantID)
,LastDate_CTE AS
(SELECT  ConsultantID, MIN(PeriodID) PeriodID
FROM dbo.KPI
WHERE  KPIEndDate > @EndDate
GROUP BY ConsultantID)

SELECT     *
FROM    (SELECT  P.*
		FROM dbo.KPI P
			INNER JOIN FirstDate_CTE C
			ON P.ConsultantID = C.ConsultantID
        WHERE  P.PeriodID = C.PeriodID) x
UNION ALL
SELECT     P.*
FROM      dbo.KPI P
WHERE     (P.KPIStartDate > @StartDate and P.KPIEndDate < @EndDate) 
union ALL
SELECT   *
FROM    (SELECT  P.*
		FROM dbo.KPI P
			INNER JOIN LastDate_CTE C
			ON P.ConsultantID = C.ConsultantID
        WHERE  P.PeriodID = C.PeriodID) Y
ORDER BY ConsultantID, PeriodID

Open in new window

0
 
dgloverukAuthor Commented:
Thanks guys, I used a variation of Vitors solution since I was having problems with row_number() over  partition which I'm thinking was not compatible with my sql server since it didn't recognise row_number().
Thanks you both of you for pointing out the fault in my join too which would have gone unnoticed!
Regards,
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 9
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now