[RESOLVED]Noobie here automating query that change

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 2005 Forums

Transact-SQL (2005)

[RESOLVED]Noobie here automating query that change

Author

Topic

jar21

51 Posts

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

I need to automate a query BUT there is an area in the query (located below) that needs to be edited each day that it is run. The date must always reflect that days date, is there a command like %DATE that can do this? Having to run this manually and changed the date every day is getting old, I've done this 5 times already. Please let me know if anyone can help, also I can show you the rest of the query if needed but this is the only part of the query that needs to be edited at each execution (once a day after 5pm)

EXECUTE OA_END_BASED_ON_ESTIMATE '2008-8-13', '100', 'S1'
GO

Enjoy Life This Is Not A Rehearsal.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 10:23:34

i guess you want something around GETDATE(), but without the rest of the code it's hard to know what exactly to do with it. strip off the time and pass it through a variable probably?

Em

jar21

51 Posts

Posted - 2008-08-13 : 10:35:37

Here is the full query

IF OBJECT_ID('OA_END_BASED_ON_ESTIMATE') IS NOT NULL
DROP PROCEDURE OA_END_BASED_ON_ESTIMATE
GO
CREATE PROCEDURE OA_END_BASED_ON_ESTIMATE
(
@CutoffDate DATETIME,
@OAReasonEnd VARCHAR(3),
@OMReasonEnd VARCHAR(3),
@BranchKey VARCHAR(6) = NULL
)
AS

--$PRAGMA-STANDARD

SET NOCOUNT ON
SET ANSI_PADDING OFF
SET ANSI_NULLS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET XACT_ABORT ON

--$PRAGMA-STANDARD-END

DECLARE @AssignmentKey VARCHAR(8)
DECLARE @OrderKey VARCHAR(8)

CREATE TABLE #REMOVE
(
AssignmentKey VARCHAR(8) NOT NULL,
OrderKey VARCHAR(8) NOT NULL,
OAEstimateEnd DATETIME NOT NULL,
BranchKey VARCHAR(6) NOT NULL
)

INSERT INTO #REMOVE
SELECT OA.Assignment_ID, OA.Order_ID, OA.End_Estimate_Date, OM.Branch_ID
FROM OrderAssignment OA, OrderMaster OM
WHERE OA.End_Actual_Date IS NULL
AND OA.End_Estimate_Date < @CutoffDate
AND OA.Order_ID = OM.Order_ID

IF (@BranchKey IS NOT NULL)
BEGIN
DELETE FROM #REMOVE WHERE BranchKey <> @BranchKey
END

DECLARE cLoopOA CURSOR
FOR SELECT AssignmentKey FROM #REMOVE
OPEN cLoopOA

FETCH NEXT FROM cLoopOA INTO @AssignmentKey
WHILE (@@FETCH_STATUS = 0)
BEGIN

UPDATE OrderAssignment
SET End_Actual_Date = End_Estimate_Date,
End_Actual_Reason = @OAReasonEnd
WHERE Assignment_ID = @AssignmentKey

FETCH NEXT FROM cLoopOA INTO @AssignmentKey
END
CLOSE cLoopOA
DEALLOCATE cLoopOA

DELETE FROM #REMOVE

INSERT INTO #REMOVE
SELECT '00000000', Order_ID, End_Estimate_Date, Branch_ID
FROM OrderMaster
WHERE End_Actual_Date IS NULL
AND End_Estimate_Date < @CutoffDate

IF (@BranchKey IS NOT NULL)
DELETE FROM #REMOVE WHERE BranchKey <> @BranchKey

DELETE FROM #REMOVE
WHERE OrderKey IN (SELECT Order_ID
FROM OrderAssignment
WHERE End_Actual_Date IS NULL)

DECLARE cLoopOM CURSOR
FOR SELECT OrderKey FROM #REMOVE
OPEN cLoopOM

FETCH NEXT FROM cLoopOM INTO @OrderKey
WHILE (@@FETCH_STATUS = 0)
BEGIN

UPDATE OrderMaster
SET End_Actual_Date = End_Estimate_Date,
End_Actual_Reason = @OMReasonEnd
WHERE Order_ID = @OrderKey

FETCH NEXT FROM cLoopOM INTO @OrderKey
END
CLOSE cLoopOM
DEALLOCATE cLoopOM

DROP TABLE #REMOVE
GO
GRANT EXECUTE ON OA_END_BASED_ON_ESTIMATE TO PUBLIC
GO

EXECUTE OA_END_BASED_ON_ESTIMATE '2008-8-13', '100', 'S1'
GO
the bold is the only thing that needs to be edited at each runtime to "todays date"

currently I edit it and run it when I get home....

Enjoy Life This Is Not A Rehearsal.

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 10:48:02

It sounds like you just need todays date when you run that monsterous procedure. If so..

EXECUTE OA_END_BASED_ON_ESTIMATE '2008-8-13', '100', 'S1'

Needs to Be

EXECUTE OA_END_BASED_ON_ESTIMATE GetDate(), '100', 'S1'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 11:22:51

quote:
Originally posted by drdream

It sounds like you just need todays date when you run that monsterous procedure. If so..

EXECUTE OA_END_BASED_ON_ESTIMATE '2008-8-13', '100', 'S1'

Needs to Be

EXECUTE OA_END_BASED_ON_ESTIMATE GetDate(), '100', 'S1'

please note that getdate() returns the timepart as well. so results might not be what you desire. may be you want this

DATEADD(d,DATEADD(d,0,GETDATE()),0)

this strips out timepart from getdate()

jar21

51 Posts

Posted - 2008-08-13 : 11:28:12

Thank you all for the prompt responses, is there a way you can recommend me to test this to see if it works without actually effecting the database?

Enjoy Life This Is Not A Rehearsal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 11:43:35

quote:
Originally posted by rabino67

Thank you all for the prompt responses, is there a way you can recommend me to test this to see if it works without actually effecting the database?

Enjoy Life This Is Not A Rehearsal.

didnt understand. what do you mean without affecting db? May be use a test table in procedure and trying out? or changing final update to select so as not affect the actual table?

jar21

51 Posts

Posted - 2008-08-13 : 11:49:12

quote:
Originally posted by visakh16

quote:
Originally posted by rabino67

Thank you all for the prompt responses, is there a way you can recommend me to test this to see if it works without actually effecting the database?

Enjoy Life This Is Not A Rehearsal.

didnt understand. what do you mean without affecting db? May be use a test table in procedure and trying out? or changing final update to select so as not affect the actual table?

yes, I would like to test Query before implementing it, I am very cautious because know next to nothing about MSSQL and don't want to do something without being absolutely positive it does what I need

Enjoy Life This Is Not A Rehearsal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:11:35

quote:
Originally posted by rabino67

quote:
Originally posted by visakh16

quote:
Originally posted by rabino67

Thank you all for the prompt responses, is there a way you can recommend me to test this to see if it works without actually effecting the database?

Enjoy Life This Is Not A Rehearsal.

didnt understand. what do you mean without affecting db? May be use a test table in procedure and trying out? or changing final update to select so as not affect the actual table?

yes, I would like to test Query before implementing it, I am very cautious because know next to nothing about MSSQL and don't want to do something without being absolutely positive it does what I need without doing something I don't want. I'm next to scared sh!tless about changing things because I am the only IT guy here and I've done very little with databases (recent grad) :-)

Enjoy Life This Is Not A Rehearsal.

one way will be to change last update in procedure to select the new values and check if its as expected.

jar21

51 Posts

Posted - 2008-08-13 : 15:40:02

I forwarded your solution so someone who is overseeing our database activity and they tried it and they replied with this.

"the reason it will not work is because the procedure that actually closes the order and assignment are looking for an actual date ex) 01/01/2008. "

so I guess no luck... anyone have any ideas for the hopeful?

Enjoy Life This Is Not A Rehearsal.

chrpeter
Starting Member

31 Posts

Posted - 2008-08-13 : 16:20:46

That should work just the same.

Try declaring a date time and passing in that instead of just the GetDate() function.

DECLARE @today datetime
SET @today = CONVERT(varchar(4),YEAR(cus_moddttm) )+ '-' + CONVERT(varchar(2),MONTH(cus_moddttm))+ '-' + CONVERT(varchar(2),DAY(cus_moddttm))

Then on execution do
EXECUTE OA_END_BASED_ON_ESTIMATE @today, '100', 'S1'

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-08-13 : 17:20:12

quote:
Originally posted by rabino67

I forwarded your solution so someone who is overseeing our database activity and they tried it and they replied with this.

"the reason it will not work is because the procedure that actually closes the order and assignment are looking for an actual date ex) 01/01/2008. "

so I guess no luck... anyone have any ideas for the hopeful?

Enjoy Life This Is Not A Rehearsal.

I venture to say the person that told you this doesn't understand what they are talking about. If you need to pass it the current date each time it runs you can use getdate() or getdate() with the time stripped off (as shown in an above post). In other words getdate() is an actual date just like '1/1/2008' is an actual date. Just so happens that getdate() is the current date. Maybe what you sent them had the time part with it and didn't work. If that's the case then use the code above to stip off the time and only pass the date portion of getdate().

jar21

51 Posts

Posted - 2008-08-14 : 11:35:27

yes I agree van, unfortunately I know little to nothing about it either.

he sent me this code saying instead of that very long query this will do everything I need

I'm not 100% how, but does this raise any red flags with anyone?

DECLARE @today datetime
SET @today = GETDATE()

EXECUTE OA_END_BASED_ON_ESTIMATE @today, '100', 'S1'

Enjoy Life This Is Not A Rehearsal.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-08-14 : 12:17:49

This:

DECLARE @today datetime
SET @today = GETDATE()

EXECUTE OA_END_BASED_ON_ESTIMATE @today, '100', 'S1'

Should be the same as this (which was also a suggestion):

EXECUTE OA_END_BASED_ON_ESTIMATE getdate(), '100', 'S1'

jar21

51 Posts

Posted - 2008-08-18 : 11:58:27

Here is my quick question, There are two separate queries below, Currently I was told the bottom one will do everything the first one has done because the bottom one calls a stored procedure that has already been created. Can someone verify this for me (is that possible?) .. currently I haven't automated anything but thats eventually what I want to do and it looks like the bottom one won't need changing so that can be done!

IF OBJECT_ID('OA_END_BASED_ON_ESTIMATE') IS NOT NULL
DROP PROCEDURE OA_END_BASED_ON_ESTIMATE
GO
CREATE PROCEDURE OA_END_BASED_ON_ESTIMATE
(
@CutoffDate DATETIME,
@OAReasonEnd VARCHAR(3),
@OMReasonEnd VARCHAR(3),
@BranchKey VARCHAR(6) = NULL
)
AS

--$PRAGMA-STANDARD

SET NOCOUNT ON
SET ANSI_PADDING OFF
SET ANSI_NULLS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET XACT_ABORT ON

--$PRAGMA-STANDARD-END

DECLARE @AssignmentKey VARCHAR(8)
DECLARE @OrderKey VARCHAR(8)

CREATE TABLE #REMOVE
(
AssignmentKey VARCHAR(8) NOT NULL,
OrderKey VARCHAR(8) NOT NULL,
OAEstimateEnd DATETIME NOT NULL,
BranchKey VARCHAR(6) NOT NULL
)

INSERT INTO #REMOVE
SELECT OA.Assignment_ID, OA.Order_ID, OA.End_Estimate_Date, OM.Branch_ID
FROM OrderAssignment OA, OrderMaster OM
WHERE OA.End_Actual_Date IS NULL
AND OA.End_Estimate_Date < @CutoffDate
AND OA.Order_ID = OM.Order_ID

IF (@BranchKey IS NOT NULL)
BEGIN
DELETE FROM #REMOVE WHERE BranchKey <> @BranchKey
END

DECLARE cLoopOA CURSOR
FOR SELECT AssignmentKey FROM #REMOVE
OPEN cLoopOA

FETCH NEXT FROM cLoopOA INTO @AssignmentKey
WHILE (@@FETCH_STATUS = 0)
BEGIN

UPDATE OrderAssignment
SET End_Actual_Date = End_Estimate_Date,
End_Actual_Reason = @OAReasonEnd
WHERE Assignment_ID = @AssignmentKey

FETCH NEXT FROM cLoopOA INTO @AssignmentKey
END
CLOSE cLoopOA
DEALLOCATE cLoopOA

DELETE FROM #REMOVE

INSERT INTO #REMOVE
SELECT '00000000', Order_ID, End_Estimate_Date, Branch_ID
FROM OrderMaster
WHERE End_Actual_Date IS NULL
AND End_Estimate_Date < @CutoffDate

IF (@BranchKey IS NOT NULL)
DELETE FROM #REMOVE WHERE BranchKey <> @BranchKey

DELETE FROM #REMOVE
WHERE OrderKey IN (SELECT Order_ID
FROM OrderAssignment
WHERE End_Actual_Date IS NULL)

DECLARE cLoopOM CURSOR
FOR SELECT OrderKey FROM #REMOVE
OPEN cLoopOM

FETCH NEXT FROM cLoopOM INTO @OrderKey
WHILE (@@FETCH_STATUS = 0)
BEGIN

Update OrderMaster
SET End_Actual_Date = End_Estimate_Date,
End_Actual_Reason = @OMReasonEnd
WHERE Order_ID = @OrderKey

FETCH NEXT FROM cLoopOM INTO @OrderKey
END
CLOSE cLoopOM
DEALLOCATE cLoopOM

DROP TABLE #REMOVE
GO
GRANT EXECUTE ON OA_END_BASED_ON_ESTIMATE TO PUBLIC
GO

EXECUTE OA_END_BASED_ON_ESTIMATE '2008-8-18', '100', 'S1'
GO

###REVISED QUERY###

DECLARE @today datetime
SET @today = GETDATE()

EXECUTE OA_END_BASED_ON_ESTIMATE @today, '100', 'S1'

if you need more info let me know, thank you all soo much for all of your time and effort!

Enjoy Life This Is Not A Rehearsal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 12:39:42

They are two different things. the first one creates the procedure for you and second one excutes it. once you have created the procedure using the first query you can then execute it using the second query.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-08-18 : 13:18:02

Again, the 2nd query can be rewritten to the following if perfered:

EXECUTE OA_END_BASED_ON_ESTIMATE @GETDATE(), '100', 'S1'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 13:48:15

quote:
Originally posted by Van

Again, the 2nd query can be rewritten to the following if perfered:

EXECUTE OA_END_BASED_ON_ESTIMATE @GETDATE(), '100', 'S1'

Typo no need of @ for GETDATE()

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-19 : 03:51:55

quote:
Originally posted by Van

This:

DECLARE @today datetime
SET @today = GETDATE()

EXECUTE OA_END_BASED_ON_ESTIMATE @today, '100', 'S1'

Should be the same as this (which was also a suggestion):

EXECUTE OA_END_BASED_ON_ESTIMATE getdate(), '100', 'S1'

It is not possible to pass system functions directly to the procedure as shown in your second example

Madhivanan

Failing to plan is Planning to fail

jar21

51 Posts

Posted - 2008-08-20 : 08:16:55

Well I've begun using the second, it called the stored proc. that was created with the first long query (I'm almost beginning to catch a glimpse of whats going on here!)

Problem Solved!

Thanks for all of your help again!

Enjoy Life This Is Not A Rehearsal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources