OLTP Database Design Help For Bank's Customer Table

Aug 9, 2007


Hello,friends

1) CustomerID
2) FirstName
3) MiddleName
4) SurName
5) Title
6) Marital Status
7) Education
8) Occupation
9) Annual Income
10) Line of Business
11) DOB
12) Father Name
13) Mother Name
14) SpouseName
15) Gender
16) Email
17) MainTel
18) Home Tel
19) Passport Number
20)----------------------
21)- - - - - - - - - - -


100)-------------------
Above mentioned list is a snapshot of our customer master table ,which contain approximately 100 attributes related to a customer.

We are designing an application for banking sector (but NOT Core banking solution),for which we may need to capture variable number of addresses for bank's customer,i.e more then three types of addresses Fixed,Temporary and Communication addresses(which is generally the case with all banks).
A single address includes address1/address2/city/country/state/pincode fields.
In context of OLTP database,We have option to put multiple addresses in child table but that involves various joins at the time of data retrival and slow down the query.


As another option we can can create redundent addresses columns(address1/address2/city/country/state/pincode) in master table that will accumulate addresses if demand for more then three type addresses arises(although there is reasonable numer of extra addresses is expected, i.e 10)

Database is expected to serve the records of 25 million(approx) bank's customer,so does someone can suggest me how to maintan the balance between two approches.

View 2 Replies


ADVERTISEMENT

How To Design Database To Search Faster From 1 Million Customer's

Jul 30, 2007

I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.

Application serves two functionalities



Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.



Scenario: The above requirement can be complemented by following example

Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.

Now if some new customer want to open a new account in bank, then bank officials want to know whether the

new customer is one of the existing customer or not(without asking to customer itself).

System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface

then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).



Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).

Scenario: The above requirement can be complemented by following example

If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.



I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.

View 5 Replies View Related

DB Design :: Extracting History From OLTP?

Nov 3, 2015

Simple and conventional OLTP database, we need to capture all changes for insert into a DW via staging / ods etc.

Is there a recommended approach for this? Obviously it has to be real time as there might be multiple updates for a time period. I'm thinking of triggers on OLTP tables (bad for performance as it's synchronous), or change data capture, service broker as asynchronous methods.

View 8 Replies View Related

Bank Of 15 Records Cannot Be Edited In A Table

Oct 10, 2007

Hi,
I am using SQL Server 2000 as part of SBS 2003. I have an odd problem with a table in my database.

On Monday, due to a problem we had on Friday afternoon, I was forced to restore the database to 12:30pm Friday. Since then, everything has been fine, except that in one table, a bank of 15 records cannot be edited. There are 25,000+ records in the table, and all of the rest are fine and can be edited. If I try to edit one of the "bad" records, SQL times out or hangs indefinately. This is true if I edit the table direct, if I use SQL Analyser or if I use my FE application. There is no other error message. There are no triggers on the table.

As far as I can see, the id's of all of the bad records are the same as those which would have been entered on Friday afternoon, but which were lost with the restore. Records before and after the bank of 15 can be edited ok.

Last night, with no other users on the system, I exported the table, deleted the original, created a new table with the same name and fields as the original and used Analyser to populate the new table from the export. I then tried to edit the bad records and it worked fine. However, this morning, with other users on, it fails again and the records cannot be edited.

I have used DBCC CHECKDB and it displays no errors.

Can anybody suggest what this might be and how I can get round it?

Thanks for any help

Colin

View 20 Replies View Related

SQL Server 2012 :: Query Design - Find Most Recent Datetime Record Each Day For A Customer

Apr 2, 2015

So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:

SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)

[Code] ....

View 4 Replies View Related

Create Date Table With UK & Easter Bank Holidays

May 12, 2005

Also includes ISOWeek & Weekends

Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.

So while looking at Bretts blog and another post on here, i thought i'd post this on here
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698

Special thanks to rockmoose & BOL (as always)

Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend

SET DATEFIRST 1
SET NOCOUNT ON
GO

--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
GO
--END ISOweek

--CREATE Easter algorithm function
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,
@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT

SET @A = @YEAR%19
SET @B = @YEAR / 100
SET @C = @YEAR%100
SET @D = @B / 4
SET @E = @B%4
SET @F = (@B + 8) / 25
SET @G = (@B - @F + 1) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15)%30
SET @I = @C / 4
SET @K = @C%4
SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7
SET @M = (@A + 11 * @H + 22 * @L) / 451
SET @O = 22 + @H + @L - 7 * @M

IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END

RETURN @R
END
GO
--END fnDLA_GetEasterdate

--Create the table
CREATE TABLE MyDateTable
(
FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED,
Period int,
ISOWeek int,
WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create

--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
INSERT MyDateTable(FullDate, Period, ISOWeek)
SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom)
SET @DateFrom = DATEADD(dd,+1,@DateFrom)
END
GO
--End population


/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday
OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday
GO

UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7)
GROUP BY DATEPART(yy,FullDate))
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
DATEADD(dd,+2,FullDate) ELSE FullDate END
FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO

---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
/* End of WorkingDays UPDATE */

--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable

SET NOCOUNT OFF


Andy

Beauty is in the eyes of the beerholder

View 4 Replies View Related

Writeback To The Source OLTP Database.

Apr 22, 2008

I was wondering if it is possible to use SQL Server Reporting Services 2005 with the 'writeback' feature to the source OLTP database?

I have seen article's that refer to using SQL Server Analyses Services (SSAS) and writing back to the ROLAP/MOLAP database, however this is not desirable for our case.

I have almost come to the conclusion that it is not possible without SSAS.

Cheers,

View 3 Replies View Related

DB Engine :: Unexpected Update / Delete On OLTP Database In 2005

Jun 17, 2015

We had one of the major issues where one of the table on a heavily used OLTP database seems to have updated the records which were not expected.

Scenario:

We got around more than 12K contracts updated to status expired even though the expiry date is not set to be so:

for E.G : Below table has a column contract status which overnight seems to have updated the values to expired.

Even though the start and expiry date does not follow the logic for above.

We had the above working for past 3 years via a SP scheduled via SQL agent Job which Expire active contracts whose expiration date is less than today's 12:00AM.There has been no change in SP.

How can i track how it happened and what caused it?

View 28 Replies View Related

Database Table Design

Feb 13, 2008

I have a question regarding table design (I'm using SQL Server Express 2005). Is it better and is there a performance gain by using layout # 2 instead of layout # 1? Someone told me it was better to divide everything up but it seems kind of useless here, plus it complicates the handling. So I want to know which one is faster (or better) considering large amount of records. Thanks! Layout # 1Objet==========ObjetIDClientIDObjetTypeStateObjetNoTitleDescriptionStartDateReturnDateEndDateLayout # 2
Objet==========ObjetIDClientIDObjetTypeStateDetailsObjet==========DetailIDObjetIDObjetNoTitleDescriptionStartDateReturnDateEndDate

View 3 Replies View Related

Database Table Design

Feb 27, 2005

Im trying to design a database to use in SQL server , the design i've got now is working but It just doesn't look right to me and there must be a better way to set things out.

Heres the current situation

Theres 3 tables

USERTYPES

USERS

TICKETS

The users table holds the user information i.e.

userID
userName
Phone Number
userTypeID *

It contains a foreign key called userTypeID which is a primary key of USERTYPES

The USERTYPES table contains the two types of users on the system , these are standard and admin, so the table looks like this.


USERTYPESID
typeName

The TICKETS table holds the ticket details of tickets submitted by users. so

ticketID
ticketName
ticketDescription
DueDate
ticketRequestor *
AssignedTo *

Now here is where I have the problem, the ticketRequestor is a foreign key of the STAFF table staffID however AssignedTo is also a foreign key of the STAFF table staffID.

Currently in order to get this working i've had to turn off the enforce relationship for the AssignedTO - staffID link but idealy I think this enforce relationship should be on to ensure its a truely relational.

any ideas as to what I need to do to organise the relationships better ?

thanks

View 2 Replies View Related

Customer Table

Feb 13, 2007

I have a customer table with the column "invoice_customer" (this is the customer_acccount of account where the bill is invoiced..)

so to get the invoice_customer address for the customer account - 13301

SELECT B1.address as InvoiceAddress
from CUSTOMER AS B1 , CUSTOMER AS E1
WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'

i want to add the above InvoiceAddress to the query below:

select customer_account, order_no, date_req, del_address (InvoiceAddress)
from CUSTOMERS
INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account
where status 'D'

How would I put the 2 togtheer...

Thanks in advance!!!

View 2 Replies View Related

Database/Table Design-URGENT HELP!!!

Jan 15, 2008

Hi all,

I have to come up with a design choice for a DB Table for a financial firm(i.e. Tables with Fixed column or Value-Pair).

Case: SQL Server 2005 is used as a back-end for a web application(which basically is designed for doing web based analysis). The business people are insisting on a feature that enable power users to add fields to the underlying tables. One option proposed is named value-pair design approach? But from performance point of view, this seems a nightmare. How can this be integrated into static-column table approach. FYI, security policy of the client do not allow the underlying table structures to be changed by the user and currently
there are 5000 fields, with a possibility to grow.

Thanks so much for your suggestions!!
Dilla

View 5 Replies View Related

Design Responses Table In Survey Database

Mar 23, 2015

I have questions table and answers in survey database. Now I design responses table from users !

CREATE TABLE [dbo].[Response](
[ResponseId] [int] IDENTITY(1,1) NOT NULL,
[SurveyId] [int] NULL,
[QuestionId] [int] NULL,
[AnswerId] [int] NULL,
[VoterId] [int] NULL,

[Code] ...

My Questions : How can I store data if questions is multiply answers (checkbox type) ?What is best way ?

View 16 Replies View Related

SQL 2005 Database Design (Table Names)

Apr 14, 2006

In the past, we had table names such as:
PurchaseOrders
SalesOrders

Now with SQL 2005, you can use schema namespaces, so it could become:
Purchasing.Orders
Sales.Orders

So essentially you can use exactly the SAME table names, in different schemas.

So... would you recommend to change to the last model, or just stay with the old way?
What is best practice?



One of the reasons why I ask this question is that I looked at the AdventureWorks sample database included with SQL Server 2005, and Microsoft is using:

Sales.SalesOrderDetail
Purchasing.PurchaseOrderDetail
etc...

Why not:
Sales.OrderDetail
Purchasing.OrderDetail
etc...

????????

View 1 Replies View Related

Updating Orders From Customer Table

Sep 26, 2012

I want to update the orders that has invoice number empty when doing a change in the customer table Nm.

My order table is named order and customer is named customer, they both have a column named CustId and Nm (name).

This works fine and shows in my case column Nm in 4 rows (for orders);

select
Order.Nm
from
Order
Join Customer
on Order.CustId=Customer.CustId
where
Order.CustId=@variable and InvoNo=''

But the update statement gets error;

Update
Order
set
Order.Nm=Customer.Nm
from
Order
inner join Customer
on
Order.CustId=Customer.CustId
where
Ord.CustId=@Variable and InvoNo=''

I use SQL 2008....

View 13 Replies View Related

Determine That ID Is Unique In Customer Table

Oct 12, 2014

I would need to rewrite SQL code to determine that id is unique in the Customer table.

My two tables are:

CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,

[Code] ....

View 2 Replies View Related

Database Table Design For Huge Number Of Columns

Jan 24, 2008

Hi

I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%

So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax

same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.

So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.

I was wondering which is a better approach out of the below 3:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.

Your help is greatly appreciated.
Thanks

View 3 Replies View Related

Database Table Design For Huge Number Of Columns

Jan 24, 2008

Hi

I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%

So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax

same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.

So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.

I was wondering which is a better approach out of the below 4:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.

4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.



Your help is greatly appreciated.
Thanks

View 4 Replies View Related

DB Design :: Unable To View Table Record In Database?

Jul 24, 2015

I am creating a simple application form using visual studio 2015. I can create database.mdf successfully and create dbo.table successfully. but when i tried to view table by expanding the table icon on the server explorer, the table should be able to show list of table but it didn't show any record and why is it like that.

View 4 Replies View Related

Select Most Recent Customer Record From Table Only?

Nov 6, 2007

If I have a table structure similar to the following, how might I query it to obtain the Transaction ID, Transaction Date, and Customer Name for the most recent transaction per customer only:


TransactionTable:
TransactionID TransactionDate TransactionType CustomerName
1 10/1/07 1 Bob
2 8/30/07 2 Janet
3 9/17/07 1 Mike
4 9/25/07 1 Bob


The following query will return all records in the table other than Janets...not what I want:
SELECT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC


The following query will return all records in the table other than Janets again, because DISTINCT will use the combo of TransactionID, TransactionDate, and Customer name for uniqueness...not what I want:
SELECT DISTINCT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC

The results set I'm looking for would be the following, where only the most recent entry per customer with TransactionType 1 is returned (i.e. one record for Bob):


TransactionTable:
TransactionID TransactionDate CustomerName
1 10/1/07 Bob
3 9/17/07 Mike


I believe I need an appropriate subquery to yield the results I desire, but can't sort out what it is? I do not want to execute multiple queries but subqueries are fine. Unfortunately there's probably an easy answer that my brain is not currently generating. Any help would be appreciated.


Note, this is not a real table, but a sample to illustrate the concept for the query I need.

Thanks.

View 6 Replies View Related

Database/Table Design Question - Object/Event Model

Dec 11, 2007

Hi,Facts:I created a database to support an application that tracks events ondifferent objects. The two main tables are tbl_Object andtbl_EventLog. Each table has unique ID and on the tbl_EventLog thereis FK for a record in the tbl_Object. The events are inserted all thetime for the same or different objects from the tbl_Object. There areabout 600,000 objects in the tbl_Object and 1,500,000 (and growing)events in tbl_EventLog.Question:The user often wants to know what the last event was for a specificobject.What is the best way of retrieving the last event?Should I simply do a max(eventdatetime) on a specific object? orShould I add a LastEventID column to tbl_Object and update it everytime a new event is inserted? or any other way to implement it?I chose the second method because I didn't think it made sense searchthe event table everytime the user wants to know the last event, but Iwanted to know what the experts thought.Please let me know what you think.Thank you,Oran Levin

View 5 Replies View Related

Table Design Question - Subject Matter Expert Database

Aug 8, 2007

I would like to create a database that keeps track of our companies subject matter experts. I have roughed out some of the tables. I would appreciate any feedback on if this is the right approach and if there might be any issues when I start writting a front-end (probably VB 2005).

What makes this interesting (at least for me) is that a subject has an owner and at least 1 "expert", possibly up to 3. Here is what I am thinking for tables:

tblEmployee

EmployeeID (PK)
LastName
FirstName
etc......
tblSubject

SubjectID (PK)
Description
tblOwner

SubjectID (FK)
EmployeeID (FK)
tblExpert1

SubjectID (FK)
EmployeeID (FK)
tblExpert2

SubjectID (FK)
EmployeeID (FK)
tblExpert3

SubjectID (FK)
EmployeeID (FK)



Does this make sense? Would I run into any issues when trying to display this on a form in VB?

Thanks in advance for any help!!!!!

Cal

View 8 Replies View Related

DB Design :: Generate A Script To Choose Only Top 10 Items From Each Table In Database?

Aug 22, 2015

I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements? These top 10 should be ordered by the primary key.

View 4 Replies View Related

SQL Server 2008 :: How To Load A Customer Database

May 26, 2015

I need to load a customer database onto our SQL 2008 server. I always use restore database option in the management studio and create a new database from device (customer database backup file), it used to work just fine. But when I do the same now, I get this below error:

CREATE DATABASE permission denied in database 'master'.
RESTORE HEADERONLY is terminating abnormally.(Microsoft SQL Server,Error: 262)

I also tried to create new database option in the management studio, but get the same error. I did run management studio as 'Run as admin'.

Server - Windows 2008 R2
SQL Server 2008 R2, SP1

View 4 Replies View Related

Analysis :: Sales Fact Table - Customer Who Didn't Buy Top 10 Products

Nov 3, 2015

We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.

We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?

View 4 Replies View Related

DB Design :: Database Design For Matrix Representation

May 13, 2015

I have a scenario like below

Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1

How to design tables in SQL Server for the above.

View 2 Replies View Related

Bank Numbers Check

Aug 3, 2007

I want to make a query that check in a table for bank numbers that are incorrect. This need to be done in sql and using modulo 97.
Thanks

View 9 Replies View Related

Code To Import Bank Files (BAI, BAI2)

Oct 31, 2006

Anyone have some code to import bank files to SQL Server 2000 tables in the formats BAI or BAI2? I'm using a DTS package currently, but I'd like to have more control over this by using T-SQL or vbscript or something.
Thanks!

View 2 Replies View Related

Bank Statement Problem - Another Question Page 2

Jul 23, 2007

Hi all,

Im new so apologies in advance if this is an easy one.

I have a single table which holds:
ID (PK)
EnteredDate
MonetaryValue
REF_ValueType

It's basically used to record income and expenditure and the records look something like:

ID EnteredDate MonetaryValue REF_ValueType
1 21/01/07 12.34 Received
2 22/01/07 -8.34 Paid
3 23/02/07 100.28 Received

What Im trying to do is to write sql that will return all the records in the form of a bank statement with received values in one column and paid values in another e.g.
Date Income Expenditure
21/01/07 12.34
22/01/07 -8.34
23/02/07 100.28

Ive tried all ways but I still can't get it to work. Is there a simple way.

All advice most definitely appreciated, it's sending me crazy. Many thanks in advance

Mike

View 20 Replies View Related

Code To Import Bank Files (BAI, BAI2)

Nov 1, 2006

Anyone have some code to import bank files to SQL Server 2000 tables in the formats BAI or BAI2? I'm using a DTS package currently, but I'd like to have more control over this by using T-SQL or vbscript or something.

These files have complex structures/multiple record types. Not to mention my main problem of trying to use a bulk insert which doesn't seem to work because of something to do with the row delimiters. Not too sure what's up with that because I usually do this for all my flat file imports. I'm certain vbscript will work, but I'd like to know if anyone out there has already built something for these particular files??
Thanks!

View 3 Replies View Related

DB Design :: Table Design For Packages

Aug 18, 2015

I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show.  How to design the table.. 

Product name   :  
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |

CTN, NOs
Price:
50,20,5 |
40,6

(Remarks for your reference):CTN=10nos, OTR=4 nos  
| CTN=8 Nos

View 3 Replies View Related

Calculate Date Difference Excluding Bank Holidays

Aug 28, 2013

I have a Bank holiday table (ID column, and Date column), how to exclude bank holidays in the datediff returned.

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN
declare @i int
Declare @count int

[Code] .....

View 4 Replies View Related

Database Design/query Design

Feb 13, 2002

Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:

-------
Fixture
-------
fix_id
fix_date
fix_played

----
Team
----
tem_id
tem_name

-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals

It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.

Fixture contains the details about the fixture like date and fixture id and has it been played

Team contains team info like team id, name, associated graphic

TeamFixture is the table which links the fixture to it's home and away team.

TeamFixture exists to prevent a many to many type relationship.

Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved