Stopdate - 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

General SQL Server Forums

New to SQL Server Programming

Stopdate

Author

Topic

Dianne
Starting Member

10 Posts

Posted - 2012-09-12 : 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

52326 Posts

Posted - 2012-09-12 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-12 : 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

10 Posts

Posted - 2012-09-12 : 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

52326 Posts

Posted - 2012-09-12 : 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

10 Posts

Posted - 2012-09-12 : 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

52326 Posts

Posted - 2012-09-12 : 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

10 Posts

Posted - 2012-09-12 : 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

52326 Posts

Posted - 2012-09-12 : 13:25:24

welcome

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

Dianne
Starting Member

10 Posts

Posted - 2012-09-28 : 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

52326 Posts

Posted - 2012-10-01 : 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

10 Posts

Posted - 2012-10-01 : 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

52326 Posts

Posted - 2012-10-01 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources