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.
EQPTSeqActivityDateType
Vehicle12211/11/13IN
Vehicle12231/15/13OUT
Vehicle12261/15/13IN
Vehicle12271/15/13OUT
Vehicle22094/24/13IN
Vehicle22104/24/13OUT
Vehicle22114/24/13IN
Vehicle22124/30/13OUT
Vehicle22134/30/13IN
Vehicle321/11/13OUT
Vehicle331/14/13IN
Vehicle41521/3/13IN
Vehicle41531/4/13OUT
Vehicle41541/7/13IN
Vehicle41581/17/13OUT
Vehicle41611/21/13IN
Vehicle41621/21/13OUT
Vehicle41631/25/13IN
Vehicle41641/30/13OUT
Vehicle51757/28/13IN
Vehicle51777/31/13OUT
Vehicle51827/31/13IN
Vehicle51848/20/13OUT
Vehicle51878/22/13IN
Vehicle51888/23/13OUT
Vehicle12211/11/13IN 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://www.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