May 20, 2014 at 7:50 am
Hello SQL Server family,
I am having trouble recalling how to create script to display results side by side instead of in a union. Here is the problem, I am a table to records traffic IN and OUT of workarea. I create two scripts one for max IN and one for max OUT. I want to see the max IN and max OUT on the same row, so I can list only record that have a max IN time greater than max OUT time. This will show me who is actullay in the workarea at the time of report.
I am trying to accomplish this without any temp tables, stored Procs, because this is on a server outside of my organization. So I am looking for a simple script that will do the trick. Here is what I current have using a UNION.
select x.guid, x.max_intime
from
(
select guid, max (Gatetime) as max_intime
from table_log
where gatestatus = 1
group by guid
)as x
inner join table_log xx
on xx.guid = x.guid and xx.intime = x.max_intime
group by x.guid, x.max_intime
union
select x.guid, x.max_OUTtime
from
(
select guid, max (gatetime) as max_Outtime
from table_log
where gatestatus = 0
group by guid
)as x
inner join table_log xx
on xx.guid = x.guid and xx.gatetime = x.max_Outtime
group by x.guid, x.max_Outtime
Thanks for any help.
May 20, 2014 at 8:01 am
Hi there,
I would look to be using two CTE to replace your selects and then query those to produce your report so something like
WITH cte1 <columns>
<SELECT Statement>,
cte2 <columns>
<SELECT Statement>
select <columns>
from CTE1 t1
join CTE2 t2 on t2.col1 = t1.col1
May 20, 2014 at 1:13 pm
Maybe this; not 100% sure of the final output you want to see:
select x.guid, x.max_intime, x.max_outtime
from
(
select guid,
max (case when gatestatus = 1 then Gatetime else 0 end) as max_intime,
max (case when gatestatus = 0 then Gatetime else 0 end) as max_outtime
from table_log
where gatestatus in (0, 1)
group by guid
)as x
/*
inner join table_log xx
on xx.guid = x.guid and (xx.Gatetime = x.max_intime or xx.Gatetime = max_outtime)
*/
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 21, 2014 at 2:51 am
Thanks for the reply. That was helpful, but it gave me results listing the value for max IN and max OUT. I am needing to see the acutally max TIME IN and max TIME OUT, on the same line with the guid. So would I drop case or keep case?
An example of what I am trying to get.
guid, 2014-05-10 01:04, 2014-05-10 01:14
May 21, 2014 at 8:02 am
Hmm, I expected my code to give you that.
Can the same guid appear more than once??
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 21, 2014 at 8:21 pm
Hello Scott,
No the guid will not appear more than once, but of course when I union the two in my script above I get the guid twice, a record for in and a record for out.
Thanks again for all your help.
May 21, 2014 at 8:36 pm
Scott,
On second thought, you made me realize something about the guid appearing more than once. The GUID may appear more than once. My goal is to create a daily snapshot showing the duration of a visit when a person comes in until the time they leave. I forgot to account for the fact visitors can return during the same day, so in that case additional records would be created for each visit during that day. Here is what my end game will look like when done.
GUID, Time_IN, Time_Out, Total_Visit_Time, ActionDay
Thanks again.
May 22, 2014 at 10:11 am
Sample data in a consumable format would be most helpful.
An example of what I am trying to get.
guid, 2014-05-10 01:04, 2014-05-10 01:14
I'm pretty sure my query gives you that. The time difference is easy to add IF there are always matching in and out times.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 22, 2014 at 10:51 pm
Scott ,
I hope I understood you correctly about the format. I attached an example excel of what I get now when I use my union and what I am trying to get without using a union. I used one person coming in twice so you could see what I am looking for and the other once. I hope that I am explaining this correctly. I also really do appreciate the help.
May 23, 2014 at 8:46 am
See if this works:
CREATE TABLE #temp
(
guidchar(5),
statuschar(3),
INOUT_timedatetime
)
insert into #temp
SELECT '1djey', 'OUT', '05/22/2014 17:13:00'
UNION
SELECT '1djey', 'IN', '05/22/2014 05:00:00'
UNION
SELECT '1djey', 'OUT', '05/22/2014 4:13:00'
UNION
SELECT '1djey', 'IN', '05/22/2014 2:00:00'
UNION
SELECT '4heyk', 'OUT', '05/22/2014 18:10:00'
UNION
SELECT '4heyk', 'IN', '05/22/2014 5:30:00'
SELECT l.guid, MAX(r.INOUT_time) as IN_time, l.INOUT_time as OUT_time
FROM #temp l
INNER JOIN #temp r on
l.guid = r.guid
and r.status = 'IN'
WHERE l.status = 'OUT'
and r.INOUT_time < l.INOUT_time
GROUP BY l.guid, l.INOUT_time
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 8, 2014 at 6:40 pm
An example of what I am trying to get.
guid, 2014-05-10 01:04, 2014-05-10 01:14
Would the data only be for one specific date? To span it across multiple days it is a simple matter of grouping by the date itself, Example
SELECT cast(GateTime as DATE) , Guid....
Group By cast(GateTime as DATE) , Guid
Does the data follow that a person has to exit the same day they came in?
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply