March 24, 2011 at 8:20 pm
I Have table with 3 fields. EmployeeID,CheckDate,CheckAmount
I need to run a query by check date that I specify and will give the result in the following manner.
EmployeeID Current MTD QTD YTD
The current will be date I specify for example. 5/25/11.
The MTD will be total of all checks from 5/1/11 to 5/25/11
The QTD will be total of all checks from 4/1/11 to 5/25/11
The YTD will be total of all checks from 1/1/11 to 5/25/11
There should be only one row per employee and the dates are based on calendar year, quarter, and month.
Thanks
March 24, 2011 at 8:31 pm
Try this:
declare @UserDate DATETIME
select main.EID ,
MTD = ( select SUM(checkamount)
from table t_mtd
where t_tmd.EID = Main.EID
AND ( checkdate >= DATEADD( MONTH , datediff(MONTH ,0,@UserDate) , 0) AND
checkdate <= DATEADD( DD , datediff(DD ,0,@UserDate) , 0)
)
),
QTD = ( select SUM(checkamount)
from table t_qtd
where t_qtd.EID = Main.EID
AND ( t_qtd.checkdate >= DATEADD( quarter , datediff(quarter ,0,@UserDate) , 0) AND
t_qtd.checkdate <= DATEADD( DD , datediff(DD ,0,@UserDate) , 0)
)
) ,
YTD = ( select SUM(checkamount)
from table t_ytd
where t_ytd.EID = Main.EID
AND ( t_ytd.checkdate >= DATEADD( YEAR , datediff(YEAR ,0,@UserDate) , 0) AND
t_ytd.checkdate <= DATEADD( DD , datediff(DD ,0,@UserDate) , 0)
)
)
from YourTable Main
Group BY main.EID
April 5, 2011 at 8:07 am
Hi
I have no sample data to work with but maybe this will help you...
declare @UserDate datetime
;with cteMain AS (select * from Employees where datepart(yy, CheckDate) = datepart(yy,@UserDate)),
cteYTD as (select EmployeeID, sum (CheckAmount) as 'YTD' from cteMain),
cteQTD as (select EmployeedID, sum (CheckAmount) as 'QTD' from cteMain where datepart(q, CheckDate) = datepart(q, @UserDate)),
cteMTD as (select EmployeedID, sum (CheckAmount) as 'MTD' from cteMain where datepart(mm, CheckDate) = datepart(mm, @UserDate))
select y.EmployeeID, y.YTD, q.QTD, m.MTD from cteYTD y
join cteQTD q on y.EmployeeID=q.EmployeeID
join cteMTD m on y.EmployeeID=m.EmployeeID
I hope I have understood your problem correctly.
April 6, 2011 at 11:53 am
I would use some variables in the query to make it easier to read and do the whole thing in one pass rather than use 3 subqueries.
Something like:
DECLARE
@TheDate DATETIME
, @MTDStart DATETIME
, @QTDStart DATETIME
, @YTDStart DATETIME
SET @TheDate = '2011-05-25'
SET @MTDStart = DATEADD( MONTH , datediff(MONTH ,0,@TheDate) , 0)
SET @QTDStart = DATEADD( quarter , datediff(quarter ,0,@TheDate) , 0)
SET @YTDStart = DATEADD( YEAR , datediff(YEAR ,0,@TheDate) , 0)
SELECT
EmployeeID
, SUM(CASE WHEN CheckDate BETWEEN @YTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS YTD
, SUM(CASE WHEN CheckDate BETWEEN @QTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS QTD
, SUM(CASE WHEN CheckDate BETWEEN @MTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS MTD
FROM YourTable
A single pass through the data will make it run faster.
Todd Fifield
May 15, 2011 at 6:40 pm
tfifield (4/6/2011)
I would use some variables in the query to make it easier to read and do the whole thing in one pass rather than use 3 subqueries.Something like:
DECLARE
@TheDate DATETIME
, @MTDStart DATETIME
, @QTDStart DATETIME
, @YTDStart DATETIME
SET @TheDate = '2011-05-25'
SET @MTDStart = DATEADD( MONTH , datediff(MONTH ,0,@TheDate) , 0)
SET @QTDStart = DATEADD( quarter , datediff(quarter ,0,@TheDate) , 0)
SET @YTDStart = DATEADD( YEAR , datediff(YEAR ,0,@TheDate) , 0)
SELECT
EmployeeID
, SUM(CASE WHEN CheckDate BETWEEN @YTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS YTD
, SUM(CASE WHEN CheckDate BETWEEN @QTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS QTD
, SUM(CASE WHEN CheckDate BETWEEN @MTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS MTD
FROM YourTable
A single pass through the data will make it run faster.
Todd Fifield
Not just a little faster, either. Nicely done, Todd.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2011 at 9:13 pm
Jeff Moden (5/15/2011)
tfifield (4/6/2011)
I would use some variables in the query to make it easier to read and do the whole thing in one pass rather than use 3 subqueries.Something like:
DECLARE
@TheDate DATETIME
, @MTDStart DATETIME
, @QTDStart DATETIME
, @YTDStart DATETIME
SET @TheDate = '2011-05-25'
SET @MTDStart = DATEADD( MONTH , datediff(MONTH ,0,@TheDate) , 0)
SET @QTDStart = DATEADD( quarter , datediff(quarter ,0,@TheDate) , 0)
SET @YTDStart = DATEADD( YEAR , datediff(YEAR ,0,@TheDate) , 0)
SELECT
EmployeeID
, SUM(CASE WHEN CheckDate BETWEEN @YTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS YTD
, SUM(CASE WHEN CheckDate BETWEEN @QTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS QTD
, SUM(CASE WHEN CheckDate BETWEEN @MTDStart And @TheDate THEN CheckAmount ELSE 0 END) AS MTD
FROM YourTable
A single pass through the data will make it run faster.
Todd Fifield
Not just a little faster, either. Nicely done, Todd.
Thanks Jeff. I thought people were dis'ing me when the thread just ran cold after my post.
Todd
May 17, 2011 at 6:25 am
It's always hard to tell, Todd... did they quit because the got the correct answer (like yours) or did they quit because they don't think it's correct or did they quit because they got an answer from somewhere else? I hope it's the former in this case because, at work, I'm currently cleaning up some real performance problems that uses multiple correlated sub-queries in the SELECT and the method I'm using (Cross Tab) is my method of choice. I don't know if you ever saw it, but I also did a speed test between the Cross Tab method and PIVOTs and wrote an article about it. The PIVOT didn't do so well especially if the data was preaggregated by a CTE or Derived Table. 🙂
Looking back at it, you do need to make a minor correction though (I thought I just didn't scroll down far enough). You need to add a GROUP BY EmployeeID to your code. Yeah, I know... without test data to test on, it's difficult to catch everything.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2011 at 6:42 am
Howdy folks,
I was trying to NOT steal Todd's thunder because he posted the right idea... this problem needs a Cross Tab. The use of multiple correlated aggregate sub-queries in the main SELECT list will actually become the source of a huge performance problem as the data scales.
Like I told Todd in the post above, I'm repairing a bunch of such code at work right now. It's called by and times-out in a GUI on only 700,000 rows even with tight filtering. When the code is executed from SSMS, it takes over 25 minutes (I stopped it, then) to execute and it doesn't do anything more than simple sums similar to those is this post. Once I repaired (rewrote, actually) the code to use the Cross Tab method that Todd posted, the returns were nearly instantaneous with tight filtering and took only seconds with no filtering.
For more information on Cross Tabs, how they work, and why I think they're better than PIVOTs, please see the following article (which also includes some performance testing with the "normal" million row table).
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2011 at 1:37 pm
Jeff Moden (5/17/2011)
Howdy folks,For more information on Cross Tabs, how they work, and why I think they're better than PIVOTs, please see the following article (which also includes some performance testing with the "normal" million row table).
Jeff,
I would never think you were trying to steal my thunder (not that I have much thunder to start with - I prefer lightning).
I did a similar test and found that PIVOT tends to be from a little to a lot slower than the old fashioned Cross Tab. There was a particular case where an aggregate wasn't really necessary - I just wanted a pivot - and used MAX on the data column to satisfy a PIVOT requirement. The query plan showed the number of rows bloated out to the actual number of row times the number of pivot columns and then shrunk it back to the actual number of data rows.
However, I did bench mark UNPIVOT vs a bunch of UNION queries where the table was not normalized (had a bunch of repeating columns) and found that UNPIVOT will usually beat the pants off of a bunch of UNION queries. It also weeds out the NULLs quite nicely.
Todd Fifield
May 17, 2011 at 3:44 pm
tfifield (5/17/2011)
However, I did bench mark UNPIVOT vs a bunch of UNION queries where the table was not normalized (had a bunch of repeating columns) and found that UNPIVOT will usually beat the pants off of a bunch of UNION queries. It also weeds out the NULLs quite nicely.Todd Fifield
Remind me to show you the trick Paul White came up with using Cross Apply. It works especially nice when trying to unpivot a table with an unknown number of columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2011 at 7:23 am
Thanks Jeff. I thought people were dis'ing me when the thread just ran cold after my post.
Todd
Thread probably went cold because you came up with a great answer. I was going to post a CASE statement solution similar to yours when I saw your post. Considering the terrible speed at which I type, thanks for saving me the time!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 18, 2011 at 7:28 am
Jeff Moden (5/17/2011)
For more information on Cross Tabs, how they work, and why I think they're better than PIVOTs, please see the following article (which also includes some performance testing with the "normal" million row table).
Thanks for the article on pivots. I've read about them in the forums and felt "less than" since I've never used them, I always just used cross tabs, because that's just what made sense to me given my limited knowledge. Glad there's no performance boost from moving to pivots - one less thing to have to master.
In general, I'm becoming a fan of your posts. If I had a son, I'd name him Million Row Table in your honor. 🙂
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 22, 2011 at 11:55 am
Thomas Abraham (5/18/2011)
In general, I'm becoming a fan of your posts.
Thanks for the great feedback, Thomas. :blush:
If I had a son, I'd name him Million Row Table in your honor. 🙂
That's quite the honor especially since my Mom really wanted to name me "Runs with Scissors". 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2011 at 12:53 pm
Reminder... 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply