Query out Monthly Payments

[vanuta]

I was wondering if their was a way to query out monthly payments from a table. The table would look like this:

Vendor_Num Vendor_Name Invoice_Num Inv_Dte Inv_Amt
1 Tyco 10001 10/11/01 5000.00
1 Tyco 10010 11/11/01 6000.00
1 Tyco 10020 01/15/01 7000.00
1 Tyco 10051 12/11/01 2500.00
56 BrandCo 25001 08/01/00 3000.00
56 BrandCo 27000 09/01/00 4500.00
56 BrandCo 58000 11/28/00 6500.00
56 BrandCo 98000 10/01/00 9801.00

I'm looking to query out the monthly payments for Tyco for 10/11/01,11/11/01,12/11/01 and the monthly payments for BrandCo. for 08/01/00,09/01/00,10/01/00. Is there a stored procedure or function to do this. Any help would be appreciated.

 

[garwain]

to get the sum of the inv_amt and the vendor information for each month you should be able to use the following query:

SELECT vendor_id,vendor_name, sum(inv_amt)
FROMinvoices
GROUP BY LEFT(CAST(inv_dte as char(8)), 5), --get the month and year
vendor_id,
vendor_name

 

[vanuta]

I don't want to sum the invoice amount. I just need to query out monthly payments for each vendor. If we have a payment on the 1st of every month, then I want to see all 12 payments for that year.

 

[garwain]

select vendor_id, vendor_name, cast(inv_dte as char(5)), inv_num, inv_amt
order by vendor_name, cast(inv_dte as char(5))

if that one is wrong as well, try reposting your question giving what the table contains and what the resultset should contain.

 

[tlbroadbent]

I think your requirements statement is difficult to understand. It would appear that a simple query with an order by statement would suffice. However, I uncertain what you mean by "query out monthly payments." Perhaps, I am missing something.

SELECT
vendor_id, vendor_name,
inv_dte , inv_amt
FROM invoices
Order BY vendor_name, inv_dte Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[vanuta]

I want my results to be:

Vendor_Num Vendor_Name Invoice_Num Inv_Dte Inv_Amt
1 Tyco 10001 10/11/01 5000.00
1 Tyco 10010 11/11/01 6000.00
1 Tyco 10051 12/11/01 2500.00
56 BrandCo 25001 08/01/00 3000.00
56 BrandCo 27000 09/01/00 4500.00
56 BrandCo 98000 10/01/00 9801.00

I want any invoice that appears to be paid every month on the same day.

 

[tlbroadbent]

This should do it!

SELECT
i.vendor_num,
i.vendor_name,
i.invoice_number,
inv_dte=convert(char(8), i.inv_dte, 1),
i.inv_amt
FROM invoices i
WHERE Exists
(SELECT *
From invoices
Where vendor_num=i.vendor_num
And day(inv_dte)=day(i.inv_dte)
And inv_dte<>i.inv_dte)
Order BY vendor_name, inv_dte Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com