SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 trying to find next date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mayerl
Yak Posting Veteran

95 Posts

Posted - 08/13/2010 :  15:20:31  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 08/14/2010 :  02:39:26  Show Profile  Reply with Quote
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 - 08/16/2010 :  13:23:53  Show Profile  Reply with Quote
Sorry guess I'm confused. Isn't that what I did?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/16/2010 :  13:30:54  Show Profile  Reply with Quote
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 - 08/16/2010 :  14:32:31  Show Profile  Reply with Quote
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

USA
268 Posts

Posted - 08/16/2010 :  23:40:24  Show Profile  Reply with Quote
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 - 08/17/2010 :  15:51:47  Show Profile  Reply with Quote
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

USA
268 Posts

Posted - 08/17/2010 :  18:41:04  Show Profile  Reply with Quote
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 - 08/18/2010 :  07:38:57  Show Profile  Reply with Quote
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 - 08/18/2010 :  08:10:36  Show Profile  Reply with Quote
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 - 08/18/2010 :  08:30:47  Show Profile  Reply with Quote
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 - 08/18/2010 :  10:25:42  Show Profile  Reply with Quote
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 - 08/18/2010 :  10:48:46  Show Profile  Reply with Quote
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

Edited by - mayerl on 08/18/2010 11:23:07
Go to Top of Page

MSquared
Yak Posting Veteran

52 Posts

Posted - 08/18/2010 :  11:24:44  Show Profile  Reply with Quote
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 - 08/18/2010 :  14:58:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000