How to get data on Quarterly basis? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2012 Forums

Transact-SQL (2012)

How to get data on Quarterly basis?

Author

Topic

eligiable
Starting Member

4 Posts

Posted - 2014-05-14 : 04:39:26

I need to get results on quarterly basis, matching 2 quarters AUTOMATICALLY.
- As the new quarter starts, it needs to match the last quarter results.
Found something, but STUCK! Thanx in Advance ...


SELECT DATEADD(mm, (QUARTER - 1) * 3, year_date) StartDate,
       DATEADD(dd, -1, DATEADD(mm, QUARTER * 3, year_date)) EndDate,
       QUARTER QuarterNo
FROM
(
	SELECT '2013-09-01' year_date
) s CROSS JOIN 
(
	SELECT 1 QUARTER UNION ALL
	SELECT 2 UNION ALL
	SELECT 3 UNION ALL
	SELECT 4
) q

Here is my Query, I don't know whether I'm getting it right?


--Quarter 1
SELECT	D.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO	#Quarter1
FROM	dbo.tblRPT_Spend AS A INNER JOIN
		dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
		dbo.tblMer_Outlet AS C ON B.OutletID = C.OutletID LEFT OUTER JOIN
		dbo.tblGen_Merchants AS D ON C.MerchantID = D.MerchantID LEFT OUTER JOIN
		dbo.tblGen_Users AS E ON C.SignedByUserID = E.UserID
WHERE	E.GroupID = 4 AND E.ISActive = 2 AND A.SpendFrom >= '2013-09-01' AND SpendTo <= '2013-11-30'
		--AND YEAR(DATEADD(MONTH, -1, GeneratedON)) = '2013-09-01'
GROUP BY D.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail
ORDER BY E.FullName, A.MID, A.TID

--Quarter 2
SELECT	D.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO	#Quarter2
FROM	dbo.tblRPT_Spend AS A INNER JOIN
		dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
		dbo.tblMer_Outlet AS C ON B.OutletID = C.OutletID LEFT OUTER JOIN
		dbo.tblGen_Merchants AS D ON C.MerchantID = D.MerchantID LEFT OUTER JOIN
		dbo.tblGen_Users AS E ON C.SignedByUserID = E.UserID
WHERE	E.GroupID = 4 AND E.ISActive = 2 AND A.SpendFrom >= '2013-12-01' AND SpendTo <= '2014-02-28'
		--AND YEAR(DATEADD(MONTH, -1, GeneratedON)) = '2014'
GROUP BY D.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail
ORDER BY E.FullName, A.MID, A.TID

--DROP TABLE #Quarter1, #Quarter2

SELECT * FROM #Quarter1
SELECT * FROM #Quarter2

--Result
SELECT A.MerchantName, A.MID, A.TID, A.FullName, A.DxBEmail, ISNULL(A.SumTrxnMon, 0) AS SumTrxnMonA, ISNULL(B.SumTrxnMon, 0) AS SumTrxnMonB, ISNULL(B.SumTrxnMon, 0) - ISNULL(A.SumTrxnMon, 0) AS SumTrxnMonDiff
FROM #Quarter1 A, #Quarter2 B
WHERE A.TID = B.TID AND ISNULL(B.SumTrxnMon, 0) - ISNULL(A.SumTrxnMon, 0) <= '50000'
ORDER BY A.FullName, A.MID, A.TID

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-05-14 : 06:18:54

;WITH CTE
AS
(
SELECT DATEADD(mm, (QUARTER - 1) * 3, year_date) StartDate,
DATEADD(dd, -1, DATEADD(mm, QUARTER * 3, year_date)) EndDate,
QUARTER QuarterNo
FROM
(
SELECT '2014-01-01' year_date
) s CROSS JOIN
(
SELECT 1 QUARTER UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
) q
)
SELECT D.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
FROM dbo.tblRPT_Spend AS A INNER JOIN
dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
dbo.tblMer_Outlet AS C ON B.OutletID = C.OutletID LEFT OUTER JOIN
dbo.tblGen_Merchants AS D ON C.MerchantID = D.MerchantID LEFT OUTER JOIN
dbo.tblGen_Users AS E ON C.SignedByUserID = E.UserID INNER JOIN
CTE AS Ct ON A.SpendFrom BETWEEN Ct.StartDate AND ct.EndDate
WHERE E.GroupID = 4 AND E.ISActive = 2 AND A.SpendFrom BETWEEN CT.StartDate AND CTE.EndDate
AND ISNULL(B.SumTrxnMon, 0) - ISNULL(A.SumTrxnMon, 0) <= '50000'
AND ct.QuarterNo IN (1,2)
--AND YEAR(DATEADD(MONTH, -1, GeneratedON)) = '2013-09-01'
GROUP BY D.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail
ORDER BY E.FullName, A.MID, A.TID

Veera

eligiable
Starting Member

4 Posts

Posted - 2014-05-15 : 08:48:30

Thanx VeeranjaneyuluAnnapureddy!
Here I'm matching two Results SETS and finding a difference out of both.
Whereas in your case, I'm not able to do that.

No matter what, I'm getting the same result.

Thanx in Advance ...

Here something I changed, can you please pick the odds out ...



--Quarter 1
WITH Quarter1_CTE (MerchantName, MID, TID, FullName, DxBEmail, SumTrxnMon)
AS
(
SELECT	D.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon
FROM	dbo.tblRPT_Spend AS A INNER JOIN
		dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
		dbo.tblMer_Outlet AS C ON B.OutletID = C.OutletID LEFT OUTER JOIN
		dbo.tblGen_Merchants AS D ON C.MerchantID = D.MerchantID LEFT OUTER JOIN
		dbo.tblGen_Users AS E ON C.SignedByUserID = E.UserID
WHERE	E.GroupID = 4 AND E.ISActive = 2 AND DATEADD(MONTH, -1, A.GeneratedON) BETWEEN '2013-09-01' AND '2013-11-30'
GROUP BY D.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail
),

--Quarter 2
Quarter2_CTE (MerchantName, MID, TID, FullName, DxBEmail, SumTrxnMon)
AS
(
SELECT	D.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon
FROM	dbo.tblRPT_Spend AS A INNER JOIN
		dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
		dbo.tblMer_Outlet AS C ON B.OutletID = C.OutletID LEFT OUTER JOIN
		dbo.tblGen_Merchants AS D ON C.MerchantID = D.MerchantID LEFT OUTER JOIN
		dbo.tblGen_Users AS E ON C.SignedByUserID = E.UserID
WHERE	E.GroupID = 4 AND E.ISActive = 2 AND DATEADD(MONTH, -1, A.GeneratedON) BETWEEN '2013-12-01' AND '2014-02-28'
GROUP BY D.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail
)

SELECT A.MerchantName, A.MID, A.TID, A.FullName, A.DxBEmail, A.SumTrxnMon, B.SumTrxnMon, ISNULL(B.SumTrxnMon, 0) - ISNULL(A.SumTrxnMon, 0) AS SumTrxnMonDiff
FROM Quarter1_CTE A INNER JOIN Quarter2_CTE B ON A.TID = B.TID
WHERE A.TID = B.TID AND ISNULL(B.SumTrxnMon, 0) - ISNULL(A.SumTrxnMon, 0) <= '50000'
ORDER BY A.FullName, A.MID, A.TID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources