SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stopdate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dianne
Starting Member

USA
10 Posts

Posted - 09/12/2012 :  10:38:13  Show Profile  Reply with Quote
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
52325 Posts

Posted - 09/12/2012 :  10:56:48  Show Profile  Reply with Quote
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/

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/12/2012 :  11:00:28  Show Profile  Reply with Quote
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'
  );
Go to Top of Page

Dianne
Starting Member

USA
10 Posts

Posted - 09/12/2012 :  11:02:36  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/12/2012 :  11:13:54  Show Profile  Reply with Quote
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/

Go to Top of Page

Dianne
Starting Member

USA
10 Posts

Posted - 09/12/2012 :  11:27:46  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/12/2012 :  11:37:52  Show Profile  Reply with Quote
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/

Go to Top of Page

Dianne
Starting Member

USA
10 Posts

Posted - 09/12/2012 :  13:23:58  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/12/2012 :  13:25:24  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Dianne
Starting Member

USA
10 Posts

Posted - 09/28/2012 :  18:07:44  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/01/2012 :  11:08:34  Show Profile  Reply with Quote
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/

Go to Top of Page

Dianne
Starting Member

USA
10 Posts

Posted - 10/01/2012 :  11:35:31  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/01/2012 :  11:52:55  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000