get counts including previous years, months (Network Steve Forum)

get counts including previous years, months

Hi All,

I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'

How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...

Any help is appreciated.

Thanks,

Punia

Edited by babbupunia 13 hours 7 minutes ago

May 28th, 2015 2:00pm

Can you post your table definition (DDL) along with some sample data? Thanks.

Free Windows Admin Tool Kit Click here and download it now

May 28th, 2015 2:05pm

ID (varchar)	ConnectionDate	Year	WeekEnding
217	4/20/2015	2015	25-Apr
217	4/24/2015	2015	25-Apr
217	4/25/2015	2015	25-Apr
217	4/27/2015	2015	2-May
217	4/30/2015	2015	2-May
217	5/3/2015	2015	9-May
217	5/4/2015	2015	9-May
217	5/7/2015	2015	9-May
217	5/11/2015	2015	16-May
217	5/14/2015	2015	16-May
217	5/15/2015	2015	16-May
217	5/17/2015	2015	23-May
217	5/18/2015	2015	23-May
217	5/21/2015	2015	23-May
217	5/22/2015	2015	23-May
217	5/25/2015	2015	30-May
217	5/27/2015	2015	30-May
3568	5/19/2015	2015	23-May
3568	5/21/2015	2015	23-May
2701	4/20/2015	2015	25-Apr
2701	4/21/2015	2015	25-Apr
2701	4/22/2015	2015	25-Apr
2701	4/23/2015	2015	25-Apr
2701	4/24/2015	2015	25-Apr
2701	4/27/2015	2015	2-May
2701	4/28/2015	2015	2-May
2701	4/29/2015	2015	2-May
2701	4/30/2015	2015	2-May
2701	5/4/2015	2015	9-May
2701	5/5/2015	2015	9-May
2701	5/6/2015	2015	9-May
2701	5/7/2015	2015	9-May
2701	5/11/2015	2015	16-May
2701	5/12/2015	2015	16-May
2701	5/13/2015	2015	16-May
2701	5/18/2015	2015	23-May
2701	5/19/2015	2015	23-May
2701	5/20/2015	2015	23-May
2701	5/21/2015	2015	23-May
2701	5/22/2015	2015	23-May
2701	5/26/2015	2015	30-May
2701	5/27/2015	2015	30-May
3475	5/4/2015	2015	9-May
3316	4/27/2015	2015	2-May
3316	4/29/2015	2015	2-May
3316	4/30/2015	2015	2-May
3316	5/12/2015	2015	16-May
3316	5/13/2015	2015	16-May
3316	5/19/2015	2015	23-May
3316	5/22/2015	2015	23-May
3316	5/27/2015	2015	30-May

May 28th, 2015 2:34pm

Create table yourtablename(ID varchar(50), ConnectionDate varchar(50), Year int, WeekEnding varchar(50))

Insert into yourtablename values('217','4/20/2015','2015','25-Apr')

...

Finish these for your sample data. It helps all of us.

But you need to tell us the exact data type you are using for these columns.

What do you want from the sample data as your expected result?

Free Windows Admin Tool Kit Click here and download it now

May 28th, 2015 2:40pm

Very easy to do with SQL 2012 Window Functions.

select year, WeekEnding, count(distinct ID) over (partition by year order by WeekEnding rows between unbounded preceding and current row ) from yourTable

Edited by Shakeel Ahsan 11 hours 58 minutes ago

May 28th, 2015 3:10pm

this is what I got - Use of DISTINCT is not allowed with the OVER clause.

Free Windows Admin Tool Kit Click here and download it now

May 28th, 2015 3:29pm

Create table table1 (ID int, Connection Date ,
Year int, WeekEnding varchar(10))

this what I have... Somehow excel do not work for date and weekending thing...

