Northwind Related 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

SQL Server 2008 Forums

Transact-SQL (2008)

Northwind Related Question

Author

Topic

rutvij1984
Starting Member

7 Posts

Posted - 2010-08-11 : 20:33:52

Use Northwind Database.By using tables Orders, Order Details.
COMPARE THE SALES OF 1997 AND 1998.
DISPLAY RESULT FOR SALES OF EACH MONTH IN 1997 AND 1998 & ALSO FIND THE CHANGES IN REVENUE IN EACH MONTH FROM 1997 TO 1998.

rnj

Sachin.Nand

2937 Posts

Posted - 2010-08-12 : 00:05:00

I dont have northwind database installed so maybe a bit of sample data would help.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

PavanKK
Starting Member

32 Posts

Posted - 2010-08-12 : 04:28:44

Hope this helps

---------

SELECT Month,[1997],[1998],[1998]-[1997] AS RevenueChange FROM
(
SELECT DATEPART(M,O.OrderDate) AS IntMonth,DATENAME(M,O.OrderDate) As [Month]
,SUM(CASE WHEN YEAR(O.OrderDate) = 1997 THEN OD.Quantity*OD.UnitPrice ELSE 0 END) AS [1997]
,SUM(CASE WHEN YEAR(O.OrderDate) = 1998 THEN OD.Quantity*OD.UnitPrice ELSE 0 END) AS [1998]
FROM Orders O
JOIN [Order Details] OD ON O.OrderID = OD.OrderID
GROUP BY DATENAME(M,O.OrderDate),DATEPART(M,O.OrderDate)
) S
ORDER BY IntMonth

---------

KK

s2002
Starting Member

49 Posts

Posted - 2010-08-12 : 07:35:36

Hello PavanKK,
I wanted to ask which part in your query calculate Revenue changes?
Suppose we only like to have Orders count for each year while changes in orders count. then why I could not below:

count(CASE WHEN YEAR(O.OrderDate) = 1997 THEN O.OrderID ELSE 0 END) AS [1997]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources