May 28, 2014 at 10:14 am
Hi all
a database containing the following tables
card ,distributer,country,shop, offer and its category and type
in fact as you can see
the table card contani serial number, phone number, date of buying it, code offer and codeshop
in fact if i want to know the number of cards i should do a count of serial number
my purpose is to do reports of the number of cards by country by shop by offer by offer type by offer category
i forget to add the dimension date containing (keydate having this form (XX/YY/ZZZZ) day,month and year )
the date in table card has this form (XX/YY/ZZZZ)
and if is possible to show statistics n a geographic card for country
i hope that my question was clear
thanks a lot for any answer
how can i do that ?
the tables are related like this snapshot
May 28, 2014 at 10:30 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
Additionally I would recommend you use a naming convention that adds clarity to your system. You have tables using sql reserved words. Your column names are so vague they don't provide any indication of what they are. Simply naming a column date is going to be very painful. First of all, what date? Is that the date the row was created? Maybe the effective date? The expiration date? Then add to that you will be forced to wrap the definition in [] each and every time you use it. Make it easier on yourself and others who will come along after you. Name your objects and columns something that provides some clarity.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2014 at 10:54 am
Hi Sean Lange thanks for replying
in fact i am too pressed i should finish this part now 🙁
as i modified above it is the date of buying the card
it has this form XX/YY/ZZZZ
3. Expected results based on the sample data
evolution of cards buying by country , offer's category , offer's type , by shop, by distributer
May 28, 2014 at 11:46 am
prepaess (5/28/2014)
Hi Sean Lange thanks for replyingin fact i am too pressed i should finish this part now 🙁
as i modified above it is the date of buying the card
it has this form XX/YY/ZZZZ
3. Expected results based on the sample data
evolution of cards buying by country , offer's category , offer's type , by shop, by distributer
You are too busy to provide details for somebody who is offering to help you for free? I can't write sql against non existent tables.
About all I can offer is to look at some aggregation. BTW, there is no "e" in Distributor. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2014 at 3:02 pm
you are right sorry but i have to send this work to the professor efter 5hours and i did nothing yet 🙁 this is why i am pressed :'(
my question is how can i get statistics from these tables using sqlserver reporting
for example i want to give the evolution of sales by offer type , category
give for example the percentage of sales by country using geographic card
study the evolution of sales over time. are sales increasing day by day or not. compare sales to the sales goal ..
i rectified document and attach data samples
i hope it is more clear now
please help me friends i am really in a trouble 🙁 thnx
May 28, 2014 at 3:16 pm
prepaess (5/28/2014)
you are right sorry but i have to send this work to the professor efter 5hours and i did nothing yet 🙁 this is why i am pressed :'(my question is how can i get statistics from these tables using sqlserver reporting
for example i want to give the evolution of sales by offer type , category
give for example the percentage of sales by country using geographic card
study the evolution of sales over time. are sales increasing day by day or not. compare sales to the sales goal ..
i rectified document and attach data samples
i hope it is more clear now
please help me friends i am really in a trouble 🙁 thnx
We really like helping people learn around here. However, most of us frown on doing homework. I don't need to do this, I don't need the practice. If I provide you the queries you will learn nothing and then you do yourself a disservice by not learning this. The queries here are fundamental to progressing down the path of extracting data from a database. This stuff all builds on the previous lessons so if you don't understand this you will struggle even harder on the next lesson.
Trust me when I say that your lack of planning of ahead does not constitute an emergency on my part. I would strongly advise you pay attention in class and start earlier on your homework.
I am not even sure you know what you want here. You posted this in the Data Warehousing section but you are talking about using reporting and the real issue here is not the report but the query that feeds it.
As I said previously, look at doing some aggregation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2014 at 4:10 pm
what pushs me to post here is that i used the integration service of sql , in fact, i extract the date from table card and applied functions day([date]), month([date]) and YEAR([date]) using derived column then i put these variables in other table named dateact having datekey as primary key like that (XX/YY/ZZ) which is equal to the variable date in table cards and column day, column month and coulumn year
i thought to do a sort of cube with a fact table and dimensions then have the reporting and analysis swich the axis but i did not know how in fact i don't have numbers in my tables so i should do requests , i don't know if what i said is logic or not but i tried 🙁 ?
in fact i want that the reports that i will have to be dynamic means when the data base changes statistics and graphics change too
i am not lazy but i found things difficult
can you please make things more clear for me by giving examples and saying if what i said is logic or not
thanks a lot for any answer
May 28, 2014 at 5:50 pm
in fact i want statistics like this
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply