SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 need help with an exercise sheet for uni
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Julian_HTW_student
Starting Member

Germany
5 Posts

Posted - 06/13/2013 :  09:08:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 06/13/2013 :  09:31:56  Show Profile  Reply with Quote
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

Edited by - bandi on 06/13/2013 09:37:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/14/2013 :  02:06:43  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/14/2013 :  08:34:35  Show Profile  Reply with Quote
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.

Edited by - James K on 06/14/2013 08:37:22
Go to Top of Page

Julian_HTW_student
Starting Member

Germany
5 Posts

Posted - 06/14/2013 :  08:48:31  Show Profile  Reply with Quote
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.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/14/2013 :  10:45:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/24/2013 :  06:44:49  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/24/2013 06:47:58
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000