Clicky

I am trying to convert a SQL script to what (for all appearances) seems to be a more efficient version.  But I am not quite getting it.  Our current SQL Query is:
 USE [eDox] GO /****** Object:  StoredProcedure [dbo].[usp_GetUserActivityLog]    Script Date: 12/01/2011 08:20:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[usp_GetUserActivityLog] ( 	@UserID		varchar(4), 	@sDate		datetime, 	@eDate		datetime ) AS 	IF @UserID IS NULL AND @sDate IS NOT NULL AND @eDate IS NOT NULL 		SELECT UsrNumber, TrackingID, WebPage, ActivityDate 		FROM TrackUsrAction 		WHERE ActivityDate BETWEEN @sDate AND @eDate  		ORDER BY ActivityDate ASC  	IF @UserID IS NULL AND @sDate IS NOT NULL AND @eDate IS NULL 		SELECT UsrNumber, TrackingID, WebPage, ActivityDate 		FROM TrackUsrAction 		WHERE ActivityDate >= @sDate  		ORDER BY ActivityDate ASC  	IF @UserID IS NULL AND @eDate IS NOT NULL AND @sDate IS NULL 		SELECT UsrNumber, TrackingID, WebPage, ActivityDate 		FROM TrackUsrAction 		WHERE ActivityDate <= @eDate  		ORDER BY ActivityDate ASC  	IF @UserID IS NOT NULL AND @eDate IS NOT NULL AND @sDate IS NULL 		SELECT UsrNumber, TrackingID, WebPage, ActivityDate 		FROM TrackUsrAction 		WHERE UserID = @UserID AND ActivityDate <= @eDate  		ORDER BY ActivityDate ASC  	IF @UserID IS NOT NULL AND @sDate IS NOT NULL AND @eDate IS NULL 		SELECT UsrNumber, TrackingID, WebPage, ActivityDate 		FROM TrackUsrAction 		WHERE UserID = @UserID AND ActivityDate >= @sDate  		ORDER BY ActivityDate ASC  	IF @UserID IS NOT NULL AND @sDate IS NULL AND @eDate IS NULL 		SELECT UsrNumber, TrackingID, WebPage, ActivityDate 		FROM TrackUsrAction 		WHERE UserID = @UserID  		ORDER BY ActivityDate ASC  	IF @UserID IS NOT NULL AND @sDate IS NOT NULL AND @eDate IS NOT NULL 		SELECT UsrNumber, TrackingID, WebPage, ActivityDate 		FROM TrackUsrAction 		WHERE (UserID = @UserID OR @UserID IS NULL) AND ActivityDate BETWEEN @sDate AND @eDate  		ORDER BY ActivityDate ASC                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 

Select allOpen in new window

Here is my failed attempt to make this script more efficient:
 USE [eDox] GO /****** Object:  StoredProcedure [dbo].[usp_GetUserActivityLog]    Script Date: 12/01/2011 08:20:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[usp_GetUserActivityLog] ( 	@UserID		varchar(4), 	@sDate		datetime, 	@eDate		datetime ) AS 	SELECT UsrNumber, TrackingID, WebPage, ActivityDate 	FROM TrackUsrAction 	WHERE 		CASE WHEN @UserID IS NULL 			THEN 				CASE WHEN @sDate IS NULL AND @eDate IS NOT NULL 					THEN ActivityDate <= @eDate 				CASE WHEN @sDate IS NOT NULL AND @eDate IS NULL 					THEN ActivityDate >= @sDate 				CASE WHEN @sDate IS NOT NULL AND @eDate IS NOT NULL 					THEN ActivityDate BETWEEN @sDate AND @eDate 			ELSE 				CASE WHEN @sDate IS NULL AND @eDate IS NOT NULL 					THEN UsrNumber = @UserID AND ActivityDate <= @eDate 				CASE WHEN @sDate IS NOT NULL AND @eDate IS NULL 					THEN UsrNumber = @UserID AND ActivityDate >= @sDate 				CASE WHEN @sDate IS NOT NULL AND @eDate IS NOT NULL 					THEN UsrNumber = @UserID AND ActivityDate BETWEEN @sDate AND @eDate  	ORDER BY ActivityDate ASC                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 

Select allOpen in new window

Any assistance would be most appreciated.

-saige-

asked 12/01/2011 01:57

it_saige's gravatar image

it_saige ♦♦


9 Answers:
Actually your original is more efficient.

What you can do though is this
1:
2:
3:
4:
5:
6:
SELECT UsrNumber, TrackingID, WebPage, ActivityDate
FROM TrackUsrAction
WHERE 	(@UserID is null or UserID = @UserID) and
	(@eDate is null or ActivityDate <= @eDate) and
	(@sDate is null or ActivityDate >= @sDate)
ORDER BY ActivityDate ASC
link

answered

ralmada's gravatar image

ralmada

this is what you need

SELECT UsrNumber, TrackingID, WebPage, ActivityDate
FROM TrackUsrAction
WHERE      (            @UserID IS NULL      
                  OR      UserID = @UserID
            )
      AND      (            (            (@sDate IS NULL AND @eDate IS NULL)
                              OR      ActivityDate BETWEEN @sDate AND @eDate                        
                        )
                  OR      (            @eDate      IS NULL
                              AND      ActivityDate >= @sDate
                        )
                  OR      (            @sDate      IS NULL
                              AND      ActivityDate <= @eDate
                        )
            )
      
ORDER BY ActivityDate ASC
link

answered 2011-12-01 at 10:15:49

penyCuicas's gravatar image

penyCuicas

ralmada's approach is what I would use myself.
link

answered 2011-12-01 at 10:28:22

Qlemo's gravatar image

Qlemo

Peny,

You rock!!!  That was exactly the behaviour I was looking for.

-saige-
link

answered 2011-12-01 at 10:46:33

it_saige's gravatar image

it_saige

Moderator:  I just noticed that I accepted the wrong solution.  Could you please swith accepted to comment by penyCuicas.

Thank you

-saige-
link

answered 2011-12-01 at 10:54:27

it_saige's gravatar image

it_saige

Thank you Qlemo.

-saige-
link

answered 2011-12-01 at 10:57:22

it_saige's gravatar image

it_saige

>>You rock!!!  That was exactly the behaviour I was looking for.<<

hmmm, I'm sorry, but have you checked my comment? I have provided a valid solution and more efficient than Penny's

link

answered 2011-12-01 at 10:59:01

ralmada's gravatar image

ralmada

When I initially read Ralmada's response I skipped over it because the proposed query looked like a query that we had initially attempted before moving onto the if..then's that were used.  This caused me to initially attempt peny's query that produced the intended behaviour.  It was only after I had accepted peny's answer and saw the other response calling to use Ralmada's query that I attempted the query submitted by Ralmada.

When I use Ralmada's query the same results as proposed by peny's query are achieved.

I personally have no problem with accepting either or both proposed solutions.

-saige-
link

answered 2011-12-01 at 11:51:53

it_saige's gravatar image

it_saige

Thank it_saige for taking the time in clarifying.

Can we say that a split between http:#a37221646 and http:#a37221728 is a fair disposition?
link

answered 2011-12-05 at 13:25:31

ralmada's gravatar image

ralmada

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×17
×1
×132

Asked: 12/01/2011 01:57

Seen: 295 times

Last updated: 12/16/2011 06:38