Simple Query Question - 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

Simple Query Question

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-08-01 : 16:54:07

Hi All !!! I have the following Sales Table

[CODE]
ORDER_ID; REP; DATE_OF_SALE

001 jsmith 01/01/2013
002 jsmith 02/03/2013
003 jsmith 07/01/2013
004 jsmith 08/01/2013

005 rsimon 01/01/2013
006 rsimon 02/01/2013
007 rsimon 03/01/2013
008 rsimon 04/01/2013

[/CODE]
I need to have sales quantity table that outputs their Year-To-Date sales IF they have completed at least one sale in July. For example, the output for the above table would be as follows:

[CODE]
REP SALES_QTY
jsmith 4
[/CODE]
I'm not quite sure how to do this, Any ideas? Thanks!

[CODE]
SELECT REP, COUNT(*) FROM SALES T1
GROUP BY REP
[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-01 : 17:20:58

I think you need a WHERE clause also to eliminate rsimon, like shown below

SELECT REP, COUNT(*) FROM SALES T1
WHERE EXISTS 
(
	SELECT * FROM SALES T2
	WHERE T2.REP=T1.REP
	AND T2.DATE_OF_SALE >= '20130801'
	AND T2.DATE_OF_SALE < '20130901'
)
GROUP BY REP;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-01 : 18:09:03

[code]SELECT REP,
COUNT(*)
FROM dbo.Sales
WHERE Date_Of_Sale <= GETDATE() -- Make sure Year-To-Date is preserved
GROUP BY REP
HAVING MAX(CASE WHEN Date_Of_Sale >= '20130701' AND Date_Of_Sale < '20130801' THEN 1 ELSE 0 END) -- Check for sale in July[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-01 : 20:09:02

quote:

Originally posted by SwePeso

SELECT		REP,
		COUNT(*)
FROM		dbo.Sales
WHERE		Date_Of_Sale <= GETDATE()	-- Make sure Year-To-Date is preserved
GROUP BY	REP
HAVING		MAX(CASE WHEN Date_Of_Sale >= '20130701' AND Date_Of_Sale < '20130801' THEN 1 ELSE 0 END) >0	-- Check for sale in July

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

See in red.

WAmin
Starting Member

16 Posts

Posted - 2013-08-01 : 21:21:37

Here is sample data

REP SaleDate SaleQuantity
--------------- ----------------------- ------------
jsmith 2013-01-01 00:00:00.000 1
jsmith 2013-02-03 00:00:00.000 2
jsmith 2013-07-01 00:00:00.000 3
jsmith 2013-08-01 00:00:00.000 4
rsimon 2013-01-01 00:00:00.000 5
rsimon 2013-02-01 00:00:00.000 6
rsimon 2013-03-01 00:00:00.000 7
rsimon 2013-04-01 00:00:00.000 8

(8 row(s) affected)

Here is my version

SELECT
SUM(SaleQuantity),REP FROM Sales
WHERE
SaleDate>=CONVERT(DATETIME,'07/01/' + CONVERT(VARCHAR,DATEPART(yyyy,GETDATE())))
AND
SaleDate<CONVERT(DATETIME,'08/01/' + CONVERT(VARCHAR,DATEPART(yyyy,GETDATE())))
GROUP BY
REP

Here is result

TotalSales REP
----------- --------
3 jsmith

(1 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 01:34:43

[code]
SELECT REP, COUNT(*) FROM SALES T1
WHERE DATE_OF_SALE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
GROUP BY REP
HAVING SUM(CASE WHEN DATE_OF_SALE > = DATEADD(mm,6,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AND DATE_OF_SALE < DATEADD(mm,7,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) THEN 1 ELSE 0 END) >0
[/code]

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

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-08-02 : 08:52:47

Thanks All! I used James K suggestion of using the EXISTS function. This seemed to be the most simple.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-02 : 09:44:24

Swepeso's suggestion would be faster; if performance becomes an issue, consider using his approach.

xiaomingzc
Starting Member

3 Posts

Posted - 2013-08-03 : 03:17:01

unspammed