Nested Query-Sequential Dates Of Business Processes

Question

Nested Query-Sequential Dates Of Business Processes

erdem1973

SSCommitted

Points: 1843
More actions
June 8, 2008 at 1:24 pm

#77013

Hi folks,
I have a database table named ProcessTest1. I would like to get the deadline dates of business processes. This table contains parametric values of the sequential business processes.The table structure is like below:
CREATE TABLE [dbo].[ProcessTest1](
[ProcessID] [char](4) ,
[ProcessName] [char](50) ,
[FabricType] [char](1) ,
[ProductType] [int] NULL,
[ProductStyle] [char](1) ,
[StockType] [int] NULL,
[ProductLine] [char](1) ,
[DayCount] [int] NULL,
[OverlappedTo] [char](4) ,
[OverlapDayCount] [int] NULL,
[ProcessAfter] [char](4)
) ON [PRIMARY]
------------
--------------------------------------
My sample data is like below:
INSERT INTO ProcessTest1 VALUES ('001','Process1','D',1,'T',2,'W',12,NULL,NULL,NULL)
INSERT INTO ProcessTest1 VALUES ('002','Process2','D',1,'T',2,'W',1,'001',1,'001')
INSERT INTO ProcessTest1 VALUES ('003','Process3','D',1,'T',2,'W',2,'001',2,'002')
INSERT INTO ProcessTest1 VALUES ('004','Process4','D',1,'T',2,'W',2,'001',2,'003')
INSERT INTO ProcessTest1 VALUES ('005','Process5','D',1,'T',2,'W',4,NULL,NULL,'001')
INSERT INTO ProcessTest1 VALUES ('006','Process6','D',1,'T',2,'W',4,NULL,NULL,'005')
-----------
Expected Result is:
'001', '21.06.2008' (Root Process)
'002','10.06.2008' (OverlappedTo root process)
'003','12.06.2008' (OverlappedTo root process and Sequential to previous process '002')
'004','14.06.2008' (OverlappedTo root process and Sequential to previous process '003')
'005','25.06.2008' (Sequential to previous process '001')
'006','29.06.2008' (sequential to previous process '005')
If I enter the current date and then how can I get the final deadline time by using this database table?
I mean, I would like to get a new resultset that contains 2 columns such as:
ProcessID and DeadLine
Please help..

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

raja_saminathan SSCommitted Points: 1607 More actions · Answer 1

Where are you entering the Current date and also there is no deadLine COlumn in this table.

Are you trying to calculate the DeadLine based on the Given Current Date using some calculations. Can you give some Samole Datas and Expected Results

Rajesh

erdem1973 SSCommitted Points: 1843 More actions · Answer 2

I should enter getdate() (or another date value) as an input parameter. The sub-process final dates will vary based on this input parameter.And there should be a recursion in this query.Right?

erdem1973 SSCommitted Points: 1843 More actions · Answer 3

Oh God!!!

I really dont believe this..My message has been read more than 650 times and there is still no comment or solution :):):)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 4

DECLARE@ProcessTest TABLE

(

ProcessID CHAR(3),

ProcessName VARCHAR(50),

FabricType CHAR(1),

ProductType INT,

ProductStyle CHAR(1),

StockType INT,

ProductLine CHAR(1),

DayCount INT,

OverlappedTo CHAR(3),

OverlapDayCount INT,

ProcessAfter CHAR(3)

)

INSERT@ProcessTest

SELECT'001', 'Process1', 'D', 1, 'T', 2, 'W', 12, NULL, NULL, NULL UNION ALL

SELECT'002', 'Process2', 'D', 1, 'T', 2, 'W', 1, '001', 1, '001' UNION ALL

SELECT'003', 'Process3', 'D', 1, 'T', 2, 'W', 2, '001', 2, '002' UNION ALL

SELECT'004', 'Process4', 'D', 1, 'T', 2, 'W', 2, '001', 2, '003' UNION ALL

SELECT'005', 'Process5', 'D', 1, 'T', 2, 'W', 4, NULL, NULL, '001' UNION ALL

SELECT'006', 'Process6', 'D', 1, 'T', 2, 'W', 4, NULL, NULL, '005'

/*

'001', '21.06.2008' (Root Process)

'002', '10.06.2008' (OverlappedTo root process)

'003', '12.06.2008' (OverlappedTo root process and Sequential to previous process '002')

'004', '14.06.2008' (OverlappedTo root process and Sequential to previous process '003')

'005', '25.06.2008' (Sequential to previous process '001')

'006', '29.06.2008' (sequential to previous process '005')

*/

; WITH Yak (ProcessID, theDate, thePath, dc)

AS (

SELECTProcessID,

DATEADD(DAY, DayCount + DATEDIFF(DAY, '19000101', GETDATE()), '19000101'),

'/' + CAST(ProcessID AS VARCHAR(MAX)) + '/',

DayCount

FROM@ProcessTest

WHEREProcessAfter IS NULL

UNION ALL

SELECTpt.ProcessID,

DATEADD(DAY, CASE

WHEN y.thePath = '/' + pt.OverlappedTo + '/' THEN pt.OverlapDayCount - y.dc

ELSE pt.DayCount

END, y.theDate),

y.thePath + CAST(pt.ProcessID AS VARCHAR(12)) + '/',

dc

FROM@ProcessTest AS pt

INNER JOINYak AS y ON y.ProcessID = pt.ProcessAfter

)

SELECTProcessID,

theDate

FROMYak

ORDER BYthePath

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Heh... yeah... 648 of them were me trying to figure out your problem... 😉

I could make it 651 ya know... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

Nicely done, Peter! I'm still kinda new to the 2005 world and I keep forgetting about stuff like that...

heh... especially on a 2k forum 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 7

I see that now.

I responded to a PM and saw that OP posted same question on the forums.

Didn't notice the version.

N 56°04'39.16"
E 12°55'05.25"

erdem1973 SSCommitted Points: 1843 More actions · Answer 8

Oh god!!!!!

Where were you :):):)

Well, viewer count exceeded 700 🙂

Thank you very much for your replies (also thanks to all readers..:) ).If it possible I would like to ask another question based on this query.

How can I use a date parameter instead of GetDate() ?

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 9

ALTER PROCEDURE dbo.uspMyProcedure

(

@Today DATETIME = NULL

)

AS

SET NOCOUNT ON

IF @Today IS NULL

SET@Today = DATEDIFF(DAY, '19000101', GETDATE())

ELSE

SET@Today = DATEDIFF(DAY, '19000101', @Today)

;WITH Yak (ProcessID, theDate, thePath, dc)

AS (

SELECTProcessID,

DATEADD(DAY, DayCount, @Today),

'/' + CAST(ProcessID AS VARCHAR(MAX)) + '/',

DayCount

FROMProcessTest1

WHEREProcessAfter IS NULL

UNION ALL

SELECTpt.ProcessID,

DATEADD(DAY, CASE

WHEN y.thePath = '/' + pt.OverlappedTo + '/' THEN pt.OverlapDayCount - y.dc

ELSE pt.DayCount

END, y.theDate),

y.thePath + CAST(pt.ProcessID AS VARCHAR(12)) + '/',

y.dc

FROMProcessTest1 AS pt

INNER JOINYak AS y ON y.ProcessID = pt.ProcessAfter

)

SELECTProcessID,

theDate

FROMYak

ORDER BYthePath

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 10

Peso (6/9/2008)
I see that now.
I responded to a PM and saw that OP posted same question on the forums.
Didn't notice the version.

Thanks for the feedback, Peter... Op seems happy with the excellent 2k5 solution. Too bad... was putting the final touches on the 2k solution but I'll quit now. I, too, was responding to a PM... guess I won't respond to those types of PM's anymore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

erdem1973 SSCommitted Points: 1843 More actions · Answer 11

It works like a ferrari... 🙂

Now I will try to extend this procedure by using "business days=working days" of a week..Is it possible to use working days of a week?

THANK YOU VERY MUCH......

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 12

I just clicked OP's "all post" to see if I didn't invent the wheel again.

If I hadn't, both you and me would present a working solution. What a waste of time.

N 56°04'39.16"
E 12°55'05.25"

erdem1973 SSCommitted Points: 1843 More actions · Answer 13

erdem1973

SSCommitted

Points: 1843

June 9, 2008 at 7:53 am

#826103

Ok...You are exactly right..

Thanks again

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 14

Peso (6/9/2008)
I just clicked OP's "all post" to see if I didn't invent the wheel again.
If I hadn't, both you and me would present a working solution. What a waste of time.

Yep, me too! I'm outa here... keep the shiney side up ol' friend.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)