Trying to get latest topmost records

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 2005 Forums

Transact-SQL (2005)

Trying to get latest topmost records

Author

Topic

gena_mak
Starting Member

26 Posts

Posted - 2010-10-05 : 16:01:07

Edited: added client_id, this does not really change the question but, I should add that I need to put this query into a function and call it with client ID

Hi,
I am having little trouble in constracting a query. Database is SQL 2005.

Here is the table and data definition:

CREATE TABLE #tProductPurchases(prodpurch_id int identity, client_id int, product_id int, purchase_date datetime, moneyspent float)
SET NOCOUNT ON
INSERT INTO #tProductPurchases (client_id, product_id, purchase_date, moneyspent)
VALUES (2, 10, '2010-5-25', 50)
INSERT INTO #tProductPurchases (client_id, product_id, purchase_date, moneyspent)
VALUES (2, 11, '2010-5-27', 10)
INSERT INTO #tProductPurchases (client_id, product_id, purchase_date, moneyspent)
VALUES (2, 10, '2010-8-20', 20)
INSERT INTO #tProductPurchases (client_id, product_id, purchase_date, moneyspent)
VALUES (2, 11, '2010-9-11', 100)
INSERT INTO #tProductPurchases (client_id, product_id, purchase_date, moneyspent)
VALUES (2, 11, '2010-10-12', 120)
SET NOCOUNT OFF

SELECT * FROM #tProductPurchases
DROP TABLE #tProductPurchases

==============================================================

client_id prodpurch_id product_id purchase_date moneyspent
------------ ----------- ----------------------- ----------------------
2 1 10 2010-05-25 00:00:00.000 50
2 2 11 2010-05-27 00:00:00.000 10
2 3 10 2010-08-20 00:00:00.000 20
2 4 11 2010-09-11 00:00:00.000 100
2 5 11 2010-10-12 00:00:00.000 120

What I need is to pull the SUM() of 2 latest purchases of both product id's 10 and 11 before OR on the given date.

For example I need to pull total for purchases made before OR on June 10, 2010. In the result set above it will be sum of moneyspent for prodpurch_id = 1 and prodpurch_id = 2 which is $60.

Next, I need to pull total for purchases made before OR on September 15, 2010. The result set must be sum of prodpurch_id = 3 and prodpurch_id = 4 for the total of $120.

Next, I need to pull total for purchases made before OR on November 10, 2010. The result set must be sum of prodpurch_id = 3 and prodpurch_id = 5 for the total of $140.

There can be more product id's (9,15...), the key is I need to pull all DISTINCT topmost records for each product id

Help is very much appreciated.
Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-05 : 16:26:02

Here's one ugly way. The commented out "top 2" and "order by.." was from before your edits when I thought you just needed the first two.


declare @dt datetime
set @dt = '2010-06-10'
set @dt = '2010-11-10'
select sum(moneySpent)
from   (
       select --top 2 
              convert(money, 
              substring(
              max(
              convert(char(23), purchase_date, 121) + convert(varchar(30), moneyspent)
              )
              , 23+1, 30)
              ) as moneyspent
       from   #tProductPurchases
       where  purchase_date < dateadd(day, 1, @dt)
       group by product_id
       --order by max(purchase_date) desc
       ) d

Be One with the Optimizer
TG

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-05 : 16:42:44

Try this:


DECLARE @ProductIDs TABLE
(
   product_id INT NOT NULL PRIMARY KEY
);

INSERT INTO @ProductIDs(product_id)
   SELECT 10 UNION ALL
   SELECT 11;

SELECT SUM(MS.moneyspent) AS purchases_total
  FROM @ProductIDs AS PID
       OUTER APPLY
       (SELECT TOP(1) moneyspent
          FROM #tProductPurchases AS PP
         WHERE PP.product_id = PID.product_id
           AND PP.purchase_date <= '20100610'
         ORDER BY PP.purchase_date DESC) AS MS

gena_mak
Starting Member

26 Posts

Posted - 2010-10-06 : 08:50:14

Thanks a lot. :)
You saved my day. Will study both solutions and run against execution plan.

gena_mak
Starting Member

26 Posts

Posted - 2010-10-06 : 10:11:09

Thank you TG
Your solution works. Looks very interesting. I am somewhat worried about performance hit dealing with substrings and conversions.
I am going to put this query into a function and call it whenever I require to run a total.

What do you think?

quote:

Originally posted by TG

Here's one ugly way. The commented out "top 2" and "order by.." was from before your edits when I thought you just needed the first two.


declare @dt datetime
set @dt = '2010-06-10'
set @dt = '2010-11-10'
select sum(moneySpent)
from   (
       select --top 2 
              convert(money, 
              substring(
              max(
              convert(char(23), purchase_date, 121) + convert(varchar(30), moneyspent)
              )
              , 23+1, 30)
              ) as moneyspent
       from   #tProductPurchases
       where  purchase_date < dateadd(day, 1, @dt)
       group by product_id
       --order by max(purchase_date) desc
       ) d

Be One with the Optimizer
TG

gena_mak
Starting Member

26 Posts

Posted - 2010-10-06 : 10:16:31

Thank you malpashaa
Your solution is interesting. However, I can not pass product id's all the time I need to pull the total. I can only pass client ID which I see I failed to add to the original question. Sorry about confusion.

Basically, the question should be modified to add client_id to the #tProductPurchases

quote:

Originally posted by malpashaa

Try this:


DECLARE @ProductIDs TABLE
(
   product_id INT NOT NULL PRIMARY KEY
);

INSERT INTO @ProductIDs(product_id)
   SELECT 10 UNION ALL
   SELECT 11;

SELECT SUM(MS.moneyspent) AS purchases_total
  FROM @ProductIDs AS PID
       OUTER APPLY
       (SELECT TOP(1) moneyspent
          FROM #tProductPurchases AS PP
         WHERE PP.product_id = PID.product_id
           AND PP.purchase_date <= '20100610'
         ORDER BY PP.purchase_date DESC) AS MS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-06 : 10:22:12

>>what do you think?
I suggest you try it out, look at the execution plan as well as performance and judge for yourself.
Here is an alternative to compare with:


select sum(pp.moneyspent)
from   (
       select product_id
              ,max(purchase_date) dt
       from   #tProductPurchases
       where  purchase_date < dateadd(day, 1, @dt)
       group by product_id
       ) d
join   #tProductPurchases pp
       on pp.product_id = d.product_id
       and pp.purchase_date = d.dt

Be One with the Optimizer
TG

gena_mak
Starting Member

26 Posts

Posted - 2010-10-06 : 11:23:37

Thank you, this one little better in terms of performace on real table

quote:
Originally posted by TG

>>what do you think?
I suggest you try it out, look at the execution plan as well as performance and judge for yourself.
Here is an alternative to compare with:

select sum(pp.moneyspent)
from (
select product_id
,max(purchase_date) dt
from #tProductPurchases
where purchase_date < dateadd(day, 1, @dt)
group by product_id
) d
join #tProductPurchases pp
on pp.product_id = d.product_id
and pp.purchase_date = d.dt

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources