Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-11-04 : 23:44:58

Hello

this is my procedure

ALTER procedure [dbo].[vocuher]
@VoucherNo int,
@VoucherType varchar(50),
@FromDate DATETIME,
@ToDate DATETIME
as
begin
select vt.voucherTypeCode,vt.VoucherType,vm.VoucherNo,vm.VoucherDate,vm.Narration
from VoucherType vt
left join VoucherMaster vm on vt.voucherTypeCode = vm.voucherTypeCode
left join VoucherDetail vd on vm.voucherTypeCode = vd.voucherTypeCode
where
vm.VoucherDate BETWEEN @FromDate and @ToDate
AND (ISNULL(@VoucherType,'')='' OR vt.VoucherType = @VoucherType)
AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)
end

i am getting problem the problem is when i execute from date its giving me the result but when i give vouchertype parameter aur voucher number parameter its giving me null result

please help me out thanks

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 00:34:32

try


ALTER procedure [dbo].[vocuher]
@VoucherNo int,
@VoucherType varchar(50),
@FromDate DATETIME,
@ToDate DATETIME
as
begin
select vt.voucherTypeCode,vt.VoucherType,vm.VoucherNo,vm.VoucherDate,vm.Narration
from VoucherType vt 
left join VoucherMaster vm on vt.voucherTypeCode = vm.voucherTypeCode
and vm.VoucherDate BETWEEN @FromDate and @ToDate
AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)
left join VoucherDetail vd on vm.voucherTypeCode = vd.voucherTypeCode
where  (ISNULL(@VoucherType,'')='' OR vt.VoucherType = @VoucherType)
end

Unless you do like above the LEFT JOIN doesnt make any sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-11-05 : 02:21:55

i fix the query but i am getting problem after where clause

select vm.VoucherTypeCode,vm.VoucherNo,vm.VoucherDate,vm.Narration, vt.VoucherType
from VoucherMaster vm
left join VoucherDetail vd on vm.VoucherTypeCode = vd.VoucherTypeCode
left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode
where
vm.VoucherDate BETWEEN @FromDate and @ToDate
AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

i am getting problem in this row AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

when i search from voucherno its give me null result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 03:18:20

quote:
Originally posted by immad

i fix the query but i am getting problem after where clause

select vm.VoucherTypeCode,vm.VoucherNo,vm.VoucherDate,vm.Narration, vt.VoucherType
from VoucherMaster vm
left join VoucherDetail vd on vm.VoucherTypeCode = vd.VoucherTypeCode
left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode
where
vm.VoucherDate BETWEEN @FromDate and @ToDate
AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

i am getting problem in this row AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

when i search from voucherno its give me null result

show us how your data is. Otherwise we cant understand why its not giving you any result.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-11-05 : 03:25:51

my data is like this

1st table VoucherMaster

VoucherTypeCode---VoucherNo-----VoucherDate----Narration
JV----------------13140001------2013-03-26-----Sale Summary Closed.
JV----------------13140002------2013-03-26-----Sale Summary Closed.
JV----------------13140003------2013-03-28-----Stock Transfer
JV----------------13140004------2013-03-28-----Sale Summary Closed.

----------------------------------
2nd table VoucherDetail

VoucherTypeCode----VoucherNo
PU-----------------13140001
PU-----------------13140001
PU-----------------13140001
PU-----------------13140002
PU-----------------13140002

------------------------------------
3rd table VoucherType

VoucherTypeCode-----VoucherType
AD------------------ADVANCE VOUCHER
BP------------------BANK PAYEMENT VOUCHER
BR------------------BANK RECEIVED VOUCHER
CO------------------Client Order
CP------------------CASH PAYMENT VOUCHER

this is the query

select
vm.VoucherTypeCode,
vd.VoucherNo,
vm.VoucherDate,
vm.Narration,
vt.VoucherType
from VoucherMaster vm
left join VoucherDetail vd on vm.VoucherTypeCode = vd.VoucherTypeCode
left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode
where
vm.VoucherDate BETWEEN @FromDate and @ToDate
AND vm.VoucherNo = @VoucherNo
thanks 4 the help

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 04:21:41

As per your sample data there's no matching VoucherType values between the three tables. So it wont give you any matches.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-11-05 : 05:10:48

but the voucher type filed is matching values are also matching i didnt show please suggest me the query
thanks

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 05:58:21

quote:
Originally posted by immad

but the voucher type filed is matching values are also matching i didnt show please suggest me the query
thanks

immad uddin ahmed

show me some examples of that
I dont have the time to play guessing game

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-11-05 : 06:03:00

example of data

VoucherTypeCode---VoucherNo-----VoucherDate----Narration
JV----------------13140001------2013-03-26-----Sale Summary Closed.
JV----------------13140002------2013-03-26-----Sale Summary Closed.
JV----------------13140003------2013-03-28-----Stock Transfer
JV----------------13140004------2013-03-28-----Sale Summary Closed.
PU----------------13140001------2013-03-27-----Stock Transfer

----------------------------------
2nd table VoucherDetail

VoucherTypeCode----VoucherNo
PU-----------------13140001
PU-----------------13140001
JV-----------------13140001
PU-----------------13140002
JV-----------------13140002

------------------------------------
3rd table VoucherType

VoucherTypeCode-----VoucherType
JV------------------ADVANCE VOUCHER
BP------------------BANK PAYEMENT VOUCHER
JV------------------BANK RECEIVED VOUCHER
PU------------------Client Order
CP------------------CASH PAYMENT VOUCHER

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 06:05:59

so what should be your expected output for these?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-11-05 : 06:50:07

expected result will be like this
i use VoucherDate parameter from 2013-03-26 to 2013-03-27
VoucherTypeCode-------VoucherType------------------VoucherNo------VoucherDate--------------------Narration
JV--------------------ADVANCE VOUCHER--------------13140001-------2013-03-26 00:00:00.000--------Sale Summary Closed.
JV--------------------BANK RECEIVED VOUCHER--------13140001-------2013-03-26 00:00:00.000--------Sale Summary Closed.
JV--------------------JOURNAL VOUCHER--------------13140001-------2013-03-26 00:00:00.000--------Sale Summary Closed.
PU--------------------JOURNAL VOUCHER--------------13140001-------2013-03-27 00:00:00.000--------Sale Summary Closed.

if i use voucherNo paramter then result should be like this
VoucherTypeCode-------VoucherType------------------VoucherNo------VoucherDate--------------------Narration
JV--------------------JOURNAL VOUCHER--------------13140001------2013-03-26 00:00:00.000-------Sale Summary Closed.
JV--------------------JOURNAL VOUCHER--------------13140001------2013-03-26 00:00:00.000-------Sale Summary Closed.
PU--------------------JOURNAL VOUCHER--------------13140001-------2013-03-27 00:00:00.000--------Sale Summary Closed.
CP--------------------CASH PAYMENT VOUCHER---------13140001------2013-03-28 00:00:00.000-------CASH PAYMENT VOUCHER

i hope u understand.

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 08:24:59

where did you get JOURNAL VOUCHER? i cant even see such a value in your sample data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-11-06 : 05:51:58

why u want to know that where journal vocuher value come i just ask a simple query that if a user want to search from date then he use date parameter and if he wants to search from a voucher number not from a date then he use voucher number parameter.

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 06:32:36

quote:
Originally posted by immad

why u want to know that where journal vocuher value come i just ask a simple query that if a user want to search from date then he use date parameter and if he wants to search from a voucher number not from a date then he use voucher number parameter.

immad uddin ahmed

thats what i gave solution long before. then you responde thts not what you expected.
So unless i get more info how can i suggest something.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources