SQL over counting month on month

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

Transact-SQL (2008)

SQL over counting month on month

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-11-14 : 21:00:42

So I have the strangest issue I need someone to help me with cause I am going crazy lookin at it.

I have a query that looks like this:

Select
site.*,
Network.Network_count
from
(
Select Year(comm_date_utc) as Year, Month(comm_date_utc) as Month,COUNT(comm_id)as Correspondences_count, location from Correspondences
left join Agents a on owner_agent_login = agent_login_id
where
location in ('AUS', 'USA', 'SA', 'IRE')
and comm_date_utc between '2012/11/01 12:47:00' and '2013/10/31 23:59:00'
and start_date < DATEADD(day,DATEDIFF(day,0,'2013/10/31 23:59:00')-90,0)

group by Year(comm_date_utc), Month(comm_date_utc),location
) Site
Left outer join
(
Select Year(comm_date_utc) as Year, Month(comm_date_utc) as Month,COUNT(comm_id)as Network_count from Correspondences
left join Agents a on owner_agent_login = agent_login_id
where comm_date_utc between '2012/11/01 12:47:00' and '2013/10/31 23:59:00'
and start_date < DATEADD(day,DATEDIFF(day,0,'2013/10/31 23:59:00')-90,0)
group by Year(comm_date_utc), Month(comm_date_utc)
) Network
on site.Year = network.Year
and site.month = network.Month
group by
site.Year,site.Month, Site.Correspondences_count, site.location, network.Network_count
order by year asc, Month asc

rather than giving you all values let me share the part that is important (Cant share the data public forum and all):

So for the the NETWORK_COUNT for the the 8th, 9th and 10th month I am getting as follows:
8th = 19715
9th = 18106
10th = 18542

But If I go and run the same query BUT change the dates as follows:
Select
site.*,
Network.Network_count
from
(
Select Year(comm_date_utc) as Year, Month(comm_date_utc) as Month,COUNT(comm_id)as Correspondences_count, location from Correspondences
left join Agents a on owner_agent_login = agent_login_id
where
location in ('AUS', 'USA', 'SA', 'IRE')
and comm_date_utc between '2012/11/01 12:47:00' and '2013/11/30 23:59:00'
and start_date < DATEADD(day,DATEDIFF(day,0,'2013/11/30 23:59:00')-90,0)

group by Year(comm_date_utc), Month(comm_date_utc),location
) Site
Left outer join
(
Select Year(comm_date_utc) as Year, Month(comm_date_utc) as Month,COUNT(comm_id)as Network_count from Correspondences
left join Agents a on owner_agent_login = agent_login_id
where comm_date_utc between '2012/11/01 12:47:00' and '2013/11/3023:59:00'
and start_date < DATEADD(day,DATEDIFF(day,0,'2013/11/30 23:59:00')-90,0)
group by Year(comm_date_utc), Month(comm_date_utc)
) Network
on site.Year = network.Year
and site.month = network.Month
group by
site.Year,site.Month, Site.Correspondences_count, site.location, network.Network_count
order by year asc, Month asc

then I am getting these results!

8th = 19771
9th = 18686
10th = 20261

You can see the number is accumulating, and it should not be at all. Why on earth would this happen?

2revup
Posting Yak Master

112 Posts

Posted - 2013-11-16 : 16:54:59

Anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-17 : 12:00:55

Your question is not clear. Can you post some sample data and then explain out of that your exact issue

see how to post a question in proper format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

2revup
Posting Yak Master

112 Posts

Posted - 2013-11-17 : 21:20:33

I have worked the issue out and I am not sure how to correct it, giving you sample data would take quite a few hours let me try and explain this a little.

I will break the query down by a smaller one. So IF I run this:
Select Year(comm_date_utc) as Year, Month(comm_date_utc) as Month, owner_agent_login, COUNT(comm_id)as Correspondences_count, location from Correspondences
left join Agents a on owner_agent_login = agent_login_id
where
location in ('AUS', 'USA', 'SA', 'IRE')
and comm_date_utc between '2012/11/01 12:47:00' and '2013/9/30 23:59:00'
and start_date < DATEADD(day,DATEDIFF(day,0,'2013/9/30 23:59:00')-90,0)
group by Year(comm_date_utc), Month(comm_date_utc),location, owner_agent_login

Its giving me results showing me each agent and how many items they have done broken down into months.

IF I change the end date which is these 2 items:
and '2013/9/30 23:59:00'
and start_date < DATEADD(day,DATEDIFF(day,0,'2013/9/30 23:59:00')-90,0)

What is happening is that because the end date is NOT showing any results for an employee that has started within the last 90 days, which works well. BUT when someone has been on for more than 90 days it then show all of the employees results, what I need this to actully do is say on 90 days show ONLY the last month, not the last 3 months. So to best articulate this from data here is what it might look like:

If I ran the query and the user login3 start date has been longer than 90 days it will show ALL of his results like so.
Year Month owner_agent_login Correspondences_count location
2013 1 Login1 180 IRE
2013 2 Login1 209 IRE
2013 3 Login1 208 IRE
2013 4 Login1 189 IRE
2013 5 Login1 191 IRE
2013 1 Login2 140 USA
2013 2 Login2 103 USA
2013 3 Login2 137 USA
2013 4 Login2 156 USA
2013 5 Login2 183 USA
2013 3 Login3 30 USA
2013 4 Login3 76 AUS
2013 5 Login3 98 AUS

What I need it to do is show only the results after the 90 days, so the other results would be NOT counted. it would look like this:

Year Month owner_agent_login Correspondences_count location
2013 1 Login1 180 IRE
2013 2 Login1 209 IRE
2013 3 Login1 208 IRE
2013 4 Login1 189 IRE
2013 5 Login1 191 IRE
2013 1 Login2 140 USA
2013 2 Login2 103 USA
2013 3 Login2 137 USA
2013 4 Login2 156 USA
2013 5 Login2 183 USA
2013 5 Login3 12 AUS

Does this make more sense?

2revup
Posting Yak Master

112 Posts

Posted - 2013-11-18 : 02:12:53

Turns out this was an easy correction, I just needed stop over complicating it. just change this:

AND start_date < DATEADD(day, DATEDIFF(day, 0, '2013/9/30 23:59:00') - 90, 0)

to:

and comm_date_utc between DATEADD(MONTH,3,start_date) and '2013/10/31 23:59:00'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 05:55:33

quote:
Originally posted by 2revup

Turns out this was an easy correction, I just needed stop over complicating it. just change this:

AND start_date < DATEADD(day, DATEDIFF(day, 0, '2013/9/30 23:59:00') - 90, 0)

to:

and comm_date_utc between DATEADD(MONTH,3,start_date) and '2013/10/31 23:59:00'

dont hardcode time like this. This has a chance of losing out some records if created between 23:59 and 00:00

why not use >= and < instead

see
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources