asked
how to split one date column into two culumns and get data from the start date to last date.
Hi good people any one can help me I am stack here. I am working on SQL database I want to display three column from the table I have. I tried in many ways but it not do what is needed. Original table is like this below: Refno TransactionDate Code L820774729 2012-09-01 00:00:00.000 99 L820774729 2012-09-01 00:00:00.000 99 L820774729 2012-10-01 00:00:00.000 99 L820774729 2012-10-01 00:00:00.000 99 L820774729 2012-12-01 00:00:00.000 99 L820774729 2013-01-01 00:00:00.000 99 L820774729 2013-02-01 00:00:00.000 99 L820774729 2013-03-01 00:00:00.000 99 L820774729 2013-03-01 00:00:00.000 99 L820774729 2013-04-01 00:00:00.000 99 L820774729 2013-06-01 00:00:00.000 99 L820774729 2013-07-01 00:00:00.000 99 L820774729 2013-09-01 00:00:00.000 99 L820774729 2013-10-01 00:00:00.000 99 L820774729 2013-10-01 00:00:00.000 99 L820774729 2013-11-01 00:00:00.000 2 L820774729 2013-11-01 00:00:00.000 2 L820774729 2013-11-01 00:00:00.000 99 L820774729 2013-11-01 00:00:00.000 99 L820774729 2013-12-01 00:00:00.000 2 L820774729 2014-01-01 00:00:00.000 2 L820774729 2014-02-01 00:00:00.000 2 L820774729 2014-03-01 00:00:00.000 2 L820774729 2014-04-01 00:00:00.000 2 L820774729 2014-05-01 00:00:00.000 2 L820774729 2014-06-01 00:00:00.000 2 L820774729 2014-07-01 00:00:00.000 2 L820774729 2014-08-01 00:00:00.000 2 L820774729 2014-08-01 00:00:00.000 2 L820774729 2014-08-01 00:00:00.000 2 L820774729 2014-09-01 00:00:00.000 2 L820774729 2014-10-01 00:00:00.000 2 L820774729 2014-11-01 00:00:00.000 1 L820774729 2014-12-01 00:00:00.000 1 L820774729 2015-01-01 00:00:00.000 1 L820774729 2015-02-01 00:00:00.000 1 L820774729 2015-03-01 00:00:00.000 1 L820774729 2015-04-01 00:00:00.000 1 L820774729 2015-05-01 00:00:00.000 1 L820774729 2015-06-01 00:00:00.000 1 **The out put that is needed is just like this below:** From To Code Sep 2012 Oct 2013 99 Nov 2013 Nov 2013 2 Nov 2013 Nov 2013 99 Dec 2013 Oct 2014 2 Nov 2014 Jun 2015 1 **My current code is like this:** SELECT [Refno]As Ref,[TransactionDate]As[From],( SELECT MIN(TransactionDate) FROM LevyTransactions AS LlevyTransactions WHERE LlevyTransactions.TransactionDate>LevyTransactions.TransactionDate)AS [To],[Code]FROM dbo.LevyTransactions WHERE Refno='L82077479' **and my current output is like this.** From To Code 2012-09-01 00:00:00.000 2012-10-01 00:00:00.000 99 2012-09-01 00:00:00.000 2012-10-01 00:00:00.000 99 2012-10-01 00:00:00.000 2012-11-01 00:00:00.000 99 2012-10-01 00:00:00.000 2012-11-01 00:00:00.000 99 2012-12-01 00:00:00.000 2013-01-01 00:00:00.000 99 2013-01-01 00:00:00.000 2013-02-01 00:00:00.000 99 2013-02-01 00:00:00.000 2013-03-01 00:00:00.000 99 2013-03-01 00:00:00.000 2013-04-01 00:00:00.000 99 2013-03-01 00:00:00.000 2013-04-01 00:00:00.000 99 2013-04-01 00:00:00.000 2013-05-01 00:00:00.000 99 2013-06-01 00:00:00.000 2013-07-01 00:00:00.000 99 2013-07-01 00:00:00.000 2013-08-01 00:00:00.000 99 2013-09-01 00:00:00.000 2013-10-01 00:00:00.000 99 2013-10-01 00:00:00.000 2013-11-01 00:00:00.000 99 2013-10-01 00:00:00.000 2013-11-01 00:00:00.000 99 2013-11-01 00:00:00.000 2013-12-01 00:00:00.000 2 2013-11-01 00:00:00.000 2013-12-01 00:00:00.000 2 2013-11-01 00:00:00.000 2013-12-01 00:00:00.000 99 2013-11-01 00:00:00.000 2013-12-01 00:00:00.000 99 2013-12-01 00:00:00.000 2014-01-01 00:00:00.000 2 2014-01-01 00:00:00.000 2014-02-01 00:00:00.000 2 2014-02-01 00:00:00.000 2014-03-01 00:00:00.000 2 2014-03-01 00:00:00.000 2014-04-01 00:00:00.000 2 2014-04-01 00:00:00.000 2014-05-01 00:00:00.000 2 2014-05-01 00:00:00.000 2014-06-01 00:00:00.000 2 2014-06-01 00:00:00.000 2014-07-01 00:00:00.000 2 2014-07-01 00:00:00.000 2014-08-01 00:00:00.000 2 2014-08-01 00:00:00.000 2014-09-01 00:00:00.000 2 2014-08-01 00:00:00.000 2014-09-01 00:00:00.000 2 2014-08-01 00:00:00.000 2014-09-01 00:00:00.000 2 2014-09-01 00:00:00.000 2014-10-01 00:00:00.000 2 2014-10-01 00:00:00.000 2014-11-01 00:00:00.000 2 2014-11-01 00:00:00.000 2014-12-01 00:00:00.000 1 2014-12-01 00:00:00.000 2015-01-01 00:00:00.000 1 2015-01-01 00:00:00.000 2015-02-01 00:00:00.000 1 2015-02-01 00:00:00.000 2015-03-01 00:00:00.000 1 2015-03-01 00:00:00.000 2015-04-01 00:00:00.000 1 2015-04-01 00:00:00.000 2015-05-01 00:00:00.000 1 2015-05-01 00:00:00.000 2015-06-01 00:00:00.000 1 2015-06-01 00:00:00.000 NULL 1 Please help me if you have any idea about the solution. Even if you have idea of doing it on c#.net no problem you can reply please help me.
You have tagged your question with both SQL Server 2008 and SQL Server 2012. The version is rather important for solving this. If it's SQL Server 2012, I would spontaniously use LEAD and LAG to find which rows are first and last in a transaction and from that I would translate the date to a month/year combination for first and last date within each transaction. But if you are running SQL Server 2008 you don't have LEAD and LAG functions.
Here's a solution which will work on SQL Server 2012, but not in SQL Server 2008. It gives your desired result based on your input, but you might want to look over how it should be handled when a two codes appear on the same date and time. use sandbox; WITH CTE AS ( SELECT refno, transactiondate, code, case when LAG(code,1,255) OVER(ORDER BY TransactionDate,code) code THEN 1 ELSE 0 END AS FirstInTransaction, case when LEAD(code,1,255) OVER(ORDER BY TransactionDate,code) code THEN 1 ELSE 0 END AS LastInTransaction from RefnoTransactions ),CTE2 AS( SELECT refno, transactiondate,LEAD(transactiondate,1,'1900-01-01 00:00:00.000') OVER(ORDER BY code,transactiondate) as EndTransaction,code,FirstIntransaction,LastIntransaction FROM CTE WHERE FirstIntransaction=1 or LastIntransaction=1 ) SELECT refno,datename(month,transactiondate) + ', ' + datename(year,transactiondate) as [FROM], datename(month,EndTransaction) + ', ' + datename(year,EndTransaction)as [TO],code FROM CTE2 WHERE FirstInTransaction=1 ORDER BY TransactionDate,code
WITH Txns As (SELECT [Refno]As Ref, GL,MIN ([TransactionDate]) As[From], MAX(TransactionDate) AS To FROM LevyTransactions WHERE Refno='L82077479' ) SELECT Ref, GL , RIGHT (CONVERT (VARCHAR, [From], 106), 8) AS [From] , Right (CONVERT (VARCHAR, [To], 106), 8) AS [To] FROM Txns
Thank you guys for your support I manage to get a solution my solution code is like this. WITH movements AS( select *, (row_number() over (partition by ref order by [TransactionDate]) - row_number() over (partition by ref, code order by [TransactionDate]) ) as group_no from LevyTransactions t ) SELECT Ref, MIN(Code) AS Code, MIN(TransactionDate) AS StartDate, MAX(TransactionDate) AS EndDate FROM movements GROUP BY Ref, group_no ORDER BY 1,3 God Bless!
WITH movements AS( select *, (row_number() over (partition by ref order by [TransactionDate]) - row_number() over (partition by ref, code order by [TransactionDate]) ) as group_no from LevyTransactions t ) SELECT Ref, MIN(Code) AS Code, MIN(TransactionDate) AS StartDate, MAX(TransactionDate) AS EndDate FROM movements GROUP BY Ref, group_no ORDER BY 1,3
Thank you guys for your support but I manage to do it I wrote my code as follow: WITH movements AS( select *, (row_number() over (partition by ref order by [TransactionDate]) - row_number() over (partition by ref, code order by [TransactionDate]) ) as group_no from LevyTransactions t ) SELECT Ref, MIN(Code) AS Code, MIN(TransactionDate) AS StartDate, MAX(TransactionDate) AS EndDate FROM movements GROUP BY Ref, group_no ORDER BY 1,3 **GOD BLESS YOU ALL**
21 People are following this question.
