CODEWITHSUNDEEP
sqlloops
st87_top
st87_top

Reputation: 57

iterate through results of one table as input into another

Hope you could please help me. I have looked at a few questions on here that are similar in nature, but I have not been able to find what I am looking for, so hopefully this isn't a duplicate question.

I am trying to build a process whereby customers get billed for the number of business days they were renting a product.

I have one table which shows the start date and the end date for a particular rental.

And my organisation provides a Calendar service where if you feed it the start date, end date and calendar, it will return the number of business days.

My problem is that the start dates and end dates from the first table need to be passed as arguments in the query for the second table and I am struggling to find an efficient way to do this.

Table Customer_Data (c.200k rows):

| FirstName | LastName | StartDate | EndDate |
|:-----:|:-----:|:------:|:------|
| John | Doe | 02-02-2022 | 09-02-2022 |

CalendarService: requires 3 arguments - a start date, an end date and a calendar (e.g. LDN or NYK etc).

I have the following code:

SELECT
*
FROM CalendarService
WHERE Calendar = 'LDN' 
and StartDate in (select startdate from customer_data)
and endDate in (select endDate from customer_data)

My issue is this returns over 5m rows as it essentially takes every single start date and compares that to every single end date to give me all the possible business day combinations, instead of only running it for the combinations in the first table.

Is there a more efficient way to pass each row in customer_data through the calendar_service?

I am using a proprietary data platform for my organisation that uses an adapted SQL variant so need to keep solutions generic. The platform does also take Python and R, but I dont know either of them.

Upvotes: 0

Views: 57

Answers (1)

svenGUTT
svenGUTT

Reputation: 419

Here is an example inline table function. Not sure what you are pulling out of the CalendarService table so this leaves it open to add additional fields to the output opposed to using a single valued scalar function. You will still need some way of determining that Calendar value more dynamically...not sure on your options for that so I just have it set to a static value in this example.It would seem to me that should be something to track on your Customer_Data table, but that's up to you and your options.

IF OBJECT_ID('dbo.fnGetBusinessDaysFromDateRange') IS NOT NULL
    DROP FUNCTION dbo.fnGetBusinessDaysFromDateRange
GO

CREATE FUNCTION dbo.fnGetBusinessDaysFromDateRange
(
    @StartDate DATETIME, @EndDate DATETIME, @CalendarType VARCHAR(8)
)
RETURNS TABLE
AS   
    RETURN (
        SELECT
            CalendarService.DaysValue as BusinessDays,
            CalendarService.SomeValue
        FROM CalendarService
        WHERE CalendarService.Calendar = @CalendarType
        AND CalendarService.StartDate = @StartDate
        AND CalendarService.EndDate = @EndDate
    )

GO

--Usage:
SELECT 
    CD.*, 
    CalendarBusinessDays.BusinessDays, 
    CalendarBusinessDays.SomeValue
FROM Customer_Data CD
CROSS APPLY dbo.fnGetBusinessDaysFromDateRange(CD.StartDate, CD.EndDate, 'NKY') as CalendarBusinessDays

Upvotes: 1

Related Questions