anwarmir
asked on
CASE STATEMENT IN QUERY
Hi I am trying to Modify a query so to return any invoices that have had no payments against them. Payments are stored in a table called capaymentdetails and are linked to a the invoice table by columns iinvoicenumber and iinvoicelinenumber. What i need to do is return any invoices that do not exists in the capaymentdetails table or where ALL the lines in capaymentdetails are set to status "A". I have made some modifications but cant get them to work firstly I am no getting the invoices that do not exist in capaymentdetails and secondly I am getting some invoices back in my resultset but some of the invoicelines are set to status "A". Hope this make sense. Appreciate if some one Can help me on This Thanks.
HERE IS THE LOGIC THAT I ADDED TO THE QUERY IN THE WHERE CLAUSE:
and (@bNoPayments = 0 or (@bNoPayments = 1 and exists(select distinct id1.iInvoicenumber from ominvoicedetail id1
left join capaymentdetails pd
on(id1.iInvoiceNumber = pd.iInvoiceNumber and id1.iInvoiceLineNumber = pd.iInvoiceLineNumber)
where id1.iInvoiceNumber = id.iInvoiceNumber and id1.iInvoiceLineNumber = id.iInvoiceLineNumber and pd.chStatus <> 'A' )))
HERE IS THE FULL QUERY WITH MY MODIFICATION IN THE WHERE CLASUSE:
select distinct
isnull(@ShowCd, OH.chShowCd) as 'chShowCd',
isnull(@ShowYear, OH.iShowYear) as 'iShowYear',
isnull(OH.iBillerId, OH.iSignerId) as 'iIndividualId',
isnull(OH.iBillerAddrTypID
ID.iInvoiceNumber,
OH.iOwnerId,
C.vchCompanyName,
C.chCountryCode
---into #Invoices
from omOrderHeader OH
inner join omInvoiceDetail ID on OH.iOrderNumber = ID.iOrderNumber
inner join company C on OH.iOwnerId = C.iCompanyId
inner join omInvoiceHeader IH on ID.iInvoiceNumber = IH.iInvoiceNumber
where
/* If @ShowCd and @ShowYear are not passed, select ALL shows and years */
OH.chShowCd = isnull(@ShowCd, OH.chShowCd) and
OH.iShowYear = isnull(@ShowYear, OH.iShowYear) and
/* If @StartDate and @EndDate not passed, select ALL date ranges */
datediff(dd,IH.dtInsertDat
datediff(dd,IH.dtInsertDat
/* This next bit was in the previous SP and defaults the invoice line number to 1 when @StartDate and @EndDate are passed */
/* Not sure why, but to keep the results the same I've added the logic */
ID.iInvoiceLineNumber = case isnull(@StartDate, IH.dtInsertDate) when IH.dtInsertDate then ID.iInvoiceLineNumber else
case isnull(@EndDate, IH.dtInsertDate) when IH.dtInsertDate then ID.iInvoiceLineNumber else 1 end
end and
/* Check InvoiceAmount=BalanceAmoun
ID.mInvoiceAmount >= case @NoPaymentFlag when 0 then ID.mInvoiceAmount else ID.mBalanceAmount end and --MODIFYIED 03 Oct 2005 Ahmed Moghal CSD LOG #6894 was "="
/*BEGIN INSERT 03 Oct 2005 Ahmed Moghal CSD LOG #6894 sp should return invoices with outstanding balances when @NoPaymentFlag =1 and not*/
--check balance amount if @NoPaymentFlag = 1 then check for positive balance, else ensure all balances are bought back by making sure condition is not met
ID.mBalanceAmount <> case @NoPaymentFlag when 0 then mBalanceAmount + 1 else 0 end and
/*END INSERT 03 Oct 2005 Ahmed Moghal CSD LOG #6894 sp should return invoices with outstanding balances when @NoPaymentFlag =1 and not*/
/* Check InvoiceAmount is not zero if @NoPaymentFlag is selected otherwise make sure condition is never met */
ID.mInvoiceAmount <> case @NoPaymentFlag when 0 then ID.mInvoiceAmount + 1 else 0 end and
/* Set the range to ALL if not passed as parameters */
ID.iInvoiceNumber between isnull(@StartNum, ID.iInvoiceNumber) and
isnull(@EndNum, ID.iInvoiceNumber) and
(@bNoPayments = 0 or (@bNoPayments = 1 and exists( select distinct id1.iInvoicenumber
from ominvoicedetail id1
--inner join omInvoiceDetail id
--on(id1.iInvoiceNumber = id.iInvoiceNumber and id1.iInvoiceLineNumber = id.iInvoiceLineNumber)
left join capaymentdetails pd
on(id1.iInvoiceNumber = pd.iInvoiceNumber and id1.iInvoiceLineNumber = pd.iInvoiceLineNumber)
where id1.iInvoiceNumber = id.iInvoiceNumber and id1.iInvoiceLineNumber = id.iInvoiceLineNumber and pd.chStatus <> 'A' )))
HERE IS THE LOGIC THAT I ADDED TO THE QUERY IN THE WHERE CLAUSE:
and (@bNoPayments = 0 or (@bNoPayments = 1 and exists(select distinct id1.iInvoicenumber from ominvoicedetail id1
left join capaymentdetails pd
on(id1.iInvoiceNumber = pd.iInvoiceNumber and id1.iInvoiceLineNumber = pd.iInvoiceLineNumber)
where id1.iInvoiceNumber = id.iInvoiceNumber and id1.iInvoiceLineNumber = id.iInvoiceLineNumber and pd.chStatus <> 'A' )))
HERE IS THE FULL QUERY WITH MY MODIFICATION IN THE WHERE CLASUSE:
select distinct
isnull(@ShowCd, OH.chShowCd) as 'chShowCd',
isnull(@ShowYear, OH.iShowYear) as 'iShowYear',
isnull(OH.iBillerId, OH.iSignerId) as 'iIndividualId',
isnull(OH.iBillerAddrTypID
ID.iInvoiceNumber,
OH.iOwnerId,
C.vchCompanyName,
C.chCountryCode
---into #Invoices
from omOrderHeader OH
inner join omInvoiceDetail ID on OH.iOrderNumber = ID.iOrderNumber
inner join company C on OH.iOwnerId = C.iCompanyId
inner join omInvoiceHeader IH on ID.iInvoiceNumber = IH.iInvoiceNumber
where
/* If @ShowCd and @ShowYear are not passed, select ALL shows and years */
OH.chShowCd = isnull(@ShowCd, OH.chShowCd) and
OH.iShowYear = isnull(@ShowYear, OH.iShowYear) and
/* If @StartDate and @EndDate not passed, select ALL date ranges */
datediff(dd,IH.dtInsertDat
datediff(dd,IH.dtInsertDat
/* This next bit was in the previous SP and defaults the invoice line number to 1 when @StartDate and @EndDate are passed */
/* Not sure why, but to keep the results the same I've added the logic */
ID.iInvoiceLineNumber = case isnull(@StartDate, IH.dtInsertDate) when IH.dtInsertDate then ID.iInvoiceLineNumber else
case isnull(@EndDate, IH.dtInsertDate) when IH.dtInsertDate then ID.iInvoiceLineNumber else 1 end
end and
/* Check InvoiceAmount=BalanceAmoun
ID.mInvoiceAmount >= case @NoPaymentFlag when 0 then ID.mInvoiceAmount else ID.mBalanceAmount end and --MODIFYIED 03 Oct 2005 Ahmed Moghal CSD LOG #6894 was "="
/*BEGIN INSERT 03 Oct 2005 Ahmed Moghal CSD LOG #6894 sp should return invoices with outstanding balances when @NoPaymentFlag =1 and not*/
--check balance amount if @NoPaymentFlag = 1 then check for positive balance, else ensure all balances are bought back by making sure condition is not met
ID.mBalanceAmount <> case @NoPaymentFlag when 0 then mBalanceAmount + 1 else 0 end and
/*END INSERT 03 Oct 2005 Ahmed Moghal CSD LOG #6894 sp should return invoices with outstanding balances when @NoPaymentFlag =1 and not*/
/* Check InvoiceAmount is not zero if @NoPaymentFlag is selected otherwise make sure condition is never met */
ID.mInvoiceAmount <> case @NoPaymentFlag when 0 then ID.mInvoiceAmount + 1 else 0 end and
/* Set the range to ALL if not passed as parameters */
ID.iInvoiceNumber between isnull(@StartNum, ID.iInvoiceNumber) and
isnull(@EndNum, ID.iInvoiceNumber) and
(@bNoPayments = 0 or (@bNoPayments = 1 and exists( select distinct id1.iInvoicenumber
from ominvoicedetail id1
--inner join omInvoiceDetail id
--on(id1.iInvoiceNumber = id.iInvoiceNumber and id1.iInvoiceLineNumber = id.iInvoiceLineNumber)
left join capaymentdetails pd
on(id1.iInvoiceNumber = pd.iInvoiceNumber and id1.iInvoiceLineNumber = pd.iInvoiceLineNumber)
where id1.iInvoiceNumber = id.iInvoiceNumber and id1.iInvoiceLineNumber = id.iInvoiceLineNumber and pd.chStatus <> 'A' )))
I think I'd start afresh.
looks like you code is trying to do too many things
The only way i can think of doing this is in 2 parts
first fint the invoices that do not have a record in capaymentdetails
then union this to the records where all the records from capaymentdetails are status A
something like this
select < your fields> from invoice
where invoicenumber not in (select iinvoicelinenumber from capaymentdetails )
union
select < your fields> from invoice
where invoicenumber in
(select invoicenumber from capaymentdetails where status ='A' and
invoicenumber not in (
select invoicenumber from capaymentdetails where status <> 'A'))
looks like you code is trying to do too many things
The only way i can think of doing this is in 2 parts
first fint the invoices that do not have a record in capaymentdetails
then union this to the records where all the records from capaymentdetails are status A
something like this
select < your fields> from invoice
where invoicenumber not in (select iinvoicelinenumber from capaymentdetails )
union
select < your fields> from invoice
where invoicenumber in
(select invoicenumber from capaymentdetails where status ='A' and
invoicenumber not in (
select invoicenumber from capaymentdetails where status <> 'A'))
just spotted a mistake in that
Or if you must
and (@bNoPayments = 0 or (@bNoPayments = 1 and invoicenumber in (select invoicenumber from invoice
where invoicenumber not in (select invoicenumber from capaymentdetails )
union
select < your fields> from invoice
where invoicenumber in
(select invoicenumber from capaymentdetails where status ='A' and
invoicenumber not in (
select invoicenumber from capaymentdetails where status <> 'A'))
Or if you must
and (@bNoPayments = 0 or (@bNoPayments = 1 and invoicenumber in (select invoicenumber from invoice
where invoicenumber not in (select invoicenumber from capaymentdetails )
union
select < your fields> from invoice
where invoicenumber in
(select invoicenumber from capaymentdetails where status ='A' and
invoicenumber not in (
select invoicenumber from capaymentdetails where status <> 'A'))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks People..Let me try these suggestions out and i will get back to you
Cheers
Cheers
ps since ID is a SqlServer reserved word i'd avoid using it as a Table Alias...
ASKER
Hi LowFatSpread
I tried your suggestion and i Got back invoices with some lines as non 'A' However there were still some other lines on the invoice still set to status 'A' I only want invoices where none of the lines for that invoice are "A"
Hi Stephen,
I tried your suggestion modified the query a bit:
The first part of the union worked fine but the second part to retieve any invoicelines not status 'A' just took ages to run so i had to cancel the query the capaymentdetails lines contains millions of rows
and (@bNoPayments = 0 or (@bNoPayments = 1 and id.iinvoicenumber in
(select iinvoicenumber from ominvoicedetail
where iinvoicenumber not in (select iinvoicenumber from capaymentdetails )
union
select id.iinvoicenumber from ominvoicedetail
where iinvoicenumber in
(select iinvoicenumber from capaymentdetails where chstatus <>'A' ))))
I tried your suggestion and i Got back invoices with some lines as non 'A' However there were still some other lines on the invoice still set to status 'A' I only want invoices where none of the lines for that invoice are "A"
Hi Stephen,
I tried your suggestion modified the query a bit:
The first part of the union worked fine but the second part to retieve any invoicelines not status 'A' just took ages to run so i had to cancel the query the capaymentdetails lines contains millions of rows
and (@bNoPayments = 0 or (@bNoPayments = 1 and id.iinvoicenumber in
(select iinvoicenumber from ominvoicedetail
where iinvoicenumber not in (select iinvoicenumber from capaymentdetails )
union
select id.iinvoicenumber from ominvoicedetail
where iinvoicenumber in
(select iinvoicenumber from capaymentdetails where chstatus <>'A' ))))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks lowfatspread i will try this on monday
ASKER
Thanks Guys Used this as the final query
and (@bNoPayments = 0 or (@bNoPayments = 1 and
NOT exists ( select pd.iInvoicenumber
from capaymentdetails pd
where pd.iInvoiceNumber = id.iInvoiceNumber
and pd.chStatus = 'A' ) ))
modified pd.chStatus <> 'A' to pd.chStatus = 'A' to get all invoices where status not equal to A:
Thanks
Anwar
and (@bNoPayments = 0 or (@bNoPayments = 1 and
NOT exists ( select pd.iInvoicenumber
from capaymentdetails pd
where pd.iInvoiceNumber = id.iInvoiceNumber
and pd.chStatus = 'A' ) ))
modified pd.chStatus <> 'A' to pd.chStatus = 'A' to get all invoices where status not equal to A:
Thanks
Anwar
and (@bNoPayments = 0 or (@bNoPayments = 1 and NOT exists (select id1.iInvoicenumber
from ominvoicedetail id1
left join capaymentdetails pd
on id1.iInvoiceNumber = pd.iInvoiceNumber
and id1.iInvoiceLineNumber = pd.iInvoiceLineNumber
where id1.iInvoiceNumber = id.iInvoiceNumber
and id1.iInvoiceLineNumber = id.iInvoiceLineNumber
and pd.chStatus <> 'A' )))