May 7, 2010 at 1:09 am
Hi All,
I've a table name consumption_detail
CREATE TABLE [dbo].[cunsumption_detail](
[form38_no] [varchar](15) NULL,
[isused] [bit] NULL,
[used_Date] [datetime] NULL,
[Demand_No] [varchar](14) NULL,
[forConsumptionReport] [bit] NOT NULL
)
Column Information from sp_help
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
form38_novarcharno15 yesnoyesSQL_Latin1_General_CP1_CI_AS
PROBLEM:
I've been trying to get a result set showing me form38_no between AA-14 to AA-110
Used the following query:
DECLARE @form_prefix varchar(5), @form_No1 varchar(10), @form_No2 varchar(10)
SET @form_prefix = 'AA'
SET @form_No1 = 11
SET @form_No2 = 110
Select * FROM cunsumption_detail
WHERE RTRIM(LTRIM(form38_no)) BETWEEN @form_prefix+'-'+@form_No1 and @form_prefix+'-'+@form_No2
AND CAST(REVERSE(LEFT(REVERSE(form38_no),CHARINDEX('-',REVERSE(form38_no))-1)) as INT)>=@form_No1
AND CAST(REVERSE(LEFT(REVERSE(form38_no),CHARINDEX('-',REVERSE(form38_no))-1)) as INT)<=@form_No2
ORDER BY SUBSTRING(form38_no,0,CHARINDEX('-',form38_no)) ASC,
CAST(REVERSE(LEFT(REVERSE(form38_no),CHARINDEX('-',REVERSE(form38_no))-1)) as INT) ASC
Unfortunately, am not getting the desired result. It just brings out two results having AA-11 and AA-110 form numbers.
If I remove my first where condition and then run the query it shows all the results BUT shows some forms having form_prefix of AAM. Sample Result set is as below.
form38_noisusedused_DateDemand_NoforConsumptionReport
AAM-10601/18/10 3:01 PM10189990000140
AAM-10801/18/10 7:56 PM10189990000140
AA-7701/19/10 1:14 PMNULL0
AA-7801/19/10 1:15 PMNULL0
AA-7901/19/10 1:23 PMNULL0
AA-8001/19/10 1:24 PMNULL0
AA-7601/19/10 1:30 PMNULL0
AA-3301/20/10 11:16 AMNULL0
Can someone help me write the correct query which'll fetch me the result set I want.
I want only AA series form numbers between 11 and 110.
SQL Statements have been attached.
Thanks
Ankit Mathur
May 7, 2010 at 3:33 am
This was removed by the editor as SPAM
May 7, 2010 at 4:39 am
Hope this helps...
select * from cunsumption_detail
where
(form38_no like ('AA-[1-9][0-9]') and form38_no not like ('AA-10'))
or
form38_no like ('AA-10[0-9]') and form38_no like ('AA-110')
Cheers
May 7, 2010 at 6:26 am
Thanks Stewart,
Your solution worked like a charm. But if I'm not bothering much can you guide me where I was wrong in my trials. 🙂
Ankur: Thanks to you too. Your solution also worked but I was looking for a more universal query so Stewart's query helps me more.
Thanks to both of you once again.
Ankit Mathur 😀
May 7, 2010 at 7:10 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply