Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 11:52 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:05 AM
Points: 99, Visits: 304
hello

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

please help me out
Post #1511373
Posted Tuesday, November 5, 2013 2:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Nobody can help you here ..we dont know your data and table definition.
PLease post table defintion along with (sample) data to get the result.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1511411
Posted Tuesday, November 5, 2013 3:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 1,090, Visits: 6,542
Fix your join:
left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode

Comment out the date filter and rerun the query.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1511413
Posted Tuesday, November 5, 2013 3:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:05 AM
Points: 99, Visits: 304
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
JV------------------ADVANCE VOUCHER
BP------------------BANK PAYEMENT VOUCHER
JV------------------BANK RECEIVED VOUCHER
PU------------------Client Order
CP------------------CASH PAYMENT VOUCHER

thanks for the help
Post #1511414
Posted Tuesday, November 5, 2013 3:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
First ..fix the LEFT join as chris has asked. another thing where you are getting "NULL" results (in which column)


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1511418
Posted Tuesday, November 5, 2013 3:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:05 AM
Points: 99, Visits: 304
i fix it

and i just want that if i didnt give date parameter and i use voucher no parameter then result gives me result from voucher number


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


thanks for the help
Post #1511419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse