July 21, 2020 at 5:04 pm
So I am trying to get the datediff for parameters selected, even though the 2 dates could span 60 days, for an example I have 2 dates available(2018-01-05 11:08:00) and unavailable (2017-10-30 05:05:00) I only want the datediff where @p_start_date = 2017-12-01 and @p_end_date = 2017-12-31 those are the selected parameters.
July 21, 2020 at 6:07 pm
What "DateDiff" are you trying to get? The number of days, weeks, months, years, hours, seconds, etc?
I suppose the easiest way to help you would be if you could post some DDL of your source data, such as:
DECLARE @tmp TABLE (dates DATETIME)
INSERT INTO @tmp
VALUES (GETDATE()), (DATEADD(days,-1,GETDATE())
And your expected output. Basically, you haven't really told us what dates you are comparing to get a datediff of. Is DateDiff a column or do you mean the actual SQL function "DATEDIFF", which requires 2 dates as input and gives you the difference between them?
If you just want the datediff on 2 input parameters, then something like this:
DECLARE @p_start_date DATETIME = CAST('2017-12-01 00:00:00' AS DATETIME);
DECLARE @p_end_date DATETIME = CAST('2017-12-31 00:00:00' AS DATETIME);
SELECT
DATEDIFF(DAY
, @p_start_date
, @p_end_date
);You say you have 2 dates, Available and Unavailable, but I don't understand what those are telling me or how they are stored in your database. Are "Available" and "Unavailable" columns in the table? Are you wanting to compare the @p_start_date and @p_end_date to one or both of these columns?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 21, 2020 at 6:08 pm
I am trying to get the hours difference.
July 21, 2020 at 6:11 pm
DECLARE @p_start_date DATETIME = CAST('2017-12-01 00:00:00' AS DATETIME);
DECLARE @p_end_date DATETIME = CAST('2017-12-31 00:00:00' AS DATETIME);
SELECT
DATEDIFF( HOUR
, @p_start_date
, @p_end_date
); July 21, 2020 at 11:17 pm
So I am trying to get the datediff for parameters selected, even though the 2 dates could span 60 days, for an example I have 2 dates available(2018-01-05 11:08:00) and unavailable (2017-10-30 05:05:00) I only want the datediff where @p_start_date = 2017-12-01 and @p_end_date = 2017-12-31 those are the selected parameters.
So, since you want hours, what do you want to do with the end date? Include all the hours for that end date or none of them?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2020 at 5:19 pm
This is pure undeluted guesswork, since you haven't provided the requested input and output data samples yet.
I'm imagening a scenario, where products may be unavailable for periods of time (i.e. not on stock).
DROP TABLE IF EXISTS #products;
DROP TABLE IF EXISTS #AvailabilityStatus;
create table #products (
ItemId varchar(20) PRIMARY KEY,
ItemDescription varchar(80)
);
create table #AvailabilityStatus (
ItemId varchar(20) not null,
StatusCode char(1) not null,
StatusChangeDate datetime not null,
constraint PK_AvailabilityStatus primary key (
ItemId asc,
StatusCode asc,
StatusChangeDate asc
)
);
insert into #products (ItemId, ItemDescription) -- some products, whatever...
values('AK99','Wine Combo Pack #1, 2015'),
('BK230','Wine Combo Pack #2, 2015'),
('WF882B','Wine Combo South Africa 2016'),
('BI1993','Wine Combo Pack Chile 2016'),
('CI022','The Wine & Dine Book'),
('CI023','Treats for your Guests, Book'),
('UJ2757','Belgian Chocolade, 200 pieces')
insert into #AvailabilityStatus (ItemId, StatusCode, StatusChangeDate) -- some recorded changes to product availability
values('AK99','U','2017-10-27 17:03:17'),
('AK99','A','2017-11-04 14:16:42'),
('AK99','U','2018-04-27 08:03:22'),
('AK99','A','2018-06-04 14:03:47'),
('AK99','U','2020-06-26 08:52:26'),
('BK230','U','2017-10-30 05:05:00'),
('BK230','A','2018-01-05 11:08:00'),
('CI022','U','2017-08-22 17:05:59'),
('CI022','A','2017-09-12 09:32:17'),
('UJ2757','U','2019-12-09 23:18:42'),
('UJ2757','A','2019-12-30 07:33:49');
-- Define period of interest...
declare
@p_start_date date = '2017-12-01',
@p_end_date date = '2017-12-31';
-- Return data with calculated hours of unavailability
with UnavailablePeriods AS (
select
as_u.ItemId,
as_u.StatusChangeDate AS UnavailableDateTime,
as_a.StatusChangeDate AS AvailableDateTime
from #AvailabilityStatus as_u
outer apply (
select top(1) StatusChangeDate
from #AvailabilityStatus
where ItemId=as_u.ItemId
and StatusCode='A'
and StatusChangeDate > as_u.StatusChangeDate
order by StatusChangeDate asc
) as_a
where as_u.StatusCode='U'
)
select
p.ItemId,
p.ItemDescription,
up.UnavailableDateTime,
up.AvailableDateTime,
datediff(hour,up.UnavailableDateTime,up.AvailableDateTime) as HoursOfUnavailability
from #products p
left join UnavailablePeriods up on up.ItemId=p.ItemId
--where up.UnavailableDateTime between @p_start_date and @p_end_date
--and up.AvailableDateTime is null or up.AvailableDateTime <= @p_end_date
order by
ItemId,
UnavailableDateTime
Obviously I don't know if this is anything like what you have, but it might help you describe what you have and what you want.
Also, I haven't done much testing. I think the output is correct given the input, but if not bear with me. 🙂
PS. The commented out "where section" is what I think you want to use your parameters for, but I'm not sure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply