# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Calculate mtd and ytd.

## Ron SIssons

I have a table that has a providername and totals field. 
I want to be able to calculate the MTD and YTD totals for each provider.

EX. Provider   MTD        YTD

    Bob      100.00      300.00    

But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks 
Ron

----------


## Alex

how do you get them separatly ?
could you post you code we could combine it
I can get each numeric value on seperately  like 
------------------------------------------------ 
1                             
0                             
0                             
.                             
0                             
0                             
2                             
0                             
0                             
.                             
0                             
0                             
-------------------------- 
bob 100.00 200.00
---------------------------

------------
Ron SIssons at 3/7/01 11:43:21 AM

I have a table that has a providername and totals field. 
I want to be able to calculate the MTD and YTD totals for each provider.

EX. Provider   MTD        YTD

    Bob      100.00      300.00    

But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks 
Ron

----------


## Jim W

Well, probably the simplest way to do it is to union the data. You get one set of rows for mtd and one set for ytd.

If you want them on one row with both, you can do it by throwing the mtd into a temp table, and updating the temp table with the results from the ytd query.

It may be possible to do it in one query and no temp tables if you join the sales tables twice, but you might have to use a case statement to avoid getting too much data in the mtd column.

If you only have one row and two columns, you could also do it like so:
select (
    select sum(sales) from salestable where...
       ) as mtd, (
    select sum(sales) from salestable where...
       ) as ytd


------------
Alex at 3/7/01 3:44:42 PM

how do you get them separatly ?
could you post you code we could combine it
I can get each numeric value on seperately  like 
------------------------------------------------ 
1                             
0                             
0                             
.                             
0                             
0                             
2                             
0                             
0                             
.                             
0                             
0                             
-------------------------- 
bob 100.00 200.00
---------------------------

------------
Ron SIssons at 3/7/01 11:43:21 AM

I have a table that has a providername and totals field. 
I want to be able to calculate the MTD and YTD totals for each provider.

EX. Provider   MTD        YTD

    Bob      100.00      300.00    

But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks 
Ron

----------


## Dale Shaw

Hi

Two ways, corelated subquery and a CASE statement.

1) Subquery:

use pubs

DECLARE @Today datetime
SET @Today=&#39;1993-10-30&#39; --Would be Getdate()

SELECT Title_ID,
(
SELECT Sum(qty) 
FROM Sales 
WHERE year(ord_date)=year(@Today) 
AND month(ord_date)=month(@Today)
AND Sales.Title_ID=Titles.Title_ID
) AS MTD,
(
SELECT Sum(qty) 
FROM Sales 
WHERE year(ord_date)=year(@Today) 
AND month(ord_date)<=month(@Today)
AND Sales.Title_ID=Titles.Title_ID
) AS YTD
FROM Titles
ORDER BY Title_ID

2) CASE

use pubs

DECLARE @Today datetime
SET @Today=&#39;1993-10-30&#39;--Would be Getdate()

SELECT Titles.Title_ID, 
  sum(CASE WHEN year(ord_date)=year(@Today) AND month(ord_date)=month(@Today) THEN qty ELSE 0 END) AS MTD,
  sum(CASE WHEN year(ord_date)=year(@Today) AND month(ord_date)<=month(@Today) THEN qty ELSE 0 END) AS YTD
FROM Titles 
INNER JOIN Sales
ON Titles.Title_ID=Sales.Title_ID
GROUP BY Titles.Title_ID
ORDER BY Titles.Title_ID

----------

