November 23, 2015 at 4:44 pm
Hello experts,
-----------------------------------------------------------
I have below data
ID start enddate p1 p2 Update date
31441872015-07-01 00:00:00.0002015-12-31 00:00:00.00018005test1 2015-09-18 12:28:05.197
30965052014-12-06 00:00:00.0002015-05-20 00:00:00.00018005test1 2015-08-31 12:24:59.353
30071232014-12-06 00:00:00.0002015-06-30 00:00:00.00018005test1 2015-07-14 12:56:57.683
When the user selects a date range from '07/01/2015' to '07/20/2015' for the update date. I need result like below (both matched record and data from previous record)
30965052014-12-06 00:00:00.0002015-05-20 00:00:00.00018005test1 2015-08-31 12:24:59.353
30071232014-12-06 00:00:00.0002015-06-30 00:00:00.00018005test1 2015-07-14 12:56:57.683
----------------------------------------------------
create table #test (id integer,start datetime,enddate datetime, p1 integer,p2 varchar(20), updatedate datetime)
insert into #test
values (3144187,'2015-07-01 00:00:00.000','2015-12-31 00:00:00.000',18005,'test1','2015-09-18 12:28:05.197')
insert into #test
values (3096505,'2014-12-06 00:00:00.000','2015-05-20 00:00:00.000',18005,'test1','2015-08-31 12:24:59.353')
insert into #test
values (3007123,'2014-12-06 00:00:00.000','2015-06-30 00:00:00.000',18005,'test1','2015-07-14 12:56:57.683')
November 23, 2015 at 4:49 pm
If you're using 2012, use LAG to get the previous record, then you can compare/do math on the two in the same row.
November 23, 2015 at 4:51 pm
I use 2014. I thought about that but it gives it on the same row. But, I need the data it in separate rows.
November 23, 2015 at 5:05 pm
Could you please post your expected output?
November 23, 2015 at 5:17 pm
You could use ROW_NUMBER() and maybe select the columns you need and filter by ROW_NUMBER() to put them into separate records.
Please show your desired output. (My psychic skills are failing)
November 23, 2015 at 5:23 pm
I had posted in my first post, but here you go
expected output result (even if the date range on update date is 07/01-07/30)
ID start enddate p1 p2 Update date
30965052014-12-06 00:00:00.0002015-05-20 00:00:00.00018005test1 2015-08-31 12:24:59.353
30071232014-12-06 00:00:00.0002015-06-30 00:00:00.00018005test1 2015-07-14 12:56:57.683
November 23, 2015 at 7:05 pm
November 23, 2015 at 7:38 pm
I had posted everything in the original post, but here it is --
create table #test (id integer,start datetime,enddate datetime, p1 integer,p2 varchar(20), updatedate datetime)
insert into #test
values (3144187,'2015-07-01 00:00:00.000','2015-12-31 00:00:00.000',18005,'test1','2015-09-18 12:28:05.197')
insert into #test
values (3096505,'2014-12-06 00:00:00.000','2015-05-20 00:00:00.000',18005,'test1','2015-08-31 12:24:59.353')
insert into #test
values (3007123,'2014-12-06 00:00:00.000','2015-06-30 00:00:00.000',18005,'test1','2015-07-14 12:56:57.683')
November 23, 2015 at 7:53 pm
What's previous?
The order of rows has no meaning in SQL Server. The way you insert them doesn't matter.
Right now your second row has a larger id and a later last updatedate, than the row you search for. I'm assuming you are searching on updatedate, but you haven't made this clear. When the user searches, what fields do you match on? How do you think rows need to be ordered?
November 23, 2015 at 9:12 pm
I am ordering by updatedate desc and then I am applying the filter.
November 23, 2015 at 10:56 pm
For the same [start]?
November 24, 2015 at 1:07 am
misstryguy (11/23/2015)
I am ordering by updatedate desc and then I am applying the filter.
If i've got right
create table #test (id integer
,start datetime
,enddate datetime
,p1 integer
,p2 varchar(20)
,updatedate datetime)
insert into #test
values (3144187, '2015-07-01 00:00:00.000', '2015-12-31 00:00:00.000', 18005, 'test1','2015-09-18 12:28:05.197')
insert into #test
values (3096505, '2014-12-06 00:00:00.000', '2015-05-20 00:00:00.000', 18005, 'test1','2015-08-31 12:24:59.353')
insert into #test
values (3007123, '2014-12-06 00:00:00.000', '2015-06-30 00:00:00.000', 18005, 'test1','2015-07-14 12:56:57.683')
;
with t1 as (
select *, rn = row_number() over (order by updatedate desc)
from #test
), t2 as (
select *
from t1
where updatedate between '2015-07-01' and '2015-07-30'
)
select *
from t2
union
select t1.*
from t1
join t2 on t2.rn=t1.rn+1;
November 24, 2015 at 1:18 am
UNION ALL should perfom better
...
select *
from t2
union all
select t1.*
from t1
join t2 on t1.updatedate > '2015-07-30' and t2.rn=t1.rn+1;
November 24, 2015 at 8:57 am
I was using window functions, and this appeared to work, but it's a small sample size, so perhaps there's a flaw here:
SELECT t.*
FROM ( SELECT top 10
id
, start
, 'prev id' = LEAD(id) OVER (PARTITION BY p1 ORDER BY updatedate)
, 'prev start' = LEAD(enddate) OVER (PARTITION BY p1 ORDER BY updatedate)
, updatedate
FROM #test AS t
ORDER BY updatedate desc) a
INNER JOIN #test t ON t.id IN (a.id, a.[prev id])
WHERE a.updatedate > '20150701'
AND a.updatedate < '20150720'
Some extra columns in there, just to show what's happening.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply