Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-12 : 07:41:57
|
| Bill writes "I have data from a terminal persons use to check in and out.For example:bookingdata:PersonID TheDay TheTime BookingType1 20050901 061045 co1 20050901 101201 go1 20050901 110422 co1 20050901 130211 go2 20050901 060302 co2 20050501 120403 co3 20050501 061202 go3 20050501 100101 coPersonID is for the person the data belong to, TheDay has format yyyymmdd and time has hhmmss, co means comes and go means goes. There can be only an even number of bookings per person and day and the right sequence is 'co go' or 'co go co go' and so on.You see that bookings of PersonId 1 are ok, but for 2 and 3 are not correct.How do I find those persons per day who did some incorrect bookings?The only idea I have is to create a user defined aggregate functions that sums the bookingstypes. This would be 'cogocogo' for PersonId 1 and 'goco' for PersonId 1. As I know the allowed string lengths and their contents I could do a check for incorrect bookings.BOL gave me no answer and also google did not help.So if you think this could be the way how to do it?Or do know something better in terms of ressources (mainly time)?I hope for some help.Thank you. " |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-12 : 09:31:08
|
try this:select sum(case when BookingType='co' then 1 else 0 end) as cos, sum(case when BookingType='go' then 1 else 0 end) as gos, PersonID from TableNamegroup by PersonID Go with the flow & have fun! Else fight the flow  |
 |
|
|
Bill Schneider
Starting Member
5 Posts |
Posted - 2005-09-13 : 12:40:57
|
| Thank you spirit1,I forgot to mention that before every go(es) there must be a co(mes) booking.Does your code takes this into account?As far as I can see you cannot build user defined aggregate functions in mssql. So I really have no idea to solve the problem. But this must be a well known problem with a solution in t-sql - looking for the right order in a chain under the condition that one part can only follow after a certain other part.Please help.Thank you. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Bill Schneider
Starting Member
5 Posts |
Posted - 2005-09-22 : 04:40:09
|
| Thank you again, spirit1the info behind your link is very interesting but I do not manage to solve my problem with it.I think I try another way. The data from the terminals are written by some proprietary software into one table. I treat them as raw data and will transfer them with an insert trigger into mytable. With an m insert trigger I check for the right sequence and if I find one I mark this record as bad. So I have a easy way to find bad bookings.Another problem is that some people do not notice their succesful booking and so they book again. So I find double or even triple bookings with small time differences in the raw data. I will try to cancel these neeedless booking data with the trigger, too.I will post another question with this new direction the next days to ask for some help with these triggers.Thank you again. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-22 : 04:53:09
|
| maybe you should change the front end to not allow double bookings?like disabling the submit button when you submit your booking...Go with the flow & have fun! Else fight the flow |
|
|
|
|
|
|
Is user defined aggregate function a solution?