| Author |
 Topic  |
|
|
akpaga
Constraint Violating Yak Guru
USA
303 Posts |
 Posted - 12/06/2012 : 18:22:07
|
Hi friends I have a record in the following manner
objectid,Price,Customer_Date,TableDateStamp,State(GetDate() when record is inserted)
15,2.00,11/09/2012,11/09/2012 1:00:45,TX
15,4.00,11/10/2012,11/10/2012 9:00:45,TX
15,5.00,11/13/2012,11/13/2012 8:00:45,TX
16, 7.00, 11/13/2012,11/13/2012 8:00:45,CA
I want to write a query when the a user passes a date range say
11/12/2012-11/14/2012.
Then the objectid 15 has a customer date 11/13/2012
hence i want to subtract that price of 5.00 from the most recent price that falls out side the date range which is 11 /10/2012 price
of 4.00 .
hence my result should be like state wise
TX ,CA
1, 7(since CA customer has only one record)
Hope i am clear. Please excuse me if i am not
Thank you |
|
|
bandi
Flowing Fount of Yak Knowledge
India
2124 Posts |
Posted - 12/07/2012 : 01:32:22
|
DECLARE @tab TABLE(objectid int, Price dec(5,2),Customer_Date date, TableDateStamp datetime2, State varchar(4))--(GetDate() when record is inserted)
INSERT INTO @tab
SELECT 15,2.00,'11/09/2012','11/09/2012 1:00:45','TX' union all
SELECT 15,4.00,'11/10/2012','11/10/2012 9:00:45','TX' union all
SELECT 15,5.00,'11/13/2012','11/13/2012 8:00:45','TX' union all
SELECT 16,7.00, '11/13/2012','11/13/2012 8:00:45','CA'
--I want to write a query when the a user passes a date range say
DECLARE @FromDate date = '11/12/2012', @ToDate date ='11/14/2012'
SELECT c.objectid, c.State, c.Customer_Date, c.Price - COALESCE(t.Price,0) Price
FROM (SELECT objectid, [state], customer_date, Price
FROM @tab
WHERE Customer_Date >= @FromDate AND Customer_Date <= @ToDate
) c
LEFT JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY objectid, [state] ORDER BY Customer_Date DESC) rn
FROM @tab
WHERE Customer_Date NOT BETWEEN @FromDate AND @ToDate
) t
ON t.rn = 1 AND c.objectid = t.objectid
--
Chandu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
51185 Posts |
Posted - 12/07/2012 : 08:09:57
|
SELECT [TX],[CA],...
FROM
(
SELECT t1.State,t1.Price - COALESCE(t2.Price,0) AS Diff
FROM table t1
OUTER APPLY (SELECT TOP 1 Price
FROM Table
WHERE State = t1.State
AND Customer_Date < t1.Customer_Date
ORDER BY Customer_Date DESC
)t2
WHERE t1.Customer_Date > = @StartDate
AND t1.Customer_Date < @EndDate+1
)m
PIVOT(MAX(Diff) FOR State IN ([TX],[CA],..))n
for dynamic states see
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/07/2012 : 09:00:11
|
quote:
Originally posted by bandi
DECLARE @tab TABLE(objectid int, Price dec(5,2),Customer_Date date, TableDateStamp datetime2, State varchar(4))--(GetDate() when record is inserted)
INSERT INTO @tab
SELECT 15,2.00,'11/09/2012','11/09/2012 1:00:45','TX' union all
SELECT 15,4.00,'11/10/2012','11/10/2012 9:00:45','TX' union all
SELECT 15,5.00,'11/13/2012','11/13/2012 8:00:45','TX' union all
SELECT 16,7.00, '11/13/2012','11/13/2012 8:00:45','CA'
--I want to write a query when the a user passes a date range say
DECLARE @FromDate date = '11/12/2012', @ToDate date ='11/14/2012'
SELECT c.objectid, c.State, c.Customer_Date, c.Price - COALESCE(t.Price,0) Price
FROM (SELECT objectid, [state], customer_date, Price
FROM @tab
WHERE Customer_Date >= @FromDate AND Customer_Date <= @ToDate
) c
LEFT JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY objectid, [state] ORDER BY Customer_Date DESC) rn
FROM @tab
WHERE Customer_Date NOT BETWEEN @FromDate AND @ToDate
) t
ON t.rn = 1 AND c.objectid = t.objectid
--
Chandu
This will not work if
DECLARE @FromDate datetime, @ToDate datetime
Set @FromDate = '11/10/2012'
Set @ToDate ='11/12/2012'
|
|
|
| |
Topic |
|
|
|
help with query
