Help with SPROC from .asp please...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there - sorry if this is a bit long winded, but I am really stuck and
would appreciate any advice anyone has on this.

I have the following set up in a stored procedure (below) which is called
from an asp application and displays in a datagrid. The purpose is to
retrieve information on overdue invoices depending upon the payment type for
the customer (2 distinct payment types in the first two Select Statements -
all others in the 3rd)

This works fine. I then only show one record per customer in the datagrid
with the totals for total amount invoiced, total amount paid against those
invoices and total still owed - I filter the datagrid once this data is in
the asp code ondatabind making all rows that do not meet the following
criteria not visible - leaving one row visible per customer:

SPARE_NUMBER#1 > -1
CURRENT_CREDIT > -1
TRANSACTION_DATE = "01/01/1900 00:00:00"

I have added the:
COUNT(SALES_TRANSACTIONS.REFERENCE_NUMBER) AS TOTAL_INVOICES
line which allows the totalled rows to have a data field showing the total
number of outstanding invoices for each individual customer.... my problem is
that I want to add another field on the end which contains the oldest
transaction_date that is
still outstanding for each individual customer regardless of the
TRANSACTION_DATE already retrieved for each row.

For example: here is a clip from the total data retrieved (this clip relates
to a single customer) - so the 7th field is the total owed and the 10th field
is the total number of invoices against that customer

Unique Acc Number Account Name -1 -2 01/01/1900 00:00 1078.38 0 1078.38 9

Unique Acc Number Account Name 0 -2 01/01/1900 00:00 1078.38 0 1078.38 9

Unique Acc Number Account Name 0 1078.38 01/01/1900 00:00 1078.38 0 1078.38 9

Unique Acc Number Account Name 0 1078.38 20/08/2004 00:00 354.03 0 354.03 1

Unique Acc Number Account Name 0 1078.38 01/07/2004 00:00 141 0 141 1

Unique Acc Number Account Name 0 1078.38 21/07/2004 00:00 121.61 0 121.61 1

Unique Acc Number Account Name 0 1078.38 07/07/2004 00:00 115.44 0 115.44 1

Unique Acc Number Account Name 0 1078.38 04/08/2004 00:00 115.44 0 115.44 1

Unique Acc Number Account Name 0 1078.38 13/08/2004 00:00 105.16 0 105.16 1

Unique Acc Number Account Name 0 1078.38 01/09/2004 00:00 79.29 0 79.29 2

Unique Acc Number Account Name 0 1078.38 05/08/2004 00:00 46.41 0 46.41 1


Obviously the 1 row for each customer I am currently displaying has a
transaction date of 01/01/1900 which is necessary, but nonsense and hidden
from view - I want to place another field in to each row with the oldest
transaction date on a per customer basis - I don't know how....

Here is the SPROC - obviously I would appreciate any thoughts - whether I
can add the data field in the SPROC somehow, or in the .asp code I do not
mind...

CREATE PROCEDURE RETRIEVE_OVERDUE_INVOICE_SUMMARY

AS

SELECT
CASE GROUPING
(SALES_LEDGER.ACCOUNT_NUMBER) WHEN 0 THEN SALES_LEDGER.ACCOUNT_NUMBER ELSE
'NUMBER'
END AS ACCOUNT_NUMBER,
CASE GROUPING
(SALES_LEDGER.ACCOUNT_NAME) WHEN 0 THEN SALES_LEDGER.ACCOUNT_NAME ELSE 'NAME'
END AS ACCOUNT_NAME,
CASE GROUPING
(SALES_LEDGER.SPARE_NUMBER#1) WHEN 0 THEN SALES_LEDGER.SPARE_NUMBER#1 ELSE -1
END AS SPARE_NUMBER#1,
CASE GROUPING
(SALES_LEDGER.CURRENT_CREDIT) WHEN 0 THEN SALES_LEDGER.CURRENT_CREDIT ELSE -2
END AS CURRENT_CREDIT,
CASE GROUPING
(SALES_TRANSACTIONS.TRANSACTION_DATE) WHEN 0 THEN
SALES_TRANSACTIONS.TRANSACTION_DATE ELSE '1900/01/01'
END AS TRANSACTION_DATE,
SUM(SALES_TRANSACTIONS.GOODS_AMOUNT) AS TOTAL_GOODS_AMOUNT,
SUM(SALES_TRANSACTIONS.ALLOCATED_AMOUNT) AS TOTAL_ALLOCATED_AMOUNT,
SUM(SALES_TRANSACTIONS.GOODS_AMOUNT - SALES_TRANSACTIONS.ALLOCATED_AMOUNT)
AS TOTAL_OUTSTANDING_AMOUNT,
COUNT(SALES_TRANSACTIONS.REFERENCE_NUMBER) AS TOTAL_INVOICES
FROM SALES_TRANSACTIONS
INNER JOIN
SALES_LEDGER ON SALES_TRANSACTIONS.PARENT_RECORD = SALES_LEDGER.THIS_RECORD
WHERE
SALES_TRANSACTIONS.TRANSACTION_TYPE <> 1
AND
SALES_TRANSACTIONS.GOODS_AMOUNT - SALES_TRANSACTIONS.ALLOCATED_AMOUNT > 0
AND
SALES_LEDGER.ANALYSIS_2 = 'EOF'
AND
GETDATE() > DATEADD(MONTH, 2, (SALES_TRANSACTIONS.TRANSACTION_DATE -
DATEPART(DAY, SALES_TRANSACTIONS.TRANSACTION_DATE) + 10))
GROUP BY SALES_LEDGER.ACCOUNT_NUMBER, SALES_LEDGER.ACCOUNT_NAME,
SALES_LEDGER.SPARE_NUMBER#1,
SALES_LEDGER.CURRENT_CREDIT,SALES_TRANSACTIONS.TRANSACTION_DATE WITH ROLLUP

UNION
SELECT
CASE GROUPING
(SALES_LEDGER.ACCOUNT_NUMBER) WHEN 0 THEN SALES_LEDGER.ACCOUNT_NUMBER ELSE
'NUMBER'
END AS ACCOUNT_NUMBER,
CASE GROUPING
(SALES_LEDGER.ACCOUNT_NAME) WHEN 0 THEN SALES_LEDGER.ACCOUNT_NAME ELSE 'NAME'
END AS ACCOUNT_NAME,
CASE GROUPING
(SALES_LEDGER.SPARE_NUMBER#1) WHEN 0 THEN SALES_LEDGER.SPARE_NUMBER#1 ELSE -1
END AS SPARE_NUMBER#1,
CASE GROUPING
(SALES_LEDGER.CURRENT_CREDIT) WHEN 0 THEN SALES_LEDGER.CURRENT_CREDIT ELSE -2
END AS CURRENT_CREDIT,
CASE GROUPING
(SALES_TRANSACTIONS.TRANSACTION_DATE) WHEN 0 THEN
SALES_TRANSACTIONS.TRANSACTION_DATE ELSE '1900/01/01'
END AS TRANSACTION_DATE,
SUM(SALES_TRANSACTIONS.GOODS_AMOUNT) AS TOTAL_GOODS_AMOUNT,
SUM(SALES_TRANSACTIONS.ALLOCATED_AMOUNT) AS TOTAL_ALLOCATED_AMOUNT,
SUM(SALES_TRANSACTIONS.GOODS_AMOUNT - SALES_TRANSACTIONS.ALLOCATED_AMOUNT)
AS TOTAL_OUTSTANDING_AMOUNT,
COUNT(SALES_TRANSACTIONS.REFERENCE_NUMBER) AS TOTAL_INVOICES
FROM SALES_TRANSACTIONS
INNER JOIN
SALES_LEDGER ON SALES_TRANSACTIONS.PARENT_RECORD = SALES_LEDGER.THIS_RECORD
WHERE
SALES_TRANSACTIONS.TRANSACTION_TYPE <> 1
AND
SALES_TRANSACTIONS.GOODS_AMOUNT - SALES_TRANSACTIONS.ALLOCATED_AMOUNT > 0
AND
SALES_LEDGER.ANALYSIS_2 = 'DD'
AND
GETDATE() > DATEADD(DAY, 61, SALES_TRANSACTIONS.TRANSACTION_DATE)
GROUP BY SALES_LEDGER.ACCOUNT_NUMBER, SALES_LEDGER.ACCOUNT_NAME,
SALES_LEDGER.SPARE_NUMBER#1,
SALES_LEDGER.CURRENT_CREDIT,SALES_TRANSACTIONS.TRANSACTION_DATE WITH ROLLUP

UNION
SELECT
CASE GROUPING
(SALES_LEDGER.ACCOUNT_NUMBER) WHEN 0 THEN SALES_LEDGER.ACCOUNT_NUMBER ELSE
'NUMBER'
END AS ACCOUNT_NUMBER,
CASE GROUPING
(SALES_LEDGER.ACCOUNT_NAME) WHEN 0 THEN SALES_LEDGER.ACCOUNT_NAME ELSE 'NAME'
END AS ACCOUNT_NAME,
CASE GROUPING
(SALES_LEDGER.SPARE_NUMBER#1) WHEN 0 THEN SALES_LEDGER.SPARE_NUMBER#1 ELSE -1
END AS SPARE_NUMBER#1,
CASE GROUPING
(SALES_LEDGER.CURRENT_CREDIT) WHEN 0 THEN SALES_LEDGER.CURRENT_CREDIT ELSE -2
END AS CURRENT_CREDIT,
CASE GROUPING
(SALES_TRANSACTIONS.TRANSACTION_DATE) WHEN 0 THEN
SALES_TRANSACTIONS.TRANSACTION_DATE ELSE '1900/01/01'
END AS TRANSACTION_DATE,
SUM(SALES_TRANSACTIONS.GOODS_AMOUNT) AS TOTAL_GOODS_AMOUNT,
SUM(SALES_TRANSACTIONS.ALLOCATED_AMOUNT) AS TOTAL_ALLOCATED_AMOUNT,
SUM(SALES_TRANSACTIONS.GOODS_AMOUNT - SALES_TRANSACTIONS.ALLOCATED_AMOUNT)
AS TOTAL_OUTSTANDING_AMOUNT,
COUNT(SALES_TRANSACTIONS.REFERENCE_NUMBER) AS TOTAL_INVOICES
FROM SALES_TRANSACTIONS
INNER JOIN
SALES_LEDGER ON SALES_TRANSACTIONS.PARENT_RECORD = SALES_LEDGER.THIS_RECORD
WHERE
SALES_TRANSACTIONS.TRANSACTION_TYPE <> 1
AND
SALES_TRANSACTIONS.GOODS_AMOUNT - SALES_TRANSACTIONS.ALLOCATED_AMOUNT > 0
AND
SALES_LEDGER.ANALYSIS_2 <> 'DD'
AND
SALES_LEDGER.ANALYSIS_2 <> 'EOF'
AND
GETDATE() > DATEADD(DAY, 51, SALES_TRANSACTIONS.TRANSACTION_DATE)
GROUP BY SALES_LEDGER.ACCOUNT_NUMBER, SALES_LEDGER.ACCOUNT_NAME,
SALES_LEDGER.SPARE_NUMBER#1,
SALES_LEDGER.CURRENT_CREDIT,SALES_TRANSACTIONS.TRANSACTION_DATE WITH ROLLUP

ORDER BY ACCOUNT_NAME, TOTAL_OUTSTANDING_AMOUNT DESC
GO
 
Yikes! Stuart you may want to try doing some of this client side - You can
bind your grid to a dataView which is filtered to show only the records you
want. You can use DataTable.Compute run the aggregates and the good part of
doing it this way is that you can hold the Total values or antying else in
Textboxes or anywhere else you feel like it.
 
Thanks - there is some method to my apparent madness (I think !)

as it turns out, having played around with it a little more... adding a line
to the select statement of:

MIN(SALES_TRANSACTIONS.TRANSACTION_DATE) AS OLDEST_INVOICE

.... does precisely what I required...

Thanks very much for your input and help though

Stuart
 
Back
Top