Kinda fancy reports with multiple counts and multiple group by
-
Ok, I don't have an easy way to provide some sample data for this, but hopefully this is a simple conceptual question for someone who's good with doing summary reports in SQL. Let me try to explain in a simplified version. Also attached is a wireframe of how I hope the final output will look.
Let's say there are 3 tables: Providers, Locations, and Customers.
The report I need is for a 4 day event. There are several providers, each that have a kiosk setup at one or multiple location. So provider1 might have only 1 location, provider2 might have 3 location, and privider3 also has 3 locations.
I need to get the following:
- Cumulative totals
- Cumulative totals per provider
- How many customers bought something per provider per day of the event
- How many customers bought something per location (total per location)
- How many customers bought something per location per day (day totals)
And then it gets even more interesting cause I need a count of people who provided their email address out of those who purchased something for each category.
Can someone please give me a general example for how to do this? The only way I can think off is running the big query in SQL to get all the data, and then looping over that query in my code to get all the subtotals. There must be a way to do it in SQL though.
-
using common table expressions you can get the aggergated data but with out the DDL and sample data code is hard to do. there are also cross tabs that may be useful but again i cant see your data. the best thing to do is post some sample data you have sanitized for public consumption and we can go from there.
for help formating the DDL and sample data the link in my signature will give you all the information you need
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Thanks for the reply, the actual database table structure is much more complex than what I need as an example.
Let's say there are 4 tables
Partners:
-ID
-Name
Locations:
-ID
-Name
-Partner_id (FK)
Customers:
-ID
-Name
-email
LocationScans:
-Location_ID (FK)
-Customer_ID (FK)
-date_entered
Here is a wire-frame of what I'd like the output to look like:
-
i count 3 distinct queries with changes on the where clauses and SSRS to make it all nice and pretty.
one for the partner level (changing the date range for your daily's) a slight change to it for the locations (where partner = x and add location instead of partner to your select) and a wholly different query for the unique scans of the different partner combinations.
once you have the queries using reporting services to make it nice and pretty and generate the graphs. you could maby make due with access or some other front end but it may be a little more complicated.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Humm, I wish I could do it in 3 queries. So far I have 8 unique ones and then 4 more that are repeated in loops. The outputs are correct, and at least for now it's fast enough, but I wish I could simplify it. I did make a view that all the queries hit, so they are pretty short. But I have to run one to get all partners, get overall partner counts, overall partner totals, unique event days, partner daily counts, partner location counts and partner location totals.
And I have no idea how to go about building the query for Unique/Repeat Scans at all.
-
pixelwiz (4/3/2012)
Humm, I wish I could do it in 3 queries. So far I have 8 unique ones and then 4 more that are repeated in loops. The outputs are correct, and at least for now it's fast enough, but I wish I could simplify it. I did make a view that all the queries hit, so they are pretty short. But I have to run one to get all partners, get overall partner counts, overall partner totals, unique event days, partner daily counts, partner location counts and partner location totals.And I have no idea how to go about building the query for Unique/Repeat Scans at all.
im counting 6 different queries (maby more for the graphs). what i meant by 3 queries is one for the overall and all you add is a date time range for the day you want as one. the partner locations are 1 query with changes in the where clause for the different partners (the data is the same you are pulling) as 2. and the third is the unique/repeat scans. you should not need loops. if you post the DDL for the tables and sample data i can get you close to what you need for the queries. but with out seeing what you are trying to aggregate there is not much more help i can give. as far as making it into an ssrs report (or what every reporting software you are using) i wont be of much help but the raw sql we can get knocked out.
in the words of Jerry Maguire, "Help me help you."
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply