compare data for each date

Question

Post reply

compare data for each date

x_japanfans5312

SSC-Addicted

Points: 458
More actions
August 21, 2011 at 7:31 pm

#255967

Hi,
i have a problem with comparing the data from the several period with the previous data
the problem is that i need to display the data side by side for each date.
meaning date if the choosen date range are between 01-JUL-2011 until 07-AUG-2011
it will show like this
CURRENT PREVIOUS
01-Jul-1101-Jun-11
02-Jul-1102-Jun-11
03-Jul-1103-Jun-11
04-Jul-1104-Jun-11
05-Jul-1105-Jun-11
06-Jul-1106-Jun-11
07-Jul-1107-Jun-11
08-Jul-1108-Jun-11
09-Jul-1109-Jun-11
10-Jul-1110-Jun-11
11-Jul-1111-Jun-11
12-Jul-1112-Jun-11
13-Jul-1113-Jun-11
14-Jul-1114-Jun-11
15-Jul-1115-Jun-11
16-Jul-1116-Jun-11
17-Jul-1117-Jun-11
18-Jul-1118-Jun-11
19-Jul-1119-Jun-11
20-Jul-1120-Jun-11
21-Jul-1121-Jun-11
22-Jul-1122-Jun-11
23-Jul-1123-Jun-11
24-Jul-1124-Jun-11
25-Jul-1125-Jun-11
26-Jul-1126-Jun-11
27-Jul-1127-Jun-11
28-Jul-1128-Jun-11
29-Jul-1129-Jun-11
30-Jul-1130-Jun-11
31-Jul-11NULL
01-Aug-1101-Jul-11
02-Aug-1102-Jul-11
03-Aug-1103-Jul-11
04-Aug-1104-Jul-11
05-Aug-1105-Jul-11
06-Aug-1106-Jul-11
07-Aug-1107-Jul-11
or like this
CURRENT PREVIOUS
01-Sep-1101-Aug-11
02-Sep-1102-Aug-11
03-Sep-1103-Aug-11
04-Sep-1104-Aug-11
05-Sep-1105-Aug-11
06-Sep-1106-Aug-11
07-Sep-1107-Aug-11
08-Sep-1108-Aug-11
09-Sep-1109-Aug-11
10-Sep-1110-Aug-11
11-Sep-1111-Aug-11
12-Sep-1112-Aug-11
13-Sep-1113-Aug-11
14-Sep-1114-Aug-11
15-Sep-1115-Aug-11
16-Sep-1116-Aug-11
17-Sep-1117-Aug-11
18-Sep-1118-Aug-11
19-Sep-1119-Aug-11
20-Sep-1120-Aug-11
21-Sep-1121-Aug-11
22-Sep-1122-Aug-11
23-Sep-1123-Aug-11
24-Sep-1124-Aug-11
25-Sep-1125-Aug-11
26-Sep-1126-Aug-11
27-Sep-1127-Aug-11
28-Sep-1128-Aug-11
29-Sep-1129-Aug-11
30-Sep-1130-Aug-11
NULL 31-Aug-11
01-Oct-1101-Sep-11
02-Oct-1102-Sep-11
03-Oct-1103-Sep-11
04-Oct-1104-Sep-11
I am thinking to use the dateadd functionality but i think it won't work, since after 30-Jun-2011 it will jump to 01-Jul-2011. i need to return so called "31-JUN-2011" but since it is invalid then it become NULL.
any help will be appreciated.
I have created a table that has rows from 1- 1000 and trying to query like this.
SELECT
CASE WHEN
ISDATE(CAST((Number % 32) AS VARCHAR(20)) + '-' + DATENAME(MONTH,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartDate )) +'-' + CAST(DATEPART(YEAR,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartDate ))AS VARCHAR(20))) = 1
THEN CAST((Number % 32) AS VARCHAR(20)) + '-' + DATENAME(MONTH,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartDate )) +'-' + CAST(DATEPART(YEAR,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartDate ))AS VARCHAR(20))
ELSE NULL
END CurrentMonth,
CASE WHEN
ISDATE(CAST((Number % 32) AS VARCHAR(20)) + '-' + DATENAME(MONTH,DATEADD(MONTH,-1,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartNewDate))) +'-' + CAST(DATEPART(YEAR,DATEADD(MONTH,-1,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartDate ))) AS VARCHAR(20))) = 1
THEN CAST((Number % 32) AS VARCHAR(20)) + '-' + DATENAME(MONTH,DATEADD(MONTH,-1,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartNewDate))) +'-' + CAST(DATEPART(YEAR,DATEADD(MONTH,-1,DATEADD(MONTH,((Number / 32) % 31 +1) -1,@StartDate ))) AS VARCHAR(20))
ELSE NULL
END PreviousMonth
FROM
Arch.Number
WHERE
Number % 32 <> 0
AND ..... BETWEEN @StartDate AND @EndDate
but then i have difficulty what is the condition after AND to limit the date range based on the specified date.
but if there is another way that more efficient, it will be great.
any help will be appreciated.
thanks before 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

x_japanfans5312 SSC-Addicted Points: 458 More actions · Answer 1

finally after trying..

i answer my own question

here is the solution

DECLARE @StartNewDate DATE

DECLARE @EndDate DATE

DECLARE @StartHistoryDate DATE

DECLARE @EndHistoryDate DATE

SELECT @StartNewDate = '01-SEP-2011'

SELECT @EndDate = '05-OCT-2011'

SELECT @StartHistoryDate = '01-AUG-2011'

SELECT @EndHistoryDate = '05-SEP-2011'

SELECT

[Current],

Previous,

N.Number,

N2.Number AA

FROM

(

SELECT

Number,

DATEADD(day,N.Number -1 ,@StartNewDate) [Current]

FROM

Arch.Number N

WHERE

Number < DATEDIFF(D,@StartNewDate,@EndDate)

)N

FULL JOIN

(

SELECT

Number,

DATEADD(day,N2.Number -1 ,@StartHistoryDate) Previous

FROM

Arch.Number N2

WHERE

Number < DATEDIFF(D,@StartHistoryDate,@EndHistoryDate)

)N2

ON( DATEPART(day,DATEADD(day,N.Number -1 ,@StartNewDate)) = DATEPART(DAY,DATEADD(DAY,N2.Number -1 ,@StartHistoryDate))

AND DATEPART(month,DATEADD(day,N.Number -1 ,@StartNewDate)) = DATEPART(MONTH,DATEADD(MONTH,1,DATEADD(DAY,N2.Number -1 ,@StartHistoryDate)) )

AND DATEPART(year,DATEADD(day,N.Number -1 ,@StartNewDate)) = DATEPART(YEAR,DATEADD(MONTH,1,DATEADD(DAY,N2.Number -1 ,@StartHistoryDate)) )

)

ORDER BY

COALESCE(N.Number, N2.Number)

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

A calendar table or a tally table (i.e. to generate a dynamic calendar table) would have been a lot easier.

And as a plus for these alternatives: if both periods don't have any data for a particular day, your solution will simply skip that row, not drawing any attention to that missing day. Using either of the both alternatives you would see null's in both columns, making the row "jump out".

Search for "tally table" or "calendar table" for numerous examples on this forum.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

Here is an example of using a calendar table as the 'Anchor' to the query. This particular example uses a day type table and the days when nothing happened show up.

http://www.sqlservercentral.com/articles/T-SQL/70743/">

http://www.sqlservercentral.com/articles/T-SQL/70743/

Todd Fifield