Insert into table1 values(217,42114,2015,'42119')
Insert into table1 values(217,42118,2015,'42119')
Insert into table1 values(217,42119,2015,'42119')
Insert into table1 values(217,42121,2015,'42126')
Insert into table1 values(217,42124,2015,'42126')
Insert into table1 values(217,42127,2015,'42133')
Insert into table1 values(217,42128,2015,'42133')
Insert into table1 values(217,42131,2015,'42133')
Insert into table1 values(217,42135,2015,'42140')
Insert into table1 values(217,42138,2015,'42140')
Insert into table1 values(217,42139,2015,'42140')
Insert into table1 values(217,42141,2015,'42147')
Insert into table1 values(217,42142,2015,'42147')
Insert into table1 values(217,42145,2015,'42147')
Insert into table1 values(217,42146,2015,'42147')
Insert into table1 values(217,42149,2015,'42154')
Insert into table1 values(217,42151,2015,'42154')
Insert into table1 values(3568,42143,2015,'42147')
Insert into table1 values(3568,42145,2015,'42147')
Insert into table1 values(2701,42114,2015,'42119')
Insert into table1 values(2701,42115,2015,'42119')
Insert into table1 values(2701,42116,2015,'42119')
Insert into table1 values(2701,42117,2015,'42119')
Insert into table1 values(2701,42118,2015,'42119')
Insert into table1 values(2701,42121,2015,'42126')
Insert into table1 values(2701,42122,2015,'42126')
Insert into table1 values(2701,42123,2015,'42126')
Insert into table1 values(2701,42124,2015,'42126')
Insert into table1 values(2701,42128,2015,'42133')
Insert into table1 values(2701,42129,2015,'42133')
Insert into table1 values(2701,42130,2015,'42133')
Insert into table1 values(2701,42131,2015,'42133')
Insert into table1 values(2701,42135,2015,'42140')
Insert into table1 values(2701,42136,2015,'42140')
Insert into table1 values(2701,42137,2015,'42140')
Insert into table1 values(2701,42142,2015,'42147')
Insert into table1 values(2701,42143,2015,'42147')
Insert into table1 values(2701,42144,2015,'42147')
Insert into table1 values(2701,42145,2015,'42147')
Insert into table1 values(2701,42146,2015,'42147')
Insert into table1 values(2701,42150,2015,'42154')
Insert into table1 values(2701,42151,2015,'42154')
Insert into table1 values(3475,42128,2015,'42133')
Insert into table1 values(3316,42121,2015,'42126')
Insert into table1 values(3316,42123,2015,'42126')
Insert into table1 values(3316,42124,2015,'42126')
Insert into table1 values(3316,42136,2015,'42140')
Insert into table1 values(3316,42137,2015,'42140')
Insert into table1 values(3316,42143,2015,'42147')
Insert into table1 values(3316,42146,2015,'42147')
Insert into table1 values(3316,42151,2015,'42154')
Insert into table1 values(3119,42114,2015,'42119')
Insert into table1 values(3119,42115,2015,'42119')
Insert into table1 values(3119,42117,2015,'42119')
Insert into table1 values(3119,42118,2015,'42119')
Insert into table1 values(3119,42121,2015,'42126')
Insert into table1 values(3119,42122,2015,'42126')
Insert into table1 values(3119,42123,2015,'42126')
Insert into table1 values(3119,42124,2015,'42126')
Insert into table1 values(3119,42128,2015,'42133')
Insert into table1 values(3119,42129,2015,'42133')
Insert into table1 values(3119,42130,2015,'42133')
Insert into table1 values(3119,42131,2015,'42133')
Insert into table1 values(3119,42135,2015,'42140')
Insert into table1 values(3119,42136,2015,'42140')
Insert into table1 values(3119,42137,2015,'42140')
Insert into table1 values(3119,42142,2015,'42147')
Insert into table1 values(3119,42143,2015,'42147')
Insert into table1 values(3119,42144,2015,'42147')
Insert into table1 values(3119,42145,2015,'42147')
Insert into table1 values(3119,42146,2015,'42147')
Insert into table1 values(3119,42150,2015,'42154')
Insert into table1 values(3119,42151,2015,'42154')
Insert into table1 values(134,42114,2015,'42119')
Insert into table1 values(134,42115,2015,'42119')
Insert into table1 values(134,42116,2015,'42119')
Insert into table1 values(134,42117,2015,'42119')
Insert into table1 values(134,42118,2015,'42119')
Insert into table1 values(134,42120,2015,'42126')
Insert into table1 values(134,42121,2015,'42126')
Insert into table1 values(134,42122,2015,'42126')
Insert into table1 values(134,42123,2015,'42126')

May 28th, 2015 3:53pm

 
CREATE TABLE [dbo].[table1](
	[ID] [int] NULL,
	[Connection] [date] NULL,
	[Year] [int] NULL,
	[WeekEnding] [varchar](10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-04-20' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-04-24' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-04-25' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-04-27' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-04-30' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-03' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-04' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-07' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-11' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-14' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-15' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-17' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-18' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-21' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-22' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-25' AS Date), 2015, N'30-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (217, CAST(N'2015-05-27' AS Date), 2015, N'30-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3568, CAST(N'2015-05-19' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3568, CAST(N'2015-05-21' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-20' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-21' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-22' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-23' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-24' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-27' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-28' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-29' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-04-30' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-04' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-05' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-06' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-07' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-11' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-12' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-13' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-18' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-19' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-20' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-21' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-22' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-26' AS Date), 2015, N'30-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (2701, CAST(N'2015-05-27' AS Date), 2015, N'30-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3475, CAST(N'2015-05-04' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-04-27' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-04-29' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-04-30' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-05-12' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-05-13' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-05-19' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-05-22' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3316, CAST(N'2015-05-27' AS Date), 2015, N'30-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-20' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-21' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-23' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-24' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-27' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-28' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-29' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-04-30' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-04' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-05' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-06' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-07' AS Date), 2015, N'9-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-11' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-12' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-13' AS Date), 2015, N'16-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-18' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-19' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-20' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-21' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-22' AS Date), 2015, N'23-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-26' AS Date), 2015, N'30-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (3119, CAST(N'2015-05-27' AS Date), 2015, N'30-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-20' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-21' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-22' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-23' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-24' AS Date), 2015, N'25-Apr')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-26' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-27' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-28' AS Date), 2015, N'2-May')
