March 5, 2006 at 11:36 pm
Dear All,
I have a transaction Table Trans and a Customer Table Cust.
Transaction Table has following fields
Trans
TrID
CustomerID
DateReceived
Customer Table has following fields
Cust
CustomerID
CutomerName
SalesPersonID
Unfortunately, there is no any date in Customer Table, when he was inserted in our database. Incase its transaction is present in TransactionTable, it should be in Customer Table.
I have to make a report for SalesPersons and need commission on every Transaction (trans) for a Customer who is not one year old. There are thousands of Customers who are repeating in current year plus past five years since database exits.
First I made a query to get those customers who are within the range of current year Transaction (within 1Year).
SELECT DISTINCT CustomerID
FROM Trans
WHERE DateReceived >= dateadd("yyyy",-1, GetDate())
Then I made a query which gives me those customers whose transaction is older then Range (older then 1Year)
SELECT DISTINCT CustomerID
FROM Trans
WHERE DateReceived >= dateadd("yyyy",-1, GetDate()) and
Customerid NOT IN
(
select distinct CustomerID from Trans
where DateReceived < dateadd("yyyy",-1, GetDate))
This is my primary query to give those customer which are within the range then I will be able to make join with Customer table and Sales Person Table.
but the above query gives me no record. Even there are more than 500 New Customers within a year dateadd("yyyy",-1, GetDate))
Please help and make a scenario and let me know how to solve it.
March 6, 2006 at 9:42 am
The derived table will create a list of customers that have transactions older than a year thus omitting the new customers within the year, but will still return transactions for the old customers that took place in the last year.
Select
cu.CustomerName
,cu.CustomerId
,tr.TrId
,tr.DateReceived
From (Select Distinct cu.CustomerID
From dbo.Cust cu
Inner Join dbo.Trans tr
On cu.CustomerID = tr.CustomerID
And tr.DateReceived <= DateAdd(yy, -1, GetDate())) d_cu
Inner Join dbo.Cust cu
On d_cu.CustomerID = cu.CustomerID
Inner Join dbo.Trans tr
On cu.CustomerID = tr.CustomerID
Order By cu.CustomerName
,tr.DateReceived
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply