Finding Locations with no monthly activity

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 2005 Forums

Transact-SQL (2005)

Finding Locations with no monthly activity

Author

Topic

johnr2000
Starting Member

7 Posts

Posted - 2010-08-06 : 21:05:18

I have a table that has the following fields:
LocationID (int), Type (nvarchar 2), Month (int), ServiceDate (DateTime), TransactionID (int)

I am trying to get a count of records for a particular location for a particular type for a particular month so I can use it in a report.

My report will eventually look like this:
LocationID: 1
Type April May June
I 96 84 64
O 132 147 172
EA 48 0 47

The problem I run into is when there are no transactions for a particular location for a particular type for a particular month. I need to get a 0 count. For each location I need a record for EACH of the 3 types for EACH of the months in my range I specify.

I'm doing this:
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
SELECT @FromDate = '4/1/2010', @ToDate = '6/30/2010'
SELECT LocationID, Type, [Month], COUNT(TransactionID) AS CountOfTransId
FROM MyTable
WHERE ServiceDate BETWEEN @FromDate AND @ToDate
GROUP BY LocationID, Type, [Month]

So, for all the locations with a service date between @FromDate and @ToDate, get a count for each Type for each of the months between those two dates.

I can get the list of months between the two dates using the following code:

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
SELECT @FromDate = '4/1/2010', @ToDate = '6/30/2010'
SELECT TOP (DATEDIFF(mm,
DATEADD(mm,DATEDIFF(mm,0,@FromDate),0), --First of start month
DATEADD(mm,DATEDIFF(mm,0,@ToDate)+1,0))) --First of month after end month
MONTH(DATEADD(mm, DATEDIFF(mm, 0, @FromDate) + ROW_NUMBER() OVER (ORDER BY t1.Object_ID) - 1, 0))
FROM Master.sys.All_Columns t1

I just haven't figured out how to make sure each Location has a record for each Type for each included Month.

I have been struggling with this for a while now. Hopefully someone can help.

Thanks in advance,

John

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-07 : 07:49:07

please post some sample data and DDL schemas.
and what is your desired output (also for cases where there is not records for particular month).

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-08 : 02:35:12

you need to generate a calendar table for that and use it as master table. then left join with your current table and use ISNULL() to convert NULL values to 0 (NULL values will come when you dont have any record for that type in that day)

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johnr2000
Starting Member

7 Posts

Posted - 2010-08-11 : 14:47:06

I have tried left joining with my current table and it doesn't work. I don't get anymore records back.

Here's what my table looks like:

CREATE TABLE [dbo].[MyTable](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NULL CONSTRAINT [DF__Locat__023D5A04] DEFAULT ((0)),
[Type] [nvarchar](50) NULL,
[Month] [int] NULL,
[ServiceDate] [datetime] NULL
) ON [PRIMARY]

Here's what my data looks like:

LocationID Type Month TransactionID
1               EA      4       607377
1               EA      5       609738
1               EA      5       609739
1               EA      6       613271
1               I        4       609561
1               I        5       609557
1               I        6       609558
1               O       4       609461
1               O       5       609462
1               O       6       609328
249            O       4       608230
249            O       6       614794
249            O       6       614795
249            O       6       614048
249            O       6       614050
249            O       6       612441

There are 3 Types: EA, I, and O.

There are 3 Months: 4, 5, and 6.

FOR EACH LocationID I want a count by Type AND Month.

Here's what I'd like to see:

LocationID Type Month TransCount
1               EA      4       1
1               EA      5       2
1               EA      6       1
1               I        4       1
1               I        5       1
1               I        6       1
1               O       4       1
1               O       5       1
1               O       6       1
249            EA      4       0
249            EA      5       0
249            EA      6       0
249            I        4       0
249            I        5       0
249            I        6       0
249            O       4       1
249            O       5       0
249            O       6       5

As you can see LocationID 249 doesn't have any 'EA' and 'I' Types for Months 4, 5, and 6 and doesn't have any 'O' Types for Month 5. In this case I want to see a zero count. Right now I don't get a record for these cases.

I have tried making CTE tables for the Types and Months and Left Joining them in with my data but I still cannot get a record with a count for each Location and Type and Month.

Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 13:55:35

[code]
SELECT p.LocationID,p.Type,p.Month,
ISNULL(q.Cnt,0) AS TransCount
FROM
(
SELECT l.LocationID,t.Type,m.Month
FROM (SELECT DISTINCT LocationID FROM Table)l
CROSS JOIN (SELECT DISTINCT Type FROM table) t
CROSS JOIN (SELECT DISTINCT Month FROM Table)m
)p
LEFT JOIN (SELECT LocationID,Type,Month,COUNT(TransactionID) AS Cnt
FROM Table
GROUP BY LocationID,Type,Month)q
ON q.LocationID = p.LocationID
AND q.Type = p.Type
AND q.Month = p.Month
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johnr2000
Starting Member

7 Posts

Posted - 2010-08-13 : 12:59:20

Thanks, that works!

I have to remember CROSS JOIN. That definitely comes in handy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 02:16:46

welcome

yeah ...it really is a cool feature
see what all you can do with it

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources