Compare Records based from the next Record

[Lethugs]

Hi,

I need to compare record using sql ce

I have this data, can be thousand rows

ID      Type        Date        Time
02-0009  I          01/02/2014  8:00
02-0009  O          01/02/2014  18:00
02-0009  O          01/03/2014  18:00
02-0009  I          01/04/2014  8:00
02-0009  O          01/04/2014  18:00

For every date there should be a 'I' and 'O' type
If last Type for a date, for example 01/02/2014 is 'O', the Type for the next date should be 'I' and vice versa
The situation in dates 01/02/2014 with type 'O' and 01/03/2014 with type 'O' is invalid
The invalid record could be with type 'I' or 'O'

I need to query it to show this invalid record

please help, there really lots of limits in sql ce

thanks in advance

[urtrivedi]

This is going to be complicated for you.

Whenever you store time, You should store it as date time.

That will help to analyse things in better manner.
You should have keep only single datetime column instead of 2 separate columns

Right now if you run max min fuction on your time column (as now its text field I guess), your query will return 08:00 as max value

[Lethugs]

Lets say my time is already stored in datetime,

1. How can I retrieve the time only?
2. How will now I proceed with my query?

thanks again

[Lethugs]

I tried this code but not working, no error but not pulling the data

 Select Tlogs.ID, Tlogs.Type, Tlogs.Date, Tlogs.Time From Tlogs 

Left Join (Select ID, Type, Max(Convert (Datetime, Date + Time, 0) ) as Dat From Tlogs Group by ID, Type)  incs on incs.ID = Tlogs.ID  

Left Join (Select Tlogs.ID, min(Convert (Datetime, Tlogs.Date + Tlogs.Time, 0) ) as d, Tlogs.Type From Tlogs Group by Tlogs.ID, Tlogs.Type) inc on inc.ID = Tlogs.ID 

where inc.Type = incs.Type and inc.d > incs.Dat

and Tlogs.Dates between '01/01/2014' and '04/30/2014'

anybody who can help me?
I've been trying to figure this out for many days now

thanks

[urtrivedi]

select a.maxtime maxtime_firstday,b.type type_firstday,
c.mintime mintime_secondday, d.type  type_secondday 
from (
    select 
    DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 1) nextday,
      max(tlogs.Date) maxtime from tlogs group by DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 0),
    DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 1)

    ) a
left outer join tlogs b on a.maxtime=b.date
left outer join (
    select DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 0) currday,
      min(tlogs.Date)mintime from tlogs group by  DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 0) 
) c on a.nextday=c.currday
left outer join tlogs d on c.mintime=d.date
where b.type<>d.type and a.maxtime between '2014-01-01' and '2014-01-31'

[Lethugs]

What I need to display is The first transation of the next date is the same with the previous date.

If for example the date 01/02/2014, it contains Types 'I' and 'O' so it means its a complete transaction, if the following Date for example is 01/03/2014 is with Type 'O' (meaning no 'I'), its an error in the data. That's what I need to display

Something like the last Type of a date is the same with the first Type of the following date

The system which the data is extracted should not accept a Type 'I' if the last Transaction Type is also 'I' or vice versa. So i need to show this system error

thank you urtrivedi for helping

[urtrivedi]

Are you able to run my query or not.
Have you seen the result?

[Lethugs]

Yes sir, I was able to run your code. It shows even those with complete transaction

[urtrivedi]

Now you have query, you can tweak around it. Though run following one also, I have changed b.type<>d.type to b.type=d.type. My query assumes that your date column is also having time part as you mentioned in your above post.

If your date column is not having time part, then query may not work properly

select a.maxtime maxtime_firstday,b.type type_firstday,
c.mintime mintime_secondday, d.type  type_secondday 
from (
    select 
    DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 1) nextday,
      max(tlogs.Date) maxtime from tlogs group by DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 0),
    DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 1)

    ) a
left outer join tlogs b on a.maxtime=b.date
left outer join (
    select DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 0) currday,
      min(tlogs.Date)mintime from tlogs group by  DATEADD(dd, DATEDIFF(dd, 0, tlogs.Date), 0) 
) c on a.nextday=c.currday
left outer join tlogs d on c.mintime=d.date
where b.type=d.type and a.maxtime between '2014-01-01' and '2014-01-31'

[Lethugs]

you're adding 1 day to get the next date while my data consists of indeterminate dates. The difference could be 2 days or more. Also we need to consider also the time. Im able to get the Transaction of max Time then compare it to the Transaction From min time of next date. But it shows also the max time of the next date

[Lethugs]

I tried to tweak your code and came up with this

select d.EID,  d.mintime, a.EID, a.maxtime
from (select EID, Dates, Max(Convert (Datetime, Dates + Time, 0)) maxtime from Tlogs group by Dates, EID) a

Join (Select Type, Convert(Datetime, Dates + Time, 0) as dat From Tlogs) b on b.dat = a.maxtime

Join (select c.EID,  c.mintime, c.Dates from (select EID, Dates, Min(Convert (Datetime, Dates + Time, 0) ) mintime from Tlogs group by Dates, EID) c ) d on d.EID = a.EID

Join (Select Type, Convert(Datetime, Dates + Time, 0) as dat From Tlogs) e on e.dat = d.mintime

where  a.maxtime between '01/01/2014' and '04/30/2014' and d.mintime > a.maxtime and b.Type = e.Type

But it displays all the data from the following dates with the same type
it should only display the next date only with same type

[Lethugs]

Date and Time are separate columns so i tried to concatenate them Convert(Datetime, Dates + Time, 0)

[kgariando]

You can use the ROW_NUMBER() function to isolate the most recent rows by partitioning on the ID_NUM and ordering by the CREATED_DATE descending. Then identify the occurrences of more than one DELIVERY_TYPE = 2 to filter the result set: