July 28, 2006 at 3:09 pm
I know there's got to be a way of doing this then what I have come up with and I just can't figure it out for the life of me. When I was working the query earlier I could have sworn I had it right yet after making a few edits for other reasons the thing quit working right and I can't get it back.
The following is the DDL to create part of the actual table in use. I can't list the entire table for security/privacy reasons. I have also changed the column names as well. The data types however are the same. This table stores 1 reocrd for every payment from a customer. The 3 columns below are what I am focusing in on for this query, all the rest are irrelevant for this purpose and therefore are not included in the below.
CREATE TABLE [dbo].[PAYMENT](
[hCtrlNum] [numeric]
(18, 0) NOT NULL,
[hCustomer] [numeric]
(18, 0) NULL,
[dtTranDate] [datetime]
NULL,
CONSTRAINT [PK_TRANS] PRIMARY KEY CLUSTERED ([hCtrlNum] ASC) ON [PRIMARY]
)
ON [PRIMARY]
The hCtrlNum column is the typical Numeric Primary Key column. The hCustomer column is a Foreign Key to the table that stores customer data. The last column, dtTranDate is the date that the receipt was recorded/recieved. What I'm trying to do is come up with a query that will give me the first record to each customer. I am using the Min() aggregate on hMy to get the first record and that requires grouping on the other 2 columns. This is a problem because grouping on the dtTranDate column results in more records per customer then just 1. I was going to put what I had up in this poist but I can't get the format to work right and so I'll just ask for help.
How can I get a query that will return the lowest hCtrlNum value for each unique hCustomer and get the correct dtTranDate value that goes with whatever hCTrlNum value is returned. Right now in order to get but a single dtTranDate I have to wrap it in an instance of MIN(). That doesn't work right because the first Payment entered may not be the one with the oldest date because sometimes payments are back dated.
Suggestions?
Kindest Regards,
Just say No to Facebook!July 28, 2006 at 3:45 pm
I added a customer table so you would have names displayed. If I am understanding this correctly, hCrtlNum is not necessary to this query...
DECLARE @Payment TABLE( hCtrlNum numeric(18, 0) NOT NULL,
hCustomer numeric(18, 0) NOT NULL,
dtTranDate datetime NULL)
INSERT INTO @Payment
SELECT 1, 1, '07/01/2006' UNION ALL
SELECT 2, 2, '07/02/2006' UNION ALL
SELECT 3, 3, '07/03/2006' UNION ALL
SELECT 4, 1, '07/04/2006' UNION ALL
SELECT 5, 1, '07/05/2006' UNION ALL
SELECT 6, 2, '07/06/2006' UNION ALL
SELECT 7, 3, '07/07/2006' UNION ALL
SELECT 8, 3, '07/08/2006' UNION ALL
SELECT 9, 1, '07/09/2006'
DECLARE @Client TABLE( hCustomer numeric(18, 0) NOT NULL,
hName varchar(25) NOT NULL)
INSERT INTO @Client
SELECT 1, 'John Smith' UNION ALL
SELECT 2, 'Jill Smith' UNION ALL
SELECT 3, 'Pat Smith'
SELECT C.hName AS [Customer Name], MIN( P.dtTranDate) AS MinDate
FROM @Payment P
INNER JOIN @Client C ON( P.hCustomer = C.hCustomer)
GROUP BY C.hName
I wasn't born stupid - I had to study.
July 28, 2006 at 11:55 pm
Try this. It'll cover the payment back-dating.
select p.*
from @Payment p
join (select min(hCtrlNum) as hCtrlNum, hCustomer from @Payment group by hCustomer) as v
on p.hCtrlNum = v.hCtrlNum July 30, 2006 at 8:39 pm
First off let me say thanks for the replies.
The query needs to return the following data:
Custmer ID, Min(Payment COntrol #), Date Of Min(PaymentControl#)
The problem is getting the right date for the Payment Control# returned and include both in the query without having to group by the Payment date or use any other aggregate function on the Payment date because either causes a bad result set.
I think my example table data may be making this hard to properly understand. Let me try another example. Imagine you have a single table that has the following 3 pieces of data:
Customer_Code, Receipt_Ctrl# & Receipt_Date
The Customer Code indicates who the payment is from and so it will appear once for every payment from a customer. The Recept Ctrl# is a unique value that starts at 1 and increments by 1 for every new receipt. And the Receipt_Date is the date that the receipt was entered. I need a query that will return 1 record for every unique value of Customer_Code in the table. Each of those records should return the lowest Receipt_Ctrl# for Customer_code (aka the first receipt ever done to the customer) and the Receipt_Date needs to be the date that the first receipt was entered into the system.
Now I know that's not DDL and maybe not even good pseudo code but it should be good enough to better articulate the problem.
Any help is very much appreciated. This is very frustarting for me because as I said in my first post I had it working early on and then did something to break it and darned if I can get back to the working point. UGHH!
Thanks fellow SQL fans
Bubba
Kindest Regards,
Just say No to Facebook!July 30, 2006 at 11:23 pm
Isn't that what I did? Here's the same query with explicit select list, sample data & results:
declare @Payment table( hCtrlNum numeric(18, 0) not null,
hCustomer numeric(18, 0) not null,
dtTranDate datetime null)
set nocount on
insert into @Payment values (1, 11111, '5/15/2006')
insert into @Payment values (2, 11111, '5/01/2006')
insert into @Payment values (3, 22222, '6/01/2006')
insert into @Payment values (4, 22222, '6/02/2006')
insert into @Payment values (5, 33333, '7/15/2006')
insert into @Payment values (6, 33333, '7/10/2006')
set nocount off
select p.hCtrlNum, p.hCustomer, p.dtTranDate
from @Payment p
join (select min(hCtrlNum) as hCtrlNum, hCustomer from @Payment group by hCustomer) as v
on p.hCtrlNum = v.hCtrlNumWhich returns:
hCtrlNum hCustomer dtTranDate -------- --------- ----------------------- 1 11111 2006-05-15 00:00:00.000 3 22222 2006-06-01 00:00:00.000 5 33333 2006-07-15 00:00:00.000 (3 row(s) affected)
If I'm missing something here, please post a few rows of sample data and expected results.
July 31, 2006 at 8:10 am
Hello Ed,
please explain this in more detail:
"Each of those records should return the lowest Receipt_Ctrl# for Customer_code (aka the first receipt ever done to the customer) and the Receipt_Date needs to be the date that the first receipt was entered into the system"
It can (at least theoretically) happen, that a payment is entered in an incorrect order ("backdating" mentioned by Lance). That means, you have payment with ID 100 dated today, and payment with ID 120 dated yesterday. What should be displayed in such situation? Logically, it should be decided by the datetime column - but you may have different requirements.. you could even require to display both lowest ID and lowest date, even if these two values come from two different rows.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply