Daily ,Weekly, Monthly ,Quartely, Yearly, Accumulative Query

Sameer · October 6, 2015, 4:09pm

Hi Experts !
Help to get the query which show the data Daily ,Weekly, Monthly ,Quartely, Yearly, Accumulative on range basis?

djj55 · October 6, 2015, 4:16pm

We need a lot more information.

table definition
example table data
expected output
what you have tried

Sameer · October 9, 2015, 8:42am

USE [TEST1]
GO
--DROP TABLE ITEM_VOL
CREATE TABLE [dbo].[ITEM_VOL](
	[EVENT_GROUP_ID] [char](32) NULL,
	[ITEM_ID] [char](32) NULL,
	[EVENT_TYPE] [varchar](14) NULL,
	[PERIOD] [varchar](20) NULL,
	[ITEM_NAME] [varchar](20) NULL,
	[START_DATETIME] [datetime] NULL,
	[END_DATETIME] [datetime] NULL,
	[VAL1] [numeric](28, 12) NULL,
	[VAL2] [numeric](28, 12) NULL,
	[VAL3] [numeric](28, 12) NULL
)

GO

I have results and out put .xls files how to upload there is no option?

khtan · October 9, 2015, 8:47am

take a screen shot of the required output and paste here

for this, please provide in consumable format. Example

INSERT INTO table1 VALUES  .....

Sameer · October 9, 2015, 9:16am

The out put in week to date, month to date, quarter to date, year to date, accumulatives on daily date basis

INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'12/30/2013',	'12/30/2013',1	)	
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'12/31/2013',	'12/31/2013',2	)	
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/1/2014',	'1/1/2014',	3	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/2/2014',	'1/2/2014',	4	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/3/2014',	'1/3/2014',	5	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/4/2014',	'1/4/2014',	6	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/5/2014',	'1/5/2014',	7	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/6/2014',	'1/6/2014',	8	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/7/2014',	'1/7/2014',	9	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/8/2014',	'1/8/2014',	10	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/9/2014',	'1/9/2014',	11	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/10/2014',	'1/10/2014',	12	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/11/2014',	'1/11/2014',	13	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/12/2014',	'1/12/2014',	14	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/13/2014',	'1/13/2014',	15	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/14/2014',	'1/14/2014',	16	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/15/2014',	'1/15/2014',	17	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/16/2014',	'1/16/2014',	18	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/17/2014',	'1/17/2014',	19	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/18/2014',	'1/18/2014',	20	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/19/2014',	'1/19/2014',	21	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/20/2014',	'1/20/2014',	22	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/21/2014',	'1/21/2014',	23	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/22/2014',	'1/22/2014',	24	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/23/2014',	'1/23/2014',	25	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/24/2014',	'1/24/2014',	26	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/25/2014',	'1/25/2014',	27	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/26/2014',	'1/26/2014',	28	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/27/2014',	'1/27/2014',	29	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/28/2014',	'1/28/2014',	30	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/29/2014',	'1/29/2014',	31	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/30/2014',	'1/30/2014',	32	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/31/2014',	'1/31/2014',	33	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/1/2014',	'2/1/2014',	34	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/2/2014',	'2/2/2014',	35	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/3/2014',	'2/3/2014',	36	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/4/2014',	'2/4/2014',	37	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/5/2014',	'2/5/2014',	38	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/6/2014',	'2/6/2014',	39	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/7/2014',	'2/7/2014',	40	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/8/2014',	'2/8/2014',	41	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/9/2014',	'2/9/2014',	42	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/10/2014',	'2/10/2014',	43	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/11/2014',	'2/11/2014',	44	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/12/2014',	'2/12/2014',	45	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/13/2014',	'2/13/2014',	46	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/14/2014',	'2/14/2014',	47	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/15/2014',	'2/15/2014',	48	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/16/2014',	'2/16/2014',	49	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/17/2014',	'2/17/2014',	50	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/18/2014',	'2/18/2014',	51	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/19/2014',	'2/19/2014',	52	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/20/2014',	'2/20/2014',	53	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/21/2014',	'2/21/2014',	54	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/22/2014',	'2/22/2014',	55	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/23/2014',	'2/23/2014',	56	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/24/2014',	'2/24/2014',	57	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/25/2014',	'2/25/2014',	58	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/26/2014',	'2/26/2014',	59	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/27/2014',	'2/27/2014',	60	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'2/28/2014',	'2/28/2014',	61	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/1/2014',	'3/1/2014',	62	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/2/2014',	'3/2/2014',	63	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/3/2014',	'3/3/2014',	64	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/4/2014',	'3/4/2014',	65	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/5/2014',	'3/5/2014',	66	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/6/2014',	'3/6/2014',	67	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/7/2014',	'3/7/2014',	68	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/8/2014',	'3/8/2014',	69	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/9/2014',	'3/9/2014',	70	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/10/2014',	'3/10/2014',	71	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/11/2014',	'3/11/2014',	72	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/12/2014',	'3/12/2014',	73	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/13/2014',	'3/13/2014',	74	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/14/2014',	'3/14/2014',	75	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/15/2014',	'3/15/2014',	76	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/16/2014',	'3/16/2014',	77	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/17/2014',	'3/17/2014',	78	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/18/2014',	'3/18/2014',	79	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/19/2014',	'3/19/2014',	80	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/20/2014',	'3/20/2014',	81	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/21/2014',	'3/21/2014',	82	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/22/2014',	'3/22/2014',	83	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/23/2014',	'3/23/2014',	84	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/24/2014',	'3/24/2014',	85	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/25/2014',	'3/25/2014',	86	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/26/2014',	'3/26/2014',	87	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/27/2014',	'3/27/2014',	88	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/28/2014',	'3/28/2014',	89	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/29/2014',	'3/29/2014',	90	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/30/2014',	'3/30/2014',	91	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'3/31/2014',	'3/31/2014',	92	)

khtan · October 9, 2015, 9:36am

SELECT    
         [Date]                 = START_DATETIME,
         [Daily data]           = ROW_NUMBER() OVER (ORDER BY START_DATETIME),
         [Week to date]         = SUM(VAL1) OVER (PARTITION BY DATEPART(ISO_WEEK, START_DATETIME) ORDER BY START_DATETIME),
         [Month to date]        = SUM(VAL1) OVER (PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, START_DATETIME), 0) ORDER BY START_DATETIME),
         [Year to date]         = SUM(VAL1) OVER (PARTITION BY DATEADD(YEAR, DATEDIFF(YEAR, 0, START_DATETIME), 0) ORDER BY START_DATETIME),
         [Accumulative to date] = SUM(VAL1) OVER (ORDER BY START_DATETIME)
FROM     [ITEM_VOL] v
ORDER BY [Date]

khtan · October 9, 2015, 9:39am

Now i notice the sql2008r2 tag, the sum() over (order by . ..) is not supported in this version

khtan · October 9, 2015, 10:34am

here is the sql2008r2 version

SELECT    [Date]            = START_DATETIME,
    [Daily data]        = ROW_NUMBER() OVER (ORDER BY START_DATETIME),
    [Week to date]        = w.val,
    [Month to date]     = m.val,
    [Year to date]        = y.val,
    [Accumulative to date]    = a.val
FROM    [ITEM_VOL] v
    CROSS APPLY
    (
        SELECT    val    = SUM(x.VAL1)
        FROM    [ITEM_VOL] x
        WHERE    x.START_DATETIME            <= v.START_DATETIME
         AND    DATEPART(ISO_WEEK, x.START_DATETIME)    
            = DATEPART(ISO_WEEK, v.START_DATETIME)
    ) w
    CROSS APPLY
    (
        SELECT    val    = SUM(x.VAL1)
        FROM    [ITEM_VOL] x
        WHERE    x.START_DATETIME        <= v.START_DATETIME
         AND    DATEADD(MONTH, DATEDIFF(MONTH, 0, x.START_DATETIME), 0)    
            = DATEADD(MONTH, DATEDIFF(MONTH, 0, v.START_DATETIME), 0)
    ) m
    CROSS APPLY
    (
        SELECT    val    = SUM(x.VAL1)
        FROM    [ITEM_VOL] x
        WHERE    x.START_DATETIME    <= v.START_DATETIME
         AND    DATEADD(YEAR, DATEDIFF(YEAR, 0, x.START_DATETIME), 0)    
            = DATEADD(YEAR, DATEDIFF(YEAR, 0, v.START_DATETIME), 0)
    ) y
    CROSS APPLY
    (
        SELECT    val    = SUM(x.VAL1)
        FROM    [ITEM_VOL] x
        WHERE    x.START_DATETIME    <= v.START_DATETIME
    ) a
ORDER BY [Date]

Sameer · October 9, 2015, 12:36pm

Awesome thank you Khtan work great ...
For adding the quarter to date may be used like below.
One question if the week starts from Thrusday to Wednesday then how to deal ISO_WEEK?
Did the query will work on leap days?

(
    SELECT    val    = SUM(x.VAL1)
    FROM    [ITEM_VOL] x
    WHERE    x.START_DATETIME        <= v.START_DATETIME
     AND    DATEPART(QUARTER, x.START_DATETIME)    
        = DATEPART(QUARTER, v.START_DATETIME)
) Q

khtan · October 9, 2015, 2:20pm

Yes. I missed that.

You mean you week is not ISO_WEEK ?

Sameer · October 9, 2015, 4:37pm

Yes my week not ISO_WEEK . My week starts from thrusday to Wednesday.
Will this query works good when there is leap day in a year?

khtan · October 10, 2015, 3:33am

use this to determine the week no

select [week no] = dateadd ( day , (datediff ( day , '17530104', [START_DATETIME]) / 7) * 7 , '17530104' )

JeffModen · October 18, 2015, 5:59am

Yowch. All of those calculations are based on Triangular Joins. That'll get you out of the woods but there could be some major performance problems there depending on the actual number of rows. You can get some ok performance with the right kind of indexing but logical reads (even for a small number of rows) are going to be very high.

If you run into such performance problems (several years of this kind of data should be subsecond), post back and I'll demonstrate a very high performance, low resource usage, alternative.

Sameer · November 11, 2015, 6:07am

Hi JeffModen,
Thank you !
Please demonstrate really appreciated.

JeffModen · November 11, 2015, 2:48pm

Before I spend the time demonstrating, let me ask... are you allowed to use a stored procedure and are you allowed to use Temp Tables in your solution?

Sameer · November 12, 2015, 12:18pm

Thank you jeffModen,
Ya no issue to use stored procedure and temp tables

JeffModen · November 22, 2015, 10:30pm

Ok. Here's some code to create the table and populate it on-the-fly. It replaces the CREATE TABLE and all of the INSERTs you were good enough to provide plus it creates an additional 600 rows.

--=================================================================================================
--      Create the test data. This is NOT a part of the solution!
--      We're just building a test table here.
--=================================================================================================
--===== If it exists, drop the test table to make reruns easier in SSMS.
     IF OBJECT_ID('tempdb..#Item_Vol','U') IS NOT NULL
        DROP TABLE #Item_Vol
;
--===== Local variable for starting date of data
DECLARE @StartDate DATETIME --Change this to DATETIME if using 2005 and it'll all still work.
;
 SELECT @StartDate = '2013-12-30'
;
--===== Create the right amount of numbered rows as a row source using
     -- a "pseudo-cursor" in the form of a constrained CROSS JOIN.
   WITH cteTally AS
        (
         SELECT TOP (DATEDIFF(dd,@StartDate,GETDATE())) --"Today" isn't done, yet (not included).
                N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
           FROM sys.all_columns ac1
          CROSS JOIN sys.all_columns ac2
        )
 SELECT  EVENT_GROUP_ID = CAST('1' AS CHAR(32))
        ,ITEM_ID        = CAST('20607ddfa3a445ceb1fb03e76c8620e6' AS CHAR(32))
        ,EVENT_TYPE     = CAST('PROD_TEST' AS VARCHAR(14))
        ,PERIOD         = CAST('EVENT' AS VARCHAR(20))
        ,ITEM_NAME      = CAST('FACILITY' AS VARCHAR(20))
        ,START_DATETIME = DATEADD(dd,t.N-1,@StartDate)
        ,END_DATETIME   = DATEADD(dd,t.N,@StartDate)
        ,VAL1           = CAST(t.N AS NUMERIC(28,12)) --Incremental numbers 1 <= X
        ,VAL2           = CAST( ABS(CHECKSUM(NEWID()))%1001 AS NUMERIC(28,12)) --Random Numbers 0 <= X <= 1000
        ,VAL3           = CAST(RAND(CHECKSUM(NEWID()))*1000 AS NUMERIC(28,12)) --Random Numbers 0 <= X < 1000
   INTO #Item_Vol
   FROM cteTally t
;

Then, the following does the desired calculations in a Temp Table (along with some other goodies) at a very high rate of speed will low resource usage. Note that this next section of code is what I refer to as the "Second Section" of code at the end of this post.

--=================================================================================================
--      High performance solution
--=================================================================================================
--===== If it exists, "Running Total" table to make reruns easier in SSMS.
     -- We do the runnning totals in a temp table to avoid any problems with the original table.
     IF OBJECT_ID('tempdb..#RunningTotal','U') IS NOT NULL
        DROP TABLE #RunningTotal
;
--===== Copy the data we need from the original table and build a couple of columns that we'll need.
 SELECT  RowNum         = ROW_NUMBER() OVER (ORDER BY START_DATETIME)
        ,[Date]         = ISNULL(START_DATETIME,0) --ISNULL make this column NOT NULL
        ,DailyData      = ISNULL(VAL1,0)           -- Keeps nulls from messing things up.
        ,WeekToDate     = CAST(NULL AS NUMERIC(28,12))
        ,MonthToDate    = CAST(NULL AS NUMERIC(28,12))
        ,QuarterToDate  = CAST(NULL AS NUMERIC(28,12))
        ,YearToDate     = CAST(NULL AS NUMERIC(28,12))
        ,CumeToDate     = CAST(NULL AS NUMERIC(28,12))
        ,WKDate         = DATEADD(dd,DATEDIFF(dd,3,START_DATETIME)/7*7,3)
        ,MMDate         = DATEADD(mm,DATEDIFF(mm,0,START_DATETIME),0)
        ,QQDate         = DATEADD(qq,DATEDIFF(qq,0,START_DATETIME),0)
        ,YYDate         = DATEADD(yy,DATEDIFF(yy,0,START_DATETIME),0)
   INTO #RunningTotal
   FROM #Item_Vol
  WHERE START_DATETIME IS NOT NULL
;
--===== Add the quintessential Unique Clustered Index as a PK
  ALTER TABLE #RunningTotal
    ADD PRIMARY KEY CLUSTERED ([Date])
;
--===== Create some obviously named variables
DECLARE  @PrevDate   DATETIME
        ,@PrevWKCume NUMERIC(28,12) ,@PrevWKDate DATETIME
        ,@PrevMMCume NUMERIC(28,12) ,@PrevMMDate DATETIME
        ,@PrevQQCume NUMERIC(28,12) ,@PrevQQDate DATETIME
        ,@PrevYYCume NUMERIC(28,12) ,@PrevYYDate DATETIME
        ,@PrevCume   NUMERIC(28,12)
        ,@Safety     BIGINT --Checks for correct sequence of rows during update
;
 SELECT @Safety = 1
;
--===== Do the running totals.
 UPDATE rt
   SET   @Safety     = CASE WHEN @Safety = RowNum THEN @Safety+1 ELSE 1/0 END --Force an error if out of sync.
        ,@PrevWKCume = WeekToDate    = DailyData + CASE WHEN WKDate = @PrevWKDate THEN @PrevWKCume ELSE 0 END
        ,@PrevMMCume = MonthToDate   = DailyData + CASE WHEN MMDate = @PrevMMDate THEN @PrevMMCume ELSE 0 END
        ,@PrevQQCume = QuarterToDate = DailyData + CASE WHEN QQDate = @PrevQQDate THEN @PrevQQCume ELSE 0 END
        ,@PrevYYCume = YearToDate    = DailyData + CASE WHEN YYDate = @PrevYYDate THEN @PrevYYCume ELSE 0 END
        ,@PrevCume   = CumeToDate    = DailyData + ISNULL(@PrevCume,0)
        ,@PrevWKDate = WKDate
        ,@PrevMMDate = MMDate
        ,@PrevQQDate = QQDate
        ,@PrevYYDate = YYDate
   FROM #RunningTotal rt WITH (TABLOCKX,INDEX(0)) --These hints MUST be included
 OPTION (MAXDOP 1)                                --This option MUST be included to prevent parallelism
;
 
 SELECT * FROM #RunningTotal
;

It looks a bit complicated because it does exactly the same thing that you would do with managed code but used the underlying "pseudo-cursor" of UPDATE to do the job instead of a DO LOOP in managed code. It also avoids all 4 Triangular Joins (1/2 of a Cartesian Product), which are starting to get a bit heavy even with just 692 rows (as of today).

Code works as is if you run it against the test table I made. If you want to run it against your test table, then DO NOT RUN THE FIRST SECTION OF CODE!!!! Just change #ITEM_VOL to just ITEM_VOL every where in the second section of code and run the second section of code.

Ok... now DUCK!!!! You'll see why in another post or two.

JeffModen · December 9, 2015, 2:59pm

@Sameer,

Any feedback on this problem?

Sameer · December 10, 2015, 2:02pm

Hi Jeffmoden I am very sorry currently on vacation as I reach back test and feed you back...

Thank you appreciated for hard work .

Sameer · May 12, 2016, 4:58am

Hello jefmoden /Khatn
I tested your hard work ( worked on temp table ) , working great well done. One modification required please Khatan / Jefmoden to Map the ITEM_ID field that the data will reflect to each individual ITEM_ID.

I have many item_ids while running the scripts the data not appears based ITEM_ID's

This will make my life very easy and very thankful to you both.