Duplicate results - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Duplicate results

Author

Topic

confuzed04
Starting Member

39 Posts

Posted - 2007-06-13 : 17:36:23

I have the following query. I want it to find how many minutes used by MDN (which is the column for the phone numbers in my database). It keeps giving me duplicate MDN's. How can I get it to give me the total minutes used by an MDN per day?

SELECT DISTINCT MDN,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE Durationseconds >0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group By MDN
UNION
SELECT DISTINT MDN,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE Durationseconds > 0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group By MDN
order by mdn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 21:40:27

did your query in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84979 did that ?

confuzed04
Starting Member

39 Posts

Posted - 2007-06-14 : 09:23:52

?? I did do it the way you told me to in that thread and it put it in date order, but the MDN is still duplicating. I know that there will be more then one occurrance of the MDN because multiple calls were made by each, but I am wanting the query to total these together.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 09:30:13

[code]
select MDN, CallDate, sum(Mintues) as Mintues
from
(
select MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate, sum( . . . ) as Minutes
from . . .
group by MDN, dateadd(day, datediff(day, 0, CallDate), 0)

union all

select MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate, sum( . . . ) as Minutes
from . . .
group by MDN, dateadd(day, datediff(day, 0, CallDate), 0)
) d
group by MDN, CallDate
[/code]

confuzed04
Starting Member

39 Posts

Posted - 2007-06-14 : 10:51:00

I set it up like this, plugging in my info, and it didn't work. Please advise. Thanks.

select MDN, CallDate, sum(Mintues) as Mintues
from
(
select MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
from VoiceCallDetailRecord
group by MDN, dateadd(day, datediff(day, 0, CallDate), 0)

union all

select MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
from ZeroChargeVCDRecord
group by MDN, dateadd(day, datediff(day, 0, CallDate), 0)
)
group by MDN, CallDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 10:56:42

Can you post same of the result from the last query and also what you wanted ?

confuzed04
Starting Member

39 Posts

Posted - 2007-06-14 : 11:21:14

Here is the query and some of the results. As you can see, it is givining me duplicate MDN's, and I want it to combine them so that I get one lump sum report per distinct MDN.
MDN Calldate Minutes
2012088143 2007-02-19 00:00:00.000 1
2012088143 2007-02-19 00:00:00.000 2
2012088185 2007-02-19 00:00:00.000 2
2012088185 2007-02-19 00:00:00.000 8
2012088185 2007-02-19 00:00:00.000 117
2012088185 2007-02-19 00:00:00.000 1
2012088246 2007-02-19 00:00:00.000 1
2012088246 2007-02-19 00:00:00.000 2
2012088320 2007-02-19 00:00:00.000 18
2012088320 2007-02-19 00:00:00.000 3
2012088320 2007-02-19 00:00:00.000 1
2012088320 2007-02-19 00:00:00.000 5

Query
SELECT DISTINCT mdn,dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE Durationseconds >0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
Group By MDN,dateadd(day, datediff(day, 0, CallDate), 0),calldate
UNION
SELECT DISTINCT mdn,dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE Durationseconds > 0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
Group By MDN,dateadd(day, datediff(day, 0, CallDate), 0),calldate
order by mdn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 11:24:19

Why do you want to group by calldate again ?


SELECT DISTINCT mdn,dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE Durationseconds >0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
Group By MDN,dateadd(day, datediff(day, 0, CallDate), 0), ,calldate
UNION
SELECT DISTINCT mdn,dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE Durationseconds > 0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
Group By MDN,dateadd(day, datediff(day, 0, CallDate), 0) ,calldate
order by mdn

Follow what i posted on 06/14/2007 : 09:30:13

confuzed04
Starting Member

39 Posts

Posted - 2007-06-14 : 11:52:58

I want it to group by calldate because the date span is supposed to be from 02/19/2007-03/19/2007. I need to see total minutes per MDN per day

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 12:09:29

as your calldate contain date and time, you cannot group by calldate
you have to remove the time component first. Which you have done it here.

Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources