Current date and past 30 days total value

Question

The code I currently have shows me the current value for everyday and gives the running total.

select t.lgl_entity_nm, d.date_key, 
count(distinct s.site_key) AS Dis, 
SUM(Dis) over (partition by t.lgl_entity_nm order by d.date_key ASC rows unbounded preceding) RunningTotal
from site_v s
join touchpoint_v t
on s.site_key = t.site_key
join omni_promo_varnt_fact_v o
on o.touchpoint_key = t.touchpoint_key
join date_v d
on d.date_key = o.date_key
where d.date_key between 20190901 and 20190931
and t.lgl_entity_nbr = 1
and tot_selected_qty > 0
and event_typ_cd in ('IS-SPRINT-T', 'IS-PRINT-T')
group by 1,2

Giving me this output:

lgl_entity_nm   date_key    dis runningtotal
Ahold USA      20190901     729     729
Ahold USA      20190902     733     1462

If you look at the date its set between a certain time period. What I want achieve is that it shows me the current date or any set date value and past 30 days total in a single row. Suppose the date is 2019-09-30:

lgl_entity_nm   date_key(current date)  dis    total (past30 days)
Ahold USA       20190930                739     21953

Can this be achieved? If so how?

score 0 · Answer 1 · answered Dec 05 '19 at 21:45

0

Try this?

DECLARE @CurrentDate    DATE = GETDATE()
        ,@MonthBack     DATE = DATEADD(DAY,-30,GETDATE())

SELECT  t.lgl_entity_nm
        ,@CurrentDate AS CurrentDate
        ,COUNT(DISTINCT s.site_key) AS Dis
        ,SUM(Dis) AS RunningTotal
FROM    site_v AS s
        JOIN touchpoint_v AS t ON s.site_key = t.site_key
        JOIN omni_promo_varnt_fact_v AS o ON o.touchpoint_key = t.touchpoint_key
        JOIN date_v AS d ON d.date_key = o.date_key
WHERE   d.date_key BETWEEN @MonthBack AND @CurrentDate
        AND t.lgl_entity_nbr = 1
        AND tot_selected_qty > 0
        AND event_typ_cd IN ('IS-SPRINT-T', 'IS-PRINT-T')
GROUP BY
        t.lgl_entity_nm

answered Dec 05 '19 at 21:45

Mike Petri

570
3
10

I see the logic in yours which could do the job. But working on netezza I don't think I can do the @declare option. Might have to find a way around it. – darkpunk Dec 05 '19 at 21:54
@darkpunk His script initializes the variables. Maybe use that in the suery? Or is date_key actually a number? – LukStorms Dec 05 '19 at 22:14
Don't know Netezza. But according [here](https://stackoverflow.com/q/3149284/4003419) and [here](http://dwgeek.com/netezza-date-functions-examples.html/) it should be possible to use something like `to_char(current_date,'YYYYMMDD')`. Or maybe that should still be `CAST` to a number. – LukStorms Dec 06 '19 at 15:41
Hey @LukStorms I will see if I can make it work using the CAST or TO_CHAR. – darkpunk Dec 09 '19 at 14:10

score 0 · Answer 2 · answered Dec 06 '19 at 00:20

I think you can just remove the date key from the aggregation:

select t.lgl_entity_nm, max(d.date_key), 
       count(distinct s.site_key) as as RunningTotal
from site_v s join
     touchpoint_v t
     on s.site_key = t.site_key join
     omni_promo_varnt_fact_v o join
     on o.touchpoint_key = t.touchpoint_key join
     date_v d
     on d.date_key = o.date_key
where d.date_key between 20190901 and 20190931 and
      t.lgl_entity_nbr = 1 and
      tot_selected_qty > 0 and
      event_typ_cd in ('IS-SPRINT-T', 'IS-PRINT-T')
group by 1;

Actually, this subtly different from your query, because you are not counting distinct site_key over the entire period, but adding up the distinct counts per day. For that:

count(distinct d.date_key || ':' || s.site_key) as RunningTotal

This did give me a running total, i feel like finding a way to make the date work would do the job, like current and last 30. — darkpunk, Dec 06 '19 at 15:20

Current date and past 30 days total value

2 Answers2