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