SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 11/04/2013 :  23:44:58  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 11/05/2013 :  00:34:32  Show Profile  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 11/05/2013 :  02:21:55  Show Profile  Reply with Quote

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

Edited by - immad on 11/05/2013 03:12:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2013 :  03:18:20  Show Profile  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 11/05/2013 :  03:25:51  Show Profile  Reply with Quote
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

Edited by - immad on 11/05/2013 03:26:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2013 :  04:21:41  Show Profile  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 11/05/2013 :  05:10:48  Show Profile  Reply with Quote
but the voucher type filed is matching values are also matching i didnt show please suggest me the query
thanks

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2013 :  05:58:21  Show Profile  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 11/05/2013 :  06:03:00  Show Profile  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2013 :  06:05:59  Show Profile  Reply with Quote
so what should be your expected output for these?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 11/05/2013 :  06:50:07  Show Profile  Reply with Quote
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

Edited by - immad on 11/05/2013 06:50:42
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2013 :  08:24:59  Show Profile  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 11/06/2013 :  05:51:58  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2013 :  06:32:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000