SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL over counting month on month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

112 Posts

Posted - 11/14/2013 :  21:00:42  Show Profile  Reply with Quote
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 - 11/16/2013 :  16:54:59  Show Profile  Reply with Quote
Anyone?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/17/2013 :  12:00:55  Show Profile  Reply with Quote
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
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 11/17/2013 :  21:20:33  Show Profile  Reply with Quote
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?





Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 11/18/2013 :  02:12:53  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/18/2013 :  05:55:33  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000