'next date' method - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

'next date' method

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-12 : 10:30:01

Heres the background: Using vb.Net 03...The user gets some data (from ms sql db) to a DataGrid by entering a Date. Then there is a button, "Next Date" that will find the next date in the row and bring up that data.

What I am doing: I have a seperate dataTable will all the dates in it. After that first date is inputted ... I will match that date with the date in the DataTable and then find the NEXT available date. Then take that date and run it through my first query to disply the data on the dataGrid.

make sense? I just do not know how to take the first stored date and compare/search through all of my other dates to find the next date.

Is there a better way doing what I want? Can anyone help?? Thank you all.

Chester
Starting Member

27 Posts

Posted - 2004-10-12 : 10:47:54

Try something like this:

select top 1 the_date
from DataTable
where the_date is not null and the_date > 'date_inputted'
order by the_date

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-12 : 11:24:46

Thank you Chester. That works perfectly! BUT, it only returns one row from that date. How can I get it to loop so it will get ALL the rows in that Date???

Chester
Starting Member

27 Posts

Posted - 2004-10-12 : 12:34:21

Add this line to the where clause:

and 1 = (case when the_date = (select min(the_date) from DataTable where the_date is not null and the_date > 'date_inputted') then 1 else 0 end)

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-12 : 12:43:51

Thanks for the reply Chester. That doesn't seem to be doing anything. I'll have to keep playing with it. Does everything "look" right?

I'm using your examples in this query (It's working fine except for the returning only 1 row..):

SELECT top 1 Hours.Datewrk, Employee.Lastname, Employee.Firstname, Employee.EmployNo, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, PurchaseOrder.Purchord, Hours.TicketNo
FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.EmployNo = Employee.EmployNo
WHERE Hours.Datewrk is not null and Hours.Datewrk > '06/21/2004' and 1 = (case when Hours.Datewrk = (select min(Hours.Datewrk) from Hours where Hours.Datewrk is not null and Hours.Datewrk > '06/21/2004') then 1 else 0 end) And PurchaseOrder.JobNo = 'GE4112'
ORDER BY Employee.Lastname, Employee.Firstname

Chester
Starting Member

27 Posts

Posted - 2004-10-12 : 13:01:49

Oops, I forgot to tell you to get rid of "top 1" in the select clause.

Sorry.

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-12 : 13:57:32

ahhhh!!!! That makes sense! Thanks again Chester. :)

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-12 : 14:39:49

Well, after testing it some..... The way it is now, it doesn't "skip" to the next available date. Before adding the code to grab ALL the dates it would jump from say, 10/10/2004 to 10/15/2004 fine. But now it will not. It will only continue if the dates are all in row. Why is this????

edit: Also, I found... if you pick the date right before a valid row, as in there are rows of data for Date 6/18/2004 and you pick 6/17/2004 it will bring up the next date fine. BUT if you pick 6/16/2004 it will not. ???

Here is some simplified example code: This works fine (but doesn't grab all rows with the Date)

select top 1 datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and purchord = '4112'
order by datewrk

This grabs all the rows with the Date, but doesn't 'skip' correctly as mentioned above:

select datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and 1 = (case when Datewrk = (select min(Datewrk) from Hours where Datewrk is not null and Datewrk > '06/15/2004') then 1 else 0 end) and purchord = '4112'
order by datewrk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources