1

I have a table that contains the daily consumptions for each existing "connection".

create table dbo.Consumptiondata
(
    ConnectionId nvarchar(125) not null,
    StartDateTime datetime not null,
    EndDateTime datetime not null,
    Value numeric(20, 10) not null
)

I need to write a query that yields the days without consumptions (which have no records in the table) for each connection.

I started by writing a query generating a temporary table of all expected dates between a given start and end date:

--  Temporary tables
declare @DatesRange table (DateValue datetime);

--  Build date range
set @currentDateTime = @startDateTime
while @currentDateTime <= @endDateTime
    begin
        insert into @DatesRange values (@currentDateTime);
        set @currentDateTime = dateadd(day, 1, @currentDateTime);
    end

I tried then to get the job done by linking (left or right join) with the consumption table, but I didn't find a way to make it work due to the connection field.

I then modified my temporary table to add the needed date range for each connection:

-- Date filters
declare @startDateTime varchar(100) = '2023-05-01 00:00:00.000';
declare @endDateTime varchar(100) = '2023-06-01 00:00:00.000'
declare @currentDate datetime;

--  Temporary tables
declare @ExpectedDatesPerConnectionId table (ExternalConnectionId nvarchar(125), ExpectedDate datetime);

set @currentDate = @StartDateTime;
while @currentDate < @EndDateTime 
begin
    insert into @ExpectedDatesPerConnectionId
    (ConnectionId, ExpectedDate)
    (select distinct ConnectionId, @currentDate
    from Consumptiondata)
    
    set @currentDate = dateadd(day, 1, @currentDate)
end

Then I merely execute the following query, which gives me the expected result:

select b.ConnectionId, b.ExpectedDate
from @ExpectedDatesPerConnectionId b
where b.ExpectedDate not in (select DateConsumption from Consumptiondata where ConnectionId = b.ExternalConnectionId)
order by b.ConnectionId, b.ExpectedDate

The problem? It takes over 8 minutes to run.

So, I'm wondering if anyone here would know of another means of obtaining the same result?

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Fysicus
  • 183
  • 2
  • 14
  • 2
    Why not use your calendar table to help you achieve this? – Thom A Jun 29 '23 at 13:08
  • Feels more like a Gaps-and-Islands problem, also LOOPS should be a last resort... a set based approach is usually best. Sample data and desired results would be helpful – John Cappelletti Jun 29 '23 at 13:28
  • 2
    Furthermore, why would your @variables be varchar(100) ? – John Cappelletti Jun 29 '23 at 13:30
  • 1
    Can start/enddate span multiple days? – siggemannen Jun 29 '23 at 13:54
  • Start/enddate will typically span several weeks, since the idea would be to regularly check the tables. – Fysicus Jun 29 '23 at 14:15
  • Those were the parameters I was provided with, nothing more. You are right though, they're way too big. Though I don't see the relevance to my question. :-) – Fysicus Jun 29 '23 at 14:19
  • Can start/enddate span multiple days in _ConsumptionData_ ? Do these values have a _time_ component that you care about for this report? If your output is only at day level, it would be simpler to introduce a calculated column of type date to the table to represent those days, index them, and join to a daily calendar table. – Nick.Mc Jul 04 '23 at 22:59
  • Really what you're after is a cross join of the date period of interest, and the list of unique connections. That is your "Expected" list. Then you outer join that to your `Consumption` table to get the answer you want – Nick.Mc Jul 04 '23 at 23:01

3 Answers3

1

You can use a calendar to list all days between your start/end dates, and then simply extrat days that are not used in your consumption table.

;with
n as (select ROW_NUMBER() over (order by object_id) -1 n from sys.objects),
cal as (
    select *, dateadd(DAY, n, @startDateTime) theday
    from n
    where n < datediff(DAY, @startDateTime, @endDateTime)
)
select *
from cal c
where not exists (
    select null
    from Consumptiondata d
    where c.theday between d.startDateTime and d.endDateTime
    and ConnectionId = @externalConnectionId
)

P.S. sys.objects is just a quick and simple solution to build the calendar, you can use a tally table or any other way you like

MtwStark
  • 3,866
  • 1
  • 18
  • 32
  • I think I will try this solution, did found several mentions of this approach on Google, but couldn't make heads or tails of it... – Fysicus Jun 29 '23 at 14:16
  • The problem I keep on running into is I need to query this on ConnectionId. So, generate a list for each seperate connectionId... Running it like this gives me a list over there entire dataset, not per ConnectionId. – Fysicus Jun 29 '23 at 16:42
  • I have updated the query to use a filter on parametrized ConnectionId – MtwStark Jun 30 '23 at 09:41
0

You just need somewhere to pull all the dates from. It's pretty common to use a calendar table of some description for this. I have a favored function to handle that for me.

Then, it just becomes an exercise in pulling the non-matches:

DECLARE @Table TABLE (Date DATE);
INSERT INTO @Table (Date) VALUES ('2023-06-01'),('2023-06-03'),('2023-06-04'),('2023-06-09'),('2023-06-12'),('2023-06-13'),('2023-06-14'),('2023-06-15'),('2023-06-28'),('2023-06-29');

SELECT c.Date
  FROM Calendar(1,1) c
    LEFT OUTER JOIN @Table t
      ON c.Date = t.Date
 WHERE c.Month = 6
   AND c.Year = 2023
   AND t.Date IS NULL;

Here we just return all the rows not in the @Table.

If you'd like the function I reference:

CREATE OR ALTER FUNCTION [dbo].[Calendar] (@StartYear INT, @EndYear INT)
RETURNS @Calender TABLE (
Date DATE, DateEndTime DATETIME2, Year INT, Month INT, Day INT, Quarter INT, WeekNumber INT, MonthName NVARCHAR(20), DayName NVARCHAR(20), WeekStartDate DATE, WeekEndDate DATE, MonthStartDate DATE, MonthEndDate DATE,
 QuarterStartDate DATE, QuarterEndDate DATE, YearStartDate DATE, YearEndDate DATE, WeekStartTime DATETIME2, WeekEndTime DATETIME2, MonthStartTime DATETIME2, MonthEndTime DATETIME2, QuarterStartTime DATETIME2, QuarterEndTime DATETIME2,
 YearStartTime DATETIME2, YearEndTime DATETIME2, IsWeekDay BIT) 
AS
BEGIN

WITH CalendarHistory AS (
SELECT CAST(GETUTCDATE() AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,-1,Date)
  FROM CalendarHistory
 WHERE DATEADD(DAY,-1,Date) > DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,-@StartYear,GETUTCDATE())),1,1))
), CalendarFuture AS (
SELECT CAST(DATEADD(DAY,1,GETUTCDATE()) AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,1,Date)
  FROM CalendarFuture
 WHERE DATEADD(DAY,1,Date) < DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,@EndYear,GETUTCDATE())),1,1)
), Calendar AS (
SELECT Date
  FROM CalendarHistory
UNION ALL
SELECT Date
  FROM CalendarFuture
)

INSERT INTO @Calender
SELECT Date, 
       DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,Date) AS DATETIME2)) AS DateEndTime,
       DATEPART(YEAR,Date) AS Year, DATEPART(MONTH,Date) AS Month, DATEPART(DAY,Date) AS Day, DATEPART(QUARTER,Date) AS Quarter, DATEPART(WEEK,Date) AS WeekNumber,
       DATENAME(MONTH,Date) AS MonthName, DATENAME(WEEKDAY,Date) AS DayName,
       DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS WeekStartDate, DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date) AS WeekEndDate,
       DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS MonthStartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS MonthEndDate,
       DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS QuarterStartDate, DATEADD(DAY,-1,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS QuarterEndDate,
       DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS YearStartDate, DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS YearEndDate,
       CAST(DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS DATETIME2) AS WeekStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date)) AS DATETIME2)) AS WeekEndTime,
       CAST(DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS DATETIME2) AS MonthStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS DATETIME2)) AS MonthEndTime,
       CAST(DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS DATETIME2) AS QuarterStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS DATETIME2)) AS QuarterEndTime,
       CAST(DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS DATETIME2) AS YearStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS DATETIME2)) AS YearEndTime,
       CASE WHEN DATEPART(WEEKDAY,Date) IN (1,7) THEN 0 ELSE 1 END AS IsWeekDay
  FROM Calendar
 ORDER BY Date
 OPTION (MAXRECURSION 0)
 RETURN
END;

It accepts two ints, representing the number of years back/forward you'd like it to go.

Patrick Hurst
  • 2,086
  • 1
  • 3
  • 13
0

Looked into it with one of my colleagues, and he proposed the following solution:

-- Date filters
declare @startDateTime datetime = '2021-08-01 00:00:00.000';
declare @endDateTime datetime = '2021-09-01 00:00:00.000';

drop table if exists #daysInRange;

 with daysInRange
as (
    select @startDateTime as 'StartDateTime', dateadd(day, 1, @startDateTime) as 'EndDateTime'
    union all
    select dateadd(day, 1, StartDateTime) as 'StartDateTime',
           dateadd(day, 1, EndDateTime) as 'EndDateTime'
    from daysInRange
    where StartDateTime < @endDateTime
)

select *
into #daysInRange
from daysInRange

 select conn.ConnectionId,
        r.StartDateTime,
        r.EndDateTime
from dbo.ConnectionData conn,
     cross apply (
        select *
        from #daysInRange
    ) as r
    left outer join dbo.ConsumptionData cons on cons.ConnectionId = conn.ConnectionId and cons.StartDateTime = r.StartDateTime and cons.EndDateTime = r.EndDateTime
where conn.STARTDATETIME < @endDateTime
  and conn.ENDDATETIME > @startDateTime
  and conn.Active = 1
  and cons.ConnectionMember is null
group by conn.ConnectionId,
         r.StartDateTime,
         r.EndDateTime
order by r.StartDateTime
go

I originally ommitted the ConnectionData table from my posting since in my query it only served as an additional filter on the ConsumptionData. Here it's an integral part of the query so I left it in this time.

Regardless, the execution time dropped from 8 minutes to less than 3O seconds.

Fysicus
  • 183
  • 2
  • 14