March 8, 2012 at 2:04 pm
I have query that I need to sum data on.
the query would be like
select name, sum(total) YTD, sum(total) Today
What is the best practice for designing a query to do this.
The query needs to run nightly and send out an email.
March 8, 2012 at 2:05 pm
The question is a bit vague, so the only thing I can add is that you will also need a GROUP BY clause to make your query work.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2012 at 2:09 pm
Sorry for being vague I know I need the group by.
Select user, sum(sales) ytd, sum(sales) today
where salesdate>user.Startdate
group by user
This give me the users year to date sales but I also need the sales for the previous day.
March 8, 2012 at 2:12 pm
See the first two links in my signature? Read them and then post back. It will allow us to help you a whole lot better.
Right now it's too much guessing involved...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2012 at 2:34 pm
SELECT
name,
SUM(CASE WHEN date_column >= CAST(YEAR(GETDATE()) AS char(4)) + '0101'
THEN total ELSE 0 END) AS YTD,
SUM(CASE WHEN date_column >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
date_column < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
THEN total ELSE 0 END) AS Today
FROM dbo.table_name
GROUP BY
name
ORDER BY
name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2012 at 3:14 pm
For performance would it be better to handle the YTD sum using a WHERE clause in the SELECT or is good to just do it in CASE statement
March 8, 2012 at 3:23 pm
I would just use a CASE statement.
But you raised a good point I overlooked.
If you do need only current year's data, I should have added a WHERE clause with a condition on the year:
WHERE
date_column >= CAST(YEAR(GETDATE()) AS char(4)) + '0101'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2012 at 3:54 pm
Or you could do it this way. Notice the slight changes in the code:
SELECT
name,
SUM(CASE WHEN date_column >= DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0)
THEN total ELSE 0 END) AS YTD,
SUM(CASE WHEN date_column >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
date_column < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
THEN total ELSE 0 END) AS Today
FROM
dbo.table_name
WHERE
date_column >= DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0)
GROUP BY
name
ORDER BY
name
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply