dateadd Function

[ajmister]

Hi

I have a table

Create table tmp_name
( name char(15),
sales float,
yr char (4),
mth char (2)
)

and it has the following data

Joe Smith 15452.00 2001 03
Joe Smith 12157.00 2001 06
Joe Smith 14342.00 2001 09
Joe Smith 12429.00 2001 12
Joe Smith 13434.00 2002 03
Joe Smith 15529.00 2002 06
Joe Smith 15352.00 2002 09
Joe Smith 16436.00 2002 12

and I need to extract data for (max (yr) + max(mth)) and ((max(yr) +
max(mth)) - 12 mth) i.e.

name sales cur_yr cur_mth sales prv_yr
prv_mth
Joe Smith 16436.00 2002 12 12429.00 2001 12


Any suggestions
Ajay

 

[Phil Robyn]

Hi

I have a table

Create table tmp_name
( name char(15),
sales float,
yr char (4),
mth char (2)
)

and it has the following data

Joe Smith 15452.00 2001 03
Joe Smith 12157.00 2001 06
Joe Smith 14342.00 2001 09
Joe Smith 12429.00 2001 12
Joe Smith 13434.00 2002 03
Joe Smith 15529.00 2002 06
Joe Smith 15352.00 2002 09
Joe Smith 16436.00 2002 12

and I need to extract data for (max (yr) + max(mth)) and ((max(yr) +
max(mth)) - 12 mth) i.e.

name sales cur_yr cur_mth sales prv_yr
prv_mth
Joe Smith 16436.00 2002 12 12429.00 2001 12


Any suggestions
Ajay

- - - - - - - - - - begin screen capture Win2000 - - - - - - - - - -
C:\cmd>type c:\temp\mydata.dat
Joe Smith 15452.00 2001 03
Joe Smith 12157.00 2001 06
Joe Smith 14342.00 2001 09
Joe Smith 12429.00 2001 12
Joe Smith 13434.00 2002 03
Joe Smith 15529.00 2002 06
Joe Smith 15352.00 2002 09
Joe Smith 16436.00 2002 12

C:\cmd>demo\DoMyData
name sales cur_yr cur_mth sales prev_yr prev_mth
Joe Smith 16436.00 2002 12 12429.00 2001 12

C:\cmd>rlist demo\DoMyData.cmd
=====begin C:\cmd\demo\DoMyData.cmd ====================
01. @echo off
02. setlocal
03. for /f %%a in (
04. 'find /v /c "" ^< c:\temp\mydata.dat'
05. ) do set linecount=%%a
06. set /a start = linecount - 1
07. for /f "tokens=*" %%a in (
08. 'more /e +%start% ^< c:\temp\mydata.dat'
09. ) do set lastline=%%a
10. for /f "tokens=3-5" %%a in (
11. "%lastline%"
12. ) do set cur_sales=%%a&set cur_yr=%%b&set cur_month=%%c
13. set /a prev_yr = cur_yr - 1
14. for /f "tokens=*" %%a in (
15. 'findstr /c:"%prev_yr% %cur_month%" c:\temp\mydata.dat'
16. ) do set prev_results=%%a
17. echo name sales cur_yr cur_mth sales ^
18. prev_yr prev_mth
19. echo %prev_results:~0,9% %cur_sales% %cur_yr% %cur_month% ^
20. %prev_results:~11%
=====end C:\cmd\demo\DoMyData.cmd ====================
- - - - - - - - - - end screen capture Win2000 - - - - - - - - - -