Query to extract data in a specified format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sattaluri - 08 Sep 2005 21:47 GMT

I am new to SQL and need help in extracting some data from a table

My table has the following data -

FUND_ID        BEGIN_DATE    END_DATE    FEE
50        19910101    19930830    0.5
50        19930831    19980429    0.6
50        19980430    20000228    0.7
50        20000229    20030331    0.72
51        19620930    19950330    1.2
51        19950331    19990929    1.3
51        19990930    20031230    1.25
51        20031231    20050130    1.4
51        20050131    20050331    1.5%
52        19960531    19971129    0.2
52        19971130    19990530    0.25
52        19990531    20010530    0.3

The above dates are stored as numbers in the database

My output should be -

FUND_ID    JAN-94    FEB-94    MAR-94.....APR-01 MAY-01 JUN-01....JAN-03
FEB-03 MAR-03......JAN-05 FEB-05 MAR-0
50 (0.6/12
(0.6/12)(0.6/12)..(0.72/12)(0.72/12)(0.72/12).(0.72/12)(0.72/12)(0.72/12)..--    --    -
51 (1.2/12
(1.2/12)(1.2/12)..(1.25/12)(1.25/12)(1.25/12).(1.25/12)(1.25/12)(1.25/12)..(1.4/12
(1.5/12) (1.5/12
52 --    --    -- (0.3/12) (0.3/12) --.....--        --    --    ..
--        --    -

Reply to this Message

KARL DEWEY - 09 Sep 2005 01:12 GMT

How do you expect to get 12 separate monthly figures out for FUND_ID 50 with
only 4 records?

The percent sign should only be used as a a label and not in data unless it
is text like a sentence.
Dates need to be in datetime fields, not as a number. You will need to
convert your dates like --
SELECT Table18.FUND_ID,
DateSerial(Left([BEGIN_DATE],4),Right(Left([BEGIN_DATE],6),2),Right([BEGIN_DATE],2))
AS [BEGIN],
DateSerial(Left([END_DATE],4),Right(Left([END_DATE],6),2),Right([END_DATE],2)) AS [END], [FEES]/100 AS FEE
FROM Table18;

> I am new to SQL and need help in extracting some data from a table.
>
[quoted text clipped - 27 lines]
> 52 -- -- -- (0.3/12) (0.3/12) --.....-- -- -- ..
> -- -- --

Reply to this Message