big project /need help - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

big project /need help

Author

Topic

magdaf75
Starting Member

5 Posts

Posted - 2008-04-16 : 18:51:12

A Southwest Booksellers has stores in three cities:
Albuquerque {Winrock, OldTown, NobHill}
Santa Fe {VistaView, MesaVerde}
Taos {Zia, Zuni}
and sells nine categories of books:
{Computers,Science,ArtHealth,SelfHelp,Business,Fiction,History,Religion}.

The bookstores will keep a database of transaction records for one year.
A sales transaction record contains fields for city id, store id,
book category id, date, and quantity of category sold.
They would like to have a database of aggregate historical data from which
to generate the following reports:

1) Show city sales by quarter for a given year.
2) Show store sales by quarter for a given year.
3) Show Book Category sales by quarter for a given year.
4) Show city sales by year.
5) Show store sales by year.
6) Show Book Category sales by year.
7) Show sales by Book Category and quarter for a given store and year.

Your solution should simulate annual processing and
contain the following elements:

1) Script to generate transaction records.
2) Star Schema database of aggregate historical data.
3) ETL algorithm.
4) Reports implemented as Stored Procedures.

Demonstrate your solution using the following data:

Year Transactions
---- ------------ --------------------------------
2000 1000
2001 1500 TheCaverns store in Carlsbad
2002 2100
2003 2800 Philosophy category
2004 3600 CasaDelRio store in SantaFe
2005 4500 Travel category

A transaction is one book category of quantity between 1 and 5.

please see what i did
create table city
(
idc tinyint,
city varchar(20)
);

insert into city values(1,'Albuquerque');
insert into city values(2,'Santa Fe');
insert into city values(3,'Taos');

create table Alb

(
idal tinyint,
store varchar(20)
);

insert into Alb values(1,'Winrock');
insert into Alb values(2,'Oldtown');
insert into Alb values(3,'NobHill');

create table Santa

(
idsan tinyint,
store varchar(20)
);

insert into Santa values(1,'VistaView');
insert into Santa values(2,'MesaVerde');

create table Taos
(
idta tinyint,
store varchar(20)
);

insert into taos values(1,'Zia');
insert into taos values(2,'Zuni');

create table books

(
bid tinyint,
type varchar(10)
);

insert into books values(1,'Computers');
insert into books values(2,'Science');
insert into books values(3,'Arthealth');
insert into books values(4,'Selfhelp');
insert into books values(5,'Business');
insert into books values(6,'Fiction');
insert into books values(7,'History');
insert into books values(8,'Religion');

create table y2000
(
qtr tinyint,

);

insert into y2000 values(1);
insert into y2000 values(2);
insert into y2000 values(3);
insert into y2000 values(4);

create table SalesCube
(
idal tinyint,
idsan tinyint,
idta tinyint,
bid tinyint,
qtr tinyint,
qty smallint

);
create table ST
(
idal tinyint,
idsan tinyint,
idta tinyint,
bid tinyint,
qtr tinyint,
qty tinyint
)

-- Populate Transaction Table

declare @n smallint,
@idal tinyint,
@idsan tinyint,
@idta tinyint,
@bid tinyint,
@qtr tinyint,
@qty tinyint

set @n = 1

while @n <= 1000
BEGIN
set @idal = convert(tinyint, rand()*100) % 5 + 1
set @idta = convert(tinyint, rand()*100) % 5 + 1
set @idsan = convert(tinyint, rand()*100) % 5 + 1
set @bid = convert(tinyint, rand()*100) % 5 + 1
set @qty = convert(tinyint, rand()*100) % 5 + 1
set @qtr = convert(tinyint, rand()*100) % 5 + 1

insert ST values( @idal ,
@idsan ,
@idta ,
@bid ,
@qtr ,
@qty )
set @n = @n + 1
END

insert salescube (idal,idsan,idta,qtr,qty)
select idal ,
idsan,
idta,qtr,sum(qty)from st
group by idal,idsan,idta,qtr
with cube

this is not working properly. i really do not know how to aproach quarters in the year.
possibly my tables are wrong too.
how to populate transaction tables?
if anyone has any advices i will be happy to apply it.
thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 20:27:09

What is your tranasction table structure? I'm guessing the table is ST.
From the question I would expect this to hold
city id,
store id,
book category id,
date,
quantity

It looks like yours has a column for each city rather than a city id.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

magdaf75
Starting Member

5 Posts

Posted - 2008-04-16 : 22:18:38

ok,
yes st is my transaction table structure

date is going to take care of the quoters? i just do not know how to aproach quoters in the year.

quote:
Originally posted by nr

What is your tranasction table structure? I'm guessing the table is ST.
From the question I would expect this to hold
city id,
store id,
book category id,
date,
quantity

It looks like yours has a column for each city rather than a city id.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources