May 12, 2014 at 5:57 am
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
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter1
FROMdbo.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
WHEREE.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
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter2
FROMdbo.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
WHEREE.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
May 12, 2014 at 6:08 am
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.
😎
May 12, 2014 at 6:55 am
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 on googles mail service
May 14, 2014 at 2:12 am
A general help is appreciated.
May 19, 2014 at 12:36 am
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
(
SELECTD.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon
FROMdbo.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
WHEREE.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
(
SELECTD.MerchantName, A.MID, A.TID, E.FullName, E.DxBEmail, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon
FROMdbo.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
WHEREE.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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply