Kudler Fine Foods Database Report

SQL Accounting Database for Kudler Fine Foods
Mark Black

Introduction
Kudler Fine Foods wants to convert the Chart of Accounts sheet in Excel to an SQL database. In order to do so, it is important to format the excel data in proper SQL tables. Normalization plays an important part in placing data fields in proper tables.
It is also essential to import data from excel to SQL directly to avoid time wastage. The following report shall detail the creation of SQL database, tables and insertion of data in SQL tables.
Data Import
Chart of Accounts
Select * INTO COAExcel FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database= C:\Users\student\Desktop\KudlerCOA.xls;HDR=YES', 'SELECT Account,Description,[Short Description],F4
Create database Kuddler_AccountData Tables

Tables
USE Kuddler_AccountData

create table Locations(
LocationID INT PRIMARY KEY NOT NULL,
Location_Name varchar(50) NOT NULL,
)
create table AccountTypes(
AccountTypeID INT PRIMARY KEY NOT NULL,
AccountTypeName varchar(50) NOT NULL,
)
create table ChartofAccounts(
Account INT PRIMARY KEY,
AccountTypeID INT FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes (AccountTypeID),
LocationID INT FOREIGN KEY (LocationID) REFERENCES Locations (LocationID))
Balance Decimal(10,2) NOT NULL default 0.0)

create table Transactions(
TransactionID INT identity(1,1) Primary Key,
Account INT FOREIGN KEY (Account) REFERENCES ChartofAccounts (Account),
LocationID INT FOREIGN KEY (LocationID) REFERENCES Locations (LocationID),
TransactionDate DateTime NOT NULL,
Amount Decimal(10,2) NOT NULL)

Data Insertion
INSERT INTO locations(LocationID ,Location_Name)
VALUES
(1,'La Jolla'),
(2,'Del Mar'),
(3,' Encinitas')

INSERT INTO AccountTypes(AccountTypeID ,AccountTypeName)
INSERT into AccountTypes(AccountTypeName) select distinct Description from COAExcel where description is not null

INSERT INTO ChartofAccounts (Account, AccountTypeID, LocationID,
(610001,3,1,0),
(610002,3,2,0),
(610003,3,3,0)

INSERT INTO Transactions(Account, LocationID,TransactionDate,Amount)
VALUES
(520301,1,GetDate(),50),
(520302,2,GetDate(),-498),
(520303,3,GetDate()+1,200),
(600001,1,GetDate()+1,350),
(600003,3,GetDate(),150),
(610002,2,GetDate()-3,129),
(610003,3,GetDate()-2,130),
(520303,2,GetDate()+1,-498),
(520303,3,GetDate()+2,200),
(600001,1,GetDate()+4,350),
(600002,2,GetDate()+6,-320),
(600003,3,GetDate(),150)

UPDATE Balance Field
UPDATE ChartofAccounts set balance = coalesce((select SUM(Amount) from TRANSACTIONS where TRANSACTIONS.account = ChartofAccounts.account ), 0);

SELECT all fields
SELECT *
FROM chartofaccounts ca INNER JOIN accounttypes AT ON ca.AccountTypeID = AT.AccountTypeID INNER JOIN locations l ON ca.locationID = l.locationID

SELECT query for Report

select * from chartofaccounts coa order by left(coa.account, 2) compute sum(coa.balance) by left(coa.account, 2) compute sum(coa.balance);