August 8, 2006 at 8:53 am
Hi,
I have a Cash_Table which has 4 columns.
I need to write a query to return the original 4 columns, plus minimum and maximum of Cash_WTD, Cash_PTD and Cash_Daily.
Is it possible? HOW TO DO IT?
Cash_Locale Cash_WTD Cash_PTD Cash_Daily
CA -207.95 -1263 -896
CN -853 -1585 -33
CS -32 -756 -15
CW -165 -586 -686
RA 22 -40 55
WI -950 -1119 -43
The result will be like:
Cash_Locale Cash_WTD Cash_PTD Cash_Daily Cash_WTD_Min Cash_WTD_Max Cash_PTD_Min Cash_PTD_Max
CA -207.95 -1263 -896 -950 22 -1585 -40
CN -853 -1585 -33 -950 22 -1585 -40
CS -32 -756 -15 -950 22 -1585 -40
CW -165 -586 -686 -950 22 -1585 -40
RA 22 -40 55 -950 22 -1585 -40
WI -950 -1119 -43 -950 22 -1585 -40
August 8, 2006 at 9:28 am
To add a column to your table:
ALTER TABLE MyTable ADD Cash_WTD_Min INT
To populate the column:
UPDATE MyTable SET Cash_WTD_Min = (SELECT MIN(Cash_WTD) FROM MyTable)
You would need a trigger to keep all of this current. However, I wouldn't recommend that you do this at all, since it introduces redundant data into your table, breaking one of the rules of normalisation. If you need to know the max value, you can obtain it when you query the table.
John
August 8, 2006 at 9:32 am
SELECT
YT.Cash_Locale ,
YT.Cash_WTD ,
YT.Cash_PTD ,
YT.Cash_Daily ,
DT.MaxCashWTD ,
DT.MinCashWTD ,
...etc
FROM dbo.YourTable AS YT, (
SELECT MAX(Cash_WTD) AS MaxCashWTD ,
MIN(Cash_WTD) AS MinCashWTD ,
...etc
FROM dbo.YourTable
WHERE your conditions
) AS DT
WHERE your conditions
August 8, 2006 at 9:33 am
Use a cross join and a derived table:
SELECT ct.*, ct2.*
FROMCash_Table ct
CROSSJOIN(SELECT MAX(Cash_WTD) AS Max_Cash_WTD, MIN(Cash_WTD) as Min_Cash_WTD, MAX(Cash_PTD) AS Max_Cash_PTD, MIN(Cash_PTD) AS Min_Cash_PTD, MAX(Cash_Daily) AS Max_Cash_Daily, MIN(Cash_Daily) AS Min_Cash_Daily FROM Cash_Table) ct2
August 8, 2006 at 9:34 am
Sorry, there should be a space in CROSS JOIN:
SELECT ct.*, ct2.*
FROM Cash_Table ct
CROSS JOIN (SELECT MAX(Cash_WTD) AS Max_Cash_WTD, MIN(Cash_WTD) as Min_Cash_WTD, MAX(Cash_PTD) AS Max_Cash_PTD, MIN(Cash_PTD) AS Min_Cash_PTD, MAX(Cash_Daily) AS Max_Cash_Daily, MIN(Cash_Daily) AS Min_Cash_Daily FROM Cash_Table) ct2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply