?
Solved

Consolidate Codes and Dates in proper order

Posted on 2008-06-22
7
Medium Priority
?
222 Views
Last Modified: 2011-10-19
I need to group a person with enterdates,entercodes,withdrawal dates and withdrawal codes in correct sequential (date) order. One person may have 10 records with sequential dates. See file. The 'Days Attended' field may not look right if you calculate the date diff...that's ok, I just need to put the number on the correct line for now. P.S. I don't create these files, just inherit them...
Thank you!

I started the query below, but don't have a clue how to put the E code & W codes on one line (see Results table example in xls file).
select a.lastname,a.firstname,a.middlename, effdate,entercode,leavecode,
DATEDIFF(day,effdate,
   (Select MIN(effdate) from Table1 b
    where b.lastname=a.lastname and b.firstname=a.firstname and b.effdate > a.effdate)) NoDays
from Table1 a
order by a.lastname,a.firstname,a.middlename

Open in new window

Enterdateleavedates.xls
0
Comment
Question by:lbarnett419
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
7 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 21844383
I see your problem. There are two solution.
1) Use "fake" agregate. If you GROUP BY a.lastname,a.firstname,a.middlename, effdate you can use MAX(entercode), MAX(leavecode), MAX(DATEDIFF.....) because only other value is NULL and agregate skips nulls.

2) Better one. Convert subquery to JOIN and extract leave code there. I'll try to write it for you, give me a sec.

Patrik
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21844459
There is my blind shot code.
Without testing I can only guess that it works. Test it and come back with result if you want.

BTW: Structure you have looks wery strange. Start thinking about database redesign if you want to avoid problems in future.

Patrik
select a.lastname,a.firstname,a.middlename, a.effdate,a.entercode,b.effdate), b.leavecode,
DATEDIFF(day,b.effdate,a.effdate) AS NoDays
from Table1 a
JOIN Table1 b
	ON  b.lastname=a.lastname and b.firstname=a.firstname and b.effdate =(SELECT MIN(effdate) FROM Table1 c WHERE c.effdate>a.effdate)
order by a.lastname,a.firstname,a.middlename

Open in new window

0
 

Author Comment

by:lbarnett419
ID: 21857950
I tried the query(THANK YOU!)  & changed the DATEDIFF(day,b.effdate,a.effdate) to (day,a.effdate,b.effdate) so the days would be positive days. Either way, I am only getting a subset of the records for each person. The John Smith record originally had:
LN      FN      EffDate      EnterCode      LeaveCode
Abbott      Joseph      11/13/2006      E      NULL
Abbott      Joseph      12/19/2006      NULL      W20
Abbott      Joseph      2/6/2007      E      NULL
Abbott      Joseph      2/16/2007      NULL      W10
Abbott      Joseph      2/19/2007      E      NULL
Abbott      Joseph      3/22/2007      NULL      W20

The query result was:
LN      FN      EffDate      EnterCode      EffDate      LeaveCode      NoofDays
Smith      John      2/16/2007      NULL      2/19/2007      NULL      3
Any ideas?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 12

Expert Comment

by:patrikt
ID: 21863404
I'll look on it.
But you send wrong example, mixed Joseph Abbot with John Smith.

Patrik
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21864408
I think mz query is ok, but restriction on start row is missing.
I suppose Enter row has LeaveCode NULL and Leave row has EnterCode NULL.
Try this one:
select a.lastname,a.firstname,a.middlename, a.effdate,a.entercode,b.effdate), b.leavecode,
DATEDIFF(day,b.effdate,a.effdate) AS NoDays
from Table1 a
JOIN Table1 b
	ON  b.lastname=a.lastname and b.firstname=a.firstname and a.LeaveCode IS NULL AND b.effdate =(SELECT MIN(effdate) FROM Table1 c WHERE c.effdate>a.effdate AND c.LeaveCode IS NOT NULL)
order by a.lastname,a.firstname,a.middlename

Open in new window

0
 
LVL 12

Accepted Solution

by:
patrikt earned 1500 total points
ID: 21864514
Or on SQL 2005 may better perform this one.
It is same only using new APPLY.
select a.lastname,a.firstname, a.effdate,a.entercode,b.effdate, b.leavecode,
DATEDIFF(day,b.effdate,a.effdate) AS NoDays
from Table1 a
OUTER APPLY (select TOP 1 c.lastname,c.firstname,c.effdate,c.leavecode FROM Table1 c 
	WHERE 
	c.lastname=a.lastname and c.firstname=a.firstname AND
	c.effdate>a.effdate and c.leavecode IS NOT NULL 
	ORDER BY c.effdate) b
WHERE a.entercode IS NOT NULL
order by a.effdate

Open in new window

0
 
LVL 12

Expert Comment

by:patrikt
ID: 22374711
Please write comment why it is graded B. What was wrong on solution?
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 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