Incorrect query output - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Incorrect query output

Author

Topic

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-22 : 06:35:29

I have three tables Vendor, Invoice, Payment
Vendor contains vendorname and vendottype, vendorid
invoice contains invoiceid, vendorid
payment contains paymentid, vendorid

I need the following o/p

Vendor Name, Vendor Type , Invoices Uploaded, payments uploaded

Hint -
No of Invoices = count of invoices uploaded
No of payment date = count of payments uploaded

I wrote the following query however i m not getting the correct number of records as o/p

select v.VendorName,v.VendorType,count(invoiceid) as Invoices_uploaded,count(paymentdate) as Payments_Updated
FROM Vendor v
JOIN invoice inv on v.VendorID= inv.VendorID
JOIN payment pt on pt.vendorid = v.vendorid
GROUP BY v.VendorName,v.VendorType

Please let me know where i m doing wrong

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 07:25:07

You need to show us some sample data and explain how tables are related for giving an accurate soln.

ma.voice
Starting Member

12 Posts

Posted - 2008-08-22 : 19:14:50

Hi,

You query is looking for multiple related answers. You can either use inline view OR Common table expression. For example

select v.VendorName,v.VendorType,count_invoice as Invoices_uploaded,count_paymentdate as Payments_Updated
FROM Vendor v
JOIN (select vendorid,count(1) as count_invoice from invoice group by vendorid) AS inv
on v.VendorID= inv.VendorID
JOIN (select vendorid,count(1) as count_paymentdate from payment group by vendorid) AS pt
on pt.vendorid = v.vendorid

You can implement similar logic with CTE.

cheers

Silent Voice
Bill Gates, MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 01:13:13

One reason could be because your Payment contains more than 1 record existing for same invoice.

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-23 : 01:46:50

Thanks Vikash and ma.voice that solves my problem.

Regards

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-25 : 06:58:06

Hi...

I want to pull records for a specific date range ... Please let me know how can i use where clause with this query ..

select v.VendorName,v.VendorType,count_invoice as Invoices_uploaded,count_paymentdate as Payments_Updated
FROM Vendor v
JOIN (select vendorid,count(1) as count_invoice from invoice group by vendorid) AS inv
on v.VendorID= inv.VendorID
JOIN (select vendorid,count(1) as count_paymentdate from payment group by vendorid) AS pt
on pt.vendorid = v.vendorid

When i am trying
where pt.paymentdate >= '01-8-2008' and pt.paymentdate < '20-8-2008'

i am getting an error paymentdate .. Invalid column. However paymentdate exists under payment table.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 07:20:56

the error is because you're not including it in derived table.try like below and see if it gives you desired result?

select v.VendorName,v.VendorType,count_invoice as Invoices_uploaded,count_paymentdate as Payments_Updated
FROM Vendor v 
JOIN (select vendorid,count(1) as count_invoice from invoice group by vendorid) AS inv 
on v.VendorID= inv.VendorID
JOIN (select vendorid,count(1) as count_paymentdate from payment 
where pt.paymentdate >= '01-8-2008' and pt.paymentdate < '20-8-2008'
group by vendorid) AS pt 
on pt.vendorid = v.vendorid

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-25 : 08:50:25

Thanks a lot.

Regards,

NijelJ22
Starting Member

18 Posts

Posted - 2008-09-01 : 05:53:47

i have to add another field in this query. Field name is service name. Now but Service name exists in Service table which only contains two fields Serviceid & ServiceName. No way to link it fron vendor or paymnet table. However, there is another table that contains vendor id and serviceid. Name of table is serviceinvoice. Please help me in modifyng the same query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources