I need help with one stored procedure
for query for monthly data ( say 12/01/2008 to 12/31/2008).
I am only interested in ID's that have a status start and none of the other status should be 'end' for this [url removed, login to view] the ID if the status was END anytime, we are going to ignore that ID for the purpose of our report.
So Id =1 and Id=7 are out of the window.
For the monthly query I need to check the status, if it changed from start to something else during the month(12/01/2008 to 12/31/2008) then i need to include that record
ID=4 will be included in the report.
If the status changed from start to something else in future(not in queried month) that record should not be included.
If the status changed to start during the current month and did not change anywhere in the future that record should also be included.(id=6)
if there is record with two or more start status(during queried month) and status did not change in future(the last known status) the latest one should be displayed.
we are interested only in two statuses start the and the status following Start.
to give you a background this query is to find all the start phase completed that month and also whose start phase has not been completed that were sent in that month.
srno tracks each status change
I think best way to do this is get
all records that have a status as start and not and end
then get the srno for id for which status is start and look at srno+1 if it exits and with our date range show it in query .
if there is no Srno+1 then then get the record with srno if the date is with in our range
attached is the data and the result set I am expecting
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
sql server 2005