GO
INSERT [dbo].[table1] ([ID], [Connection], [Year], [WeekEnding]) VALUES (134, CAST(N'2015-04-29' AS Date), 2015, N'2-May')
GO



declare @WeekEnding varchar(6)='25-Apr'

select  Count(distinct [ID]) cnt FROM [table1] 
WHERE  Cast([WeekEnding]+ '-'+ Cast(Year as char(4)) as date) <= Cast(@WeekEnding+ '-'+ Cast(Year as char(4)) as date)

Drop table dbo.[table1]

Free Windows Admin Tool Kit Click here and download it now

May 28th, 2015 4:18pm

I have created DDL for you

DROP TABLE mytable;
CREATE TABLE mytable(
  ID INTEGER NOT NULL 
, Connection DATE 
, YearName INTEGER
, WeekEnding VARCHAR(40)
);
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/20/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/24/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/25/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/27/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/30/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/3/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/4/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/7/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/11/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/14/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/15/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/17/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/18/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/21/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/22/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/25/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/27/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3568,'5/19/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3568,'5/21/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/20/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/21/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/22/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/23/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/24/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/27/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/28/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/29/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/30/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/4/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/5/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/6/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/7/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/11/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/12/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/13/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/18/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/19/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/20/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/21/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/22/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/26/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/27/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3475,'5/4/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'4/27/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'4/29/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'4/30/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/12/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/13/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/19/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/22/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/27/2015',2015,'30-May');

Can you tell me what is your expected output? I didn't get your question correctly? Are you looking for preceding aggregates or just aggregates like below...

SELECT COUNT(*) NUMBER_OF_ID, WeekEnding 
FROM dbo.myTable
GROUP BY WeekEnding

If you can guide us about the expected output then we can precisely write your query

May 28th, 2015 4:44pm

Sorry, this should give you what you need -

select
COUNT ( distinct t2.ID ) IDCount,
t1.YearName,
t1.WeekEnding
from mytable t1
join myTable t2
on t1.YearName = t2.YearName
and t1.Connection >= t2.Connection
group by t1.YearName,
t1.WeekEnding

Free Windows Admin Tool Kit Click here and download it now

May 28th, 2015 6:25pm

Hi babbupunia,

According to your description, to achieve your requirement, you can reference the query below. Thanks the data insert statements from Anuj.

--DROP TABLE mytable;
--CREATE TABLE mytable(
--  ID INTEGER NOT NULL 
--, Connection DATE 
--, YearName INTEGER
--, WeekEnding VARCHAR(40)
--);
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/20/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/24/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/25/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/27/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'4/30/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/3/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/4/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/7/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/11/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/14/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/15/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/17/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/18/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/21/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/22/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/25/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (217,'5/27/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3568,'5/19/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3568,'5/21/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/20/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/21/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/22/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/23/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/24/2015',2015,'25-Apr');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/27/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/28/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/29/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'4/30/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/4/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/5/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/6/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/7/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/11/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/12/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/13/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/18/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/19/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/20/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/21/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/22/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/26/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (2701,'5/27/2015',2015,'30-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3475,'5/4/2015',2015,'9-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'4/27/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'4/29/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'4/30/2015',2015,'2-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/12/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/13/2015',2015,'16-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/19/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/22/2015',2015,'23-May');
INSERT INTO mytable(ID,Connection,YearName,WeekEnding) VALUES (3316,'5/27/2015',2015,'30-May');


;WITH Cte AS
(
SELECT ID,YearName,DATEADD(DAY,5,DATEADD(WEEK,DATEDIFF(WEEK,0,Connection),0)) WeekEnding FROM myTable
),
Cte2 AS(
SELECT COUNT(DISTINCT ID) cnt ,WeekEnding,YearName  FROM Cte
GROUP by YEARNAME,WeekEnding
)
SELECT YEARNAME,WeekEnding, CAT.cumCnt FROM Cte2 C
CROSS APPLY
(
SELECT SUM(cnt) cumCnt FROM Cte2 WHERE YearName=c.YearName AND WeekEnding<=C.WeekEnding
)CAT

If you have any question, feel free to let me know.

May 29th, 2015 2:43am

This topic is archived. No further replies will be accepted.