Refer to:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574
Also search the archives of this newsgroup (google) for similar requests &
solutions.

--
Anith

Hi All,

I have a table "COLLECTIONS" with the folowing DATA.

ID COLLECTION_DT AMOUNT
--------------------------------------------
1 01/15/2002 100
2 01/16/2002 75
3 02/20/2002 50
4 02/21/2002 25
5 01/18/2003 150
6 01/19/2003 200
7 02/13/2003 75
8 02/14/2003 124
----------------------------------------------

I am trying to get a output as below using a SQL Query:

CURRENT_YEAR CURRENT_MONTH AMOUNT1 PREV_YEAR PREV_MONTH
AMOUNT2
----------------------------------------------------------------------------
----------------------------------------------
2003 01 350
2002 01 175
2003 02 199
2002 02 75
----------------------------------------------------------------------------
-----------------------------------------------

Here the Amount1 column is the SUM(AMOUNT) for the each MONTH of the YEAR
and the Amont2 column is the
SUM(AMOUNT) the Same Month of the Previous Year.

Can Any one help me Building a SQL Query for this?

Thanks in Advance
Prabhat

Hi All,

I have a table "COLLECTIONS" with the folowing DATA.

ID COLLECTION_DT AMOUNT
--------------------------------------------
1 01/15/2002 100
2 01/16/2002 75
3 02/20/2002 50
4 02/21/2002 25
5 01/18/2003 150
6 01/19/2003 200
7 02/13/2003 75
8 02/14/2003 124
----------------------------------------------

I am trying to get a output as below using a SQL Query:

Curr_Year Curr_Month Amt1 Prev_Year Prev_Month Amt2
----------------------------------------------------------------------------
-
2003 01 350 2002 01
175
2003 02 199 2002 02
75
----------------------------------------------------------------------------
--

Here the Amount1 column is the SUM(AMOUNT) for the each MONTH of the YEAR
and the Amont2 column is the SUM(AMOUNT) the Same Month of the Previous
Year.

Can Any one help me Building a SQL Query for this?

Thanks in Advance
Prabhat

Hi,

The Link you have given is good. But I am still confused how to get the
output.

If anybody can help. Manu Thanks.
Prabhat

[quoted text, click to view]

Do:

SELECT MAX(CASE YEAR(collection_dt) WHEN 2003
THEN YEAR(collection_dt) END),
MAX(CASE YEAR(collection_dt) WHEN 2003
THEN MONTH(collection_dt) END),
SUM(CASE YEAR(collection_dt) WHEN 2003
THEN Amount END),
MAX(CASE YEAR(collection_dt) WHEN 2002
THEN YEAR(collection_dt) END),
MAX(CASE YEAR(collection_dt) WHEN 2002
THEN MONTH(collection_dt) END),
SUM(CASE YEAR(collection_dt) WHEN 2002
THEN Amount END)
FROM tbl
GROUP BY MONTH(collection_dt) ;

--
- Anith
( Please reply to newsgroups only )

Hi
This another one
CREATE TABLE #foo
(
dealer int,
account datetime,
amount INT
)

INSERT #foo VALUES(1,'20020101',200)
INSERT #foo VALUES(2,'20020101',150)
INSERT #foo VALUES(3,'20020101',500)
INSERT #foo VALUES(4,'20030101',350)
INSERT #foo VALUES(5,'20030101',800)
INSERT #foo VALUES(6,'20030101',250)

select * from #foo

SELECT year(account) 'Year',
SUM(CASE YEAR(account)
WHEN 2002 THEN 1
WHEN 2003 THEN 1
END * amount) AS diff
FROM #foo
GROUP BY year(account)


[quoted text, click to view]

You can use RAC for this. Please take a look at the address in sig.

--
-oj
http://www.rac4sql.net


[quoted text, click to view]

Thanks

But I will not know what will be the Dates and years will be there in the
database. So we can't Hardcode that in the QUERY.

Thanks
Prabhat

[quoted text, click to view]