August 8, 2006 at 3:33 pm
Hi All,
I have a database that logs all the calls we receive during the day. I am building an ASP page that basically shows the statistics of all the calls we have received per month, listed by day. I want to be able to show 0 calls during the days where we haven't received the calls, so I think I have two options. First is to create a table with 1 column that lists every day for the next year or so. This way I can just run a count query on the table with the log and join it with the table listing the dates. Second is to create a function that lists all of the days by month and join it with the log table. People will be accessing this many times a day, so would it be faster to go with option 1? I figure it would be since it's a lot more code for option 2.
Thanks
August 8, 2006 at 5:23 pm
I would recomment the second option with slightly different approach.
declare @Customer table
(
CustomerID varchar(2)
)
insert into @Customer values ('AA')
insert into @Customer values ('BB')
insert into @Customer values ('CC')
insert into @Customer values ('DD')
insert into @Customer values ('EE')
insert into @Customer values ('FF')
insert into @Customer values ('GG')
insert into @Customer values ('HH')
insert into @Customer values ('II')
insert into @Customer values ('JJ')
declare @calls table
(
CustomerIDvarchar(2),
CallDatedatetime
)
insert into @Calls values ('AA', '08/07/2006 08:00:00 AM')
insert into @Calls values ('CC', '08/07/2006 09:00:00 AM')
insert into @Calls values ('EE', '08/07/2006 08:02:45 AM')
insert into @Calls values ('JJ', '08/07/2006 07:45:00 AM')
insert into @Calls values ('AA', '08/07/2006 11:00:00 AM')
select c.CustomerID, count(CallDate)
from
@customer c
left join @calls t
on c.CustomerID = t.CustomerID
group by c.CustomerID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply