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.
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) SalesPerDayFROM SpaItenWHERE DATENAME(WEEKDAY, OrderDate) = 'Monday'AND YEAR(OrderDate) = 2013 -- this is for current year filterGROUP BY OrderDateb.)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 SpaItenGROUP BY CountryRegionCodec.) What catagories achieved top 20 % sales?SELECT TOP 20 PERCENT CategoryName ,SUM(ListPRICE) TotalSalesPerCatFROM SpanItenGROUP BY CategoryNameORDER 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) SalesPerDayFROM SpaItenWHERE DATENAME(WEEKDAY, OrderDate) = 'Monday'AND YEAR(OrderDate) = 2013 -- this is for current year filterGROUP BY OrderDateb.)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 SpaItenGROUP BY CountryRegionCodec.) What catagories achieved top 20 % sales?SELECT TOP 20 PERCENT CategoryName ,SUM(ListPRICE) TotalSalesPerCatFROM SpanItenGROUP BY CategoryNameORDER BY 2 DESC --Chandu
Wont work if language and regional settings are not English basedMuch safer option would beDATEDIFF(dd,0,OrderDate) % 7 = 0to make it server independenthttp://visakhm.blogspot.com/2012/08/creating-server-independent-day.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 basedMuch safer option would beDATEDIFF(dd,0,OrderDate) % 7 = 0to 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 / DeutschDoes 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.syslanguagesGives 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 / DeutschDoes 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.syslanguagesGives 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 thisnothing elseSELECT OrderDate, AVG(ListPRICE) SalesPerDayFROM SpaItenWHERE DATEDIFF(dd,0, OrderDate)%7=0AND YEAR(OrderDate) = 2013 -- this is for current year filterGROUP BY OrderDate ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 likeSELECT Datum, ROUND((AVG (VERKAUFSWERT * VERKAUFSMENGE * 1.0000)),2) UmsatzFROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN aJoin Data_Star_Awesome.dbo.DIMDATUM c on c.DATUMID=a.DATUMIDWHERE DATEDIFF(dd,0, DATUM)%7=0 AND JAHR = 2010GROUP 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 ROLLUPSELECT Datum, ROUND((AVG (VERKAUFSWERT * VERKAUFSMENGE * 1.0000)),2) UmsatzFROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN aJoin Data_Star_Awesome.dbo.DIMDATUM c on c.DATUMID=a.DATUMIDWHERE DATEDIFF(dd,0, DATUM)%7=0 AND JAHR = 2010GROUP BY DatumWITH 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|