How to retrieve boolean column based on drop down list value

i have a form in which i want to show the data based on user's selection..in this form there is a drop down list with items

'posted','drafted','all'. when user select posted i want to retrieve data by condition Voucher status=true, and when user select drafted i want to retrieve data by condition Voucher status=false. and when user select all i want to retrieve data by condition Voucher status=true or false or null.ie in third display all.(dont want to consider voucher status)

Pls help me to solve this query..

my complete query is here..

SELECT VoucherTypes.types, VoucherHead.VoucherNo, VoucherHead.VoucherDate, SUM(VoucherDetail.Debit) AS debit, SUM(VoucherDetail.Credit) AS credit, VoucherHead.VoucherStatus

FROM VoucherHead INNER JOIN VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid INNER JOIN

VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID

WHERE (VoucherHead.VoucherDate >= @fromdate) AND (VoucherHead.VoucherDate <= @todate) AND (VoucherTypes.types = @vtype or @vtype='0') AND (VoucherHead.VoucherStatus = @st) AND (VoucherHead.Branchno = @branchid)

GROUP BY VoucherTypes.types, VoucherHead.VoucherNo, VoucherHead.VoucherDate, VoucherHead.VoucherStatus

here

if (status == 1)

st = true;

if (status == 2)

st = false;

where status=drp.selectedindex;

but this works only selecting 'posted' or 'drafted'.. it also be worked when selecting 'all'...

is this can de done using 'case'..i dont know

Hi in my understadnig if u need all the data when you select all
eg>
declare @st bit = null
if (status == 1)-- 1 is selected value on
@st = true;
if (status == 2)
@st = false;

SELECT VoucherTypes.types
,VoucherHead.VoucherNo
,VoucherHead.VoucherDate
,SUM(VoucherDetail.Debit) AS debit
,SUM(VoucherDetail.Credit) AS credit
,VoucherHead.VoucherStatus
FROM VoucherHead
INNER JOIN VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid
INNER JOIN VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID
WHERE (VoucherHead.VoucherDate >= @fromdate)
AND (VoucherHead.VoucherDate <= @todate)
AND (
VoucherTypes.types = @vtype
OR @vtype = '0'
)
AND (@st is null or VoucherHead.VoucherStatus = @st )
AND (VoucherHead.Branchno = @branchid)
GROUP BY VoucherTypes.types
,VoucherHead.VoucherNo
,VoucherHead.VoucherDate
,VoucherHead.VoucherStatus

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Khan Abrar Ahmed11y ago
Hi in my understadnig if u need all the data when you select all
eg>
declare @st bit = null
if (status == 1)-- 1 is selected value on
@st = true;
if (status == 2)
@st = false;

SELECT VoucherTypes.types
,VoucherHead.VoucherNo
,VoucherHead.VoucherDate
,SUM(VoucherDetail.Debit) AS debit
,SUM(VoucherDetail.Credit) AS credit
,VoucherHead.VoucherStatus
FROM VoucherHead
INNER JOIN VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid
INNER JOIN VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID
WHERE (VoucherHead.VoucherDate >= @fromdate)
AND (VoucherHead.VoucherDate <= @todate)
AND (
VoucherTypes.types = @vtype
OR @vtype = '0'
)
AND (@st is null or VoucherHead.VoucherStatus = @st )
AND (VoucherHead.Branchno = @branchid)
GROUP BY VoucherTypes.types
,VoucherHead.VoucherNo
,VoucherHead.VoucherDate
,VoucherHead.VoucherStatus