December 19, 2014 at 9:56 pm
I am trying to take the first date of the Out transaction and put it on the record of the In transaction for each EQPT. I was planning on seperating the transactions into 2 seperate tables one for the In and one for the Out. The sample of the output is below the data. I have tried a couple of things but seem to lose some of the dates when the in and out are both for the same date.
EQPT Seq ActivityDate Type
Vehicle1 221 1/11/13 IN
Vehicle1 223 1/15/13 OUT
Vehicle1 226 1/15/13 IN
Vehicle1 227 1/15/13 OUT
Vehicle2 209 4/24/13 IN
Vehicle2 210 4/24/13 OUT
Vehicle2 211 4/24/13 IN
Vehicle2 212 4/30/13 OUT
Vehicle2 213 4/30/13 IN
Vehicle3 2 1/11/13 OUT
Vehicle3 3 1/14/13 IN
Vehicle4 152 1/3/13 IN
Vehicle4 153 1/4/13 OUT
Vehicle4 154 1/7/13 IN
Vehicle4 158 1/17/13 OUT
Vehicle4 161 1/21/13 IN
Vehicle4 162 1/21/13 OUT
Vehicle4 163 1/25/13 IN
Vehicle4 164 1/30/13 OUT
Vehicle5 175 7/28/13 IN
Vehicle5 177 7/31/13 OUT
Vehicle5 182 7/31/13 IN
Vehicle5 184 8/20/13 OUT
Vehicle5 187 8/22/13 IN
Vehicle5 188 8/23/13 OUT
Vehicle1 221 1/11/13 IN OUT 1/15/13
December 20, 2014 at 8:37 am
Hi
So OFF course there are better ways of doing think but a quick fix/idea you can use the following.
create table #Info(
EQPT varchar(20),
Seq int,
ActivityDate date,
Type varchar(5)
)
insert into #Info
select
'Vehicle1' ,221, cast('2013-1-11'as date) ,'IN'
union select
'Vehicle1' ,223, cast('2013-1-15'as date) ,'OUT'union select
'Vehicle1' ,226, cast('2013-1-15'as date) ,'IN'union select
'Vehicle1' ,227, cast('2013-1-15'as date) ,'OUT'union select
'Vehicle2' ,209, cast('2013-4-24' as date) ,'IN'union select
'Vehicle2' ,210, cast('2013-4-24'as date) ,'OUT'union select
'Vehicle2' ,211, cast('2013-4-24' as date) ,'IN'union select
'Vehicle2' ,212, cast('2013-4-30' as date),'OUT'union select
'Vehicle2' ,213, cast('2013-4-30' as date), 'IN'union select
'Vehicle3' ,2 , cast('2013-1-11' as date), 'OUT'union select
'Vehicle3' ,3 , cast('2013-1-14' as date), 'IN'union select
'Vehicle4' ,152, cast('2013-1-3'as date), 'IN'union select
'Vehicle4' ,153, cast('2013-1-4' as date), 'OUT'union select
'Vehicle4' ,154, cast('2013-1-7'as date) , 'IN'union select
'Vehicle4' ,158, cast('2013-1-17' as date), 'OUT'union select
'Vehicle4' ,161, cast('2013-1-21' as date), 'IN'union select
'Vehicle4' ,162, cast('2013-1-21' as date), 'OUT'union select
'Vehicle4' ,163, cast('2013-1-25' as date), 'IN'union select
'Vehicle4' ,164, cast('2013-1-30'as date), 'OUT'union select
'Vehicle5' ,175, cast('2013-7-28' as date), 'IN'union select
'Vehicle5' ,177, cast('2013-7-31' as date), 'OUT'union select
'Vehicle5' ,182, cast('2013-7-31' as date), 'IN'union select
'Vehicle5' ,184, cast('2013-8-20' as date), 'OUT'union select
'Vehicle5' ,187, cast('2013-8-20' as date), 'IN'union select
'Vehicle5' ,188, cast('2013-8-20'as date) , 'OUT'
with CTE as(
Select EQPT,
Seq,
ActivityDate,
Type,
DENSE_RANK() over ( order by Type) Type_rnk,
DENSE_RANK() over (partition by EQPT,Type order by seq) rnk
from #Info)
select *
from CTE a
left join CTE b on a.EQPT=b.EQPT and a.rnk = b.rnk and a.Type_rnk+1 = b.Type_rnk
where a.Type = 'IN'
This is not the best solution but it will point you in a direction 🙂
Kind Regards
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
December 20, 2014 at 8:53 am
I am getting the below error
Msg 102, Level 15, State 1, Line 38
Incorrect syntax near 'CTE'.
December 20, 2014 at 8:59 am
o sorry for that, just add a ; before the with CTE clause or copy below
create table #Info(
EQPT varchar(20),
Seq int,
ActivityDate date,
Type varchar(5)
)
insert into #Info
select
'Vehicle1' ,221, cast('2013-1-11'as date) ,'IN'
union select
'Vehicle1' ,223, cast('2013-1-15'as date) ,'OUT'union select
'Vehicle1' ,226, cast('2013-1-15'as date) ,'IN'union select
'Vehicle1' ,227, cast('2013-1-15'as date) ,'OUT'union select
'Vehicle2' ,209, cast('2013-4-24' as date) ,'IN'union select
'Vehicle2' ,210, cast('2013-4-24'as date) ,'OUT'union select
'Vehicle2' ,211, cast('2013-4-24' as date) ,'IN'union select
'Vehicle2' ,212, cast('2013-4-30' as date),'OUT'union select
'Vehicle2' ,213, cast('2013-4-30' as date), 'IN'union select
'Vehicle3' ,2 , cast('2013-1-11' as date), 'OUT'union select
'Vehicle3' ,3 , cast('2013-1-14' as date), 'IN'union select
'Vehicle4' ,152, cast('2013-1-3'as date), 'IN'union select
'Vehicle4' ,153, cast('2013-1-4' as date), 'OUT'union select
'Vehicle4' ,154, cast('2013-1-7'as date) , 'IN'union select
'Vehicle4' ,158, cast('2013-1-17' as date), 'OUT'union select
'Vehicle4' ,161, cast('2013-1-21' as date), 'IN'union select
'Vehicle4' ,162, cast('2013-1-21' as date), 'OUT'union select
'Vehicle4' ,163, cast('2013-1-25' as date), 'IN'union select
'Vehicle4' ,164, cast('2013-1-30'as date), 'OUT'union select
'Vehicle5' ,175, cast('2013-7-28' as date), 'IN'union select
'Vehicle5' ,177, cast('2013-7-31' as date), 'OUT'union select
'Vehicle5' ,182, cast('2013-7-31' as date), 'IN'union select
'Vehicle5' ,184, cast('2013-8-20' as date), 'OUT'union select
'Vehicle5' ,187, cast('2013-8-20' as date), 'IN'union select
'Vehicle5' ,188, cast('2013-8-20'as date) , 'OUT'
;
with CTE as(
Select EQPT,
Seq,
ActivityDate,
Type,
DENSE_RANK() over ( order by Type) Type_rnk,
DENSE_RANK() over (partition by EQPT,Type order by seq) rnk
from #Info)
select *
from CTE a
left join CTE b on a.EQPT=b.EQPT and a.rnk = b.rnk and a.Type_rnk+1 = b.Type_rnk
where a.Type = 'IN'
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
December 20, 2014 at 9:23 am
Thats awesome! I really appreciate your help thank you.
December 20, 2014 at 9:27 am
No prob at all. Hope this helped
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
January 3, 2015 at 8:32 am
This has worked great but I would like to store the results in a table. Can I insert the return into a permanent table?
January 3, 2015 at 8:49 am
Hi
Of course you can. Change the last select * from CTE to select * Into <permanent table> from CTE
Kind regards
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
January 4, 2015 at 7:33 am
That is awesome thanks again for the help I really appreciate it.
January 4, 2015 at 11:34 am
Hi
No problem at all. That is why are here 🙂
Kind Regards
Daniel
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
January 5, 2015 at 8:32 pm
You may want to take a look at this article as it may also help to identify your options:
http://qa.sqlservercentral.com/articles/T-SQL/106783/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply