need help with an exercise sheet for uni

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)

need help with an exercise sheet for uni

Author

Topic

Julian_HTW_student
Starting Member

5 Posts

Posted - 2013-06-13 : 09:08:59

Hey guys.

Within a lecture about data warehouse I need help with an exercise sheet. So there is a database and we had to build a star scheme with the provided data.

Well and therefore I could use some advice building the SQL statements.

Unfortunately our star scheme doesn't work perfectly and that means there is no data in it. That again means, I can't just try out. I won't see any results. But anyway, I can and have to write the SQL statements.

This is what I have to do:

a.)Calculate the average sales per day, but only show mondays of the year. And there needs to be a row showing the average sales per day.

This screenshot shows the solution:

b.)Calculate the amount of orders per region of the last two quaters in 2008. Also show regions that only got delivered in one quater.

This is supposed to look like this:

c.) What catagories achieved top 20 % sales?

Shown like this:

Well in our first step, we created a view out of our 3 dimensions and the facts table. this is what this looks like:

I'd be happy for any given advice since I do struggle with SQL.
Thanks.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 09:31:56

[code]a.)Calculate the average sales per day, but only show mondays of the year. And there needs to be a row showing the average sales per day.
SELECT OrderDate, AVG(ListPRICE) SalesPerDay
FROM SpaIten
WHERE DATENAME(WEEKDAY, OrderDate) = 'Monday'
AND YEAR(OrderDate) = 2013 -- this is for current year filter
GROUP BY OrderDate

b.)Calculate the amount of orders per region of the last two quaters in 2008. Also show regions that only got delivered in one quater.
SELECT CountryRegionCode
,SUM(CASE WHEN DATEPART(QQ, OrderDate) = 3 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q3Average
SUM(CASE WHEN DATEPART(QQ, OrderDate) = 4 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q4Average
FROM SpaIten
GROUP BY CountryRegionCode

c.) What catagories achieved top 20 % sales?
SELECT TOP 20 PERCENT
CategoryName
,SUM(ListPRICE) TotalSalesPerCat
FROM SpanIten
GROUP BY CategoryName
ORDER BY 2 DESC
[/code]
--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 02:06:43

quote:

Originally posted by bandi

a.)Calculate the average sales per day, but only show mondays of the year. And there needs to be a row showing the average sales per day.
SELECT OrderDate, AVG(ListPRICE) SalesPerDay
FROM SpaIten
WHERE DATENAME(WEEKDAY, OrderDate) = 'Monday'
AND YEAR(OrderDate) = 2013  -- this is for current year filter
GROUP BY OrderDate

b.)Calculate the amount of orders per region of the last two quaters in 2008. Also show regions that only got delivered in one quater.
SELECT CountryRegionCode
       ,SUM(CASE WHEN DATEPART(QQ, OrderDate) = 3 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q3Average
       SUM(CASE WHEN DATEPART(QQ, OrderDate) = 4 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q4Average 
FROM SpaIten
GROUP BY CountryRegionCode

c.) What catagories achieved top 20 % sales?
SELECT TOP 20 PERCENT
   CategoryName
   ,SUM(ListPRICE) TotalSalesPerCat
FROM SpanIten
GROUP BY CategoryName
ORDER BY 2 DESC

--
Chandu

Wont work if language and regional settings are not English based

Much safer option would be

DATEDIFF(dd,0,OrderDate) % 7 = 0

to make it server independent

http://visakhm.blogspot.com/2012/08/creating-server-independent-day.html

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-14 : 08:34:35

quote:
Originally posted by Julian_HTW_student

quote:
Wont work if language and regional settings are not English based

Much safer option would be

DATEDIFF(dd,0,OrderDate) % 7 = 0

to make it server independent

What does that last line do? What do you mean by an independent server? I am logged on to a university database where I have to use a VPN connection if I'm not logged on to eduroam.

Well I'm using SQL Server 2008 Enterprise R2 Management Studio in english. Aren't SQL queries in english anyway?

Keywords used in T-SQL syntax being from the English language is unrelated to the language setting on your server. You can find what the language setting on your server is by running this query from an SSMS window:

SELECT @@language

Each language has its own settings, for names of weekdays, names of months etc. You can find those using this query:

SELECT * FROM sys.syslanguages

So, if your language setting is Deutsch, then you would need to look for Samstag and Sonntag rather than Saturday and Sunday.

If you use the DATEDIFF function, it will work independently of the lanaguage setting.

DATEDIFF(dd,0,OrderDate) % 7 is equivalent to DATEDIFF(dd,'19000101,OrderDate) % 7. That is, it is finding the number of days that elapsed between January 1, 1900 and OrderDate and then computing modulus of 7. January 1, 1900 happened to be a Monday, so if order date is on a Monday, the modulus would be 0, for Tuesday it will be 1 and so on.

Julian_HTW_student
Starting Member

5 Posts

Posted - 2013-06-14 : 08:48:31

SELECT @@language

"No collumn name"
row 1 / Deutsch

Does that mean the university server is set to German which doesn't affect me using the english version of SQL Server 2008 ?

SELECT * FROM sys.syslanguages

Gives me an overview over all possible languages do I get that right?

I think I do get DATEDIFF, but it seams a bit to complicated to me so I'd probably not use it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 10:45:35

quote:
Originally posted by Julian_HTW_student

SELECT @@language

"No collumn name"
row 1 / Deutsch

Does that mean the university server is set to German which doesn't affect me using the english version of SQL Server 2008 ?

SELECT * FROM sys.syslanguages

Gives me an overview over all possible languages do I get that right?

I think I do get DATEDIFF, but it seams a bit to complicated to me so I'd probably not use it.

The suggestion was just to use the first query like this

nothing else


SELECT OrderDate, AVG(ListPRICE) SalesPerDay
FROM SpaIten
WHERE DATEDIFF(dd,0, OrderDate)%7=0
AND YEAR(OrderDate) = 2013  -- this is for current year filter
GROUP BY OrderDate

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 06:44:49

I want my Sales shown with only 2 numbers after the comma, but it gives me these 4 zeros. How do I change that?

Try like


SELECT Datum, ROUND((AVG (VERKAUFSWERT * VERKAUFSMENGE * 1.0000)),2) Umsatz
FROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN a
Join Data_Star_Awesome.dbo.DIMDATUM c on c.DATUMID=a.DATUMID
WHERE DATEDIFF(dd,0, DATUM)%7=0 AND JAHR = 2010
GROUP BY Datum

Problem 2: I need a last row that gives ma the avg sales in total per monday, not on each monday.
Add WITH CUBE or WITH ROLLUP


SELECT Datum, ROUND((AVG (VERKAUFSWERT * VERKAUFSMENGE * 1.0000)),2) Umsatz
FROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN a
Join Data_Star_Awesome.dbo.DIMDATUM c on c.DATUMID=a.DATUMID
WHERE DATEDIFF(dd,0, DATUM)%7=0 AND JAHR = 2010
GROUP BY Datum
WITH ROLLUP

Here it's the same as it is in query 1, I want my salesnumber rounded on 2 after the comma, it does work with the CAST-Function, but I'm not to sure what that function actually does... so my question is, is that right?
its right as it casts value to explicit decimal type
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources