Simple earliest date 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)

Simple earliest date query

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-02-15 : 16:50:14

This must be simple, but it somehow escapes me...

How to retrieve the earliest date in a set of rows when the sum of the set = amount?

For example, the earliest date where the total $ of orders placed by CustID 5427 = $1500. SELECT MIN(DateEntered) FROM Orders WHERE custID = 5427 HAVING SUM(Charge) = 1500 returns the earliest date of the complete set if there is a total charge of 1500, rather than returning the earliest date the cumulative orders of this customer reached 1500. So for example if the customer placed an order for $500 in October, November, December, and January I don't want to see October, I want to see December because that's when the cumulative order total reached 1500.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-16 : 00:38:36

The below example should give you some fair idea:

--Sample Data
Create Table SumTest
(
CustID Int,
OrdDate DateTime,
OrdVal int
)

Insert into SumTest
Select 1,'2010-10-05',500 union
Select 1,'2010-11-04',500 union
Select 1,'2010-12-05',500 union
Select 1,'2011-01-02',500 union
Select 2,'2010-10-05',1500 union
Select 2,'2010-11-04',500 union
Select 3,'2010-10-05',1000 union
Select 3,'2010-10-20',200 union
Select 3,'2010-11-20',500

--Sample Code

;with CTE
as
(
Select Row_number() over (Partition by CustID order by OrdDate) as Srno,
CustID, OrdDate,OrdVal
from SumTest
)

Select Custid, Min(OrdDate) from
(
Select a.CustID,a.OrdDate,Sum(b.OrdVal) as RunTot
from CTE a left join CTE b
on a.CustID = b.CustID and b.srno < a.srno + 1
group by a.CustID,a.OrdDate
) as subtab
where subtab.RunTot >=1500
group by CustId

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-02-16 : 12:21:32

Thank you! A running total was exactly what was needed.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2011-02-16 : 17:53:35

So it's not simple, and I stole it from someone on here and don't have a reference, but this is a FAST way to do running totals that could be adapted to suit your needs:



IF OBJECT_ID('tempdb..#testCTE') IS NOT NULL
    DROP TABLE #testCTE

SELECT TOP 1000000 Number, CAST(0 AS BIGINT) RT
INTO   #testCTE
FROM   dbo.udfNumberAdvanced(1000000,1,1)
ORDER BY Number


DECLARE @dtStart DATETIME
SELECT  @dtStart = GETDATE()

-- create our temp table to return data from
-- ORDER BY NEWID() -- also works just fine

-- declare helper variable
DECLARE @RT BIGINT;
SELECT @RT = 0;

WITH RunningTotals AS
(
    SELECT TOP (2147483647) -- put a int max value here to get all rows
           Number, RT
    FROM   #testCTE
    ORDER BY Number
    -- both of there work correctly
    -- ORDER BY Number DESC
    -- ORDER BY NEWID()
)
UPDATE RunningTotals 
SET       @RT = RT = @RT + Number
OUTPUT inserted.*

SELECT DATEDIFF(s, @dtStart, GETDATE()) AS DurationInSeconds

--IF OBJECT_ID('tempdb..#testCTE') IS NOT NULL
--    DROP TABLE #testCTE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources