| Author |
 Topic  |
|
|
Dianne
Starting Member
USA
10 Posts |
 Posted - 09/12/2012 : 10:38:13
|
I'm new to SQL all together, but I have the following issue I really need help solving;
We have a list of customer transactions that are grouped together by customer. Each transaction has a start date that is pulled from the database. However we have supplied a stop date by looking at the next "like" transaction in the list and making that start date the current transactiosn stop date. But we need the last transaction to stop IF there is a stop date for another type of transaction. IF there isn't a stop date for that other transaction then there should NOT be a stop date for the last transaction....
Is there a way to make a transaction look for another type of transaction and pull that transactions stop date?
Dianne |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 09/12/2012 : 10:56:48
|
so what should be value of stopdate for last transaction? current date or start date of next transaction?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/12/2012 : 11:00:28
|
Yes, you can - but the details of how you would do it depends on how the data is stored. If you can post the table schemas with some sample data, people on the forum would be able to give more concrete suggestions.
My guess would be that you will need a query that has a where clause which goes something like UPDATE y1 SET
StopDate = NextStartDate
FROM
YourTable y1
LEFT JOIN YourTable y2 ON
y1.something+1 = y2.something
AND y1.CustomerId = y2.CustomerId
AND y1.TransactionType = y2.TransactionType
WHERE
EXISTS
( SELECT * FROM YourTable y3
WHERE y3.CustomerId = y1.CustomerId
and y3.TransactionType = 'theOtherTypeOfTransaction'
);
|
|
|
|
Dianne
Starting Member
USA
10 Posts |
Posted - 09/12/2012 : 11:02:36
|
quote:
Originally posted by visakh16
so what should be value of stopdate for last transaction? current date or start date of next transaction?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
The stopdate for the last transactions should be the stopdate for the other type of transaction.
Dianne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 09/12/2012 : 11:13:54
|
quote:
Originally posted by Dianne
quote:
Originally posted by visakh16
so what should be value of stopdate for last transaction? current date or start date of next transaction?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
The stopdate for the last transactions should be the stopdate for the other type of transaction.
Dianne
sorry thats not clear
there can be multiple transaction types right
so in that what according to you determine the other type of trsnaction for a particular last transaction?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dianne
Starting Member
USA
10 Posts |
Posted - 09/12/2012 : 11:27:46
|
quote:
Originally posted by visakh16
quote:
Originally posted by Dianne
quote:
Originally posted by visakh16
so what should be value of stopdate for last transaction? current date or start date of next transaction?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
The stopdate for the last transactions should be the stopdate for the other type of transaction.
Dianne
sorry thats not clear
there can be multiple transaction types right
so in that what according to you determine the other type of trsnaction for a particular last transaction?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Sorry for the confusion...let's see if this helps this is an example of my info. This is a list of transactions for a customer for Trans "MSF" there is a StopDate and what I need to be able to do for the last Trans of "CS0" is make it look for a a Trans "MSF" and if there is a StopDate for that type of trans, put it in the StopDate line for Trans "CS0", but if there is not a StopDate for Trans "MSF" leave the StopDate for Trans "CS0" blank. There will always be a Trans "MSF", maybe more than one so I will need it to look for the latest StopDate for that particular type of Trans. The last Trans will not always be "CS0", but it should still look for a "MSF" Trans. Does that make sense?
Trans StartDate ID Name StopDate
MSF 7/27/2009 11 Harrison 8/9/2012
CS1 7/27/2009 11 Harrison 7/25/2012
AWAY 4/4/2012 11 Harrison 4/6/2012
AWAY 6/11/2012 11 Harrison 7/25/2012
CS0 7/25/2012 11 Harrison NULL
Dianne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 09/12/2012 : 11:37:52
|
ok...that makes it clear
UPDATE t
SET t.StopDate = t1.latestdate
FROM Table t
INNER JOIN (SELECT Name,
MAX(StartDate) AS StartDate,
MAX(CASE WHEN trans='MSF' AND StopDate IS NOT NULL THEN StopDate END) AS latestdate
FROM table
GROUP BY Name
HAVING MAX(CASE WHEN trans='MSF' AND StopDate IS NOT NULL THEN StopDate END) IS NOT NULL
)t1
ON t1.Name = t.Name
AND t1.StartDate = t.StartDate
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dianne
Starting Member
USA
10 Posts |
Posted - 09/12/2012 : 13:23:58
|
quote:
Originally posted by visakh16
ok...that makes it clear
UPDATE t
SET t.StopDate = t1.latestdate
FROM Table t
INNER JOIN (SELECT Name,
MAX(StartDate) AS StartDate,
MAX(CASE WHEN trans='MSF' AND StopDate IS NOT NULL THEN StopDate END) AS latestdate
FROM table
GROUP BY Name
HAVING MAX(CASE WHEN trans='MSF' AND StopDate IS NOT NULL THEN StopDate END) IS NOT NULL
)t1
ON t1.Name = t.Name
AND t1.StartDate = t.StartDate
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Thanks so much, I think this puts us on the right track.
Dianne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 09/12/2012 : 13:25:24
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dianne
Starting Member
USA
10 Posts |
Posted - 09/28/2012 : 18:07:44
|
Is there a way to modify this this so that it gives me the stop date that is part of the same transaction that has the max start date even if the latest start date has a null value for stop date?
Dianne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 10/01/2012 : 11:08:34
|
quote:
Originally posted by Dianne
Is there a way to modify this this so that it gives me the stop date that is part of the same transaction that has the max start date even if the latest start date has a null value for stop date?
Dianne
sorry didnt get that
can you illustrate with some sample data?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dianne
Starting Member
USA
10 Posts |
Posted - 10/01/2012 : 11:35:31
|
We are trying to get stop dates for 2 transaction types one is MSF and the other is CS%. We want to make sure that when someone enters a transaction LIKE CS% that it is only stopped by the last MSF transaction
EXAMPLE 1: Person has moved between apartments and has NO MSF transactions with a NULL VALUE for NEXT DATE
DATA
COMMID COMPID TRANTYPE TRANDATE NEXTDATE
1 1 MSF 1/1/2012 3/1/2012
2 1 CS1 1/1/2012
3 1 MSF 3/2/2012 8/1/2012
RESULT
COMMID COMPID TRANTYPE TRANDATE NEXTDATE
1 1 MSF 1/1/2012 3/1/2012
2 1 CS1 1/1/2012 8/1/2012
3 1 MSF 3/2/2012 8/1/2012
EXAMPLE 2: Person has moved between apartments and has a MSF transaction with a NULL NEXT DATE
DATA
COMMID COMPID TRANTYPE TRANDATE NEXTDATE
1 1 MSF 1/1/2012 3/1/2012
2 1 CS1 1/1/2012
3 1 MSF 3/2/2012 NULL
RESULT
COMMID COMPID TRANTYPE TRANDATE NEXTDATE
1 1 MSF 1/1/2012 3/1/2012
2 1 CS1 1/1/2012 NULL
3 1 MSF 3/2/2012 NULL
Dianne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 10/01/2012 : 11:52:55
|
do you mean this?
UPDATE t
SET t.StopDate = t1.latestdate
FROM Table t
INNER JOIN (SELECT Name,
MAX(StartDate) AS StartDate,
MAX(CASE WHEN trans='MSF' THEN StopDate END) AS latestdate
FROM table
GROUP BY Name)t1
ON t1.Name = t.Name
AND t1.StartDate = t.StartDate
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Stopdate
