Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get data on Quarterly basis? Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
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

Post #1569793
Posted Monday, May 12, 2014 6:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 2,181, Visits: 5,909
If you are on Sql Server 2012 or 2014, you should definitely look into the over clause and the window functions, perfect for this type of work.
Post #1569798
Posted Monday, May 12, 2014 6:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 4,396, Visits: 6,251
Here you will find a session I did on SQL Server 2012 Windowing Functions. Should get you what you need. There are two other great sessions from me on that schedule page: Common TSQL Mistakes and SQL Injection.

http://www.sqlsaturday.com/249/schedule.aspx


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1569829
Posted Wednesday, May 14, 2014 2:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
A general help is appreciated.
Post #1570675
Posted Monday, May 19, 2014 12:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:29 AM
Points: 19, Visits: 41
Made some modifications with CTE, but still no LUCK ...
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
) Y CROSS JOIN
(
SELECT 1 QUARTER UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
) Z

Need to incorporate these two statements to work automatically.

--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

Post #1572149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse