|
Hi there,
I need to write a query that gets overlapping date ranges from a table and also generate a random unique number for each group of overlaps. I was able to write the code below in SQL 2005 to get the overlaps for each employee in my table but I'm stuck on pairing up the overlaps.
My code currently generates the following:
Payment_Key Emp_No Start_date End_date
55 JDOE 2008-01-13 2008-01-26
56 JDOE 2008-01-16 2008-01-31
100 MPOE 2007-01-01 2007-01-15
104 MPOE 2007-01-10 2007-01-13
145 MPOE 2007-12-16 2007-12-31
150 MPOE 2007-12-16 2007-12-31
151 MPOE 2007-12-31 2007-12-31
But I need to generate the following:
Payment_Key Emp_No Start_date End_date Dup_Pair
55 JDOE 2008-01-13 2008-01-26 1
56 JDOE 2008-01-16 2008-01-31 1
100 MPOE 2007-01-01 2007-01-15 2
104 MPOE 2007-01-10 2007-01-13 2
145 MPOE 2007-12-16 2007-12-31 3
150 MPOE 2007-12-16 2007-12-31 3
151 MPOE 2007-12-31 2007-12-31 3
Here's my current code:
select distinct
t1.payment_key,
t1.emp_no,
t1.start_date,
t1.end_date
from tbl_empl_pymt_rate t1 join tbl_empl_pymt_rate t2
on t1.emp_no = t2.emp_no and
(t2.start_date between t1.start_date and t1.end_date or
t2.end_date between t1.start_date and t1.end_date or
((t1.start_date between t2.start_date and t2.end_date) and (t1.end_date between t2.start_date and t2.end_date)))
and t1.payment_key <> t2.payment_key
order by t1.emp_no, t1.start_date
Any help would be greatly appreciated.
modified on Thursday, September 18, 2008 12:07 PM
|
|
|
|
|
Hi,
If the dup_pair is a unique value for each overlapping date range, I believe that the easiest way would be to create a stored procedure and use cursor to read the data returned by your select.
You can use temporary table in sp to ease the logic if necessary. For example store duplicates first then do a second pass on the table and mark corresponding duplicates with a unique counter value.
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Which software does i need to create a student database. plz help me.
|
|
|
|
|
Whichever database software you're happy with. It shouldn't make that much difference overall.
|
|
|
|
|
You can use whatever database you want. Do check and see what your requirements specify, if there are any requirements.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
sql server 2005
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Seriously.
Blog link to be reinstated at a later date.
|
|
|
|
|
Hi there,
I want to write a project of airline reservation please can you let me know which language to use. Thank you.
|
|
|
|
|
English is good, people understand English.
|
|
|
|
|
I agree with Jamie - english is the international language used in aviation - Yep I'd definitely write it in english.
On another note, read the guidelines for posting, help is just not going to cut it, use a little imagination, think about the information you want to get back and ask an intelligent question. Help! will get you every sarcastic bastard in the forum having a go at you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Help! will get you every sarcastic bastard in the forum having a go at you.
Moi? Sarcastic? never.....
|
|
|
|
|
Mycroft Holmes wrote: will get you every sarcastic bastard in the forum
not every one. I sometimes just can't be bothered 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I had a friend that took a systems engineering class. They were required to design a hotel reservation system using just math.
Think the process through, then pick the language you are comfortable using.
Tim
|
|
|
|
|
If you are asking about the language, english is ok, but maybe you migh want to add spanish as well and load the corresponding language depending on the user's blowser configuration
If you are asking about programming language... Develop it using asp.net with vb or c# as the programming languaje (vb is easier)
Alexei Rodriguez
|
|
|
|
|
Hi,
Sorry i don't speak english
I started in informatic and I must make my first project for the management reviews of students.
I have the following tables:
Student:
ID_Etudiant Name
Matter
ID_matière_ Coefficient ID_Module
Notation
ID_Etudiant ID_Matière Note Année_universitaire
Module:
ID_Module Libelle_module
Moyenne_module:
ID_Etudiant ID_Module Average Année_Universitaire
Academic Year:
Année_universitaire
I registered the notes on Rating but I planted in the calculation of averages I do not know how to
I am trying to get a detailed as follows:
ID_Etudiant Moyenne_module1 Myenne_module2 Moyenne_module3
For example, the module1
Id_matiere Id_module
11 Module1
21 Module1
31 Module1
So the average _module1 be:
(Note (11) * coefficient (11) + note (21) * coefficient (21) + note (31) * coefficient (31)) / (coefficient (11) + coefficient (21) + coefficient (31))
thank you read me and show me the track to follow.
|
|
|
|
|
You are designing a students database, make it a school database software something you can install in any school, easier to think that way.
Important to think about the structure, to avoid start from the begining, I am not sure about yours it seems ok, i don´t understand very well french
forget the code, talk a little more about the database, maybe i can help.
Good luck
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
thanks a lot nelsonpaixao merci bcp;
My database:
VALIDATION_MODULE (CODE_VALIDATION_MODULE ,........)
VALIDATION_SEMESTRE (CODE_VALIDATION_SEMESTRE ,........)
AVERAGE (NOTE_MODULE, CODE_MODULE #, # NUM_student, # CODE_VALIDATION_MODUE ,........)
final_average (NOTE_SEMESTRE, NUM_INSCRIPTION #, # CODE_SEMESTRE, # CODE_VALIDATION_semestre .......)
REVIEW (review, NUM_student #, # CODE_MATIERE)
STUDENT (NUM_student. .....)
FIELD [CODE_MATIERE, COEFFICIENT, # CODE_MODULE ........)
MODULE (CODE_MODULE, .. # CODE_SEMESTRE.)
semestre (CODE_SEMESTRE ,...)
i don't understand make it a school database software something you can install .
merci encore
|
|
|
|
|
i don´t know i can assist you here, try invest in the database structure to avoid you to start from the beginning later, you will get easier sp also.
i builted a videoclub (movie rental) project, i knew the structure i needed(major table was 'rents' that got who rented and what rented, 75% of store procedures feeded from that table), in your case i imagine but no sure of structure, they did gave you a check list, right?
For exemple i am accountant i can easely built a accountant software and give suggestions because i know the algorhitms, if it is of other kind i need assistance i needed a ckeck list of what they want and i will keep asking question all the time!!!
i mean school database software, didn´t you seen a videoclub, library, or other software? i mean that because you didn´t show no info besides students and grades, nothing of teachers or rooms etc, but it may be all you need you know.
Good luck, sorry couldn´t help 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi..
I have paradox database files I opened it using Access but the arabic text looks unreadable I changed the font to arabic but this was no use.Do you have any suggestion?
I am too late but i will never give up
|
|
|
|
|
It's probably the cultureinfo of the database rather than the font, fixing it - no idea sorry.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I set the collation for the database to arabic before migrating the database using DTS and the data still unreadable.I tried and copy the data to an email and send it to another employee in the company.In outlook He set the encoding to Arabic(Windows) and it becomes readeable.So I think I have two choices to solve the problem:
1-Set the Encoding in Access to Arabic(Windows):I couldn't find any Encoding settings in Access.
2-Set the Encoding in code for that field in the dataset.
Please if any one knows how to reach one of these solutions tell me about it.
I am too late but i will never give up
|
|
|
|
|
|
Here is google translator.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
"An error occurred connecting to SQL server 2005. Unable to accept remote connections."
What you need to do is run the SQL Server Configuration Manager and make sure that protocol TCP/IP is enabled. Try that.
|
|
|
|
|
an error as occurred when connection to the server.
while connecting sql server 2005, this error is likely to be caused by, according to sql server setting, remote connections are not allowed
(provider: ... -there was not possible to connect to sql server
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
