Combine 2 queries with different where clause

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

Transact-SQL (2000)

Combine 2 queries with different where clause

Author

Topic

gtjr92
Starting Member

15 Posts

Posted - 2006-10-28 : 23:18:55

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

Kristen
Test

22859 Posts

Posted - 2006-10-29 : 01:15:44

"I would like to somehow combine to one and get one result set."

You can do that with UNION ALL but only if both queries contain the same number of columns, and each column has the same data type. You can return NULLs to get around this:

SELECT Col1, Col2, Col3, NULL, NULL, NULL
FROM MyTable1
UNION ALL
SELECT NULL, NULL, NULL, Col4, Col5, Col6
FROM MyTable2

"datepart(Month,convert(datetime,convert (varchar(8), localday)))"

This looks pretty hideous!

"localday is an INT field"

Do you mean that "localday" stores dates in a format like 20061231 ? Unless you've got some very good reason you should be storing dates using a datetime datatype, not varchar, int, or anything else.

"Business days in each month"

Have a look here:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=64762
MVJ has lots of date functions, and probably has one that will do what you want.

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%'

Bit inefficient! This would do the same job:

and Targetnum like '[2-9]%'

in particular no need for the SUBSTRING() function, and that means that if Targetnum is indexed my revised syntax can use the index, whereas using SUBSTRING() it won't.

"Count Substring (Targetnum,1,10) As TargetCnt where Targetcnt =10"

Sorry, I don;t understand what you are trying to achieve there - can you explain it differently please?

COUNT(foo) will count the number of rows where FOO is NOT null.

If you want a count of a specific value you could do something like:


SELECT SUM(CASE WHEN Targetnum '2%' THEN 1 ELSE 0 END) AS TwoCount,
       SUM(CASE WHEN Targetnum '3%' THEN 1 ELSE 0 END) AS ThreeCount,
       ...

"how many business days there have been up to the current date in the current year"

See MVJ's scripts above - hopefully he has something that does that!

Kristen

gtjr92
Starting Member

15 Posts

Posted - 2006-10-29 : 08:28:38

Thanks for the info. yes the dates really are integers I didn't create the DB it's 3rd party.
It gets data from a telephone switch, so i don't know if that is why it's an INT field or not. I even tried changing it to datetime and the db would not capture the data so i had to change it back to int.
for the substring field I actually want to count the caricatures in the field because
that field holds what phone number someone dialed. I want to get the long distance numbers
so if someone dialed long distance then they would have dialed 10 digits because the DB does not capture the local area code, or the 1 preceding it if it is long distance. so local calls are 7 digits.
Really the query i have not is not getting totally accurate data because some of the numbers it is pulling out are local calls as well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 08:56:24

Create a calculated column, based on the INT date column, as



declare @a int
select @a = 20061029

select	dateadd(month, (@a % 10000) / 100 - 1, dateadd(day, @a % 100 - 1, dateadd(year, @a / 10000 - 1900,0)))

Now you have a column that is the same date, but in DATETIME data type.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-10-29 : 11:33:43

"because the DB does not capture the local area code"

I am wrong in thinking you have alternative local areas codes in the USA - where the phone company ran out of numbers on the original area code (I'm thinking Houston, but I expect there are others)

If you want to know the number that are less than 10 digits, and the number that are 10 digits or more, you can do something like:


SELECT SUM(CASE WHEN LEN(Targetnum) < 10 THEN 1 ELSE 0 END) AS ShortNumber,
       SUM(CASE WHEN LEN(Targetnum) >= 10 THEN 1 ELSE 0 END) AS LongNumber,
       COUNT(*) AS TotalNumbers

Assuming that Targetnum is a VARCHAR - if not cast it to varchar - e.g. LEN(CONVERT(varchar(20), Targetnum))

Kristen

gtjr92
Starting Member

15 Posts

Posted - 2006-10-30 : 11:52:52

"Create a calculated column, based on the INT date column,"

I am not exactly sure how to do that in SQL 2005 I went to the formula for a new column and added the code but it kept giving me an error and would not let me save it.
How can i do this in sql 2005
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 11:55:38

Remove the SELECT keyword and paste the formula with extra ( and ) both before and after.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 12:06:12

[code]alter TABLE [mytable] add
[dt] AS (dateadd(month,([localday] % 10000 / 100 - 1),dateadd(day,([localday] % 100 - 1),dateadd(year,([localday] / 10000 - 1900),0))))[/code]

Peter Larsson
Helsingborg, Sweden

gtjr92
Starting Member

15 Posts

Posted - 2006-10-30 : 12:11:09

I am sorry i am not sure what you mean exactly I tried this but i don't think it is really what you meant.

(and) declare @a int @a = localday dateadd(month, (@a % 10000) / 100 - 1, dateadd(day, @a % 100 - 1, dateadd(year, @a / 10000 - 1900,0))) (and)

gtjr92
Starting Member

15 Posts

Posted - 2006-10-30 : 12:14:28

never mind i got it with the alter table statement

THANKS!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 12:53:53

Great! Good work.
Now your first query can be simplified like this

SELECT		datepart(Month, [dt]) As [Month],
		CallerName,
		Sum(TalkDuration) / 60.0 As TalkTime,
		Sum(TalkDuration) / 60.0 /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.0 As AvgTalk 
FROM		CDRMAIN
WHERE		CallerName IN ('Name1', 'Name2', 'Name3', 'Name4')
		AND localday BETWEEN 20060101 and 20061231
GROUP BY	datepart(Month, [dt]),
		CallerName
order by	datepart(Month, [dt])

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 12:57:41

And your second query like this

select top 6	substring(Targetnum, 1, 3) As [AreaCdCnt]
from		cdrmain
where 		Direction = '2'
		and Targetnum like '[2-9]%'
		and Targetnum not like '800%'

Peter Larsson
Helsingborg, Sweden

gtjr92
Starting Member

15 Posts

Posted - 2006-10-30 : 13:17:58

yeah i knew that thanks.
How Can I get the Disctinct days from my field
I did this, but it brings back 520 rows which is a row for every date I just want it to evaluate each distinct date. I did this but i don't know how to get the distinct from here
select SUM(CASE WHEN datename(Weekday,gtdatecalc) = 'Saturday' THEN 0 ELSE 1 END) AS busdaycount
from cdrmain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 00:29:54

select SUM(CASE WHEN datename(Weekday, q.gtdatecalc) = 'Saturday' THEN 0 ELSE 1 END) AS busdaycount
from (select distinct gtdatecalc from cdrmain) q

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources