?
Solved

Select records in a table between a time range

Posted on 2011-04-25
29
Medium Priority
?
339 Views
Last Modified: 2012-05-11
Title:
Select records in a table depending on the time range for the dates
Question: Hi,
      I am using a dynamic SQL query which displays transactions between certain dates. But now I am asked to implement within a certain time range too. But if i add a start time and end time and concatenate the date and  time strings from the web app and pass it as the @fromdate and @todate parameters in the code below, it gives results in such a way that the starting from the start date till end time on the end date.
But i want to modify it in such a way that if i enter my dates as 1/12/2008 - 1/13/2008
and times as 12:00 am - 2:00 am...then it should display results only for those two hours for both the days, rather than displaying from 12:00 am on 12th to 2:00 am on 13th.
How can i modify the query or the c# code. Please help me.
'
		select 
			*
		from  
			'+table_name+'
		where
			td_datetimestamp between coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)
		union'

Open in new window

0
Comment
Question by:WebRules
  • 15
  • 12
  • +1
29 Comments
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35461267
Its not very complicated but SQL server makes it hard to find one true path to getting what you want done.
To achieve what you need done you would need to split your criteria into two: one part testing for a day range, another testing for an hour range.
Taking advantage of the sort order of strings that leads to "1" being lesser than "2", and based on the knowledge that SQL server date and time values are really floats where the integer part is the number of days elapsed since a beginning date (01/01/1900) and the fractional part is the time, expressed in number of hours elapsed since midnight and divided by 24, we can do conversions to get to the values we want. For instance, this formula extracts the time from any date as a string:


select CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, GETDATE()) - FLOOR(CONVERT(FLOAT, getDate()))), 108)

From there you can compare it to another time converted to a varchar.

You can quote hard-coded dates and times by using the "dd-MON-yyyy hh:mm:ssam/pm" format, for instance "01-Jan-1900 01:01:01AM)

CONVERT is your friend. Read all about it here:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

HTH

Philippe
0
 

Author Comment

by:WebRules
ID: 35461337
@ radcaescar : hi, the example you mentioned is just for one day. but what if we have to get for two days within the same time range like my example mentioned in the above question.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:WebRules
ID: 35461342
@damerval: i haven't really understood your solution above.
0
 
LVL 9

Accepted Solution

by:
Philippe Damerval earned 2000 total points
ID: 35461346
For instance, if you wanted to look in the mydate field from @fromdate to @todate for records having their timestamp between @starttime and @endtime, you would write:

WHERE mydate BETWEEN @fromdate AND @todate
AND
CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, mydate) - FLOOR(CONVERT(FLOAT, mydate))), 108)
BETWEEN
CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @starttime) - FLOOR(CONVERT(FLOAT, @starttime))), 108)
AND
CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @endtime) - FLOOR(CONVERT(FLOAT, @endtime))), 108)


0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35461354
Bearing in mind that this is just one way of doing it. The "right" way or most elegant way depends on your specific application.
0
 

Author Comment

by:WebRules
ID: 35461433
oh, ok ..so you say i need to pass the time as different parameters rather than with date? right?
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35461465
Right, split the criteria into 1) select date range and 2) select time of day range within those days). To do the time of day selection you need to use CONVERT in some way, like what I have done there (I went for a string comparison).
0
 

Author Comment

by:WebRules
ID: 35461480
yeah, right now i have start date. end date, start time, end time. but my sql table has just one column td_datetimestamp...which if type 'datetime'.
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35461495
That's fine, replace the name "mydate" in my example above with your field td_datetimestamp.
0
 

Author Comment

by:WebRules
ID: 35461652
ok will try and let you know.
0
 

Author Comment

by:WebRules
ID: 35462062
hi, what should be the datatype pf starttime and endtime paramters?
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35462360
They should be datetime type parameters. You can specify them in SQL server using the "HH:MM:SSAM/PM" format, for instance: '03:30:00PM". You do not need to specify a date as the expression I've copied converts any datetime value to a comparison value that you can use to delimit time ranges within the day.
0
 

Author Comment

by:WebRules
ID: 35462567
i am getting errors. It says..declare scalar variable @fromDate...but i already declared it.

this is the code i am using. and all the four paramters are of type datetime.
'
		select 
			*
		from  
			'+table_name+'
		where
			td_datetimestamp between @fromDate and @toDate
			AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, td_datetimestamp) - FLOOR(CONVERT(FLOAT, td_datetimestamp))), 108)
		BETWEEN
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @starttime) - FLOOR(CONVERT(FLOAT, @starttime))), 108)
		AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @endtime) - FLOOR(CONVERT(FLOAT, @endtime))), 108)
		union'

Open in new window

0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35462675
Where did you declare the variables? the "@" sign precedes a SQL server variable name. You appear to be building a SQL server statement from .Net code (or similar). Perhaps it would be easiest to output the values you need so that they become constants in the SQL statement, perhaps like this:

select
                  *
            from  
                  '+table_name+'
            where
                  td_datetimestamp between '+myFromDate.ToString()+' AND '+myToDate.ToString()+'

... etc , with similar things for each time a value is needed in the SQL statement. Without knowing what your environment/programming language is or your variable names it's hard to recommend an exact expression. Do you understand what I'm trying to convey?
0
 

Author Comment

by:WebRules
ID: 35462819
I have the variables declared as:

@fromDate DateTime = null ,
      @toDate DateTime = null ,
      @starttime datetime = null,
      @endtime datetime = null,

in the stored procedure. and i am executing my stored procedure in sql server and testing it and i am getting that error.
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35462911
Can you please send the exhaustive code for your stored procedure?
Thanks
0
 

Author Comment

by:WebRules
ID: 35462918
exhaustive code?
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35462948
:) I mean the whole code for your stored procedure, including declarations.
0
 

Author Comment

by:WebRules
ID: 35462955
The code is huge. I copied below. The part which we are working on, is at the beginning.
ALTER PROCEDURE [dbo].[SP_Investigations1]
	@id nvarchar(36),
	@StoreName nvarchar(155) = null ,
	@Criteria1 nvarchar(155) = null ,
	@Criteria2 nvarchar(155) = null ,
	@fromDate DateTime = null ,
	@toDate DateTime = null ,
	@starttime datetime = null,
	@endtime datetime = null,
	@reg nvarchar(5) = null ,
	@cashier nvarchar(50) = null ,
	@type nvarchar(50) = null ,
	@evenodd int = 0,
	@cat1 int = 0,
	@cat2 int = 0,
	@exp nvarchar(10) = null ,
	@value nvarchar(50) = null,
	@exp1 nvarchar(10) = null ,
	@value1 nvarchar(50) = null,
	@qty1 nvarchar(50) = null,
	@qty2 nvarchar(50) = null,
	@op_and_or int
	
AS
BEGIN
      
DECLARE 
	@tablename nvarchar(max),
	@sql1 nvarchar(max),
	@sql2 nvarchar(max),
	@sql3 nvarchar(max),
	@ParamDefinition nvarchar(500),
	@ParamDefinition1 nvarchar(500),
	@ParamDefinition2 nvarchar(500),
	@months_list varchar(255),
	@intermediateDate as DateTime

	
if(@StoreName='')
set @StoreName=null;
if(@Criteria1='')
set @Criteria1=null;
if(@Criteria2='')
set @Criteria2=null;
if(@value='')
set @value=null;
if(@value1='')
set @value1=null;
if(@exp='')
set @exp=null;
if(@exp1='')
set @exp1=null;
if(@qty1='')
set @qty1=null;
if(@qty2='')
set @qty2=null;
if(@reg='')
set @reg=null;
if(@cashier='--Select--')
set @cashier=null;
if(@cashier = '')
set @cashier = null;
if(@cat1='0')
set @cat1=null;
if(@cat2='0')
set @cat2=null;
if(@type='')
set @type=null;
if(@evenodd='0')
set @evenodd=null;
if(@type='--Select--')
set @type=null;


-- build a comma sepparated month names list
select 
	@intermediateDate=@fromDate,
	@months_list=','
while 
	 @intermediateDate<@toDate or month(@intermediateDate)=month(@toDate)
begin
	select 
		 @months_list=isnull(@months_list,'')+REPLACE(convert(varchar(7),@intermediateDate, 121),'-','')+',',
		@intermediateDate=dateadd(month,1,@intermediateDate)
end
	
-- /*
print 'From: '+convert(varchar(23), @fromDate, 121)+';
To  : '+convert(varchar(23), @toDate, 121)+';
Month list: '+@months_list+';
GUID: '+@id
--*/

-- build a sub query that will have all the rows from the tables for the given store GUID and for the months list built above
select 
@tablename=isnull(@tablename,'')+
'
		select 
			*
		from  
			'+table_name+'
		where
			td_datetimestamp between @fromDate and @toDate
			AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, td_datetimestamp) - FLOOR(CONVERT(FLOAT, td_datetimestamp))), 108)
		BETWEEN
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @starttime) - FLOOR(CONVERT(FLOAT, @starttime))), 108)
		AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @endtime) - FLOOR(CONVERT(FLOAT, @endtime))), 108)
		union'
from 
	(select distinct table_name from information_schema.columns where @months_list like '%,'+substring(table_name, 17, 6)+',%' and replace(table_name,'_','*') like '%'+replace(@id,'-','*')) tbls 

-- prepare a subquery from the union of the tables above to be used in the big query
select 
	@tablename=
'(
		'+replace(@tablename+'#@$','union#@$','')+'
	) sp'

 --print @tablename	-- this is for test; uncomment to chech the built sting
                    
	set @sql1= N'
select TOP 1000
	tt.sale_type_name as type1,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_store_guid as guid,
	td_receipt_id as id1,
	td_department as dept,
	td_item_id as code,
	td_datetimestamp as date, 
	td_device_number as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join TransactionSaleTypes tt 
		on tt.sale_type_value = convert(nvarchar(20),td_record_type)
where 
	((
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	or
	(
		(td_device_data like ''%'' + coalesce(@Criteria2,td_device_data) + ''%'' AND coalesce(@cat2,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria2,td_item_id) + ''%'' AND coalesce(@cat2,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria2,td_department) + ''%'' AND coalesce(@cat2,''3'')=''3'')
	)) 
	and
	(tt.sale_type_name like coalesce(@type, tt.sale_type_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	(
'+
case 
	when isnull(@value,'')<>'' and isnull(@exp,'')<>'' then 
'
	(
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''2'')
	)
'
	else '1=1'
end
+
'
	)
'
+
case 
	when isnull(@qty1,'')<>'' and  isnull(@exp1,'')<>'' then
'	
	and 
	(
		(td_quantity > @qty1 AND @exp1=''>'') 
		OR (td_quantity < @qty1 AND @exp1=''<'')
		OR (td_quantity >= @qty1 AND @exp1=''>='') 
		OR (td_quantity <= @qty1 AND @exp1=''<='')
		OR (td_quantity = @qty1 AND @exp1=''='') 
		OR (td_quantity between @qty1 AND @qty2 AND @exp1=''between'')
	))
'
	else ' and 1=1)'
end
+
'
Order By 
	td_datetimestamp
'
set @sql2= N'
select TOP 1000
	tt.sale_type_name as type1,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_department as dept,
	td_store_guid as guid,
	td_item_id as code,
	td_datetimestamp as date, 
	td_device_number as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join TransactionSaleTypes tt 
		on tt.sale_type_value = convert(nvarchar(20),td_record_type)
where 
	(
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	and
	(tt.sale_type_name like coalesce(@type, tt.sale_type_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	(
'+
case 
	when isnull(@value,'')<>'' and isnull(@exp,'')<>'' then 
'
	(
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''2'')
	)
'
	else '1=1'
end
+
'
	)
'
+
case 
	when isnull(@qty1,'')<>'' and  isnull(@exp1,'')<>'' then
'	
	and 
	(
		(td_quantity > @qty1 AND @exp1=''>'') 
		OR (td_quantity < @qty1 AND @exp1=''<'')
		OR (td_quantity >= @qty1 AND @exp1=''>='') 
		OR (td_quantity <= @qty1 AND @exp1=''<='')
		OR (td_quantity = @qty1 AND @exp1=''='') 
		OR (td_quantity between @qty1 AND @qty2 AND @exp1=''between'')
	))
'
	else ' and 1=1)'
end
+
'
Order By 
	td_datetimestamp
'
set @sql3= N'
select TOP 1000
	tt.sale_type_name as type1,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_store_guid as guid,
	td_department as dept,
	td_item_id as code,
	td_datetimestamp as date, 
	td_device_number as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join TransactionSaleTypes tt 
		on tt.sale_type_value = convert(nvarchar(20),td_record_type)
where 
	(
	((
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	and 
	(td_receipt_id = ANY(Select td_receipt_id from ' + @tablename +' where 
	(
		(td_device_data like ''%'' + coalesce(@Criteria2,td_device_data) + ''%'' AND coalesce(@cat2,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria2,td_item_id) + ''%'' AND coalesce(@cat2,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria2,td_department) + ''%'' AND coalesce(@cat2,''3'')=''3'')
	)
	)
	))
	or
	((
		(td_device_data like ''%'' + coalesce(@Criteria2,td_device_data) + ''%'' AND coalesce(@cat2,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria2,td_item_id) + ''%'' AND coalesce(@cat2,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria2,td_department) + ''%'' AND coalesce(@cat2,''3'')=''3'')
	)
	and
	(td_receipt_id = ANY(Select td_receipt_id from ' + @tablename +' where 
	(
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	)
	)
	)))
	and
	(tt.sale_type_name like coalesce(@type, tt.sale_type_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	(
'+
case 
	when isnull(@value,'')<>'' and isnull(@exp,'')<>'' then 
'
	(
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''2'')
	)
'
	else '1=1'
end
+
'
	)
'
+
case 
	when isnull(@qty1,'')<>'' and  isnull(@exp1,'')<>'' then
'	
	and 
	(
		(td_quantity > @qty1 AND @exp1=''>'') 
		OR (td_quantity < @qty1 AND @exp1=''<'')
		OR (td_quantity >= @qty1 AND @exp1=''>='') 
		OR (td_quantity <= @qty1 AND @exp1=''<='')
		OR (td_quantity = @qty1 AND @exp1=''='') 
		OR (td_quantity between @qty1 AND @qty2 AND @exp1=''between'')
	))
'
	else ' and 1=1)'
end
+
'
Order By 
	td_datetimestamp
'
 print @sql2	-- this is for test; uncomment to chech the built sting

	set @ParamDefinition= N'
@Criteria1 nvarchar(155),
@Criteria2 nvarchar(155),
@reg nvarchar(5),
@cashier nvarchar(50),
@type nvarchar(50),
@evenodd int,
@cat1 int,
@cat2 int,
@exp nvarchar(10),
@value nvarchar(50),
@exp1 nvarchar(10),
@value1 nvarchar(50),
@qty1 int,
@qty2 int,
@op_and_or int
'
set @ParamDefinition1= N'
@Criteria1 nvarchar(155),
@reg nvarchar(5),
@cashier nvarchar(50),
@type nvarchar(50),
@evenodd int,
@cat1 int,
@cat2 int,
@exp nvarchar(10),
@value nvarchar(50),
@exp1 nvarchar(10),
@value1 nvarchar(50),
@qty1 int,
@qty2 int,
@op_and_or int
'
set @ParamDefinition2= N'
@Criteria1 nvarchar(155),
@reg nvarchar(5),
@cashier nvarchar(50),
@type nvarchar(50),
@evenodd int,
@cat1 int,
@cat2 int,
@exp nvarchar(10),
@value nvarchar(50),
@exp1 nvarchar(10),
@value1 nvarchar(50),
@qty1 int,
@qty2 int,
@op_and_or int
'
-- print @ParamDefinition	 --this is for test; uncomment to chech the built sting

	if(@Criteria2 is NULL)
	BEGIN
		EXEC sp_executesql @sql2, @ParamDefinition1, 
			@Criteria1=@Criteria1,
			@reg=@reg,
			@cashier=@cashier,
			@type=@type,
			@evenodd=@evenodd,
			@cat1= @cat1,
			@cat2 = @cat2,
			@exp=@exp,
			@value=@value,
			@exp1=@exp1,
			@value1=@value1,
			@qty1=@qty1,
			@qty2=@qty2,
			@op_and_or= @op_and_or
	END
	else if (@Criteria2 is not null and @op_and_or = '2')
	BEGIN
	EXEC sp_executesql @sql1, @ParamDefinition, 
			@Criteria1=@Criteria1,
			@Criteria2=@Criteria2,
			@reg=@reg,
			@cashier=@cashier,
			@type=@type,
			@evenodd=@evenodd,
			@cat1= @cat1,
			@cat2 = @cat2,
			@exp=@exp,
			@value=@value,
			@exp1=@exp1,
			@value1=@value1,
			@qty1=@qty1,
			@qty2=@qty2,
			@op_and_or= @op_and_or
	END
	else if (@Criteria2 is not null and @op_and_or = '1')
	BEGIN
	EXEC sp_executesql @sql3, @ParamDefinition, 
			@Criteria1=@Criteria1,
			@Criteria2=@Criteria2,
			@reg=@reg,
			@cashier=@cashier,
			@type=@type,
			@evenodd=@evenodd,
			@cat1= @cat1,
			@cat2 = @cat2,
			@exp=@exp,
			@value=@value,
			@exp1=@exp1,
			@value1=@value1,
			@qty1=@qty1,
			@qty2=@qty2,
			@op_and_or= @op_and_or
	END
END

Open in new window

0
 

Author Comment

by:WebRules
ID: 35463153
hey you there?
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35463202
Yes, I'm here. It's the middle of the day here, and I'm at work, so only limited time to look at things.
I see several things about your SP that raise questions. The first one is @tablename. What can you tell me about that variable, what it's for? I need to understand why you are building a SQL expression from within a SQL stored procedure, instead of using it directly.
0
 

Author Comment

by:WebRules
ID: 35463217
the tablename is the parameter...and we are choosing the table in the database depending on the paramters entered in the web app. using paramters, storename, fromdat and todate.

but now i just need to figure out the date and time part.
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35463337
so @tablename is actually a string built to contain a subquery?
0
 

Author Comment

by:WebRules
ID: 35463338
yes
0
 
LVL 9

Assisted Solution

by:Philippe Damerval
Philippe Damerval earned 2000 total points
ID: 35463415
gotcha. I don't see parentheses around the subquery in the places where it is defined or the places where they are used. That might be a problem. Also, I notice that in your executeSQL statements you do not pass @fromDate and @toDate, so that would explain why you get a message saying that these parameters need to be declared. By the time you run the statement, they are no longer in context. You would need to pass them to the executeSQL instruction as well.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35464856
Select * from Table where date between Startdate and EndDate
0
 

Author Comment

by:WebRules
ID: 35466553
it worked...thank you so much, yeah i did not declare them in executesql..my bad.
0
 

Author Closing Comment

by:WebRules
ID: 35466707
got it worked.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question