|
No problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
refer to the title, thanks....
|
|
|
|
|
I would guess that you are referring to the time the database is in active use:
- 24 x 7: 24 hours per day 7 days a week
- 8 x 5: 8 hours per day 5 days in a week (working days and working time)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
hours you work in a week VS hours you breathe in a week, could be worst! 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi, im from Germany sorry for my (bad?) english.
Actually i have a really stupid problem.
Ill try to explain it in an abstract example:
1. Imagine you have to log your cd sells, every cd has a different price
2. The price Changes every 6th months and is used for the next 6 month.
3. When i sell the CD, i want to save the date and the current price.
Now my problem:
How do i save the price logging. Do i have to save the current Price in the
CD-Table and save this price for each sell in the selling-table?
Or
Do i have to create a CD/Price/Date table wich contains the prices of a CD of
a period. And i have to look for this price manually.
Or
????
Oh man i hope you understand my problem and know a solution.
Best regards!
|
|
|
|
|
The "proper" way would be to have a price table with an id and a to and from date and log the cd id, price id (and sale date) to a sales table.
eg
Prices Table
ID Price From To
1 14.99 1 jan 2008 30 jun 2008
2 15.99 1 jul 2008 null (indicates current price)
CD Table
CD_ID Title
102 The best of...
103 The Worst of..
Sales Table
CD_ID Price_ID Sale Date
102 1 14 may 2008
102 1 17 Jun 2008
103 1 10 Jan 2008
102 2 14 aug 2008
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Very similar to my solution below, except you have not stored a reference to the item in the price table. This, i guess, allows you a bit more flexibility in re-using prices across items.
|
|
|
|
|
Great minds... 
I would proably (in real life) have a poc coe against the cd and in the price table to allow for different prices depending on the cd, but that wasn't specified 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
jesus.online wrote: How do i save the price logging. Do i have to save the current Price in the
CD-Table and save this price for each sell in the selling-table?
Not necessarily, you could represent these relationships with the right data model. I might be tempted by something along the lines of:
TABLE: Item (Holds all CDs to be sold)
ItemId (PK INT)
Title (STRING)
TABLE: Price (holds the price of an item from a date onwards)
PriceId (PK INT)
ItemId (PK INT)
FromDate (DATETIME)
Price (MONEY)
TABLE: Sales (holds a record of every sale, its date and which price it was sold at)
SaleId (PK INT)
ItemId (INT)
PriceId (INT)
SaleDate (DATETIME)
Now you can always tell the current price of an item by looking for the first record in Price where FromDate is less then Current Date (ordered by FromDate DESC)
When storing a sale you store the PriceId along with the ItemId - allowing you to total up sales at each price point if you wish.
edit: You could eliminate ItemId from the Sales table, as you can get to this via the Price table, however a certain amount of de-normalisation is useful for speeding up queries.
|
|
|
|
|
Have to ask
I'm sure you've considered this but why would you eliminate ItemId from Sales instead of PriceId? Wouldn't it make sense to keep the sold item and the sale date if the price was fixed per date range (and you have already itemid in prices)?
Mika
|
|
|
|
|
On reflection, I think you're right and im wrong. However, in practice I wouldnt eliminate either as Id like to get either the price or the item from my sales table.
|
|
|
|
|
Yeah, makes sense that both fk:s are present!
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks a lot J4mieC and Ashfield!
This is exactly what ive been looking for!
Best regards.
|
|
|
|
|
Good Afternoon
I have a Table that get recreated from multiple tables, When its time to recreate it, i found that there are other views that are accssing the table and i cant delete it. in my SP i normaly find out if it exists and drop it like this
if exists (select * from dbo.sysobjects where id = object_id(N'[sde].[PROPERTY_SUMMARY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [sde].[PROPERTY_SUMMARY]
And the Select into Statement will follow. How do i drop the Table even there are references to this table.
Thank you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
You have to drop the references first. Why are you dropping it each time - can you not just delete the contents?
|
|
|
|
|
Thank you very much, done as you adviced and everything is cool
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I have got to agree with Paddy
Delete or truncate the table rather than drop it. You should never drop a table as part of a process.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you very much, done as Paddy adviced and everything is cool
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
I have two tables as follows:
CREATE TABLE [dbo].[WebSyncHistory](
[UserID] [int] NOT NULL,
[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncHistory_SyncTableName] DEFAULT (''),
[SyncTime] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[WebSyncTimesMS](
[UserID] [int] NOT NULL,
[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncTimesMS_SyncTableName] DEFAULT (''),
[LastActionTime] [datetime] NOT NULL
) ON [PRIMARY]
I have some records in each of these tables:
INSERT INTO WebSyncHistory VALUES (1, 'Products', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (1, 'Categories', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (2, 'DeviceSettings', '01/01/2008')
INSERT INTO WebSyncTimesMS VALUES (1, 'Products', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (1, 'DeviceSettings', '01/01/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'DeviceSettings', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'Categories', '01/01/2008')
Each table has two fields with the same name and data type
What I want to do is
select all records from each table in such a way that I end up with 4 columns - UserID, SyncTableName, SyncTime, LastActionTime
UserID SyncTableName SyncTime LastActionTime
1 Products 01/01/2008 31/08/2008
1 Categories 01/01/2008 NULL
1 DeviceSettings NULL 01/01/2008
2 DeviceSettings 01/01/2008 31/08/2008
2 Categories NULL 01/01/2008
The closest I can get to what I want is the following:
SELECT [WebSyncHistory].[UserID]
,[WebSyncHistory].[SyncTableName]
,MAX([WebSyncHistory].[SyncTime]) As SyncTime
,[WebSyncTimesMS].[UserID]
,[WebSyncTimesMS].[SyncTableName]
,[WebSyncTimesMS].[LastActionTime]
FROM ([WebSyncHistory] FULL OUTER JOIN [WebSyncTimesMS]
ON [WebSyncHistory].[UserID] = [WebSyncTimesMS].[UserID] AND [WebSyncHistory].[SyncTableName] = [WebSyncTimesMS].[SyncTableName])
GROUP BY [WebSyncHistory].[UserID]
,[WebSyncHistory].[SyncTableName]
,[WebSyncTimesMS].[UserID]
,[WebSyncTimesMS].[SyncTableName]
,[WebSyncTimesMS].[LastActionTime]
which returns:
UserID SyncTableName SyncTime UserID SyncTableName LastActionTime
NULL NULL NULL 1 DeviceSettings 2008-01-01 00:00:00.000
NULL NULL NULL 2 Categories 2008-01-01 00:00:00.000
1 Categories 2008-01-01 00:00:00.000 NULL NULL NULL
1 Products 2008-01-01 00:00:00.000 1 Products 2008-08-31 00:00:00.000
2 DeviceSettings 2008-01-01 00:00:00.000 2 DeviceSettings 2008-08-31 00:00:00.000
I'm completely stumped with this one. Can anyone suggest an alternative approach?
Thanks very much,
dlarkin77
|
|
|
|
|
Looking at the output you want, I dont think you need a "full" outer join since you always want to match UserID and SyncTableName. Here's a solution.
I will use a two-step procedure to solve this:
1. Get unique UserID and SyncTableNames (Creating a CTE will be best here)
2. Have a left outer join with the two tables
--create a CTE
With UserTableMods( [UserID], [SyncTableName])
As
(
Select distinct [UserID], [SyncTableName] from [WebSyncTimesMS]
union
Select distinct [UserID], [SyncTableName] from [WebSyncHistory]
)
--Get result
Select UTM.[UserID], UTM.[SyncTableName], H.[SyncTime], M.[LastActionTime]
From UserTableMods UTM
LEFT OUTER JOIN [WebSyncHistory] H ON UTM.USERID=H.USERID and UTM.[SyncTableName]=H.[SyncTableName]
LEFT OUTER JOIN [WebSyncTimesMS] M ON UTM.USERID=M.USERID and UTM.[SyncTableName]=M.[SyncTableName]
And here's the result:
1 Categories 2008-01-01 NULL
1 DeviceSettings NULL 2008-01-01
1 Products 2008-01-01 2008-08-31
2 Categories NULL 2008-01-01
2 DeviceSettings 2008-01-01 2008-08-31
You can use the Grouping and Max functions as per your requirement. Hope that helps.
Regards,
Syed Mehroz Alam
|
|
|
|
|
That works perfectly. Thanks very much Syed
|
|
|
|
|
|
Hi
I have a column in my database named group. However when I put that column in a stored procedure the word 'group' highlights blue and I cant declare it as a column because I think it means something different for sql.
Any help would be great
thanks
Deliver yesterday, code today, think tomorrow.
"http://www.heuse.com/cphumor.htm"
|
|
|
|
|
Surround it with square brackets.
|
|
|
|
|
Cheers found that on another article, but thanks for the answer!
Deliver yesterday, code today, think tomorrow.
"http://www.heuse.com/cphumor.htm"
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
