UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculating Number Of Days Within A Date Range, Access 2016    
 
   
aedwards89
post Aug 18 2019, 04:29 PM
Post#1



Posts: 6
Joined: 23-August 18



I have a issue i need help resolving.

I am dealing with the tracking car repairs. I need to know how many actual days a acar was in the shop. I have "Maintenance actions" records along with the number of days the car was in the shop for that maintenace action.
What I would like help with:
How do I calculate the number of ACTUAL days in shop within from the start to end date? (See diagram below)
Additionally how do I show (in aquery) the records that have maintenac actions that fall in this date range? I currently have a table that lists all maintenance actions and it will be filtered by make and I want to onyl show records that fall in the date range.

START DATE END DATE
| --------------------------------------------------------------------------- |

|--------------------| Maintenance action 1 (4 days)

| -----------------------| Maintenance action 2 (6 days)
| -------------------------------------------- | Maintenance action 3 (12 days)

I want to the Exclude the days outside of the start and end dates, days not in shop within the start and end date, AND overlapping days.
This post has been edited by aedwards89: Aug 18 2019, 04:46 PM
Attached File(s)
Attached File  snapshot_081819.PNG ( 4.97K )Number of downloads: 6
 
Go to the top of the page
 
MadPiet
post Aug 18 2019, 07:22 PM
Post#2



Posts: 3,291
Joined: 27-February 09



Something like this?

CODE
use tempdb;
GO

/* Create and populate a Calendar table */
CREATE TABLE #Calendar (TheDate DATE PRIMARY KEY);

DECLARE @TheDate DATE = '01-Jan-2019';
WHILE @TheDate <'15-Jan-2019'
BEGIN
    INSERT INTO #Calendar(TheDate) VALUES (@TheDate);
    SET @TheDate = DATEADD(day,1,@TheDate);
END

/* Create and populate events table */
CREATE TABLE #Events (
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL);
GO

INSERT INTO #Events (StartDate,EndDate) VALUES
('01-Jan-2019','4-Jan-2019'),('01-Jan-2019','6-Jan-2019'),('01-Jan-2019','13-Jan-2019');

/* answer the question */
SELECT COUNT(DISTINCT c.TheDate) As DaysInShop
FROM #Calendar c
    INNER JOIN #Events e ON c.TheDate BETWEEN e.StartDate AND e.EndDate;


The syntax is slightly different when you do a Count Distinct in Access. I think it's DistinctCount, but honestly, I can't remember.
Go to the top of the page
 
MadPiet
post Aug 18 2019, 08:29 PM
Post#3



Posts: 3,291
Joined: 27-February 09



Yeah, my earlier answer is wrong... sorry. here's another try. I did it in T-SQL, because the documentation is better.

CODE
use tempdb;
GO

-- Create and populate a Calendar table, because you need one to answer this question
CREATE TABLE #Calendar (TheDate DATE PRIMARY KEY);

DECLARE @TheDate DATE = '28-Dec-2018';
WHILE @TheDate <'22-Jan-2019'
BEGIN
    INSERT INTO #Calendar(TheDate) VALUES (@TheDate);
    SET @TheDate = DATEADD(day,1,@TheDate);
END


CREATE TABLE #Events (
    CarID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL);
GO

INSERT INTO #Events (CarID, StartDate, EndDate) VALUES
(1,'01-Jan-2019','4-Jan-2019')
,(1,'06-Jan-2019','15-Jan-2019')
,(1,'13-Jan-2019','19-Jan-2019')
,(2,'02-Jan-2019','4-Jan-2019')
,(2,'10-Jan-2019','15-Jan-2019')
,(2,'12-Jan-2019','14-Jan-2019');


The Date Span is the span of dates we're checking for activity

CODE
DECLARE @StartDateSpan DATE = '28-Dec-2018', @EndDateSpan DATE = '20-Jan-2019';

SELECT sched.CarID
    ,  SUM(sched.InShop) AS DaysIn
FROM
(SELECT e.CarID
    , CASE WHEN c.TheDate >= e.StartDate AND c.TheDate<=e.EndDate THEN 1 ELSE 0 END AS InShop
FROM #Calendar c
    LEFT JOIN #Events e ON c.TheDate BETWEEN e.StartDate AND e.EndDate) sched
WHERE sched.CarID IS NOT NULL
GROUP BY sched.CarID;


The analogous structure to CASE WHEN in Access is IIF, so you'd write it like this:

CODE
SELECT sched.CarID
    ,  SUM(sched.InShop) AS DaysIn
FROM
(SELECT e.CarID
    , IIF(c.TheDate >= e.StartDate AND c.TheDate<=e.EndDate , 1 , 0) AS InShop
FROM #Calendar c
    LEFT JOIN #Events e ON c.TheDate BETWEEN e.StartDate AND e.EndDate) sched
WHERE sched.CarID IS NOT NULL
GROUP BY sched.CarID;


Maybe this helps?
Go to the top of the page
 
aedwards89
post Aug 18 2019, 09:20 PM
Post#4



Posts: 6
Joined: 23-August 18



Thank you for your response! The syntax is not familiar to me but I think I understand the logic. Will chew on this and report back.
Go to the top of the page
 
aedwards89
post Aug 18 2019, 11:34 PM
Post#5



Posts: 6
Joined: 23-August 18



I have a few questions MadPiet.

1. I am having a little trouble uderstanding the schedule query. it is a join between the calendar table and the events tables, yes?
SELECT sched.CarID
, SUM(sched.InShop) AS DaysIn
FROM
(SELECT e.CarID
, IIF(c.TheDate >= e.StartDate AND c.TheDate<=e.EndDate , 1 , 0) AS InShop
FROM #Calendar c
LEFT JOIN #Events e ON c.TheDate BETWEEN e.StartDate AND e.EndDate) sched ***not sure about the lines from the first FROM to the WHERE statement (mostly the "sched" after)
WHERE sched.CarID IS NOT NULL
GROUP BY sched.CarID;

2. Where does the total sum into play? SUM line?
Go to the top of the page
 
dale.fye
post Aug 19 2019, 04:24 AM
Post#6



Posts: 160
Joined: 28-March 18
From: Virginia


I'm confused as to how your "End Date" on the first line of the last image is before the End date of Maintenance action #3.


--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
aedwards89
post Aug 19 2019, 05:58 AM
Post#7



Posts: 6
Joined: 23-August 18



I apologize the formatting was off when i took the screen shot. The original END DATE is supposed to be above the first hash. Maintenace action #3 starts in between the START DATE and END DATE but ends after the end date. So only the days that fall in between the START and END DATE should be counted. Anything outside the START or END DATES is excluded (as well as gaps). Make sense?
This post has been edited by aedwards89: Aug 19 2019, 06:01 AM
Go to the top of the page
 
MadPiet
post Aug 21 2019, 12:46 AM
Post#8



Posts: 3,291
Joined: 27-February 09



How about this one? Maybe this works...
Setup:
CODE
use tempdb;
GO

-- shop visits (repair start/end on Car)
CREATE TABLE #ShopVisit (
    CarID INT NOT NULL,
    EnterDate DATE NOT NULL,
    ExitDate DATE NOT NULL
);

INSERT INTO #ShopVisit (CarID, EnterDate, ExitDate) VALUES
(1,'02-Jan-2019','4-Jan-2019')
,(1,'06-Jan-2019','9-Jan-2019')
,(2,'02-Jan-2019','4-Jan-2019')
,(2,'07-Jan-2019','10-Jan-2019')
,(2,'09-Jan-2019','11-Jan-2019'); -- overlaps prev;

CREATE TABLE #Calendar (TheDate DATE PRIMARY KEY);
INSERT INTO #Calendar(TheDate) VALUES ('1/2/19'),('1/3/19'),('1/4/19'),('1/5/19')
,('1/6/19'),('1/7/19'),('1/8/19'),('1/9/19'),('1/10/19'),('1/11/19');


You could use parameters in your Access query... (I just find them weird.)

CODE
-- these are T-SQL parameters
-- Only count the days in the shop between these two dates
DECLARE @SpanStart DATE = '04-Jan-2019';
DECLARE @SpanEnd DATE = '9-Jan-2019';

    SELECT  sv.CarID
        , COUNT(DISTINCT(c.TheDate)) AS DaysIn   -- Access syntax for this is DISTINCTCOUNT(c.TheDate)
    FROM #ShopVisit sv
        CROSS JOIN #Calendar c
    -- Limit the dates to the span of dates selected.
    -- then filter out the dates where the car is not in the shop.
WHERE c.TheDate>=sv.EnterDate
    AND c.TheDate<=sv.ExitDate
    /* Process the span logic */
    AND c.TheDate>=@SpanStart
    AND c.TheDate<=@SpanEnd
GROUP BY CarID;


If that doesn't work, I'd check the >= and <= and make sure they're not supposed to be > and <.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 08:25 AM