October 28, 2006 at 9:18 pm
I have a few questions (I am using Sql Server 2005)
1. I have two queries with totally different where clauses(see Below) I would like to somehow combine to one and get one result set.
something like
Month Name CallCount 411Count TalkTime AVGTime Top Area Codes for All Names
July Joe Blow 350 10 200 30 937
July Bill Nye 500 30 300 50 245
603
512
859
Here's my 2 queries that returns the above data in two results
FYI the local day field is a date field, but it is stored as an Int
--Query 1 Call Details
SELECT datepart(Month,convert(datetime,convert (varchar(8), localday))) As [Month],
CallerName,Sum (TalkDuration) /60 As TalkTime, Sum (TalkDuration) /60 /30.4 As TimeDaily,
SUM(CASE WHEN Direction = '2' THEN 1 ELSE 0 END) AS CallCount,
SUM(CASE WHEN TargetNum = '411' THEN 1 ELSE 0 END) AS [411Ccount],
Avg (TalkDuration)/60 As AvgTalk
FROM CDRMAIN
GROUP BY datepart(Month,convert(datetime,convert (varchar(8), localday))), CallerName
HAVING (CallerName = 'Name1')Or
(CallerName = 'Name2')Or
(CallerName = 'Name3')Or
(CallerName = 'Name4')
order by datepart(Month,convert(datetime,convert (varchar(8), localday)))
--query2 Top Area Codes
select top (6) (substring(Targetnum,1,3)) As [AreaCdCnt] from cdrmain
where substring(Targetnum,1,3) not like '800%' and
substring(Targetnum,1,3) not like '1%'
and Direction ='2' and substring(Targetnum,1,3) like '2%'
or substring(Targetnum,1,3) like '3%'
or substring(Targetnum,1,3) like '4%'
or substring(Targetnum,1,3) like '5%'
or substring(Targetnum,1,3) like '6%'
or substring(Targetnum,1,3) like '7%'
or substring(Targetnum,1,3) like '8%'
or substring(Targetnum,1,3) like '9%'
2. In the First Query I would also like to change the Sum (TalkDuration) /60 /30.4 (and other local day fields)
to instead divide by the number of Business days in each month (30.4 is the avg days on a month) and if the month is the current month then divide by how many Business days so far in the month. Business days being Mon-Fri. I know I can use case - else to do this somehow
But not sure exactly since localday is an INT field.
3. It would be much more advantagous to me if I could just change the second query to just count the Targetnum strings and return the data where the string count is = 10.
I thought I could do Count Substring (Targetnum,1,10) As TargetCnt where Targetcnt =10
that didn't work.
4. last but not least how can i get how many business days there have been up to the current date in the current year.
I realize I am asking for a lot, I would VERY much appreciate some help please.
Thank you
October 29, 2006 at 6:32 am
Look in books online for the union clause, as long as the data tyes and number of columns are the same you should be fine.
October 30, 2006 at 2:48 am
1. Do whatever you can to change "localday" column to DATETIME. If INT is required by some application or you can't change it for some other reason, consider creating another column that would store the date in proper datatype. It will save you lots of conversions and should improve performance.
2. Your queries don't have different just WHERE clause, they are completely different. First returns 7 columns, second returns 1 column of a different type than first column in first query. Data have fully different meaning. I don't see any use in combining them, and you can't use UNION.
3. Business days - IMHO the best solution is to have a special table in the database which would contain all days and a flag whether a day is business day or not. There are some holidays that are not considered business days, and they can be quite irregular. But even if you are fine with just Mon-Fri, this table should help you (although then there would be also other ways to calculate number of such days in a month). This will also help with the last question, about number of such days in a year.
4. Count Substring (Targetnum,1,10) As TargetCnt where Targetcnt =10 ...? I don't understand what this should do. Why substring inside count? What do you want to do?
Generally, I think it would be best if you describe (in words) what is the desired result... and add table definitions and test data. Then we could try to come up with some solution.
October 30, 2006 at 3:35 am
Also asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74180
N 56°04'39.16"
E 12°55'05.25"
October 30, 2006 at 3:52 am
Posted this recently for calculating work days between two given dates. No table required here.
create function trx_workdays1(@p_startdate datetime, @p_enddate datetime) returns integer as
begin
declare @startdate datetime
declare @enddate datetime
declare @dowSat int
declare @dowSun int
declare @normstartdate datetime
declare @normenddate datetime
declare @DW int
declare @dwdest int
declare @padded_enddate datetime
declare @padded_workdays int
declare @diff int
if @p_startdate is null or @p_enddate is null
return 0
-- truncate time portion
set @startdate=convert(varchar,year(@p_startdate))+substring(convert(varchar,month(@p_startdate)+100),2,2)+substring(convert(varchar,day(@p_startdate)+100),2,2)
set @enddate=convert(varchar,year(@p_enddate))+substring(convert(varchar,month(@p_enddate)+100),2,2)+substring(convert(varchar,day(@p_enddate)+100),2,2)
-- capture these for known dates as they may vary based on SET DATEFIRST
set @dowSat=datepart(dw,'2006-10-28')
set @dowSun=datepart(dw,'2006-10-29')
-- normalize start date by moving forward, i.e. should not be a saturday or sunday
set @normstartdate=@startdate
set @DW=datepart(dw,@normstartdate)
if @DW=@dowSun
set @normstartdate=dateadd(d,1,@normstartdate)
else
if @DW=@dowSat
set @normstartdate=dateadd(d,2,@normstartdate)
-- normalize end date by moving backwards, i.e. should not be a saturday or sunday
set @normenddate=@enddate
set @DW=datepart(dw,@normenddate)
if @DW=@dowSun
set @normenddate=dateadd(d,-2,@normenddate)
else
if @DW=@dowSat
set @normenddate=dateadd(d,-1,@normenddate)
set @padded_enddate=@normenddate
set @padded_workdays=0
-- pad end date so that difference becomes a multiple of 7 days;
-- we also need to count the number of weekdays in the days we added for the padding;
set @dwdest=datepart(dw,@startdate)-1
if @dwdest=0 set @dwdest=7
while datepart(dw,@padded_enddate)<>@dwdest -- loops no more than 6 times
begin
set @padded_enddate=dateadd(d,1,@padded_enddate)
set @DW=datepart(dw,@padded_enddate)
if @DW<>@dowSat and @DW<>@dowSun set @padded_workdays=@padded_workdays+1
end
set @diff=datediff(d,@normstartdate,@padded_enddate)+1
-- subtract number of intervening saturdays/sundays and subtract number of workdays we added for the padding
return @diff-@diff/7*2-@padded_workdays
end
October 30, 2006 at 4:31 am
Thought about Vladan's point 3 where a table approach would use a query like:
select count(*)
from cal
where d>=mydate1 and d<=mydate2 and h=1
But by adding another column holding a sequence number 'n' (which doesn't increase on saturday or sunday or holiday) the query might become a lot faster:
select c2.n-c1.n+
case when c1.h=0 and c2.h=0 then 0 else 1 end workdays
from cal c1, cal c2
where c1.d='2006-10-02' and c2.d='2006-10-13'
Any thoughts?
create table cal(
d datetime,
n int,
h int)
insert into cal values('2006-10-01',0,0)
insert into cal values('2006-10-02',1,1)
insert into cal values('2006-10-03',2,1)
insert into cal values('2006-10-04',3,1)
insert into cal values('2006-10-05',4,1)
insert into cal values('2006-10-06',5,1)
insert into cal values('2006-10-07',5,0)
insert into cal values('2006-10-08',5,0)
insert into cal values('2006-10-09',6,1)
insert into cal values('2006-10-10',7,1)
insert into cal values('2006-10-11',8,1)
insert into cal values('2006-10-12',9,1)
insert into cal values('2006-10-13',10,1)
insert into cal values('2006-10-14',10,0)
insert into cal values('2006-10-15',10,0)
insert into cal values('2006-10-16',11,1)
insert into cal values('2006-10-17',12,1)
insert into cal values('2006-10-18',13,1)
insert into cal values('2006-10-19',14,1)
insert into cal values('2006-10-20',15,1)
insert into cal values('2006-10-21',15,0)
insert into cal values('2006-10-22',15,0)
insert into cal values('2006-10-23',16,1)
insert into cal values('2006-10-24',17,1)
insert into cal values('2006-10-25',18,1)
insert into cal values('2006-10-26',19,1)
insert into cal values('2006-10-27',20,1)
insert into cal values('2006-10-28',20,0)
insert into cal values('2006-10-29',20,0)
insert into cal values('2006-10-30',21,1)
insert into cal values('2006-10-31',22,1)
October 30, 2006 at 7:19 pm
Thanks for all the replies. I know all about storing dates, but the DB is a 3rd party db used to captured data from a switch they set it up as int. I went ahead and created a calculated field.
Thaks for the input i will check these out.
October 30, 2006 at 10:18 pm
"Very procedural": Yes, but it beats accessing a table hands down.
"Very propietary": Don't understand that one. Or maybe are you referring to the fact that it's very SQL Server specific? I think it can be rendered in Oracle in about 10 minutes.
"Good Friday": That's where trx_workdays2 comes in (not shown here) which accesses a table (just a single column) for getting non-sat/sun holidays. I keep them separate to be able to choose the slower one only when it's absolutely necessary.
There are many ways to use a table and I offered two versions in my subsequent comment (neither of which I use). What's your approach using tables?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply