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.
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 jobotherwise 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_Dateelse (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 nextdatefrom SO_Detail dwhere 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 NULL200581 5 2010-07-02 00:00:00.000 B22306 REV - 66 008 2 Open next one NULL200581 5 2010-07-02 00:00:00.000 B22306 REV - 66 009 3 Open next one NULL200581 5 2010-07-20 00:00:00.000 B22306 REV - 66 010 4 Open next one NULL200581 5 2010-08-02 00:00:00.000 B22306 REV - 66 011 5 Open next one NULL200581 5 2010-09-02 00:00:00.000 B22306 REV - 66 012 6 Open next one NULL200581 5 2010-09-09 00:00:00.000 B22306 REV - 66 013 7 Open next one NULL200581 5 2010-10-04 00:00:00.000 B22306 REV - 66 014 8 Open next one NULL200581 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 muchLaura |
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
mayerl
Yak Posting Veteran
95 Posts |
Posted - 2010-08-16 : 13:23:53
|
Sorry guess I'm confused. Isn't that what I did? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 finddatefrom SO_Detail d left join SODate on d.Material = SODate.Material and d.Sales_Order = SODate.Sales_Order and d.SO_Line = SODate.SO_Linewhere 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-012 201109 Aug 160-713705 REV 05 2 nextone3 201109 Dec 160-713705 REV 05 3 nextone4 201109 Feb 160-713705 REV 05 4 nextone5 201109 Jan 160-713705 REV 05 5 nextone6 201109 July 160-713705 REV 05 6 nextone7 201109 Mar 160-713705 REV 05 7 nextone8 201109 Nov 160-713705 REV 05 8 nextone9 201109 Oct 160-713705 REV 05 9 nextone10 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 |
|
|
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. |
|
|
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, )GOINSERT INTO [SO_Detail] ([Sales_Order] ,[SO_Line] ,[Material] ,[Promised_Date]) VALUES ('200616','00010b','177662-1','2010-07-20 00:00:00.000')goINSERT INTO [SO_Detail] ([Sales_Order] ,[SO_Line] ,[Material] ,[Promised_Date]) VALUES ('200616','00010c','177662-1','2010-10-06 00:00:00.000')goINSERT INTO [SO_Detail] ([Sales_Order] ,[SO_Line] ,[Material] ,[Promised_Date]) VALUES ('200616','00010d','177662-1','2010-11-23 00:00:00.000')goINSERT INTO [SO_Detail] ([Sales_Order] ,[SO_Line] ,[Material] ,[Promised_Date]) VALUES ('200616','00010e','177662-1','2011-04-06 00:00:00.000' )GOselect *, 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)TESTfrom 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 NULL200616 00010c 177662-1 2010-10-06 00:00:00.000 2 NULL200616 00010d 177662-1 2010-11-23 00:00:00.000 3 NULL200616 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 |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-08-17 : 18:41:04
|
Hi mayerlWith 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 tempI 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. |
|
|
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 allselect '200616','00010c','177662-1','2010-10-06 00:00:00.000', 1111, 1111 union allselect '200616','00010d','177662-1','2010-11-23 00:00:00.000', 222, 2222 union allselect '200616','00010e','177662-1','2011-04-06 00:00:00.000', 3333, 33333 union allselect '200617','00010c','177662-1','2010-10-06 00:00:00.000', 1111, 111 union allselect '200617','00010d','177662-1','2010-11-23 00:00:00.000', 222, 2222 union allselect '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 |
|
|
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 |
|
|
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?ThanksLaura |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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 :) |
|
|
|
|
|
|
|