Urgent: Company Name & Count for Last one week

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

SQL Server 2008 Forums

Transact-SQL (2008)

Urgent: Company Name & Count for Last one week

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-06-12 : 02:24:46

Hi All,

I need a query to fetch the last one week Meeting records group by Company.

Table containing Columns
PhoneNumber | Company |DialedStartdate | DialedEndDate
1234 AAA 2011-03-02 2011-03-02
............
9999 zzz 2014-06-11 2014-06-11

Required Output:
Company | NoofMeetings | Date
AAA 10 6/11/2014
AAA 12 6/10/2014
..............
..............
AAA 4 6/6/2014
BBB 22 6/11/2014
BBB 10 6/10/2014
..............
.............
BBB 89 6/6/2014
.......................
.................
ZZZ 21 6/6/2014

Note: Only One week records to be required from now with getdate()-1

Thanks in Advance,
Vijay

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-12 : 05:01:35

May be this is what you want if not so show me the expected output for the input set i have taken.....


CREATE TABLE #test(PhoneNumber int, Company varchar(max),DialedStartdate datetime, DialedEndDate datetime)
INSERT INTO #test  
SELECT 1234,'AAA','2011-03-02','2011-03-02' UNION ALL
SELECT 1234,'AAA','2011-03-02','2011-03-02' UNION ALL
SELECT 5648,'AAA','2011-03-10','2011-03-10' UNION ALL
SELECT 5648,'AAA','2011-03-10','2011-03-10' UNION ALL
SELECT 5648,'AAA','2011-03-10','2011-03-10' UNION ALL
SELECT 5648,'BBB','2011-03-02','2011-03-02' UNION ALL
SELECT 5648,'BBB','2011-03-02','2011-03-02' UNION ALL
SELECT 1234,'BBB','2011-03-02','2011-03-02' UNION ALL
SELECT 9999,'zzz','2014-06-11','2014-06-11' UNION ALL
SELECT 9999,'zzz','2014-06-11','2014-06-11' 

SELECT	DISTINCT	       Company
			  ,    COUNT(DialedStartdate) OVER (PARTITION BY Company,DialedStartdate) AS NoofMeetings
		          ,    DialedStartdate
FROM #test

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

vijay1234
Starting Member

48 Posts

Posted - 2014-06-12 : 08:34:36

Thanks Murali.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources