SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help with query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
310 Posts

Posted - 12/06/2012 :  18:22:07  Show Profile  Reply with Quote
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
2168 Posts

Posted - 12/07/2012 :  01:32:22  Show Profile  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/07/2012 :  08:09:57  Show Profile  Reply with Quote

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/

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/07/2012 :  09:00:11  Show Profile  Reply with Quote
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'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000