April 5, 2014 at 12:24 pm
The following works in SQL Server 2012 and should work as far back as SQL Server 2005. Is this what you are attempting to accomplish?
/*
Eventdate. Status
20-01-2014 18:00 0
20-01-2014 18:30 0
20-01-2014 19:00 1
20-01-2014 19:30 0
20-01-2014 20:00 1
20-01-2014 20:30 0
20-01-2014 21:00 0
20-01-2014 21:30 0
20-01-2014 22:00 0
20-01-2014 22:30 1
20-01-2014 23:00 0
*/
create table dbo.EventsStatus(
EventDate datetime,
EventStatus int
);
insert into dbo.EventsStatus
values
('20140120 18:00',0),
('20140120 18:30',0),
('20140120 19:00',1),
('20140120 19:30',0),
('20140120 20:00',1),
('20140120 20:30',0),
('20140120 21:00',0),
('20140120 21:30',0),
('20140120 22:00',0),
('20140120 22:30',1),
('20140120 23:00',0);
with BaseData as (
select
EventDate,
EventStatus,
grp = dateadd(minute, row_number() over (order by EventStatus, EventDate) * -30, EventDate)
from
dbo.EventsStatus
), WorkingData as (
select
EventDate,
EventStatus,
Sequentials = row_number() over (partition by grp order by EventDate desc)
from
BaseData
)
select
EventDate,
Sequentials
from
WorkingData
where
EventStatus = 0
order by
EventDate;
go
drop table dbo.EventsStatus;
go
April 5, 2014 at 1:09 pm
You're a better man than I, Mr. Pettis. I took the time to put the originally posted data in a table along with an answer (very similar to yours but for that different problem) only to find that the problem definition changed and decided it was time for the OP to spend a little time on his own problem. Well done on your part.
--Jeff Moden
April 5, 2014 at 1:10 pm
J Livingston SQL (4/5/2014)
are these just for 2012?I "thought" that UNBOUNDED PRECEDING/FOLLOWING was introduced in 2012...so not available in 2008?
may well be wrong.
nevertheless it provide a solution...
I wonder what is really required....me thinks we are only seeing part of a bigger problem in providing end data to an app.
You are right, Framing was introduced in 2012.
Adjusting the previous for 2008 is a simple matter of self-joining instead of the Last_value function
😎
SELECT Eventdate,Status
INTO #AP_DATA
FROM (VALUES
('2008-01-20 18:00', 0)
,('2008-01-20 18:30', 0)
,('2008-01-20 19:00', 1)
,('2008-01-20 19:30', 0)
,('2008-01-20 20:00', 1)
,('2008-01-20 20:30', 0)
,('2008-01-20 21:00', 0)
,('2008-01-20 21:30', 0)
,('2008-01-20 22:00', 0)
,('2008-01-20 22:30', 1)
,('2008-01-20 23:00', 0)
) AS X(Eventdate,Status);
;WITH GROUP_PART AS
(
SELECT
CONVERT(DATETIME2(0),Eventdate,120) AS Eventdate
,(DATEDIFF(MINUTE,'1900-01-01 00:00',Eventdate) / 30) - ROW_NUMBER() OVER (ORDER BY AD.Eventdate) AS GR_NO
FROM #AP_DATA AD
WHERE AD.Status = 0
)
SELECT
AD.Eventdate
,(DATEDIFF(MINUTE,AD.Eventdate,MXD.LST_Eventdate) / 30 )AS AP_LAST
FROM GROUP_PART AD
INNER JOIN
(
SELECT
GP.GR_NO
,MAX(GP.Eventdate) LST_Eventdate
FROM GROUP_PART GP
GROUP BY GP.GR_NO
) AS MXD
ON AD.GR_NO = MXD.GR_NO;
DROP TABLE #AP_DATA;
April 5, 2014 at 1:25 pm
He he.
It works.
Thanks.:-D:-P;-)
Now...
If I want to reset the counter for all days?
When it change the day, reset the counter.
He he
April 5, 2014 at 1:30 pm
rui_leote (4/5/2014)
He he.It works.
Thanks.:-D:-P;-)
Now...
If I want to reset the counter for all days?
When it change the day, reset the counter.
He he
Which works? There are a couple of answers here to your problem. Not sure what you mean by resetting the counter for all days. At this point you need to follow the example provided by the answers given and proved DDL for the table(s), sample data for the table(s), and the expected output based on the sample data.
April 5, 2014 at 1:45 pm
This one worked.
I have included some new rows on it with day 21:
What I need is that when the day end the counter reset, the date 2014-01-20 23:00:00 should only return the 2014-01-20 23:00:00 and 2014-01-20 23:30:00 not the day 21.
SELECT Eventdate,Status
INTO #AP_DATA
FROM (VALUES
('2014-01-20 18:00', 0)
,('2014-01-20 18:30', 0)
,('2014-01-20 19:00', 1)
,('2014-01-20 19:30', 0)
,('2014-01-20 20:00', 1)
,('2014-01-20 20:30', 0)
,('2014-01-20 21:00', 0)
,('2014-01-20 21:30', 0)
,('2014-01-20 22:00', 0)
,('2014-01-20 22:30', 1)
,('2014-01-20 23:00', 0)
,('2014-01-20 23:30', 0)
,('2014-01-21 00:00', 0)
,('2014-01-21 00:30', 0)
,('2014-01-21 01:00', 0)
) AS X(Eventdate,Status);
;WITH GROUP_PART AS
(
SELECT
CONVERT(DATETIME2(0),Eventdate,120) AS Eventdate
,(DATEDIFF(MINUTE,'1900-01-01 00:00',Eventdate) / 30) - ROW_NUMBER() OVER (ORDER BY AD.Eventdate) AS GR_NO
FROM #AP_DATA AD
WHERE AD.Status = 0
)
SELECT
GP.Eventdate
,(DATEDIFF(MINUTE,GP.Eventdate,LAST_VALUE(GP.Eventdate) OVER
(
PARTITION BY GP.GR_NO
ORDER BY GP.Eventdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)) / 30 )AS AP_LAST
FROM GROUP_PART GP;
DROP TABLE #AP_DATA;
April 5, 2014 at 1:58 pm
You mean like this?
/*
Eventdate. Status
20-01-2014 18:00 0
20-01-2014 18:30 0
20-01-2014 19:00 1
20-01-2014 19:30 0
20-01-2014 20:00 1
20-01-2014 20:30 0
20-01-2014 21:00 0
20-01-2014 21:30 0
20-01-2014 22:00 0
20-01-2014 22:30 1
20-01-2014 23:00 0
*/
create table dbo.EventsStatus(
EventDate datetime,
EventStatus int
);
insert into dbo.EventsStatus
values
('20140120 18:00',0),
('20140120 18:30',0),
('20140120 19:00',1),
('20140120 19:30',0),
('20140120 20:00',1),
('20140120 20:30',0),
('20140120 21:00',0),
('20140120 21:30',0),
('20140120 22:00',0),
('20140120 22:30',1),
('20140120 23:00',0),
('20140120 23:30',0),
('20140121 00:00',0),
('20140121 00:30',0),
('20140121 01:00',0)
;
with BaseData as (
select
EventDate,
EventStatus,
grp = dateadd(minute, row_number() over (partition by cast(EventDate as DATE) order by EventStatus, EventDate) * -30, EventDate)
from
dbo.EventsStatus
), WorkingData as (
select
EventDate,
EventStatus,
Sequentials = row_number() over (partition by grp order by EventDate desc)
from
BaseData
)
select
EventDate,
Sequentials
from
WorkingData
where
EventStatus = 0
order by
EventDate;
go
drop table dbo.EventsStatus;
go
Notice that this isn't the code you said worked but the code I posted.
Also, as written, this code will not work in SQL Server 2005 now but that can be fixed fairly easily as well if needed.
April 5, 2014 at 2:08 pm
The following has both a SQL Server 2008 and newer version and a SQL Server 2005 (and newer) version:
create table dbo.EventsStatus(
EventDate datetime,
EventStatus int
);
insert into dbo.EventsStatus
values
('20140120 18:00',0),
('20140120 18:30',0),
('20140120 19:00',1),
('20140120 19:30',0),
('20140120 20:00',1),
('20140120 20:30',0),
('20140120 21:00',0),
('20140120 21:30',0),
('20140120 22:00',0),
('20140120 22:30',1),
('20140120 23:00',0),
('20140120 23:30',0),
('20140121 00:00',0),
('20140121 00:30',0),
('20140121 01:00',0)
;
with BaseData as (
select
EventDate,
EventStatus,
grp = dateadd(minute, row_number() over (partition by cast(EventDate as DATE) order by EventStatus, EventDate) * -30, EventDate)
from
dbo.EventsStatus
), WorkingData as (
select
EventDate,
EventStatus,
Sequentials = row_number() over (partition by grp order by EventDate desc)
from
BaseData
)
select
EventDate,
Sequentials
from
WorkingData
where
EventStatus = 0
order by
EventDate;
go
with BaseData as (
select
EventDate,
EventStatus,
grp = dateadd(minute, row_number() over (partition by dateadd(day,datediff(day,0,EventDate),0) order by EventStatus, EventDate) * -30, EventDate)
from
dbo.EventsStatus
), WorkingData as (
select
EventDate,
EventStatus,
Sequentials = row_number() over (partition by grp order by EventDate desc)
from
BaseData
)
select
EventDate,
Sequentials
from
WorkingData
where
EventStatus = 0
order by
EventDate;
go
drop table dbo.EventsStatus;
go
April 5, 2014 at 2:14 pm
What's next, business holidays and weekends? 😀
Just for the sake of completion:
SELECT Eventdate,Status
INTO #AP_DATA
FROM (VALUES
('2014-01-20 18:00', 0)
,('2014-01-20 18:30', 0)
,('2014-01-20 19:00', 1)
,('2014-01-20 19:30', 0)
,('2014-01-20 20:00', 1)
,('2014-01-20 20:30', 0)
,('2014-01-20 21:00', 0)
,('2014-01-20 21:30', 0)
,('2014-01-20 22:00', 0)
,('2014-01-20 22:30', 1)
,('2014-01-20 23:00', 0)
,('2014-01-20 23:30', 0)
,('2014-01-21 00:00', 0)
,('2014-01-21 00:30', 0)
,('2014-01-21 01:00', 0)
) AS X(Eventdate,Status);
;WITH GROUP_PART AS
(
SELECT
CONVERT(DATETIME2(0),Eventdate,120) AS Eventdate
,(DATEDIFF(MINUTE,'1900-01-01 00:00',Eventdate) / 30) -
ROW_NUMBER() OVER (PARTITION BY CAST( AD.Eventdate AS DATE)
ORDER BY AD.Eventdate) AS GR_NO
FROM #AP_DATA AD
WHERE AD.Status = 0
)
SELECT
GP.Eventdate
,(DATEDIFF(MINUTE,GP.Eventdate,LAST_VALUE(GP.Eventdate) OVER
(
PARTITION BY GP.GR_NO
ORDER BY GP.Eventdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)) / 30 ) + 1 AS AP_LAST
FROM GROUP_PART GP;
DROP TABLE #AP_DATA;
😎
April 5, 2014 at 2:34 pm
Hum..
now let's say that....He he.
That is what I need.
Guys thank you so much.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply