Date peak category
-
Hi all,
I got a table called 'peakprice'.
FromDate ToDate PriceUSD Peak
2010-09-01 2010-09-15 1,220.00 High
2010-09-16 2010-12-16 977.00 Low
i want to query from this 01-Sept-2010 to 21-Sept-2010, the amount must be "USD23,185" for both peaks if its falls on that date category....
Anyone can help? 😀
-
Is "USD23,185" just a given number or is it based on anything else?
I can't see any logical way to calculate the value as above based on the sample data you provided.
If you have a fixed value regardless of any real data, why run a query against a table in the first place?
SELECT CAST('20100901' AS datetime) as start ,CAST('20100921' AS datetime) as finish ,'USD23,185' amount
-
E.g:
For the rental period from 1st Sept – 21st Sept 2010
1st – 15th Sept 2010 - USD 1,220 (High) USD 1,220 x 15 Days = 18,300
16th – 21st Sept 2010– USD 977 (Low) USD 977 x 5 Days = 4,885
Total: USD 23,185
-
See if this helps
DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIME
SET @StartDate='20100901'
SET @EndDate='20100921'
SELECT CASE WHEN FromDate>=@StartDate THEN FromDate ELSE @StartDate END AS StartDate,
CASE WHEN ToDate<=@EndDate THEN ToDate ELSE @EndDate END AS EndDate,
PriceUSD,
1+DATEDIFF(day,CASE WHEN FromDate>=@StartDate THEN FromDate ELSE @StartDate END,
CASE WHEN ToDate<=@EndDate THEN ToDate ELSE @EndDate END) AS Days
FROM peakprice
WHERE FromDate<=@EndDate AND ToDate>=@StartDate
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
ok, thanks:-D
-
thanks for reply mark. your code appears to work well but i need 1 extra help. how can i SUM up the priceusd and gets the peak status? example your query output this results
Start Date End Date PriceUSD Days
2010-09-16 00:00:00.000 2010-09-21 00:00:00.000 977.00 6
2010-09-01 00:00:00.000 2010-09-15 00:00:00.000 1,220.00 15
but i want the output to be is
Total USD PeakSeason
24,162.00 Peak
For the peak season part, i have "Low", "High", and "Peak". Choose any season that has higher season from the query. Example if the select query returns "Low" and "High", then choose "High".
Pls help. Thx.
-
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate='20100901'
SET @EndDate='20100921';
WITH CTE1 AS (
SELECT CASE WHEN FromDate>=@StartDate THEN FromDate ELSE @StartDate END AS StartDate,
CASE WHEN ToDate<=@EndDate THEN ToDate ELSE @EndDate END AS EndDate,
PriceUSD,
Peak,
1+DATEDIFF(day,CASE WHEN FromDate>=@StartDate THEN FromDate ELSE @StartDate END,
CASE WHEN ToDate<=@EndDate THEN ToDate ELSE @EndDate END) AS Days
FROM peakprice
WHERE FromDate<=@EndDate AND ToDate>=@StartDate),
CTE2 AS (
SELECT StartDate,EndDate,PriceUSD,Peak,Days,
ROW_NUMBER() OVER(ORDER BY Days*PriceUSD DESC) AS rn
FROM CTE1
)
SELECT SUM(Days*PriceUSD) AS [Total USD],
MAX(CASE WHEN rn=1 THEN Peak END) AS PeakSeason
FROM CTE2
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply