Yesterday & MTD Calculations - SQL Server Forums

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

Transact-SQL (2005)

Yesterday & MTD Calculations

Author

Topic

HGClubTec
Starting Member

12 Posts

Posted - 2009-05-18 : 10:44:19

What I'm trying to accomplish is

Calculations for Amount, Tax & Gratuity for Yesterday
And calculations for Amount, Tax & Gratuity for Month-To-Date.

I think it might have to do with the MAX() function I'm putting on the date, but I don't know of a way around having a function there. I feel like I'm making it more complicated than it really is. Here is what I currently have.

SELECT
'YGrillFood' AS Label,
CASE WHEN (DATEDIFF(DD,MAX(TH.TransDate),GetDate())=1) THEN SUM(TD.Amount) END AS YAmount,
CASE WHEN (CONVERT(nvarchar,MAX(TH.TransDate), 101) = CONVERT(nvarchar,GetDate()-1, 101)) THEN SUM(TD.TaxTotal) END AS YTax,
CASE WHEN (CONVERT(nvarchar,MAX(TH. TransDate), 101) = CONVERT(nvarchar,GetDate()-1, 101)) THEN SUM(TD.ServiceChargeTotal) END AS YGrat,
CASE WHEN (DATEDIFF(MM,MAX(TH.TransDate),GETDATE())=0) THEN SUM(TD.Amount) END AS MTDAmount,
CASE WHEN (DATEDIFF(MM,MAX(TH.TransDate),GETDATE())=0) THEN SUM(TD.TaxTotal) END AS MTDTax,
CASE WHEN (DATEDIFF(MM,MAX(TH.TransDate),GETDATE())=0) THEN SUM(TD.ServiceChargeTotal) END AS MTDGrat
FROM TransHeader TH LEFT OUTER JOIN TransDetail TD ON TH.ID = TD.TransHeaderID
LEFT OUTER JOIN TransMain TM ON TH.TransMainID = TM.ID
WHERE
TD.RevenueCodeID IN (144, 290)
AND TM.BatchStatusENUM IN (3,4)

TIA for any help y'all can give!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-18 : 11:01:25

[code]
DECLARE @TODAY datetime

SELECT @TODAY = DATEADD(DAY, DATEDIFF(DAY, 0, GETDAT()), 0)

SELECT
'YGrillFood' AS Label,
YAmount = SUM(CASE WHEN TH.TransDate >= DATEADD(DAY, -1, @TODAY)
AND TH.TransDate < DATEADD(DAY, 1, @TODAY)
THEN TD.Amount
ELSE 0
END),
YTax = SUM(CASE WHEN TH.TransDate >= DATEADD(DAY, -1, @TODAY)
AND TH.TransDate < DATEADD(DAY, 1, @TODAY)
THEN TD.TaxTotal
ELSE 0
END),
YGrat = SUM(CASE WHEN TH.TransDate >= DATEADD(DAY, -1, @TODAY)
AND TH.TransDate < DATEADD(DAY, 1, @TODAY)
THEN TD.ServiceChargeTotal
ELSE 0
END),
MTDAmount = SUM(CASE WHEN TH.TransDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY), 0)
AND TH.TransDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY) + 1, -1)
THEN TD.Amount
ELSE 0
END),
MTDTax = SUM(CASE WHEN TH.TransDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY), 0)
AND TH.TransDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY) + 1, -1)
THEN TD.TaxTotal
ELSE 0
END),
MTDGrat = SUM(CASE WHEN TH.TransDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY), 0)
AND TH.TransDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @TODAY) + 1, -1)
THEN TD.ServiceChargeTotal
ELSE 0
END)
FROM TransHeader TH
LEFT OUTER JOIN TransDetail TD ON TH.ID = TD.TransHeaderID
LEFT OUTER JOIN TransMain TM ON TH.TransMainID = TM.ID
WHERE TD.RevenueCodeID IN (144, 290)
AND TM.BatchStatusENUM IN (3,4)

[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources