Assistance Required for a Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Assistance Required for a Query

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-02-05 : 07:06:26

Hi,

It may be very simple but i could not find the solution. kindly help.

I have written a query(below mentioned) to show no of records of the customer from some origin to some destination for the particualr period.

I want to display the COUNT of the bookings that particular customer have in the result as one my column. For this, i want to check the count from bookings table based on the customer type(AP & NC).

Logic is For a AP customer has multiple NC customer with respective CRA_No as below:
AP_code custname NC_Code Type CRA_No POL POD Validfrom Validtill COUNT
ABC AFSOLUTIONS BBB CRA 1111 CHENNAI DUBAI 2008-12-01 2008-12-18 1
ABC AFSOLUTIONS CCC CRA 1111 CHENNAI DUBAI 2008-12-01 2008-12-18 1
ABC AFSOLUTIONS BBB CRA 2222 CHENNAI DUBAI 2008-12-01 2008-12-18 2
ABC AFSOLUTIONS CCC CRA 2222 CHENNAI DUBAI 2008-12-01 2008-12-18 2

now count column is checking only AP customer (bkc.custid= ctdn.custcode)
i also want to check for NC customer as (bkc.custid= cpc.custcode -- i did not write in the below query)

if i put the both queries together with AND as:

where bkc.custtypeid in ('AP','NC') and
bk.status in ('C') and
bkc.custid= ctdn.custcode AND bkc.custid= cpc.custcode

i am getting above 4 records but COUNT column with zeros.
AP_code custname NC_Code Type CRA_No POL POD Validfrom Validtill COUNT
ABC AFSOLUTIONS BBB CRA 1111 CHENNAI DUBAI 2008-12-01 2008-12-18 0
ABC AFSOLUTIONS CCC CRA 1111 CHENNAI DUBAI 2008-12-01 2008-12-18 0
ABC AFSOLUTIONS BBB CRA 2222 CHENNAI DUBAI 2008-12-01 2008-12-18 0
ABC AFSOLUTIONS CCC CRA 2222 CHENNAI DUBAI 2008-12-01 2008-12-18 0

If i put both queries together with OR as:

where bkc.custtypeid in ('AP','NC') and
bk.status in ('C') and
bkc.custid= ctdn.custcode OR bkc.custid= cpc.custcode
am getting 4 records with 400 each as COUNT.
AP_code custname NC_Code Type CRA_No POL POD Validfrom Validtill COUNT
ABC AFSOLUTIONS BBB CRA 1111 CHENNAI DUBAI 2008-12-01 2008-12-18 400
ABC AFSOLUTIONS CCC CRA 1111 CHENNAI DUBAI 2008-12-01 2008-12-18 400
ABC AFSOLUTIONS BBB CRA 2222 CHENNAI DUBAI 2008-12-01 2008-12-18 400
ABC AFSOLUTIONS CCC CRA 2222 CHENNAI DUBAI 2008-12-01 2008-12-18 400

Kindly assist how to write query to check both AP as well NC for correct COUNT.

QUERY:
select distinct ctdn.custcode as AP_Code,cc.custname,cpc.custcode as NC_Code,'CRA' as typec, ctdn.corporateno as number,
pol as pol, pod as pod,validfrom as valid_from, validto as valid_till,
(select count(*) from booking bk (nolock) left join booking_cust bkc (nolock) on bkc.book_no = bk.book_no
where bkc.custtypeid in ('AP','NC') and
bk.status in ('C') and
bkc.custid= ctdn.custcode and
bk.booktype = 'CRA' and
(typeno = ctdn.corporateno +',' or typeno=ctdn.corporateno) and
loadportid = pol and dischargeportid = pod )as bkgs_cnfrmd into #tmpcra
from corporatedetailn ctdn (nolock)
left outer join corporatecustomer cpc (nolock) on ctdn.corporateno=cpc.corporateno and ctdn.itemno=cpc.itemno
left outer join customermaster cc (nolock) on ctdn.custcode=cc.custcode
where ctdn.statusflag ='A'
and not(ctdn.custcode is null) and (ctdn.custcode = 'ABC')
and ctdn.validfrom >='2008-10-01' and ctdn.validto <= '2009-02-07'
and ctdn.pol='CHENNAI' and ctdn.pod='DUBAI'

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 07:26:38

Try changing the where to this:

...
where
((bkc.custtypeid ='AP' AND bkc.custid= ctdn.custcode)
OR
(bkc.custtypeid ='NC' AND bkc.custid= cpc.custcode))
AND
bk.status in ('C')
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources