Help with WHERE clause please!

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

Transact-SQL (2012)

Help with WHERE clause please!

Author

Topic

snejsnej
Starting Member

9 Posts

Posted - 2015-04-09 : 02:39:39

Hi,

I need your help with the WHERE clause of my stored procedure. I have included some info into my methodology describing how I got to where I am now, which I hope will help explain what I want to do. My apologies if it's superfluous.

I have a stored procedure into which 2 datetime values are passed from an SSRS report, i.e. 'To' and 'From' dates.

I am formatting the datetime values into a custom date format of yyMMMdd such that (for example) '2015-02-16 07:51:16.000' and '2015-02-25 09:31:33.000' are formatted to '15Feb16' (the 'From' date) and '15Feb25' (the 'To' date):

FORMAT(@dt_DATEOFISSUE, 'yyMMMdd')

I am also manipulating a string in the BatchNum field to do the following:

1) extract a substring between two hyphens, eg '52RA-15Feb16-1' becomes '15Feb16;
2) convert the extracted string to a date;
3) formatting the date to the same format I have above:

	format	(
			convert(date,
						(
						substring(REPLACE(LEFT(SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])),CHARINDEX('-',SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])))),'-',''),6,2) +
						substring(REPLACE(LEFT(SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])),CHARINDEX('-',SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])))),'-',''),3,3) +
						substring(REPLACE(LEFT(SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])),CHARINDEX('-',SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])))),'-',''),1,2)
				),6
		),'yyMMMdd')

So, at this point I have 3 pieces of data that can be compared (I hope): From date, To date, the date taken from BatchNum... all with in the same date format: yyMMMdd.

My conundrum is that I need to pull all records that fall on and between the two dates passed in from the report, i.e. '15Feb16' and '15Feb25', where ever there is a match with the date taken from the BatchNum (let's call it "SubBatchNumDate".

In pseudocode, something like this:

SELECT

BatchNum
,Field2
,Field3
,[etc...]

FROM Table 1 INNER JOIN Table 2

WHERE

SubBatchNumDate >= FromDate AND
SubBatchNumDate =< ToDate

The actual code that I've cobbled together is below. Note that the ridiculous string manipulation in the WHERE clause is slowing the query to a crawl, so any help in optimizing it is greatly appreciated.


--/****** Object:  StoredProcedure [sp_r637_iMIS_Transaction_Amounts]    Script Date: 04/06/2015 18:14:28 ******/
--[sp_r637_iMIS_Transaction_Amounts] '2015-02-16 00:00:00.000', '2015-02-28 00:00:00.000'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [sp_r637_iMIS_Transaction_Amounts]
	-- Add the parameters for the stored procedure here

		 @dt_DATEOFISSUE	datetime
		,@dt_DATEOFISSUE2	datetime

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from.
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT top 10
  
	  dateadd(hour, -7, t.TRANSACTION_DATE) as TransactionDate
	  , substring(t.[BATCH_NUM],1,2) as Centre  
	  , t.batch_num as BatchNumber
	  , t.DESCRIPTION
	  , t.PRODUCT_CODE as ProductCode
      ,	t.AMOUNT
	  , t.ST_ID
	  , d.ID
	  , d.MemberNumber

FROM 
 
  TRANS t inner join NAMA_Demographics d
  on t.ST_ID = d.ID

WHERE
	format(@dt_DATEOFISSUE, 'yyMMMdd') = 
	format	(
			convert(date,
						(
						substring(REPLACE(LEFT(SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])),CHARINDEX('-',SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])))),'-',''),6,2) +
						substring(REPLACE(LEFT(SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])),CHARINDEX('-',SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])))),'-',''),3,3) +
						substring(REPLACE(LEFT(SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])),CHARINDEX('-',SUBSTRING(t.[BATCH_NUM],CHARINDEX('-',t.[BATCH_NUM])+1,LEN(t.[BATCH_NUM])))),'-',''),1,2)
					),6
				),'yyMMMdd') 
	and t.batch_num not like '%bill%'
	and t.batch_num not like '%web%'
	and t.batch_num not like 'Conversion'

ORDER BY t.TRANSACTION_DATE ASC
END

One consideration: I would need to pull records across multiple months, and likely from one year to the next, e.g. '15Dec25' to '16Jan03'

Many thanks. This one is giving me hives.
Cheers,
Jens

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-09 : 10:36:41

first off, here's a simpler expression:


declare @s varchar(20) = '52RA-15Feb16-1'
select left(SUBSTRING(@s, CHARINDEX('-', @s, 1) + 1, len(@s)), 7)

It uses the fact that the length parameter of SUBSTRING can be greater than the length of the string.
It also assumes that 1 feb 2001 will always be 01Feb01 and never 1Feb1. Is that the case?

Second, since your WHERE clauses are not SARGable, it will kill performance. If this is an important, frequent query,
consider adding a computed column to your table in the correct format, index it, then change your where to


WHERE computedcol = <expression>

However, the first two NOT LIKE clauses are problematic, since they are not SARGable and can't be made so

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-09 : 11:18:04

You're not going to be able to pull a range of dates using format:
yyMMMdd

Why are you using such a bizarre format for comparison? Why not just use a date??

At any rate, for what you do have, I would use CROSS APPLY to make the code much more readable by moving all the string manipulation out of the main query:


SELECT BATCH_NUM, BATCH_NUM_DATE --,FORMAT(BATCH_NUM_DATE, 'yyMMMdd')
FROM (
    SELECT '52RA-15Feb16-1' AS BATCH_NUM UNION ALL
    SELECT '52RA-6Feb6-23' UNION ALL
    SELECT '52RA-12Dec14'
) AS t
CROSS APPLY (
    SELECT SUBSTRING(BATCH_NUM, CHARINDEX('-', BATCH_NUM) + 1, 100) AS BATCH_NUM_DATE_AND_REST
) AS assign_alias_names1
CROSS APPLY (
    SELECT CONVERT(date, LEFT(BATCH_NUM_DATE_AND_REST, CHARINDEX('-', BATCH_NUM_DATE_AND_REST + '-') - 1), 6) AS BATCH_NUM_DATE
) AS assign_alias_names2

Kristen
Test

22859 Posts

Posted - 2015-04-09 : 12:59:25

quote:
Originally posted by ScottPletcher

You're not going to be able to pull a range of dates using format:
yyMMMdd

Why are you using such a bizarre format for comparison? Why not just use a date??

I shouldn't have told the O/P how to do that

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=200718
in my defence!! I did give the usual caveat that date comparison wouldn't then work, and it should be done in APP rather than SQL ...

snejsnej
Starting Member

9 Posts

Posted - 2015-04-10 : 12:37:01

Thanks guy, really appreciate your input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources