Solved

SQL group time and location

Posted on 2008-10-06
9
167 Views
Last Modified: 2010-04-21
Hello all,

I have the following data: person, date, punchtime, location
Example set:
Person, date, punchtime, location
384, 10/1/2008, 10/1/2008 8:00, 180
384, 10/1/2008, 10/1/2008 12:30, 180
384, 10/1/2008, 10/1/2008 13:30, 220

I need to have the output show me only combinations where the location has changed.
For example:
384, 10/1/2008, 12:30, 180, 13:30, 220

The initial query I wrote is returning the following:
384, 10/1/2008, 8:00, 180, 13:30, 220
384, 10/1/2008, 12:30, 180, 13:30, 220

Here is the original query I put together:
select t1.person, t1.date, t1.punchtime, t1.location, t2.punchtime, t2.location
from punches t1
left join punches t2 on t2.person = t1.person
        and t1.date = t2.date
        and t1.punchtime < t2.punchtime
        and t1.location <> t2.location
where t1.date = '10/1/2008'


I can't figure out how to exclude the first transaction.  Since it's the same location as the second transaction, it should be skipped.  Then, the second and third transactions should be returned since they are an actual location change.
0
Comment
Question by:cciesliga
  • 6
  • 3
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22652022
here is what you can do...

1.  add a meaningless sequential number to your output:

select id = ranking = dense_rank() over(partition by person order by date asc), person, date, punchtime, location
into #temp
from yourtable

2.  use that output to compare the locations

select * from temp o
join temp i on o.ranking = i.ranking + 1
where o.location <> i.location
0
 
LVL 1

Author Comment

by:cciesliga
ID: 22652534
I tried the query provided and it returns: incorrect syntax near '='.

I removed id=ranking= and ran it and each of the rank entries returned as the value 1.

I'm not familiar with the dense_rank command, so I'm not sure what needs to be changed to make the first part work.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22652576
post your exact code that you're using.
0
 
LVL 1

Author Comment

by:cciesliga
ID: 22652624
select id=ranking=dense_rank() over(partition by person order by date asc), person, date, punchtime, location
into #temp
from timesheet

Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:cciesliga
ID: 22652632
sorry,
select id=ranking=dense_rank() over(partition by person order by date asc), person, edate, punchtime, location
into #temp
from timesheet


date is actually edate.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22652743
try this:

select ranking=dense_rank() over(partition by person order by date asc), person, edate, punchtime, location
into #temp
from timesheet
0
 
LVL 1

Author Comment

by:cciesliga
ID: 22653125
ok, that seems to have done it.  testing the second part now.
0
 
LVL 1

Author Comment

by:cciesliga
ID: 22653498
looks good.

Thanks chapmandew!!
0
 
LVL 1

Author Closing Comment

by:cciesliga
ID: 31503499
Thanks for the prompt replies!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now