?
Solved

CASE STATEMENT IN QUERY

Posted on 2006-06-02
10
Medium Priority
?
265 Views
Last Modified: 2009-12-16
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,OH.iSignerAddrTypID) 'iAddressTypeId',
                  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.dtInsertDate, isnull(@StartDate, IH.dtInsertDate)) <=  0 and
                  datediff(dd,IH.dtInsertDate, isnull(@EndDate, IH.dtInsertDate)) >=  0 and
            
                  /* 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=BalanceAmount if @NoPaymentFlag is selected otherwise ignore */
                  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'  )))



0
Comment
Question by:anwarmir
  • 4
  • 3
  • 3
10 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16816383
you want NOT EXISTS....

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'  )))

0
 
LVL 10

Expert Comment

by:StephenCairns
ID: 16816415
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'))
0
 
LVL 10

Expert Comment

by:StephenCairns
ID: 16816463
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'))

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:StephenCairns
StephenCairns earned 400 total points
ID: 16816472
once more  wiht feeling
and  (@bNoPayments = 0 or (@bNoPayments = 1 and  invoicenumber  in
   (select invoicenumber from invoice
   where invoicenumber not in (select invoicenumber from capaymentdetails )
   union
   select invoicenumber  from invoice
      where invoicenumber in
          (select invoicenumber  from capaymentdetails where status ='A' and
           invoicenumber  not in ( select invoicenumber  from capaymentdetails where status <> 'A'))
0
 

Author Comment

by:anwarmir
ID: 16816931
Thanks People..Let me try these suggestions out and i will get back to you
Cheers
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16817430
ps since ID is a SqlServer reserved word i'd avoid using it as a Table Alias...
0
 

Author Comment

by:anwarmir
ID: 16817580
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' ))))
 
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1600 total points
ID: 16822289
remove the invoicelinenumber criteria then...

sorry i now also see that the test can just be directly done against the capaymentdetails table...


and  (@bNoPayments = 0 or (@bNoPayments = 1 and
 NOT exists (select pd.iInvoicenumber
                    from capaymentdetails pd
                  where  pd.iInvoiceNumber = id.iInvoiceNumber
                      and  pd.chStatus <> 'A'
                    )
       ))

0
 

Author Comment

by:anwarmir
ID: 16822656
Thanks lowfatspread i will try this on monday
0
 

Author Comment

by:anwarmir
ID: 16843696
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question