running total - 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

General SQL Server Forums

New to SQL Server Programming

running total

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2010-08-17 : 16:58:17

I need a running total

Declare @end_comm_dte varchar(10)
Set @end_comm_dte = '08/14/2010'

SELECT T.MARKET,
SCX.SALES_GRP,
E.SUP_NAME,
E.EMP_NAME,
T.[BEG COMM DATE],
T.[END COMM DATE],
T.[PAY DATE],
T.SALESREP_oor,
SUM(T.Video) VIDEO,
SUM(T.Digital) DIGITAL,
SUM(T.CHSI) CHSI,
SUM(T.CDV) CDV,
Create_Dte,
DENSE_RANK ()OVER(Partition by T.Market Order By Create_Dte asc)
--INTO #CREA
FROM
(
select CASE WHEN sa.PRIN_oor = '4400' THEN 'SLC' ELSE 'TUC' END MARKET,
p.[BEG COMM DATE],
p.[END COMM DATE],
p.[PAY DATE],
sa.ORDER_NO_oor,
sa.JOB_SEQ_NO_OPI,
sa.SUB_ACCT_NO_OPI,
sa.HSE_KEY_oor,
sa.CUST_ACCT_NO_oor,
sa.SALESREP_oor,
sa.JOB_CLASS,
case when SUM(case when sa.B1 > 0 then 1 else 0 end) > 0 then 1 else 0 end Video,
case when SUM(case when sa.DIGI_ADD_SALES > 0 then 1 else 0 end) > 0 then 1 else 0 end Digital,
case when SUM(case when sa.CHSI > 0 then 1 else 0 end) > 0 then 1 else 0 end CHSI,
case when SUM(case when sa.CDV > 0 then 1 else 0 end) > 0 then 1 else 0 end CDV,
min(sa.CREATE_DTE_oor) Create_Dte
From ReportDB.dbo.vw_sales_serv_lvl_sum_add as sa
INNER JOIN AnalystSpace.dbo.DIRECT_SALES_PAYPERIOD as p
on sa.CREATE_DTE_oor between p.[BEG COMM DATE]
and p.[END COMM DATE]
Where sa.JOB_CLASS in ('C','R','S')
AND p.[END COMM DATE] in (@end_comm_dte)
GROUP BY
CASE WHEN sa.PRIN_oor = '4400' THEN 'SLC' ELSE 'TUC' END,
p.[BEG COMM DATE],
p.[END COMM DATE],
p.[PAY DATE],
sa.ORDER_NO_oor,
sa.JOB_SEQ_NO_OPI,
sa.SUB_ACCT_NO_OPI,
sa.HSE_KEY_oor,
sa.CUST_ACCT_NO_oor,
sa.SALESREP_oor,
sa.JOB_CLASS

HAVING (case when SUM(case when sa.B1 > 0 then 1 else 0 end) > 0 then 1 else 0 end > 0
OR case when SUM(case when sa.CHSI > 0 then 1 else 0 end) > 0 then 1 else 0 end > 0
OR case when SUM(case when sa.CDV > 0 then 1 else 0 end) > 0 then 1 else 0 end > 0)
)T

INNER JOIN SMB.dbo.vw_p_sales_ch_xref SCX
ON SCX.SALES_CH_ID = [SMB].[dbo].[fnc_sales_ch_grp](T.SALESREP_oor,T.Create_Dte)
LEFT JOIN SMB.dbo.vw_emp_profile AS E ON T.SALESREP_oor = E.CSG_SALESID

WHERE SCX.SALES_CH = 'DIRECT SALES'

GROUP BY
T.MARKET,
SCX.SALES_GRP,
E.SUP_NAME,
E.EMP_NAME,
T.[BEG COMM DATE],
T.[END COMM DATE],
T.[PAY DATE],
T.SALESREP_oor,
Create_Dte

Each Day I need to add the previous days? Any suggestions?

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 10:39:20

Do you have any table scripts and sample data?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-18 : 10:47:00

Solving the "Running Total" & "Ordinal Rank" Problems
http://www.sqlservercentral.com/articles/T-SQL/68467/

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources