grouping query - 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

Transact-SQL (2000)

grouping query

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2008-10-16 : 11:50:19

[code]
CUR_DT SHIP SHIP_DT-need this result
10/16/2008 1 10/16/2008
10/17/2008 10/16/2008
10/18/2008 10/16/2008
10/19/2008 1 10/19/2008
10/20/2008 10/19/2008
10/21/2008 10/19/2008
10/22/2008 1 10/22/2008
10/23/2008 10/22/2008
10/24/2008 1 10/24/2008
10/25/2008 1 10/25/2008
10/26/2008 10/25/2008
10/27/2008 1 10/27/2008
10/28/2008 10/27/2008
[/code]

i have 3 columns in my table and the third column needs to be outputted - the third column is a reflection of the ship date to use when the ship column is set to 1 ....

in the first example for dates 16, 17 and 18 use the 16th to ship because the 16th is the only ship date up untill the 19th.

please help with the output.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-10-16 : 15:58:56

What defines ship date? Do you not ship on Friday and Saturday (10/17-10/18)? I guess that can't be accurate because you apparently don't ship on Tuesdays, sometimes, either (10/28)??? And how do you set a ship date < current date? Is there time travel involved? Maybe ESP...an order is placed on the 17th but they knew the day before that it was going to happen and shipped it on the 16th??? Detail needs to be provided if you expect help as opposed to some good-natured ridicule. What, exactly, are the rules for deriving ship date?

Terry

dpais
Yak Posting Veteran

60 Posts

Posted - 2008-10-16 : 16:07:56

the ship date is user checked off ... we donot need to calculate it ... and its in plane days ... no time calculations necessary.

thanks.

P.S. the first 2 columns are user entered ..... the 3rd column needs to be calculated by the query.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-17 : 03:58:10

create table #test(cur_dt datetime, ship char(1))
insert #test
select '10/16/2008','1' union
select '10/17/2008','' union
select '10/18/2008','' union
select '10/19/2008','1' union
select '10/20/2008','' union
select '10/21/2008','' union
select '10/22/2008','1' union
select '10/23/2008','' union
select '10/24/2008','1' union
select '10/25/2008','1' union
select '10/26/2008','' union
select '10/27/2008','1' union
select '10/28/2008',''

select
t1.cur_dt,
t1.ship,
(select max(t2.cur_dt) from #test t2 where ship=1 and t1.cur_dt>=t2.cur_dt) ship_dt
from #test t1

drop table #test

Planning replaces chance by mistake

dpais
Yak Posting Veteran

60 Posts

Posted - 2008-10-17 : 08:13:13

works like a CHARM .... thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources