June 5, 2008 at 4:25 am
Hi,
Please help me in working out the below problem. I have a table with few columns and one being a datetime column. i need to select the records for the date range passed as a parameter and at the same time display the values for the same date previous year.
eg: if i pass 1-1-2008, 31-1-2008,
then i need to display columns 1, 2, 3 for the given range and another 3 columns depicting the values for the date 1-1-2007 and 31-1-2007
June 5, 2008 at 4:48 am
1) Create a temporary table with required columns (3 columns for given year, 3 for previous year).
2) Insert values to the first 3 columns for the date range passes as parameter
3) Update values to the other 3 columns with data of the previous year.
4) Select from the temporary table.
Suresh
June 5, 2008 at 5:28 am
You can join a table to itself.
Here is a sample, the join with a function in it may be a performance issue.
[font="Courier New"]SELECT
A.Col1
, A.Col2
, A.Col3
, A.DateCol
, B.Col1
, B.Col2
, B.Col3
, B.DateCol
FROM
MyTable A
INNER JOIN MyTable B ON DATEADD(YEAR,-1,A.DateCol) = B.DateCol
WHERE
A.DateCol BETWEEN @FromDate AND @ToDate
/* The following is not necessary, but may help performance */
AND B.DateCol BETWEEN DATEADD(YEAR,-1,@FromDate) AND DATEADD(YEAR,-1,@ToDate)[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply