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 2008 Forums
 Transact-SQL (2008)
 trying to find next date

Author  Topic 

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-13 : 15:20:31
Afternoon,

I have a request, the user wants this logic:


If demand- inventory >= 0 then print the promise date of that job
otherwise print next promise date


This is what I have so far:


select d.sales_order,d.Order_Qty demand,d.Promised_Date,d.Material,
dbo.fn_GetMatInv(d.Material) inventory,d.SO_Line,
ROW_NUMBER() OVER (ORDER BY d.SO_Line ASC) AS ROWID,d.Status,
case when d.Order_Qty - dbo.fn_GetMatInv(d.Material) >= 0 then 'this one'
else 'next one' end finddate,
case when d.Order_Qty - dbo.fn_GetMatInv(d.Material) >= 0 then d.Promised_Date
else (select Promised_Date
from
(
select d2.Promised_Date, ROW_NUMBER() OVER (ORDER BY d2.SO_Line ASC) AS ROWID
from SO_Detail d2
where d2.sales_order = d.Sales_Order
and d2.Material = d.Material
and d2.Status = 'Open'
) a
where ROWID = rowid+1)
end nextdate
from SO_Detail d
where d.Sales_Order = '200581'
and d.Status = 'Open'
order by d.sales_order,d.Promised_Date, d.SO_Line


It returns the following:


200581 5 2010-05-25 00:00:00.000 B22306 REV - 66 007 1 Open next one NULL
200581 5 2010-07-02 00:00:00.000 B22306 REV - 66 008 2 Open next one NULL
200581 5 2010-07-02 00:00:00.000 B22306 REV - 66 009 3 Open next one NULL
200581 5 2010-07-20 00:00:00.000 B22306 REV - 66 010 4 Open next one NULL
200581 5 2010-08-02 00:00:00.000 B22306 REV - 66 011 5 Open next one NULL
200581 5 2010-09-02 00:00:00.000 B22306 REV - 66 012 6 Open next one NULL
200581 5 2010-09-09 00:00:00.000 B22306 REV - 66 013 7 Open next one NULL
200581 5 2010-10-04 00:00:00.000 B22306 REV - 66 014 8 Open next one NULL
200581 5 2010-11-02 00:00:00.000 B22306 REV - 66 015 9 Open next one NULL


Can someone tell me what the line "where rowid = rowid+1" should be if I want to get the next date in the series?

Thanks so much

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 02:39:26
seems like you need to add a join with subquery and then use it in else part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-16 : 13:23:53
Sorry guess I'm confused. Isn't that what I did?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 13:30:54
Nope i told to add this as a join rather than a subquery

(select Promised_Date
from
(
select d2.Promised_Date, ROW_NUMBER() OVER (ORDER BY d2.SO_Line ASC) AS ROWID
from SO_Detail d2
where d2.sales_order = d.Sales_Order
and d2.Material = d.Material
and d2.Status = 'Open'
) a
where ROWID = rowid+1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-16 : 14:32:31
It almost seems like it needs a cursor loop. I even tried a "with" statement and can't seem to figure out how to say okay now go to the next one in the series....

This is what I have:


WITH SODate AS
(select d.Promised_Date, ROW_NUMBER() OVER (ORDER BY d.SO_Line ASC) AS ROWID,d.sales_order,d.Material,d.Job,d.SO_Line
from SO_Detail d
where d.Status = 'Open'
and d.Material not like '%NRE%'
)
SELECT SODate.ROWID,
d.sales_order,d.SO_Line,
d.Material,
ROW_NUMBER() OVER (ORDER BY d.SO_Line ASC) AS ROWID,
case when d.Order_Qty - dbo.fn_GetMatInv(d.Material) >= 0 then convert(varchar(10), d.Promised_Date, 121)
else 'nextone'
end finddate
from SO_Detail d
left join SODate
on d.Material = SODate.Material
and d.Sales_Order = SODate.Sales_Order
and d.SO_Line = SODate.SO_Line
where d.Material not like '%NRE%'
and d.Status = 'Open'
order by SODate.Material, SODate.Promised_Date


and it give me this back (I took a small sample for description sake):


ROWID sales_order SO_Line Material ROWID finddate
-------------------- ----------- ------- ------------------------------ -------------------- ----------
1 200633 1000 160-713705 REV 05 1 2020-01-01
2 201109 Aug 160-713705 REV 05 2 nextone
3 201109 Dec 160-713705 REV 05 3 nextone
4 201109 Feb 160-713705 REV 05 4 nextone
5 201109 Jan 160-713705 REV 05 5 nextone
6 201109 July 160-713705 REV 05 6 nextone
7 201109 Mar 160-713705 REV 05 7 nextone
8 201109 Nov 160-713705 REV 05 8 nextone
9 201109 Oct 160-713705 REV 05 9 nextone
10 201109 Sept 160-713705 REV 05 10 nextone


My question is I can look at this and say do we have enough product to meet the job? No? Well then make it that promised date. If we do have enough product for this job then make the promised date the promised date of the next one on the list. I can look at it and say okay that June 1st or whatever. But how do I tell the program that?

Thanks for any help you can provide.

Laura
Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-16 : 23:40:24
Please, provide your table with sample data, then provide output you expect, then explanation if need.

It is hard to know what you want if only look at the code, specially when the code is not working.
Go to Top of Page

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-17 : 15:51:47
I'll try. But I'm starting to think its just a problem with SQL. I can't seem to be able to write rowid = rowid+1 or using an identity field from another table so_detail_key = so_detail_key+1 doesn't return anything either. So maybe its just sql. But here goes.


CREATE TABLE [dbo].[SO_Detail](
[Sales_Order] varchar(10) NOT NULL,
[SO_Line] varchar(6) NOT NULL,
[Material] varchar(30) NULL,
[Promised_Date] [datetime] NULL,
)
GO

INSERT INTO [SO_Detail]
([Sales_Order]
,[SO_Line]
,[Material]
,[Promised_Date])
VALUES
('200616','00010b','177662-1','2010-07-20 00:00:00.000')
go
INSERT INTO [SO_Detail]
([Sales_Order]
,[SO_Line]
,[Material]
,[Promised_Date])
VALUES
('200616','00010c','177662-1','2010-10-06 00:00:00.000')
go
INSERT INTO [SO_Detail]
([Sales_Order]
,[SO_Line]
,[Material]
,[Promised_Date])
VALUES
('200616','00010d','177662-1','2010-11-23 00:00:00.000')
go
INSERT INTO [SO_Detail]
([Sales_Order]
,[SO_Line]
,[Material]
,[Promised_Date])
VALUES
('200616','00010e','177662-1','2011-04-06 00:00:00.000'
)
GO

select *,
ROW_NUMBER() OVER (ORDER BY d.sales_order,d.Promised_Date, d.SO_Line ASC) AS ROWID,
(select Promised_Date
from
(
select d2.Promised_Date, ROW_NUMBER() OVER (ORDER BY d2.SO_Line ASC) AS ROWID
from SO_Detail d2
where d2.sales_order = d.Sales_Order
and d2.Material = d.Material
) a
where ROWID = rowid+1)TEST
from SO_Detail d





This gives me null in the test column.


Sales_Order SO_Line Material Promised_Date ROWID test
----------- ------- ------------------------------ ----------------------- -------------------- -----------------------
200616 00010b 177662-1 2010-07-20 00:00:00.000 1 NULL
200616 00010c 177662-1 2010-10-06 00:00:00.000 2 NULL
200616 00010d 177662-1 2010-11-23 00:00:00.000 3 NULL
200616 00010e 177662-1 2011-04-06 00:00:00.000 4 NULL



I expect the text column to look like 2,3,4,5

Does this make it clearer?

Thanks for any help you can provide. I'm getting close to telling them it can't be done.

Laura
Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-17 : 18:41:04
Hi mayerl

With your sample data provided in the last message, to get the expected output looks simple, just add 1 from ROWID.

;with temp as
(
select *, ROW_NUMBER() OVER (ORDER BY d.sales_order,d.Promised_Date, d.SO_Line ASC) AS ROWID from SO_Detail
)
select *, ROWID+1 as test from temp

I did not test this so not sure having syntax error or else ... just run it to to see if it works.

From your original post, I think your real problem may be more complicated than that. If so, could you explain more about logic to get value of test column in your expected output. Most difficult task of helpers is to undersand the problem posted.

Lot of people here can help you out with relational based solution. Cursor should be the very last choice.
Go to Top of Page

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 07:38:57
Here's a cte that might work. I think from the original post, if demand > inventory, then use the promise date otherwise use the next promise date. I added a demand and inventory column for clarity, since I don't know what you are using to compare.


declare @t TABLE (
[Sales_Order] varchar(10) NOT NULL,
[SO_Line] varchar(6) NOT NULL,
[Material] varchar(30) NULL,
[Promised_Date] [datetime] NULL,
demand int,
inventory int
)


INSERT INTO @t
select '200616','00010b','177662-1','2010-07-20 00:00:00.000', 10000, 1000 union all
select '200616','00010c','177662-1','2010-10-06 00:00:00.000', 1111, 1111 union all
select '200616','00010d','177662-1','2010-11-23 00:00:00.000', 222, 2222 union all
select '200616','00010e','177662-1','2011-04-06 00:00:00.000', 3333, 33333 union all
select '200617','00010c','177662-1','2010-10-06 00:00:00.000', 1111, 111 union all
select '200617','00010d','177662-1','2010-11-23 00:00:00.000', 222, 2222 union all
select '200618','00010e','177662-1','2011-04-06 00:00:00.000', 3333, 33333

;with cte as
(
select Sales_Order, SO_Line, Material, Promised_Date, demand, inventory,
ROW_NUMBER() OVER
(ORDER BY sales_order,Promised_Date, SO_Line ASC) AS ROWID from @t
)

select a.Sales_Order, a.SO_Line, a.Material, a.demand, a.inventory, a.Promised_date,
case when a.demand > a.inventory then
a.Promised_Date
else
b.Promised_date
end Calced_Promised_Date
from cte a
left outer join cte b
on a.Sales_Order = b.Sales_Order
and a.ROWID = b.ROWID + 1
Go to Top of Page

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-18 : 08:10:36
wow msquared! thanks so much that looks like it does exactly what I want. Thanks so much for everyones help with this. It was driving me crazy.

Laura
Go to Top of Page

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-18 : 08:30:47
If I could can I ask another question? I would like this to be a function. Is that possible with the with cte syntax?

Thanks

Laura
Go to Top of Page

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 10:25:42
can you explain a little more? Do you want to run this function against any table or just one table? How would the function be used? Usually a function isn't very good to use, so I'm just wondering why? When a function is used, the function has to be run against every row in the result set and that's usually not good for performance
Go to Top of Page

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-18 : 10:48:46
Okay I think I know what your were getting at. I took that and put it in the select statement and it give me what I needed.

Thanks for pointing me in the right direction.

Laura
Go to Top of Page

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 11:24:44
this could be a huge performance nightmare. Can you provide the table layouts for all tables involved? There is probably an easier way. If you take this function and use it in a view, then it will run each row through the function. If the table has any kind of volume, this will take forever and then your users will not be happy. If you can provide the table layouts and some data and expected results, we can probably make it faster/easier
Go to Top of Page

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-18 : 14:58:24
Its already a nightmare :) before they asked for this modification to a report written before I got here, said as my disclaimer, the joins look like this:


FROM Work_Center RIGHT JOIN (Job INNER JOIN (((Job_Operation
LEFT JOIN (SELECT JO.Status, JO.Job, JO.Sequence+1 as Sequence, JO.Act_Run_Qty, JO.Overlap_Qty FROM Job_Operation JO) AS Prev_Op
ON (Job_Operation.Job = Prev_Op.Job) AND (Job_Operation.Sequence = Prev_Op.Sequence))
LEFT JOIN (SELECT JO.Job, JO.Sequence - 1 as Sequence, JO.WC_Vendor, JO.Operation_Service FROM Job_Operation JO) AS Next_Op
ON (Job_Operation.Job = Next_Op.Job) AND (Job_Operation.Sequence = Next_Op.Sequence))
LEFT JOIN (SELECT JOMain.Job_Operation, JOMain.Job, JOMain.Sequence, Count(NextOP.Sequence) AS CountOfSequence,
Sum(NextOP.Rem_Total_Hrs) AS SumOfRem_Total_Hrs FROM Job_Operation AS JOMain,
(sELECT JO.Job, JO.Sequence, JO.Rem_Total_Hrs from Job_Operation as JO WHERE JO.Status IN ('o','s')) AS NextOP
WHERE NextOP.Job=JOMain.Job AND NextOP.Sequence>JOMain.Sequence AND JOMain.Status In ('s','o')
GROUP BY JOMain.Job_Operation, JOMain.Job, JOMain.Sequence) AS Rem_Ops
ON Job_Operation.Job_Operation = Rem_Ops.Job_Operation) ON Job.Job = Job_Operation.Job) ON Work_Center.Work_Center = Job_Operation.Work_Center


I appreciate your offer but I think you would be getting into something that will make you crazy :)
Go to Top of Page
   

- Advertisement -