SQL Server 2008 First Day Criteria

Question

I have a data table (there will be millions of records, but I will do it just here) that looks like this.

ID   APPROVAL_DT       DAY_DT        TRANS_COUNT     SALE_AMOUNT
1    2010-04-22        2010-04-27    2               260
1    2010-04-22        2010-04-28    1               40
2    2010-03-28        2010-04-02    1               5
2    2010-03-28        2010-04-03    5               10
2    2010-03-28        2010-04-04    1               20
3    2010-04-25        2010-05-01    6               10
3    2010-04-25        2010-05-02    4               10
4    2010-06-01        2010-06-07    1               5

I need to find out DAY_DT for each identifier, where either the sum of all previous and current DAY_DT TRANS_COUNTs> = 10 OR the sum of all previous and current DAY_DT SALE_AMOUNTs> = 25

Thus, the results of a query applied to the above table will be

ID   APPROVAL_DT  ACTIVATED_DT
1    2010-04-22   2010-04-27
2    2010-03-28   2010-04-04
3    2010-04-25   2010-05-02
4    2010-06-01   NULL

Any thoughts?

Answer 1

, , day_dt, _dt trans_count >= 10 sales_amount >= 25. "_dt". , , , , .

, , .

day_dt, ID:

with cte1 as (
select
  t.id,
  t.approval_dt,
  t.day_dt as activated_dt
from Table t
cross apply (
  select sum(trans_count) as sum_tc,
     sum(sale_amount) as sum_sa,
     max(day_dt) as max_day_dt
  from table c
  where c.id = t.id
  and c.day_dt <= t.day_dt) as p
where p.sum_tc >= 10
or p.sum_sa >=25)
, cte2 as (
  select id
   , approval_dt
   , activated_dt
   , row_number() over (partition by id order by activated_dt) as rn
  from cte1)
select *
from cte2
where rn = 1;

Answer 2

?

SQL Server 2008.

, , , 15 triangular Join. SQL CLR . TSQL , 500.

, , , , .

Answer 3

. . , UPDATE. , . , . , , APPROVAL_DAY, , . , .

CREATE TABLE #test
(
    ID   int,
    APPROVAL_DT   date,    
    DAY_DT        date,
    TRANS_COUNT     int,
    SALE_AMOUNT int,
    DailyTransCount int,
    DailySalesTotal int
)

INSERT INTO #test
SELECT 1,'2010-04-22','2010-04-27',2,260,0,0 UNION ALL
SELECT 1,'2010-04-22','2010-04-28', 1,40, 0,0 UNION ALL
SELECT 2,'2010-03-28','2010-04-02', 1,5, 0,0 UNION ALL
SELECT 2,'2010-03-28','2010-04-03', 5,10, 0,0 UNION ALL
SELECT 2,'2010-03-28','2010-04-04', 1,20, 0,0 UNION ALL
SELECT 3,'2010-04-25','2010-05-01', 6,10, 0,0 UNION ALL
SELECT 3,'2010-04-25','2010-05-02', 4,10, 0,0 UNION ALL
SELECT 4,'2010-06-01','2010-06-07', 1,5, 0,0

DECLARE @PreviousDay date; SET @PreviousDay = '29991231'
DECLARE @DailyTransCount int; SET @DailyTransCount = 0
DECLARE @DailySalesTotal int; SET @DailySalesTotal = 0
DECLARE @Group int; SET @Group = 0

UPDATE #test
    SET DailyTransCount = 0,
        DailySalesTotal = 0

UPDATE #test
    SET @DailyTransCount = DailyTransCount = CASE WHEN APPROVAL_DT = @PreviousDay THEN @DailyTransCount + Trans_Count ELSE Trans_Count END,
        @DailySalesTotal = DailySalesTotal = CASE WHEN APPROVAL_DT = @PreviousDay THEN @DailySalesTotal + SALE_AMOUNT ELSE SALE_AMOUNT END,
        @PreviousDay = APPROVAL_DT

SELECT Y.ID, X.APPROVAL_DT, X.DAY_DT FROM 
    (SELECT DISTINCT(ID) FROM #test T) Y
    LEFT JOIN ( SELECT ID, APPROVAL_DT, MIN(DAY_DT) AS DAY_DT FROM #test
                WHERE DailyTransCount >= 10 OR DailySalesTotal >= 25
                GROUP BY ID, APPROVAL_DT ) X ON X.ID = Y.ID

I have to explain a few things: I created two more columns at the end of my table. You will need to put this in the temp table (or the constant table) in order to push the totals. After I pulled all the totals into columns, it's just a choice to get the results. There is more detailed information about this method here . Please note that this is a FAST solution, but it is a little unsafe.