Query help with Join - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query help with Join

Author

Topic

srujanavinnakota
Starting Member

34 Posts

Posted - 2012-07-19 : 01:13:13

Hi,
Can anyone help with SQL please?

i have 2 tables. as shown below.

Table-1-CustomerStatusTable
Table-2-CustomerreturnsTable

CustomerStatusTable

CustID, CustStatus, CustStatusDate
1, old, 2012-04-22
1, renew, 2011-02-31
1, old, 2012--01-15
1, new, 2012-01-13

CustREturns Table
CustID, CustreturnAmount, CustReturnsDate
1, 100, 2012-04-25
1, 200, 2011-01-31
1, 300, 2012--03-15
1, 400, 2012-01-15

I need the results like shown below

CustID, CustStatus, CustReturnDate
1 old, 2012-01-15
1 old, 2012-04-22

--------------------------------------------------------------------

I need to select where Customers with Status=old and return dates occurred closet to the CustStatusTable Dates.

With out duplicating records.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 02:37:46

can you explain how the required result comes about ?

KH
[spoiler]Time is always against us[/spoiler]

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-19 : 08:43:56

Hi,

We need logic of the CustReturnsDate column in the Result. So Please provide.
Is the sample data values or right for
Table-1-CustomerStatusTable
Table-2-CustomerreturnsTable
???????????? Pls rechech.

by the way you can get result like using.

CREATE TABLE CustomerStatusTable (CustID INT , CustStatus VARCHAR(10), CustStatusDate DATE)
CREATE TABLE CustREturns (CustID INT , CustreturnAmount MONEY, CustReturnsDate DATE)

SELECT CONVERT (DATE, '2012-07-19')
INSERT INTO CustomerStatusTable (CustID, CustStatus, CustStatusDate)
VALUES (1, 'old', '2012-04-22'),
(1, 'renew', '2011-02-28'),
(1, 'old', '2012-01-15'),
(1, 'new', '2012-01-13')

SELECT * FROM CustomerStatusTable

INSERT INTO CustREturns (CustID, CustreturnAmount, CustReturnsDate)
VALUES (1, 100, '2012-04-25'),
(1, 200, '2011-01-31'),
(1, 300, '2012-03-15'),
(1, 400, '2012-01-15')

SELECT * FROM CustREturns

SELECT
CustomerStatusTable.CustID, CustStatus , CustStatusDate AS CustReturnsDate
FROM CustomerStatusTable
WHERE CustStatus= 'OLD'
ORDER BY CustReturnsDate ASC

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 10:34:28

with current sample data and output a simple select like what posted in previous thread should work. But I'm sure OP is not looking for that. So if you can explain us rules for getting result we would be able to provide accurate solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

srujanavinnakota
Starting Member

34 Posts

Posted - 2012-07-19 : 12:37:40

Thanks for all the replies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 13:08:26

quote:
Originally posted by srujanavinnakota

Thanks for all the replies.

was that your exact requirement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

srujanavinnakota
Starting Member

34 Posts

Posted - 2012-07-19 : 19:36:37

Below is the exact data.
Cust Refunds Table
CustomerID REfundsDate REfundID TransID
645 2012-02-16 7140913 36397409
645 2012-06-11 7677955 41086443
645 2012-06-11 7677956 41086444
645 2012-06-11 7677957 41087313

Cust REfundsCalc Table
CustomerID REfundsCalcDate StatusDate
645 2012-02-15 2012-01-31
645 2012-06-10 2012-05-14

Cust Status table
CustomerID StatusDate
6459103 2012-01-31
6459103 2012-05-14

After the Customer gets entered in to Status Table, Business calculates the refunds in the refund calc table and gives the refunds to Customer and closes the status. When the status changes they do the same. This is the business flow for the below requirement.
In the first record if the after 2012-02-16, they close the account and when the status got entered in Status Table they have calculated refund again and gave refund back to Customer and close the account.
Now they want to verify how many times they refunded and closed the account.
I need the data in following format. I am not able to find the link between refunds and drop statues.
CustomerID REfundsDate REfundID TransID StatusDate
645 2012-02-16 7140913 36397409 2012-01-19
645 2012-06-11 7677955 41086443 2012-04-30
645 2012-06-11 7677956 41086444 2012-04-30
645 2012-06-11 7677957 41087313 2012-04-30

srujanavinnakota
Starting Member

34 Posts

Posted - 2012-07-19 : 19:38:15

Please help me, I have been breaking my head from 2 days on this issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 23:12:29

posted sample data doesnt make much sense. None of customerid in status table is matching with ones in other tables. Also your rules are not quite clear on how refunds are calculated. Post proper data and explain clearly your rules and then somebody might be able to help!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources