Help with SQL Query

[johnhugh]

Hi, I'm using SQL Server 2005.

I'm trying to create a query which returns inventory values of the last 12 months.
The table only contains transactions, no monthly totals, so I need to calculate every month individually.
I later want to create a chart for the last 12 months so I only want to see the last 12 months total grouped by year/month.

How can I create a select which returns only the last 12 months, but each months contains the totals of all previous months?

SELECT     ICIVAL.FISCYEAR AS ICYear, ICIVAL.FISCPERIOD AS ICMonth, SUM(ICIVAL.TRANSCOST) AS Cost, LEFT(ICIVAL.LOCATION, 2) AS Country
FROM         ICIVAL AS ICIVAL INNER JOIN
                      ICITEM AS ICITEM ON ICIVAL.ITEMNO = ICITEM.ITEMNO
WHERE     ({ fn SUBSTRING(ICIVAL.LOCATION, 1, 2) } = '10')
GROUP BY ICIVAL.FISCYEAR, ICIVAL.FISCPERIOD, LEFT(ICIVAL.LOCATION, 2)
ORDER BY ICYear, ICMonth

This Statement gives me a table like below.

ICYear ICMonth  Cost            Country
2006	1	0.000	        10
2006	3	31000.000	10
2006	12	672434.970	10
2007	1	107245.920	10
2007	2	48335.130	10
2007	3	254755.000	10
2007	4	92885.340	10
2007	5	133756.410	10
2007	6	-139055.240	10
2008	1	36350.950	10
2008	2	-144585.670	10
2008	3	277219.220	10

How do I now calculate the monthly totals for i.e. 2013?
For example the monthly total for April 2013 is the sum of all previous years & months.
How do I do this in SQL?

 

[simian336]

Try this... You would not group on year or month since you only want a single month.

SELECT
SUM(ICIVAL.TRANSCOST) AS Cost,
LEFT(ICIVAL.LOCATION, 2) AS Country
FROM ICIVAL AS ICIVAL
INNER JOIN ICITEM AS ICITEM
ON ICIVAL.ITEMNO = ICITEM.ITEMNO
WHERE ({ fn SUBSTRING(ICIVAL.LOCATION, 1, 2) } = '10')
and ICIVAL.FISCYEAR <= 2013
and ICIVAL.FISCPERIOD <= 5
GROUP BY LEFT(ICIVAL.LOCATION, 2)

Simi

 

[imex]

Hi,

Try something like this:

with CTE_T as
(
    SELECT 
        ICIVAL.FISCYEAR AS ICYear, 
        ICIVAL.FISCPERIOD AS ICMonth, 
        SUM(ICIVAL.TRANSCOST) AS Cost, 
        LEFT(ICIVAL.LOCATION, 2) AS Country
    FROM ICIVAL 
    INNER JOIN ICITEM 
        ON ICIVAL.ITEMNO = ICITEM.ITEMNO
    WHERE ({ fn SUBSTRING(ICIVAL.LOCATION, 1, 2) } = '10')
    GROUP BY ICIVAL.FISCYEAR, ICIVAL.FISCPERIOD, LEFT(ICIVAL.LOCATION, 2)
)

select
    t.*,
    (select SUM(s.Cost) 
     from CTE_T as s
     where 
         s.ICYear < t.ICYear or
         (s.ICYear = t.ICYear and s.ICMonth < t.ICMonth)) as TotMonth
from CTE_T as t
where 
    t.ICYear > 2012 or 
    (t.ICYear = 2012 and t.ICMonth > 4)
ORDER BY t.ICYear, t.ICMonth

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[johnhugh]

Thanks all.
Got it working now!