Exercise: Create aggregate shopping statistics depending on store type

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the course listed below!

Category ==> SSIS Integration Services  (40 exercises)
Topic ==> Conditional split transforms  (5 exercises)
Level ==> Harder than average
Course ==> Introduction to SSIS
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

In Management Studio, open and execute the SQL script in the above folder to generate an empty table called WeirdStats.  Your mission is to fill this from the Excel workbook of shopping purchases to get this:

Statistics on shopping

For posh shops (Marks and Spencer and Waitrose), show the single most expensive purchase; for cheap shops (Aldi) show the single cheapest purchase; otherwise show the average purchase value.

 

The table should also show which statistic you've chosen to display, as well as the number of purchases made in that store.

If you're feeling confident, try doing this without reading on to see a suggested flow diagram.

Here's one possible way to solve this:

A possible answer

One possible answer (but not necessarily the best one!).

When you've got this working, close down the package. 

This page has 0 threads Add post