Hello friends,I have to design a database for banking application,but it is my first db design so i need some expert advice.
Data base has some performance constraint i.e high output ,minimum query time,need to process 2 to 5 million transaction per day.
Further specification can be revealed if appropriate help is provided.
your advice will be of great help for me.
Thx
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
All:
This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
Thanks!
Hi All Professionals Programmers,
I would like to ask a question that is very important for me. The question is how can i create a flexible data base in which i m able to create the inner levels as much as i can.
Like i have a table building, then i have another child table floor, then the floor become parent and i have its child rooms, then the rooms become parent and i have its child floor tiles etc.
you can see i am going to inner dept, so i need a flexible database because its very costly and intimadting to change the database and every time create a new table and relationships.
Hope you have understood what i am going to say and need advice of professional and expert user to resolve it.
Any concise quality material like articles, white paper etc will also be suitable for me.
Thanks in Advance
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.
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!
I am looking into a position requiring "SQL PLUS Expert" - anbody out there heard of this - is it a querying tool a la query analyzer of PL SQL.
View 2 Replies View RelatedI am looking into a position requiring a "SQL PLUS Expert" - anybody out ther heard of this - is it a querying tool a la query analyzer or PL SQL ?
View 1 Replies View RelatedHello,
Lets just say that I have really only logged into phpmyadmin once and messed up my forum.
What I was trying to do? Install a shoutbox (chatroom type software).
When I logged into phpmyadmin I clicked the phpbb_config tab on the left hand side. Then I clicked the SQL tab and pasted the code required for the shoutbox accordingly and hit the "Go" button. But what I did next is what created problems.
I thought I may have put extra spaces or loaded the code wrong in the SQL area because the shoutbox did not work properly after everything was installed. So while in phpmyadmin and after clicking phpbb_config agian, instead of hitting SQL, I hit the "Empty" button thinking that I could just erase what I had previously inserted and then try inserting it again to make sure the code was inserted properly.
If you havn't figured it out by now, I clearly don't have a clue what I am doing, what exactly I did, or what I can do to fix it.
Click on the link to see the error..
http://www.cflzone.com/forum/index.php
Can someone that knows what they are doing please help me???
thanks,
- Reider
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View RelatedHello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies View RelatedI have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies View RelatedHi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies View RelatedHello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100Or even a control:Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like: Define something (Integer, String, Class, Control, etc) Save in SQL 2005 Database Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel
Has anyone any suggestions how I might find the most recent of a series of dates from different tables? I am joining tables which all have a date_altered field and I am only interested in displaying the most recent of these. Eg.
select a.x,b.y,c.z, a.date_altered, b.date_altered, c.date_altered
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
What I would like is in effect a function Highest() Eg.
select a.x,b.y,c.z, highest(a.date_altered, b.date_altered, c.date_altered) as last_alteration
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
I am using SQL Server 7 so cannot write myself a function to achieve this - frustrating, as it is something I could have done 4 years ago when I used mostly Oracle.
Many thanks
Fact is:
I have two tables Orderposreg and Temp1 (both in [My Database]), Orderposreg is from 1994, Temp1 from 1995 and i need to Update the old table with the new one.
This should my Query do: I need it to Update old primary keys, or if the row does not exist to insert the new primary key into the Orderposreg.
But if I start the Query i get this failure message:
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Temp1' does not match with a table name or alias name used in the query.
It seems that the colums do not have the same name, but the column of both tables have exactly the same column name and data type just the table name is different.
For your information the whole Code:
Code:
declare @error varchar, @rowcount varchar
select count(*) from dbo.temp1
if (@@error <> 0)
begin
Print 'An error occurred reading the temporary import table'
goto exitpoint
end
begin transaction
update dbo.Orderposreg
set dbo.Orderposreg.Ordernr = dbo.Temp1.Ordernr,
dbo.Orderposreg.Pos = dbo.Temp1.Pos,
dbo.Orderposreg.Produktnr = dbo.Temp1.Produktnr,
dbo.Orderposreg.Artbenämn = dbo.Temp1.Artbenämn,
dbo.Orderposreg.Artikelgrupp = dbo.Temp1.Artikelgrupp,
dbo.Orderposreg.Förs_pris = dbo.Temp1.Förs_pris,
dbo.Orderposreg.Kvant = dbo.Temp1.Kvant,
dbo.Orderposreg.Total_Intäkt = dbo.Temp1.Total_Intäkt,
dbo.Orderposreg.SSort = dbo.Temp1.Sort,
dbo.Orderposreg.Datum = dbo.Temp1.Datum,
dbo.Orderposreg.Utskriven = dbo.Temp1.Utskriven,
dbo.Orderposreg.Levtid_Ö = dbo.Temp1.Levtid_Ö,
dbo.Orderposreg.Levtid_L = dbo.Temp1.Lev_kvant,
dbo.Orderposreg.Inköpskostnad = dbo.Temp1.Inköpskostnad,
dbo.Orderposreg.Vikt_tol = dbo.Temp1.Vikt_tol,
dbo.Orderposreg.AntalSt = dbo.Temp1.AntalSt,
dbo.Orderposreg.Spec_nr = dbo.Temp1.Spec_nr,
dbo.Orderposreg.Spec_utgåva = dbo.Temp1.Spec_utgåva,
dbo.Orderposreg.ID_Beräknad = dbo.Temp1.ID_Beräknad,
dbo.Orderposreg.Bredd = dbo.Temp1.Bredd,
dbo.Orderposreg.Tjocklek = dbo.Temp1.Längd,
dbo.Orderposreg.ValsnGrad_kod = dbo.Temp1.ValsnGrad_kod,
dbo.Orderposreg.Kant_Kod = dbo.Temp1.Kant_Kod,
dbo.Orderposreg.Planhets_kod = Temp1.Yt_kod,
dbo.Orderposreg.LevForm_kod = dbo.Temp1.LevForm_kod,
dbo.Orderposreg.Rakhets_kod = dbo.Temp1.Rakhets_kod,
dbo.Orderposreg.BreddTol_kod = dbo.Temp1.Breddtol_kod,
dbo.Orderposreg.TjocklTol_kod = dbo.Temp1.TjockTol_kod,
dbo.Orderposreg.LängdTol_kod = dbo.Temp1.LängdTol_kod,
dbo.Orderposreg.Stål_kod = dbo.Temp1.Stäl_kod,
dbo.Orderposreg.TotaltSek = dbo.Temp1.TotaltSek,
dbo.Orderposreg.Tullstatnr = dbo.Temp1.Tullstatnr,
dbo.Orderposreg.Fakturera = dbo.Temp1.Fakturera,
dbo.Orderposreg.Leveransstatus = dbo.Temp1.Leveransstatus,
dbo.Orderposreg.Momsbelopp = dbo.Temp1.Momsbelopp,
dbo.Orderposreg.Total_inkl_moms = dbo.Temp1.Total_inkl_moms,
dbo.Orderposreg.KloMPS = dbo.Temp1.KloMPS,
dbo.Orderposreg.ShopFloor = dbo.Temp1.ShopFloor,
dbo.Orderposreg.Status = dbo.Temp1.Status,
dbo.Orderposreg.Stålkod = dbo.Temp1.Stålkod,
dbo.Orderposreg.Kontonyckel = dbo.Temp1.Kontonyckel,
dbo.Orderposreg.PlanLevDat = dbo.Temp1.PlanLevDat,
dbo.Orderposreg.Legtillägg = dbo.Temp1.Legtillägg,
dbo.Orderposreg.StålGrp = dbo.Temp1.StålGrp,
dbo.Orderposreg.KundNr = dbo.Temp1.KundNr,
dbo.Orderposreg.FKundNr = dbo.Temp1.FKundNr,
dbo.Orderposreg.Godsmärke = dbo.Temp1.Godsmärke,
dbo.Orderposreg.LagerMtr = dbo.Temp1.LagerMtr,
dbo.Orderposreg.Sortkvant = dbo.Temp1.Sortkvant,
dbo.Orderposreg.Sortpris = dbo.Temp1.Sortpris,
dbo.Orderposreg.KundsProdNr = dbo.Temp1.KundsProdNr,
dbo.Orderposreg.Godsmärke2 = dbo.Temp1.Godsmärke2,
dbo.Orderposreg.RegDatum = dbo.Temp1.RegDatum,
dbo.Orderposreg.PlanBetDag = dbo.Temp1.PlanBetDag,
dbo.Orderposreg.Säkring = dbo.Temp1.Säkring
where dbo.Orderposreg.Ordernr_o_pos = dbo.Temp1.ordernr_o_pos
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error updating - Error number '+@error+'. Rolling back'
--this reverses your updates
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows updated'
insert into dbo.Orderposreg
(Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring)
select
Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring
from dbo.Temp1
where dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from dbo.Orderposreg)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error inserting - Error number '+@error
print ' Rolling back updates and inserts'
--this reverses your updates and inserts
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows were inserted'
--this saves your data
commit
exitpoint:
Thanks a million for somebody who helps me!!!
Hi !
I have been worked with VC++, MS SQL SERVER, Transact-SQL for
3 years. I made an axtended stored procedure (xp_test) which returns
an recordset.
From Query Analizer, I can see the recordest : exec xp_test
I want to make an User Defined Function - MyTest which return
the recordset that it is returned by xp_test after its execution.
Something like that :
CREATE function dbo.MyTest ( )
RETURNS @table ...
AS
BEGIN
exec master.. xp_test table1 output -- can I do this ?
RETURN table1
END
Table and table1 are the same design.
Thank you very much !
Hello,
I am using Enterprise Library Data Access and SQL 2005 application block and I need to know if it is possible to do the following:
Consider I have a String:
Dim MyString As String = "Hello"
or an Integer:
Dim MyInteger As Integer = 100
or a class which its properties:
Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100
Or even a control:
Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass"
Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?
Something like:
Define something (Integer, String, Class, Control, etc)
Save in SQL 2005 Database
Later in code Retrive from database given its ID
Is this possible?
How should I do this?
What type of SQL 2005 table field should be used to store the information?
Thanks,
Miguel
Is there a expert here on full text indexing on 2005 sql server express????
View 8 Replies View RelatedI do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
View 15 Replies View RelatedHi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system.
But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine.
Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID.
My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?)
My Second question, what is the best way to design this kind of structure to get the highest performance benifit?
All kind of thoughts will be appreciated much! thanks
Hi,
I have a table describing items with 3 rows:
ID : its an identity Primary key
Type : nvarchar string for itemtype
ItemData : numeric data for an item
I would like to create a query to get the results where each returned row contains
- Type
- number of items for the given type
- concatenated string of the ItemData numbers for all items for the given type
ex: Items
1,"Big",1
2,"Big",56
3,"Small",45
4,"Big",22
Expected result:
"Big",3,"1 56 22"
"Small",1,"45"
I started with this query:
SELECT Type , COUNT(Type) AS Amount FROM Items GROUP BY Type
but i cannot figure out how to do the string concatenation (its like SUM but we dont need to add the values but we need to concatenate). I was thinking some stored procedure but this query will run on Compact SQL so thats not possible according to Compact SQL specs. Can any SQL expert help, how to do this, or is it possible to do anyhow?
Thanks for the help.
First i am designing a small database fo similar types of books ( Stories table , Short stories table , ...etc ) so i the columns in each table of them is similar , so instead of using a Stored Procedure for every table to select the TOP book i used one SP with variable table name and it works well as i pass the table name from the page , here it is :
---------------------------------------------
CREATE PROCEDURE Top1
@tblname Varchar(255)
AS
EXEC ( 'SELECT TOP 1 *
FROM ' + @tblname + '
WHERE Status="OLD"
ORDER BY ID DESC')
GO
-----------------------------------------------
The Problem now that i want to make the same thing with the INSERT STATEMENT to input new books to the table by passing the table name from the page like the SELECT one , and i `ve tried this one but gives me errors :
--------------------------------------------------------------------------------------------------
CREATE PROCEDURE AddNewStory
@tblname varchar(50),
@title varchar(50),
@Content varchar(5000) ,
@Picture varchar(200) ,
@date datetime
AS
EXEC('INSERT INTO' + @tblname + '( Sttitle , StContent, StPic, StDate )
VALUES ( '+ @Title + ',' + @Content+ ',' + @Picture+ ',' + @date + ')' )
GO
----------------------------------------------------------------------------------------------------
& th error is
Server: Msg 128, Level 15, State 1, Line 2
The name 'Dickens' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Stored Procedure: db1sql.dbo.AddNewStory
Return Code = 0
SO PLEASE HELPPPPPPPPPPP
Is anybody out there using SQL Expert Pro? Can you give me some feedback please?
I've been evaluating it for two weeks now and am very pleased with the results but I'm a bit hesitant to spend $5000.
Thanks!
Pete Karhatsu
Idea here is to output results of a stored proc to a text file..
I am trying to use this query to execute a stored proc which in turn accepts 3 parameters. Can some one help me as I am getting syntax errors when I try this way:
EXEC sp_querytofile 'MyDB', "exec sp_myproc ''11/03/04'', ''ABK'', ''TFC'", 'c:sp_myproc_out.txt'
I tried different combination of quotes but couldn't make it work! Any one to help?
--------code for sp_QueryToFile---------
Create PROCEDURE sp_QueryToFile
( @db sysname,
@query VARCHAR(1000),
@file VARCHAR(255)
)
AS
SET NOCOUNT ON
SET @Query = 'SET NOCOUNT ON ' + @Query
EXECUTE ('master..xp_cmdshell ''osql -w8000 -S sqldev14corpappsdev -r -s" ", -h-1 -o' + @file + ' -d'+ @db + ' -Q"' + @query + '" -E''')
/* Procedure Ends */
Hi,
I posted last year when we were having problems with a new SQL box we had moved to here...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74650
...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.
The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.
Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.
Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.
I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?
We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.
I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.
If this post breaks any rules please let me know.
Cheers,
Tem
I have two tables - T_Assets and T_Inspections.
I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL:
SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection)
FROM T_Assets
INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID
GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this.
Jagdip
hello My SP is as follows
CREATE PROCEDURE SP_ProcessAdminResults(@UserID nvarchar(100)) As
Declare @ReqID as integer;
Declare @Assaignee as integer;
Declare @TechName as nvarchar(200);
Declare @OpenDate as datetime;
Declare @GroupID as integer;
Declare @DateActiontaken as datetime;
Declare @AssignedDate as datetime;
Declare @ActionDays as integer;
Declare @OpenDays as integer;
declare GroupCursor cursor local scroll static for select [ReqID],[Assignee],FullName,[OpenDate],[GroupID],[DateActionTaken] from V_ProcessRequestMaster where UserID=@UserID and [Status]<>'CL' order by ReqID
open GroupCursor
fetch first from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken
while (@@fetch_status <>-1)
begin
select @AssignedDate= min(DateLogged) from LOGMASTER where
analyst=@Assaignee and reqMasterID=@ReqID and UserID=@UserID and Type='INIT'
If @AssignedDate is null
Begin
select @AssignedDate=dbo.GetActionDate(LTRIM(Rtrim(@TechN ame)),@ReqID,@UserID)
End
If @AssignedDate is null
BEGIN
set @ActionDays=0
END
Else
BEGIN
If @GroupID=438030
BEGIN
set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,5 ,6)
set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),5,6)
END
Else
BEGIN
set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,1 ,7)
set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),1,7)
END
END
If @ActionDays<>0 set @ActionDays=@ActionDays-1
If @OpenDays<>0 set @OpenDays=@OpenDays-1
update requestmaster set DateAssigned=@AssignedDate, ActionDays=@ActionDays, OpenDays=@OpenDays where ReqID=@ReqID and UserID=@UserID
fetch next from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken
end
GO
The master table contains like 600,000 odd records and when i put a cursor and process the results where i need to get the business days between assigned date and action taken date the query take lot of time and eventually the front end crashes saying Time OUT expired.
Looking for an expert solution to simplify that query.
I have asked a similar question on SQLTemp.com, but I thought I would ask here in case there are people here that don't visit SQLTeam and can help me.
I have to tables:
tbSiteworkPriceList
ItemID | Descr | Material | Labour | Travel | Boarding
-----------------------------------------------------
1 | Finishing | 0.25 | 28 | 42 | 65
tbSiteworkCostTypes
CostTypeID | Descr
-------------------
1 | Materials
2 | Labour
3 | Travel
4 | Boarding
Now, the tabels above are a sitework price list for an estimating package. The tbSitworkCostTypes table is used to trace the value of the particular item back to an account after the item is added to a takeoff. My questions are:
1) How should I tie each items variable (Material, Labour, Travel, Boarding) to its appropriate cost type?
2) Should I divide the table tbSiteworkPriceList above into 2 tables as shown below?
tbSitworkPriceList
ItemID | Descr
1 | Finishing
tbSiteworkCostItems
fkItemID | fkCostTypeID | Price
-------------------------------------------
1 | 1 | 0.25
1 | 2 | 28
1 | 3 | 42
1 | 4 | 65
This seems to be a little better in the point of view that each item variable is linked to the appropriate cost item by joining the tables tbSiteworkCostItems to tbSiteworkCostTypes. They have to be linked because after the takeoff is generated, the dollar values have to be imported into an accounting system where the account code and cost type code are determined by the "CostTypeID". Any thoughts?
Mike B
i've a database where relations are hold in a special way which the projectleaders think of as "performant and uncomplicated" but which is veryquestionable to me:------------------------------------------------Table [Attributes]Fields [AttributeID] and [AttributeText]Table [Objects]Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)in AttributeIDs there is a backslash separted list of Attribute-IDs like'3412278'so to get 20 object with a special attribute (which we need often) we doSELECT TOP 20 *FROM ObjectsINNER JOIN AttributesON (Objects.AttributeIDs LIKE ('%' + (CAST AttributeID AS varchar) + '\%'))ORDER BY ObjectTextps: to store data we need for communication we include a dozen of fields in*every* table and its content makes about 100 bytes/record------------------------------------------------i would do this stuff with a table to store the object/attributecorrelations.could someone tell me if that stuff makes any sense to an expert and how tovaluate it in regard of performance(we have big customers where that *is* anissue), design, scalability, pragmatism and sense ;)thanks in advance,ViperDK
View 1 Replies View RelatedHi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
I know, it's a job posting site, but our company is urgently looking for an expert senior level sql server DBA who can be rated as 9.5 out of 10 in sql server dba activities who are hard to find! Top hourly pay on a long contract in East Coast! so anyone interested? please rush resume to MessageDBfan@yahoo.com
View 1 Replies View Related