May 14, 2016 at 3:07 am
Hi All
I have facing problem to display information like Lag function, but Lag is not working on sql server 2012,
I want wo display data like tha...
Month_NameTotal_Test Test
January 39152 0
February 37240 39152
March 41012 37240
April 39839
May 41823
June 34325
July 32885
August 35795
September 35365
October 37878
November 32039
December 28056
I want to display in next column like January data come in February, February data on march like all...
How to do this plz help me....
Thanks..
May 14, 2016 at 3:23 am
LAG is available on SQL 2012.
what have you tried that isnt working?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 14, 2016 at 3:30 am
Set DateFormat DMY
Select DATENAME(MM,TransHdr.TransDate)As Month_Name,--(It Shows Only Months Name)
Count(TransHdr.TransNum) As Total_Test,
LAG(TransHdr.TransNum,1,0)As Prvs_transaction-- Over (Order by Month(TransHdr.TransDate)) As Prvs_transaction)
From TransHdr
Where ISNULL(TransHdr.CancelDate,0)=0
And TransHdr.TransDate Between '01/01/2015' And '31/12/2015'
Group By Month(TransHdr.TransDate),
DATENAME(MM,TransHdr.TransDate)
Order By Month(TransHdr.TransDate)
Error is-----'LAG' is not a recognized built-in function name.
May 14, 2016 at 3:40 am
zahid.techk (5/14/2016)
Hi AllI have facing problem to display information like Lag function, but Lag is not working on sql server 2012,
I want wo display data like tha...
Month_NameTotal_Test Test
January 39152 0
February 37240 39152
March 41012 37240
April 39839
May 41823
June 34325
July 32885
August 35795
September 35365
October 37878
November 32039
December 28056
I want to display in next column like January data come in February, February data on march like all...
How to do this plz help me....
Thanks..
The LAG function works fine on SQL Server 2012 and later, it is your data that is flawed as it does not contain any proper column to order the data on. It does not make any sense using an order on month names, you could convert those into dates or add another numerical order column like I did in the example below.
😎
DECLARE @SAMPLE_DATA TABLE
(
SD_ID INT NOT NULL
,Month_NameVARCHAR(25) NOT NULL
,Total_Test INT NOT NULL
);
INSERT INTO @SAMPLE_DATA(SD_ID, Month_Name ,Total_Test)
VALUES ( 1, 'January' ,39152)
,( 2, 'February' ,37240)
,( 3, 'March' ,41012)
,( 4, 'April' ,39839)
,( 5, 'May' ,41823)
,( 6, 'June' ,34325)
,( 7, 'July' ,32885)
,( 8, 'August' ,35795)
,( 9, 'September' ,35365)
,( 0, 'October' ,37878)
,( 11, 'November' ,32039)
,( 12, 'December' ,28056);
SELECT
SD.Month_Name
,SD.Total_Test
,LAG(SD.Total_Test,1,0) OVER
(
ORDER BY SD.SD_ID ASC
) AS Test
FROM @SAMPLE_DATA SD
Output
Month_Name Total_Test Test
------------------------- ----------- -----------
October 37878 0
January 39152 37878
February 37240 39152
March 41012 37240
April 39839 41012
May 41823 39839
June 34325 41823
July 32885 34325
August 35795 32885
September 35365 35795
November 32039 35365
December 28056 32039
May 14, 2016 at 4:29 am
zahid.techk (5/14/2016)
Set DateFormat DMYSelect DATENAME(MM,TransHdr.TransDate)As Month_Name,--(It Shows Only Months Name)
Count(TransHdr.TransNum) As Total_Test,
LAG(TransHdr.TransNum,1,0)As Prvs_transaction-- Over (Order by Month(TransHdr.TransDate)) As Prvs_transaction)
From TransHdr
Where ISNULL(TransHdr.CancelDate,0)=0
And TransHdr.TransDate Between '01/01/2015' And '31/12/2015'
Group By Month(TransHdr.TransDate),
DATENAME(MM,TransHdr.TransDate)
Order By Month(TransHdr.TransDate)
Error is-----'LAG' is not a recognized built-in function name.
Providing you are using MS SQL 2012 and the compatabilty level of the db is 110 then I think the following is maybe what you are trying to do.
SET DATEFORMAT DMY;
CREATE TABLE #TransHdr(
TransDate DATETIME NOT NULL
,TransNum INTEGER NOT NULL
);
INSERT INTO #TransHdr(TransDate,TransNum) VALUES
('01/01/2016',15),('02/01/2016',16),('03/02/2016',17),('01/02/2016',12)
,('02/02/2016',13),('03/02/2016',14),('01/03/2016',5),('02/04/2016',6)
,('03/04/2016',7),('01/04/2016',22),('02/04/2016',23),('03/04/2016',24);
WITH sum_data as (
SELECT DATEADD(mm, DATEDIFF(mm, 0, TransDate), 0) AS Month_Start,
COUNT(TransNum) CntTrans
FROM #TransHdr
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, TransDate), 0)
)
SELECT DATENAME(MM, Month_Start) Month_Name,
CntTrans,
LAG(CntTrans, 1, 0) OVER(ORDER BY Month_Start) prior_month
FROM sum_data;
DROP TABLE #TransHdr;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply