|
This is better handled with a VIEW. I very rarely store duplicated or calculated data in a table. I only violate this rule when a frequently run query would be greatly enhanced. In psuedo-code on SQL Server it would look something like this:
CREATE VIEW FirmOpen
AS
SELECT
FirmName,
CASE
GETDATE() BETWEEN OpenTime AND CloseTime THEN 1
ELSE 0
END AS IsOpen
FROM
tblFirm
INNER JOIN
tblTime
ON (tblFirm.FirmName = tblTime.FirmName)
You will need to modify the INNER JOIN to reflect your foreign keys and handle the Date/Time calculations correctly. Once complete, you can use the FirmOpen like any other table (except in the case of UPDATE/INSERT).
If you still want to field to update automatically, you will need tocreate a SQL Job that does the UPDATE and schedule it to run every minute (poor choice in my opinion).
|
|
|
|
|
query for retrieving duplicate rows in a table?
|
|
|
|
|
select * from <tablename> where <columnname> in(select <columnname> from <tablename> group by <columnname> having count(<columnname>)>1)
G.Praveen Kumar.
|
|
|
|
|
select * from gopi6 where ename in(select ename from gopi6 group by ename having count(ename)>1)
Hi
|
|
|
|
|
I want to get only one row(first one) when multiple rows are present in a table.....
e.g
Prof ID -- Item -- Date
1 -- Mango -- 1-1-2005
1 -- Orange -- 2-1-2005
1 -- Apple -- 3-1-2005
2 -- Orange -- 2-1-2005
2 -- Apple -- 3-1-2005
I want this result:
Prof ID -- Item -- Date
1 -- Mango -- 1-1-2005
2 -- Orange -- 2-1-2005
Do good and have good.
|
|
|
|
|
That depends on your idea of what is first. Strictly speaking the rows in a database have no order although the underlying database software will put the data in a certain order it should always be thought of as unordered because the software may change and the previously implied order with it.
So, given that a database is an unordered set of data, how do you define first? What do you want to put in the ORDER BY clause? Then we can figure out how to get the first of each series.
|
|
|
|
|
infact i want to get just one record against "Prof ID". Order is not necessary here....
Waiting for reply
Do good and have good.
|
|
|
|
|
Use simplest way that is GROUP BY option
Regards
R.Arockiapathinathan
|
|
|
|
|
I have 3 tables with period history field. the Period will be replaced with each end of the month in current year.
so: Period01 = January 31, 2006 and Period12 = December 31, 2006
Here are the fields of each of them:
Vendors (Vendor_ID, Purchase_Period01, Purchase_Period02 , ... , Purchase_Period12,
Payments_Period01, Payments_Period02 , ... , Payments_Period12
)
Items (Item_ID, UnitSold_Period01 , ... , UnitSold_Period12 ,
Sales_Period01 , ... , Sales_Period12 ,
UnitReceived_Period01 , ... , UnitReceived_Period12 ,
Cost_Period01 , ... , Cost_Period12 ,
UnitDamaged_Period01 , ... , UnitDamaged_Period12 ,
Loss_Period01, ... , Loss_Period12
)
Accounts (CoA_ID, Debits_Period01, ... , Debits_Period12 ,
Credits_Period01, ... , Credits_Period12 ,
Activity_Period01, ... , Activity_Period12 ,
Balance_Period01, ... , Balance_Period12
)
My Question is: Do all Tables above is in Normal Form?
If not, can you help me make those Tables in Normal Form?
I would like to compact those Tables' Field design so that those tables consist of as little as possible of Period Fields..?
Thanks for the help..
Dogma: I :am goD
|
|
|
|
|
Tomy1402 wrote: Do all Tables above is in Normal Form?
Which particular normal form are you looking for? There are 7 (if I remember correctly) in total.
It appears to conform to the first normal form - All pieces of data are separated into their own column. (e.g. Name="Colin Mackay" does not conform, FirstName="Colin", Surname="Mackay" does conform)
It appears to conform to the second normal form - All tables have a primary key
It appears to conform to the third normal form - All non-key columns depend on the primary key.
It does not conform to the fourth normal form - The table should not contain repeating columns.
|
|
|
|
|
Which normal form??
moreover, in certain areas normalisation might lead to performance hits, i.e if you are going to have to many reads in your tables, if u have more joins n such stuff, it will impact performance seriously.
PADAYAPPA
|
|
|
|
|
karamchandrabose wrote: Which normal form??
Sorry, I don't understand your question.
karamchandrabose wrote: in certain areas normalisation might lead to performance hits, i.e if you are going to have to many reads in your tables, if u have more joins n such stuff, it will impact performance seriously.
Joins in and of themeselves do not necessarily hurt performance. It is what you are doing with the join that can hurt performance.
As a general rule I'd say that you should normalise a database as much as possible and then once you have that, denormalise as required to get the performance.
|
|
|
|
|
|
Hi,
I need to round the minutes to the nearest quarter of hour. For example if it is 00:07 (hour:minutes) then it would be 00:15, if 00:05 then it would be 00:00.
Any ideas how to go about doing this?
Many thanks for your time.
|
|
|
|
|
Make a function:
CREATE FUNCTION [dbo].[RoundMinutes]
(@dDateTime datetime)
RETURNS DateTime AS
BEGIN
declare @minutes int
set @minutes = datepart(n,@dDateTime)
set @minutes = case when @minutes < 7 then -@minutes
when @minutes < 23 then -@minutes+15
when @minutes < 37 then -@minutes+30
when @minutes < 52 then -@minutes+45
else 60-@minutes
end
return(dateadd(n,@minutes,@dDateTime))
END
Call it like this:
select dbo.roundminutes(datefield) as RoundedDateField from mytesttable
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi
I have to populate some datas from sql server database and display it directly in a CSV Format or CSV file. When i run the project it should provide me an option to whether open or save or cancel the file. when i click open it should be opened and be viewed in a excel sheet in CSV format and when i click save it should ask the destination folder and should be saved there in CSV format and when i click cancel it should be cancelled and the application should be closed. pls note that all these actions should happen in the same browser and should not be redirected to anyother page. Can anybody give me the detailed description and code in Asp.Net using C# .It's very Urgent.
Regards
Vijay.
|
|
|
|
|
Hi
Anyone please tell me that we can work only with Database.MDF file without sql server in asp.net 2.0.
if it is possible please tell me the details...
Regards
RPL
r_palanivel83 10:01 4 Jan '06
|
|
|
|
|
r_palanivel83 wrote: Anyone please tell me that we can work only with Database.MDF file without sql server in asp.net 2.0.
I wouldn't think so. SQL Server is not a file based database - although SQL Server 2005 Express edition does blur that distinction a little. When you access a SQL Server database you access the SQL Server, not the file. Only SQL Server can access the file directly - everything else must go through the server.
|
|
|
|
|
r_palanivel83 wrote: Anyone please tell me that we can work only with Database.MDF file without sql server in asp.net 2.0.
if it is possible please tell me the details...
Nope. You have to have SQL Server/SQL Server 2005 Express if you want to work with .mdf files. If you want to work with a file based database, then Access would be the way to go. You may want to look at Firebird SQL.
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
|
I have installed SQL 2005 Beta 2.It was not working properly so I have uninstall it.Now I am trying to install SQL 2005 Express that comes with Visual Studio.net 2005 but I couldnt install it because not all of the Beta 2 files have been removed (and they dont appear in the Add/Remove Programs in the control panel) so I used msiinv.exe and msiexec.exe in the command prompt to remove it but this way didnt solve my problem.Could any one tell me how could I Remove These Beta 2 files from my computer?.
I think this link will explain my problem for you more clearly:
http://blogs.msdn.com/astebner/archive/2005/07/01/using-msiinv-to-gather-information-about-what-is-installed-on-a-computer.aspx[^]
My problem is similar to Syed Irtaza Ali problem written in the comments in the link above.
I am too late but i will never give up
|
|
|
|
|
Hi ALL,
Is this the right place to ask "SQL Server 2005 - Reporting Services"
related questions? If not, direct me to the right place.
I'm new to MS-SS-Reporting Services.
I'm able to create some reports.
I just wanted to create a report like this with multi-level
columns.
Population Report
C1, C2, C3....Cities
------------------------------------------------------------------
Country-1 | Country-2
------------------------------------------------------------------
State-1 | State-2 | State-3 |
------------------------------------------------------------------
C1 | C2 | C3 |
------------------------------------------------------------------
| | |
| | |
Is it possible in MS-SS-Reporting Services?
If so, kindly let me know how to do.
As of now, I'm able create like this:
----------------------------------------------
Country | State | City | Population
----------------------------------------------
India TamilNadu Chennai ---
Trichy ---
Madurai ---
Coimbatore ---
-----------------------------------
Kerala Trivandrum ---
Cochin ---
Ennore ---
Kottayam ---
----------------------------------------------
SriLanka *** *** ---
Thanks in advance,
Sarvan AL
|
|
|
|
|
Yes it is fairly easy, You have to experiment with Matrix for report1 and use table for report2.
You also have to learn how use the embeded "IIF" statement to get your layout right. It's all trial and errror and plenty of RTFM.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi Frank,
Thanks for your suggestions.
Since I'm totally new to MS-SS-RS, let me
go through the manuals about "Matrix" report and
embedded IIF statement, and come back.
Thanks once again,
Sarvan AL
|
|
|
|
|
I need proffisionaly ADO.net Databaseconnection string Codingwith Sql server ....Pls help me
Thanking U
somasundaram
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
