Custom Count - TSQL Query

Question

Post reply

Custom Count - TSQL Query

UnionAll

SSCrazy

Points: 2102
More actions
July 22, 2010 at 12:04 pm

#223667

I need a query to find the count of available agents by month.
Please see below for the source table and how the result table should look like.
IF OBJECT_ID('#TempTable') IS NOT NULL
DROP TABLE #TempTable
IF OBJECT_ID('#Result') IS NOT NULL
DROP TABLE #Result
create table #TempTable(ParentAgtID Int, AgtID Int, Lvl int, BeginDate Datetime, EndDate Datetime)
insert into #TempTable values (1000,555,4,'1995-07-28 00:00:00.000',NULL)
insert into #TempTable values (1000,465,4,'1995-07-28 00:00:00.000','2010-03-10 00:00:00.000')
insert into #TempTable values (1000,265,4,'2010-03-15 00:00:00.000','2010-06-10 00:00:00.000')
insert into #TempTable values (2000,165,3,'2010-03-15 00:00:00.000','2010-06-10 00:00:00.000')
insert into #TempTable values (2000,165,3,'2010-03-15 00:00:00.000',NULL)
--select * from #TempTable
create table #Result(ParentAgtID Int, Jan Int,Feb Int,Mar Int,Apr Int,May Int,Jun Int,Jul Int,Aug Int,Sep Int,Oct Int,Nov Int,Dec Int)
insert into #Result values (1000,2,2,3,2,2,2,1,NULL,NULL,NULL,NULL,NULL)
insert into #Result values (2000,0,0,2,2,2,2,1,NULL,NULL,NULL,NULL,NULL)
--SELECT * FROM #Result
If you take a look at the above table (#TempTable) there are 3 agents altogether for parent 1000. However not all of them
were available for the entire period. The availability is determined by the BeginDate and EndDate columns. For example, Agent 265 started in 03/2010 and ended in 06/2010
which means he wasn't available in the months of Jan,Feb and Jul.
Please help. Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'd query against a calendar table or an on-the-fly 12month subquery.

Based on that you'd get the AgtID expanded per month.

Something like

SELECT yr,mnth,ParentAgtID,AgtID

FROM

(

SELECT CAST('20100101' AS DATETIME) AS DATE, 2010 AS yr,1 AS mnth UNION ALL

SELECT CAST('20100201' AS DATETIME) AS DATE, 2010 AS yr,2 AS mnth UNION ALL

-- the list goes on...

SELECT CAST('20101201' AS DATETIME) AS DATE, 2010 AS yr,12 AS mnth

)

calendar

LEFT OUTER JOIN #TempTable

ON calendar.date >= BeginDate

AND calendar.date <=ISNULL(EndDate,'99990101')

GROUP BY yr,mnth,ParentAgtID,AgtID

Then you can either use PIVOT or CrossTab to get your desired result.

One issue you need to clarify: what will the starting day within a month for an AgtID to be counted?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

UnionAll SSCrazy Points: 2102 More actions · Answer 2

WOW! Thanks Lutz.

This is excellent, worked excatly how i wanted. Only change i made is to use getdate() to avoid hardcoding the calendar derived table.

Thanks Again!