help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with query

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-12-06 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-07 : 01:32:22

[code]
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
[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 08:09:57

[code]
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
[/code]

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-07 : 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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources