SQL Query for database


#1

I have a problem in writing a Database Query

Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS

Data: REQUEST_STATUS may contains [new, mod,bulk load, bulk delete, delete]

Condition

scenario 1:

REQUEST_STATUS = new or mod or bulk load, or all three for a particular E_ID then i need all the E_ID but if that particular E_ID contains also Request_status of Delete, or bulk delete then i don’t need that data of E_ID till that particular Date , and if any data exist after bulk delete or delete after that particular date then i need those data.

and also i dont want to use primary key as i have huge number of data to retrieve from the table.

I have attached the sample data for better understanding

100001 55554111 SBI NEW 5/5/2013 Complete
100002 55556112 SBI NEW 6/5/2013 Complete
100003 55554111 SBI MOD 6/5/2013 Complete
100004 55554111 SBI MOD 7/5/2013 Complete
100005 55554111 SBI MOD 8/5/2013 Failure
100006 55556112 SBI MOD 8/5/2013 Complete
100007 55556113 UTI BULK LOAD 8/5/2013 Complete
100008 55556111 SBI DEL 9/5/2013 Complete
100009 55556113 UTI MOD 9/5/2013 Complete
100010 55556113 UTI MOD 10/5/2013 Failure
100011 55554111 SBI MOD 11/5/2013 Complete
100012 55556113 UTI DEL 11/5/2013 Complete
00013 55556112 SBI DEL 12/5/2013 Complete
100014 55554111 SBI MOD 12/5/2013 Completed
Result SET

100011 55554111 SBI MOD 11/5/2013 Complete
100014 55554111 SBI MOD 12/5/2013 Complete


#2

If I’m understanding you right, you’re trying to summarize the results for each E_ID (the second column) in a chronological fashion. If so, I’m not certain it’s possible to do this in a single SQL query. Using a GROUP BY query will get you part of the way there, but I don’t know if there’s any way to get it to do the complex processing for STATUS that you’re after.

If this were my problem, I’d be inclined to just select all rows with “ORDER BY E_ID, START_DATE”, then go through the rows in application code to determine which ones to display. This is effectively what the database server would have to do here anyway; this query is not going to be fast, no matter how you slice it.