All sales reported after quarter end

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

All sales reported after quarter end

Author

Topic

a2l2en
Starting Member

11 Posts

Posted - 2007-04-19 : 16:25:19

It's been a long time since I've built tough queries and need some help.

I need to report all sales that happened within a quarter but were reported after the quarter end.

Quarters

Q1 - 1/1 thru 3/31
Q2 - 4/1 thru 6/30
Q3 - 7/1 thru 9/30
Q4 - 10/1 thru 12/31

Sample Data

ticketid salespersonid saledate sale_entereddate
1234 bsmith 1/1/2006 2/1/2006
1235 jgarcia 3/31/2006 4/1/2006
1236 bsmith 1/1/2006 2/3/2006
1237 jdoe 6/23/2006 7/1/2006
1238 bsmith 8/5/2006 8/6/2006
1239 bsmith 10/1/2006 1/1/2007

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-19 : 16:57:20

select *
from yourtable
where datepart(q, saledate) != datepart(q, sale_enteredDate)

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-19 : 19:11:41

quote:
Originally posted by jsmith8858

select *
from yourtable
where datepart(q, saledate) != datepart(q, sale_enteredDate)

- Jeff
http://weblogs.sqlteam.com/JeffS

That would leave out sales where the saledate and sale_enteredDate were in the same quarter of different years.


select
	*
from
	SaleTable
where
	-- First day of Quarter where sale was made
	dateadd(qq,datediff(qq,0,saledate),0) 	 	<>
	-- First day of Quarter where sale was entered
	dateadd(qq,datediff(qq,0,sale_entereddate),0)

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-19 : 20:49:07

good catch! might not ever be a factor in the result, but it is definitely more correct.

- Jeff
http://weblogs.sqlteam.com/JeffS

a2l2en
Starting Member

11 Posts

Posted - 2007-04-20 : 10:25:58

This worked like a charm! On my own, it would have taken me forever to figure this out. Thanks so much guys!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources