Side-by-Side Dates - 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

Side-by-Side Dates

Author

Topic

CP
Starting Member

9 Posts

Posted - 2012-02-20 : 16:39:28

I am using Microsoft SQL Server Management Studio.

I need to have to date columns side-by-side comparing two dates for ex.:

Customer Number | 2011 Sales | 2012 Sales

I am using a query similar to this:

SELECT DISTINCT
CustomerNumber AS [Customer Number],
SalesAmount AS [2011 Sales],
SalesAmount AS [2012 Sales]
FROM CustomerNumberTable INNER JOIN InvoiceNumberTable ON CustomerNumberTable.CustomerNumber = CustomerNumberTable.CustomerID
WHERE (SalesAmount IN
(SELECT SalesAmount
FROM InvoiceNumberTable
WHERE (DateCreated BETWEEN '2011-01-01' AND '2011-12-31'))) AND
(SalesAmount IN
(SELECT SalesAmount
FROM InvoiceNumberTable
WHERE (DateCreated >= '2012-01-01')))

The query runs without errors, the 2011 Sales column gives the right data, but the 2012 Sales seems to just repeat the 2011 Sales data. Anyone know the most correct way to get the information?

Thank you for all your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 16:44:43

[code]
SELECT
CustomerNumber AS [Customer Number],
SUM(CASE WHEN YEAR(DateCreated)=2011 THEN SalesAmount END) AS [2011 Sales],
SUM(CASE WHEN YEAR(DateCreated)=2012 THEN SalesAmount END) AS [2012 Sales]
FROM CustomerNumberTable
INNER JOIN InvoiceNumberTable ON CustomerNumberTable.CustomerNumber = CustomerNumberTable.CustomerID
WHERE DateCreated >='2011-01-01'
AND DateCreated < '2013-01-01'
GROUP BY CustomerNumber
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-20 : 16:45:47

The where clause has an AND condition - which means, the more restrictive wins. You can do what you want to do like this:

SELECT
	CustomerNumber AS [Customer Number],
	SUM(CASE WHEN DateCreated >= '20110101' AND DateCreated < '20120101' THEN SalesAmount END)
		AS [2011 Sales],
	SUM(CASE WHEN DateCreated >= '20120101' AND DateCreated < '20130101' THEN SalesAmount END)
		AS [2012 Sales]
FROM
	CustomerNumberTable
       INNER JOIN InvoiceNumberTable
            ON  CustomerNumberTable.CustomerNumber = CustomerNumberTable.CustomerID;

You may not even need to join on CustomerNumberTable unless one of the columns is in that table - which it doesn't seem like.

On the other hand, if you want a row for customers who did not have any purchases at all, then use a LEFT join instead of INNER join in the above query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 16:55:08

one more thing is if you want columns to be generated dynamically based on years present use below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CP
Starting Member

9 Posts

Posted - 2012-02-20 : 16:56:38

This worked wonderfully!!

One more question, if I need to compare the current month with the same month from the previous year in the same query is this possible without changing the query monthly?

For example, if I want to display Feb 2011 data and Feb 2012 data, but not change the query for next month, is there a way to use the GetDate function for this?

Thank you again for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 17:02:52

[code]
SELECT
CustomerNumber AS [Customer Number], ,DATEPART(mm,DateCreated),
SUM(CASE WHEN YEAR(DateCreated)=2011 THEN SalesAmount END) AS [2011 Sales],
SUM(CASE WHEN YEAR(DateCreated)=2012 THEN SalesAmount END) AS [2012 Sales]
FROM CustomerNumberTable
INNER JOIN InvoiceNumberTable ON CustomerNumberTable.CustomerNumber = CustomerNumberTable.CustomerID
WHERE DateCreated >='2011-01-01'
AND DateCreated < '2013-01-01'
GROUP BY CustomerNumber,DATEPART(mm,DateCreated)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources