Aggregating a count of previous 365 days, each day

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

Aggregating a count of previous 365 days, each day

Author

Topic

tyler.smelley
Starting Member

2 Posts

Posted - 2015-03-20 : 01:25:56

Hi SQL Team,

I'm a marketing creative director/director of technology (weird mix of hats, right?) trying to solve a reporting problem for my company. I have extremely basic working knowledge of SQL queries and relational DB, and am mostly self-taught. I've been trying to figure this one out for a year.

We would like a report showing the change in customer frequency for each of our locations over time. Frequency is being defined as the count of tickets divided by the distinct count of customer numbers. I can easily create a crosstab in Crystal Reports that will group this data by year, and give me a frequency ratio for each year.

We are a seasonal business, so any calculation like this must include 365 days worth of data (otherwise, our frequency is very high in Q4 and very low in Q2). So, while the report grouped by year is correct for prior years, if I were to run it now, the value displayed for 2015 would be horribly wrong, since it's only taking three months into account.

I would also like to see the daily change in the metric, with each day's total reflective of the past 365 days.

I believe that I can do this with a stored procedure or a view, but I am unsure how to create it. I basically need a value for each day, displaying a count of tickets and distinct count of customers (or rather a count of tickets for each customer) that includes the previous 365 days. The daily values go back, each one reflective of the trailing 365 days, until the first record is reached.

Is this even possible? Is my logic here sound?

Thank you very much for any assistance you may be able to lend. I will gladly search out any related posts if someone can point me towards a function or search term that will guide me.

Best
Tyler

--
Thanks!
Tyler Smelley

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-20 : 10:41:12

This seems more than doable. Having said that, your table definition would be extremely helpful. Some sample data wouldn't hurt.

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

tyler.smelley
Starting Member

2 Posts

Posted - 2015-03-20 : 17:52:17

I can definitely do that:

CREATE TABLE [dbo].[PS_TKT_HIST](
[BUS_DAT] [dbo].[T_DAT_SMALL] NOT NULL,
[DOC_ID] [dbo].[T_DOC_ID] NOT NULL,
[STR_ID] [dbo].[T_COD] NOT NULL,
[EVENT_NO] [dbo].[T_DOC_NO] NOT NULL,
[STA_ID] [dbo].[T_COD] NOT NULL,
[TKT_NO] [dbo].[T_DOC_NO] NOT NULL,
[DOC_GUID] [dbo].[T_GUID] NOT NULL,
[TKT_DT] [dbo].[T_DT] NULL,
[TKT_TYP] [dbo].[T_FLG] NOT NULL,
[DRW_ID] [dbo].[T_COD] NULL,
[DRW_SESSION_ID] [dbo].[T_DOC_ID] NULL,
[USR_ID] [dbo].[T_USR_ID] NULL,
[SLS_REP] [dbo].[T_USR_ID] NULL,
[STK_LOC_ID] [dbo].[T_LOC_ID] NULL,
[PRC_LOC_ID] [dbo].[T_LOC_ID] NULL,
[PFT_CTR] [dbo].[T_ACCT_NO] NULL,
[CUST_NO] [dbo].[T_CUST_NO] NULL,
[BILL_TO_CONTACT_ID] [dbo].[T_INT_TINY] NULL,
[SHIP_TO_CONTACT_ID] [dbo].[T_INT_TINY] NULL,
[LOY_PGM_COD] [dbo].[T_COD] NULL,
[TERMS_COD] [dbo].[T_COD] NULL,
[SHIP_VIA_COD] [dbo].[T_COD] NULL,
[SHIP_ZONE_COD] [dbo].[T_COD] NULL,
[SHIP_DAT] [dbo].[T_DAT] NULL,
[NORM_TAX_COD] [dbo].[T_COD] NULL,
[TAX_COD] [dbo].[T_COD] NULL,
[TAX_EXEMPT_NO] [dbo].[T_DESCR] NULL,
[TAX_OVRD_REAS] [dbo].[T_COD] NULL,
[CUST_PO_NO] [dbo].[T_CUST_PO_NO] NULL,
[LST_FRM_GRP_PRTD] [dbo].[T_COD] NULL,
[LST_FRM_PRTD] [dbo].[T_FILENAME] NULL,
[TIMES_PRTD] [dbo].[T_INT] NULL,
[FOOD_STMP_AMT] [dbo].[T_MONEY] NULL,
[FOOD_STMP_LINS] [dbo].[T_INT] NOT NULL,
[FOOD_STMP_TAX_AMT] [dbo].[T_MONEY] NULL,
[FOOD_STMP_NORM_TAX_AMT] [dbo].[T_MONEY] NULL,
[SAL_LINS] [dbo].[T_INT] NULL,
[SAL_LIN_TOT] [dbo].[T_MONEY] NOT NULL,
[GFC_LINS] [dbo].[T_INT] NOT NULL,
[SVC_LINS] [dbo].[T_INT] NOT NULL,
[RET_LINS] [dbo].[T_INT] NULL,
[RET_LIN_TOT] [dbo].[T_MONEY] NOT NULL,
[HAS_TAX_OVRD] [dbo].[T_FLG] NOT NULL,
[TAX_OVRD_LINS] [dbo].[T_INT] NOT NULL,
[LINS] [dbo].[T_INT] NULL,
[SUB_TOT] [dbo].[T_MONEY] NOT NULL,
[TOT_EXT_COST] [dbo].[T_MONEY] NULL,
[TOT_WEIGHT] [dbo].[T_DEC4] NOT NULL,
[TOT_CUBE] [dbo].[T_DEC4] NOT NULL,
[TOT_GFC_AMT] [dbo].[T_MONEY] NULL,
[TOT_SVC_AMT] [dbo].[T_MONEY] NULL,
[TOT_MISC] [dbo].[T_MONEY] NOT NULL,
[NORM_TAX_AMT] [dbo].[T_MONEY] NOT NULL,
[TAX_AMT] [dbo].[T_MONEY] NOT NULL,
[TOT_TND] [dbo].[T_MONEY] NOT NULL,
[TOT_CHNG] [dbo].[T_MONEY] NOT NULL,
[TOT] [dbo].[T_MONEY] NOT NULL,
[DATA_UPGRADE_STAT] [dbo].[T_FLG] NULL,
[REF] [dbo].[T_REF] NULL,
[TOT_HDR_DISC] [dbo].[T_MONEY] NOT NULL,
[TOT_LIN_DISC] [dbo].[T_MONEY] NOT NULL,
[IS_OFFLINE] [dbo].[T_BIT] NOT NULL,
[AGG_STAT] [dbo].[T_FLG] NULL,
CONSTRAINT [PK_PS_TKT_HIST] PRIMARY KEY CLUSTERED
(
[BUS_DAT] ASC,
[DOC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PS_TKT_HIST] WITH CHECK ADD CONSTRAINT [CK_PS_TKT_HIST_BUS_DAT] CHECK (([BUS_DAT]=[dbo].[fnDateOnly]([BUS_DAT])))
GO

ALTER TABLE [dbo].[PS_TKT_HIST] CHECK CONSTRAINT [CK_PS_TKT_HIST_BUS_DAT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] WITH CHECK ADD CONSTRAINT [CK_PS_TKT_HIST_DOC_ID] CHECK (([DOC_ID]>=(1) AND [DOC_ID]<=(999999999999999.)))
GO

ALTER TABLE [dbo].[PS_TKT_HIST] CHECK CONSTRAINT [CK_PS_TKT_HIST_DOC_ID]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] WITH CHECK ADD CONSTRAINT [CK_PS_TKT_HIST_HAS_TAX_OVRD] CHECK (([HAS_TAX_OVRD]='A' OR [HAS_TAX_OVRD]='E' OR [HAS_TAX_OVRD]='!'))
GO

ALTER TABLE [dbo].[PS_TKT_HIST] CHECK CONSTRAINT [CK_PS_TKT_HIST_HAS_TAX_OVRD]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] WITH CHECK ADD CONSTRAINT [CK_PS_TKT_HIST_SHIP_DAT] CHECK (([SHIP_DAT] IS NULL OR [SHIP_DAT]=[dbo].[fnDateOnly]([SHIP_DAT])))
GO

ALTER TABLE [dbo].[PS_TKT_HIST] CHECK CONSTRAINT [CK_PS_TKT_HIST_SHIP_DAT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] WITH CHECK ADD CONSTRAINT [CK_PS_TKT_HIST_TKT_TYP] CHECK (([TKT_TYP]='R' OR [TKT_TYP]='A' OR [TKT_TYP]='O' OR [TKT_TYP]='I' OR [TKT_TYP]='T'))
GO

ALTER TABLE [dbo].[PS_TKT_HIST] CHECK CONSTRAINT [CK_PS_TKT_HIST_TKT_TYP]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] WITH CHECK ADD CONSTRAINT [CK_PS_TKT_HIST_TOT_CHNG] CHECK (([TOT_CHNG]>=(0)))
GO

ALTER TABLE [dbo].[PS_TKT_HIST] CHECK CONSTRAINT [CK_PS_TKT_HIST_TOT_CHNG]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] WITH CHECK ADD CONSTRAINT [CK_PS_TKT_HIST_TOT_TND] CHECK (([TOT_TND]>=(0)))
GO

ALTER TABLE [dbo].[PS_TKT_HIST] CHECK CONSTRAINT [CK_PS_TKT_HIST_TOT_TND]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_TKT_TYP] DEFAULT ('T') FOR [TKT_TYP]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_TIMES_PRTD] DEFAULT ((0)) FOR [TIMES_PRTD]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_FOOD_STMP_AMT] DEFAULT ((0)) FOR [FOOD_STMP_AMT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_FOOD_STMP_LINS] DEFAULT ((0)) FOR [FOOD_STMP_LINS]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_FOOD_STMP_TAX_AMT] DEFAULT ((0)) FOR [FOOD_STMP_TAX_AMT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_FOOD_STMP_NORM_TAX_AMT] DEFAULT ((0)) FOR [FOOD_STMP_NORM_TAX_AMT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_SAL_LINS] DEFAULT ((0)) FOR [SAL_LINS]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_SAL_LIN_TOT] DEFAULT ((0)) FOR [SAL_LIN_TOT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_GFC_LINS] DEFAULT ((0)) FOR [GFC_LINS]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_SVC_LINS] DEFAULT ((0)) FOR [SVC_LINS]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_RET_LINS] DEFAULT ((0)) FOR [RET_LINS]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_RET_LIN_TOT] DEFAULT ((0)) FOR [RET_LIN_TOT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_HAS_TAX_OVRD] DEFAULT ('!') FOR [HAS_TAX_OVRD]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_TAX_OVRD_LINS] DEFAULT ((0)) FOR [TAX_OVRD_LINS]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_LINS] DEFAULT ((0)) FOR [LINS]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_SUB_TOT] DEFAULT ((0)) FOR [SUB_TOT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_TOT_MISC] DEFAULT ((0)) FOR [TOT_MISC]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_NORM_TAX_AMT] DEFAULT ((0)) FOR [NORM_TAX_AMT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_TOT_TND] DEFAULT ((0)) FOR [TOT_TND]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_TOT_CHNG] DEFAULT ((0)) FOR [TOT_CHNG]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_TOT] DEFAULT ((0)) FOR [TOT]
GO

ALTER TABLE [dbo].[PS_TKT_HIST] ADD CONSTRAINT [DF_PS_TKT_HIST_IS_OFFLINE] DEFAULT ((0)) FOR [IS_OFFLINE]
GO

What is the best method to post sample data?

Thanks!

--
Thanks!
Tyler Smelley

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources