September 14, 2009 at 11:47 am
select distinct
A.Col1,A.Col2,A.Col3,A1.total as [current_month]
,A2.total as [current_month-1],A3.total as [current_month-2]
from viewV1 as A LEFT OUTER JOIN
viewV1 AS A1 ON A.Col1 = A1.Col1
and DATEADD(m, - 1, A.FLDT)) = 0 LEFT OUTER JOIN
viewV1 AS A2 ON A.Col1 = A2.Col1
and DATEADD(m, - 2, A.FLDT)) = 0 LEFT OUTER JOIN
viewV1 AS A3 ON A.Col1 = A3.Col1
and DATEADD(m, - 3, A.FLDT)) = 0
where A.FLDT='09'
From the above i expect only 1 record where in i get 3 records, may be because i had 3 joins, how would i get just 1 records with 3 months of total in the same record.
September 14, 2009 at 11:54 am
It would help if you provided the DDL for the view (and the underlying tables), sample data for the underlying tables, and your expected results based on the sample data.
September 14, 2009 at 2:52 pm
Sample data would help a lot, but this may help. Maybe it will at least give you an idea.
selectA.Col1,
A.Col2,
A.Col3,
SUM(A1.total) as [current_month],
SUM(A2.total) as [current_month-1],
SUM(A3.total) as [current_month-2]
fromviewV1 as A
LEFT JOIN viewV1 AS A1
ON A.Col1 = A1.Col1 and DATEADD(m, - 1, A.FLDT)) = 0
LEFT JOIN viewV1 AS A2
ON A.Col1 = A2.Col1 and DATEADD(m, - 2, A.FLDT)) = 0
LEFT JOIN viewV1 AS A3
ON A.Col1 = A3.Col1 and DATEADD(m, - 3, A.FLDT)) = 0
whereA.FLDT='09'
groupby A.Col1, A.Col2, A.Col3
orderby A.Col1, A.Col2, A.Col3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply