I have four tables that need to be loaded into an ASP.NET application. They need to be loaded together into one result set and sorted. Is it possible to load four tables together and sort them using an SQL statement?
I am aware of and am using the SELECT ... ORDER BY feature of MSSQL in my present ASP.NET application to retrieve from single database tables. I'm using merged datasets and a sort method to solve the above problem at the moment.
i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?
before anyone even says it, i checked the collation order on everything and it's the same. i get the error when the snapshot is trying to be bulk copied to the subscriber.
i'm on sql2k sp4, server and db collations are SQL_Latin1_General_SP1_CI_AS. here's a repro. 1st, run this in a blank db:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Event_Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Event_Transactions] GO
CREATE TABLE [dbo].[Event_Transactions] ( [EventTransactionId] [int] IDENTITY (1, 1) NOT NULL , [OrphanedFlag] [bit] NOT NULL , [ProcessedFlag] [bit] NOT NULL , [ProcessedTimeStamp] [datetime] NULL , [EventTimeStamp] [datetime] NOT NULL ) ON [PRIMARY] GO
CREATE CLUSTERED INDEX [EventTransactions_IDX_ProcessedOrphanedEventTimeSt amp] ON [Event_Transactions] ( [ProcessedFlag], [OrphanedFlag], [EventTimeStamp] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Event_Transactions] ADD CONSTRAINT [PK_Event_Transactions] PRIMARY KEY NONCLUSTERED ( [EventTransactionId] ) ON [PRIMARY] GO
Now, use transactional replication to replicate it to another db taking all the defaults. when the distribution agent tries to apply the snapshot, it fails with the message mentioned in the title..
Has anyone ever seen this? It's keeping us from considering MS replication for one of our major products. Thanks.
I have a database that has entries that I want sorted by date order. Each entry has an auto ID number allocated (primary key auto sequencing), which I want to change to reflect the sorting (so the first date has the first auto ID number and so on).I've gone into the database and sorted the entries as I want them. Then I've gone into Design View to delete and restablish the primary key autosequence. However, it is not keeping the date order in the database (ie entry ID 3140 date is 12/06/2015, but 3141 is 02/02/2012). How do I get it to maintain the order?
"I meant to delete just one assignment, doing which was giving me difficulty.
I deleted a whole category by accident which had most of my daily grades in it for a course with quite a few of them. I was negligent in backing them up, so I cannot restore them. I have an XLS file from the midterm, but am missing about 6 grades after that.
Is there any way to recover the grades I had in the deleted category. It has been a couple of days since I entered any new grades. HELP!"
My current backup strategy is to do a full backup 5 pm, differentials every 2 hours, logs every 30 min, all so they arenot simultaneous. Backing these all to same backup device via three different jobs.
Then the server is backed up to tape shortly after the 5 pm full backup.
Obviously, I can't restore from the backup for this situation because it would hurt other data.
But - is there a way I can take last night's backup from tape and restore it to a different database than our production database? (Such a database doesn't at this time exist. Would I detach, copy over the files, and then reattach on prod and attach on the devl side, and then restore from the backup device to the devl copy?)
I've never had to perform this kind of rescue and am wondering if my backup strategy is flawed since it isn't immediately evident how I can easily do this.
I needed a similar thing from our Oracle DBA yesterday, and he went to tape, copied out a table and put it on the Oracle database under a different owner, and I easily fixed the few records that I had botched.
That's exactly what I need to do now with MS SQL 2000.
I was hoping someone would be able to help me, I trying to go through several databases, 111 to be exact, and get the data out of a particular table which is present in all the databases on the same server.
I know how to get the names of the databases from the INFORMATION_SCHEMA, but how can use the name for it to cycle through all the databases and get data out of the one particular table.
Hi, I need some help. I have this query where i need to pull up students that have more than or equal to 3 absences in a row. Now I am getting all those students that are absent but not in a row. But i was wondering if there is a way to tell if a student was absent three days in a row . There is a date field that is being used to identify the day of the class and a status field that identifies if the student was absent or present. Please help someone.
Up to the moment I've got enough knowledge for read data stored into .LDF files by dbcc log and so on. It's very useful and interesting. Now, I wonder how to retrieve the same information but on MDF files.
At first, I want to make sure that is not possible by mean of traditional methods (dbcc or something like that) I suppose so but I'd like to hear opinions regarding that.
Thanks in advance for any idea, though or further information.
this is a simple problem but it's just driving me mad as it's not reading from my dB. basically, I've have reviews stored in my dB and want to display them in a textbox by clicking on a button called btnReviews. I think the problem might be that there is too much text stored per row of the table (as it is a review), but I have the datatype set as text in sql. here's the simple un-errorred code I have behind the button. any ideas where I went wrong. i've a feeling it's something small but it's just taken too long to figure out.protected void btnReviews_Click(object sender, EventArgs e) { String strConn = ConfigurationManager.ConnectionStrings["conLocalDatabase"].ConnectionString; SqlConnection dbConnection = new SqlConnection(strConn); SqlCommand dbCommand = new SqlCommand("Select [ReviewC] From [Review])", dbConnection); dbCommand.Parameters.AddWithValue("Review", txtReviewView.Text); try { dbConnection.Open(); dbCommand.ExecuteNonQuery(); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { if (dbConnection != null) { dbConnection.Close(); } } }
Hi I ve a datagrid . And Two Database table in sqlServer2005. The name of the tables are 'Property' and 'userid'. My datagrid wants to retrive all records from Property table and one record from userid table. The Property table contains Propertycode, lastdate , departmentname. The userid table contains so many record along with 'id' record which my datagrid wants to retrieve. pl tel me how 2 write code for that?
I'm preparing a report that will display provider number, provider name, and a single field that will show all the counties that specific provider serves. I realize from researching the coalesce posts that this can be done. However, when I try to retrieve data in the same select statement as my coalesce, I get the error: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."The listing of the counties must be specific to the provider, so my original code was:
DECLARE @Counties varchar(1000) SET @Counties = '' SELECT a.PROV_ID, a.PROV_NAME, @Counties=coalesce(@Counties,'') + b.COUNTY + ',' from ECBH.dbo.tbl_PROVIDERS a inner join ECBH.dbo.tbl_Provider_Serv_Regions c on a.PROV_ID = c.PROV_ID inner join ECBH.dbo.tbl_Regions b on b.REGION_ID = c.REGION_ID where c.PROV_ID = a.PROV_ID and a.MASTER = 1
I thought about creating a table to hold the coalesced values (need to coalesce two other fields as well), but wouldn't an insert to a table fail for the same reason?The counties table does not relate to the provider table, but does relate to a provider_county table (which in turn relates to the provider table).
Thank you for reading this. I am faced with a problem where there is no storage of deleted or updated information in an OLTP DB. I am mostly concerned about retrieving the deleted data from a table when an update occurs so as to make the deleted data show as an error. Is there a way to retrieve these kind of data from a DB, if so how? Can these data be retrieved by dates? Also how much time do you have before the data can no longer be retrieved? Assuming that the data can be retrieved.
I know the good stuff about datawarehousing but unfortunately we have no DW. Any help will be greatly appreciated.
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
I am working on a project on PocketPC in which it is required to reteive data from database. I have created database on simulator as .sdf file. I want to retreive data from eVC++ code. How i can do so?
Hi to all,I made a database using SQL Server 2005 and now I want to interact with that database through VC++ 2005. Is that possible?? If so, how can I do it?
I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.
I have been thinking of using SQL server 2005 as i would like the flexibility i get through UDT. Retrieving the UDT data in managed could is ok but i would like to retrieve it in non .NET languages too.
For example lets say i create a UDT "Point". I insert data in a table that has some columns of type point.
Now is there a way i can get the data of the type point in a point object in non .NET languages like perl, python...
I am using Visual Studio 2008 with .NET 3.5 Visual Basic. I'm having a problem retrieving data from my SQL CE database. I have a function that imports a file, parses it and writes the contents to a database. When I then query the database from my code, the data I have just written doesnt seem to be there. Even when i close and re-run the program. Only when I first view the table contents through Visual Studio then re-run the program does the data show up.
The things I know are. When I insert data there is no problem. I can view the data through visual studio server explorer. only when I view data through the server explorer can my code retrieve it.
Could this be something to do with caching? Any help would be appreciated. The (stripped down) code I use to insert data is below
kevin
Dim rowsAffected = 0
Dim connection As New SqlServerCe.SqlCeConnection(connectionstring) Dim transaction As SqlCeTransaction
' command objects for inserting a ContactList and retrieving its last identity Dim cmdInsertContactList As New SqlServerCe.SqlCeCommand("INSERT INTO ContactLists (Name, FolderID) VALUES (@Name,@FolderID)") cmdInsertContactList.Parameters.Add("@Name", SqlDbType.NVarChar) cmdInsertContactList.Parameters.Add("@FolderID", SqlDbType.NVarChar)
' insert a new list cmdInsertContactList.Parameters("@Name").Value = list.Name cmdInsertContactList.Parameters("@FolderID").Value = folder.ID rowsAffected += CInt(cmdInsertContactList.ExecuteScalar())
I attach my SQL Server Express data file to my host. I would like to copy all of my member information back to my local computer. How can I do this? My host won't allow my to physically copy the data file over. My host is discountasp.net. Thanks,Jeff
Hi, I am using visual web developer 2005 express edition and Microsoft SQL 2005 to develop a fast-food ordering website and I am having trouble in retrieving data from a database table into a form. Can some one please teach me the way to write the syntax for retrieving or selecting a inserted value from the database? I have only know the syntax to insert data value from a form which is something like this: 'Create a New Connection to our daabase Dim test As SqlDataSource = New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("Connectionstring1").ToString 'This is the SQL Insert Command test.InsertCommand = "Insert into Customer([Initial],[Correspondent_Name],[Correspondent_No],[Payment_Type]) VALUES (@Initial,@Correspondent_Name,@Correspondent_No,@Payment_Type)" 'Each of this insert a value into the appropriate command test.InsertParameters.Add("Initial", DropDownList4.SelectedValue) 'This is the selected "Initial" DropDownList value test.InsertParameters.Add("Correspondent_Name", TextBox10.Text) 'This is the Correspondent_Name value test.InsertParameters.Add("Correspondent_No", TextBox3.Text) 'This is correspondent_no value test.InsertParameters.Add("Payment_Type", "Pay upon delivery") 'This indicates that the payment is made with credit card test.Insert() But I have no idea the syntax to retrieve a data. Please help me with this matter as it is important to me. Thanks in advance!
Heres my requirement from a financial analysis im doing...I have just calculated an industry averages on financial ratios...Now i wanna upload this industry average to the system...so that I can compare it to the individual companies' averages after calculating a particular company's average; meaning i wanna be able to call the industry average of a particular ratio (eg Current Ratio) after calculating a company's corresponding ratio...Is there a code fragment i can use for this ?? Thanks in advance...Adam
Hi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC Below is my SQL statement; SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
Hi I hop i make sense with this, i have a page where you can update a products price based on the year, so for instance some products will still have prices set in 2007, while some may have prices which will be set in 2008, what i am trying to do is if the price has been set in 2008 it shows this value. The fields in the table which control the values are Rate(controls 2007), and Rate2007Period2. What i am trying to do is if Rate2007Period2 is null or 0, then display the 2007 rate, and if Rate2007Period2 is greater than 0 then display that value. Here is the view which displays the rates; SELECT dbo.tblRateSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblRateSchedule.DESCRIPTION, dbo.tblRateSchedule.SWT, dbo.tblRateSchedule.Rate, dbo.tblRateSchedule.Tstamp, dbo.tblRateSchedule.RateSchedule_ID, dbo.tblRateSchedule.RateScheduleUnit_ID, dbo.tblRateSchedule.RateScheduleType_ID, dbo.tblRateSchedule.WorkType_ID, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblRateSchedule.Work_Type_Description AS Expr1, dbo.tblRateSchedule.Rate_Active, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2FROM dbo.tblRateSchedule INNER JOIN dbo.tblHistoricalRateSchedule ON dbo.tblRateSchedule.MA_Code = dbo.tblHistoricalRateSchedule.MA_Code LEFT OUTER JOIN dbo.tblRateScheduleType ON dbo.tblRateSchedule.RateScheduleType_ID = dbo.tblRateScheduleType.RateScheduleType_ID LEFT OUTER JOIN dbo.tblWorkType ON dbo.tblRateSchedule.WorkType_ID = dbo.tblWorkType.WorkType_ID LEFT OUTER JOIN dbo.tblRateScheduleUnit ON dbo.tblRateSchedule.RateScheduleUnit_ID = dbo.tblRateScheduleUnit.RateScheduleUnit_IDWHERE (dbo.tblRateSchedule.Rate_Active = '1')
Hey, I need to retrieve info from a database and display it using a repeater control. No problems there! But, I need to add data before displaying it, and I don't mean add data to the database but rather to the repeater control. For eaxample: I have a simple database containing two fields: [date] and [event]. The repeater will display these events in a monthly view. That is: the repeater will have 31 rows and the events will be displayed next to the day it happens. Now if there's nothing happening a certain day then I need to add that day manually because it will not be bound, right! See my problem? In other words, how do i loop through a records when using the SqlDataSource? Thanks,Björn Andersson
Hi I hope i make sense this time around, I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
Code:
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID, dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID, dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID, dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category, dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID, dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE, CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage, dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0) AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID, ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1, dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate FROM
I am accessing data (YTD Hours for a selected employee) through the view "dbo_TIMS_HSTU_HoursHist_Insync" which is connected by ODBC to my application. The result "HSTUHoursPriorPeriods" is used to determine the rate of pay for a specific employee and is run literall each time a user enters a pay record at data entry.
Because the view itself returns about 1,000 records, before I select the specific record and associated Hours, it is a little slow from a data entry standpoint.
I understand I can achieve the same result through an SQL Procedure, but for the life of me I don't know how.
Hi this is my first post i am hoping its the write place. I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID, dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID, dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID, dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category, dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID, dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE, CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage, dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0) AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID, ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1, dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate FROM
For a particular application i retrieve records using stored procedure and bind the same to the data grid and display the same. The data are fetched from a table say A joined with couple of tables to staisfy the requirement. Now this table A has around 3700000 + records. The Select statement is as
Select column1, column 2, column 3...................... column 25 from table A
inner join hash table D on A.Column3 = D.Column3 and A.Column4 = D.Column4 and a.nColumn1 = @ParameterVariable Inner join table B on a.nColumn1 =b.nColumn1 inner join table C on A.column2 = C.column2
Indices defined on A are IDX1 : Column 3, Column 4 IDX2 : Column 1
Around 350 records per second are inserted into the table A during the peak operation time. During this time when executing the procedure, it takes around 4 mins to fetch just 25000 records.
I need to fine tune the procedure. Please suggest me on same.
Hi, I would like to select data from my source system based on fiscal years. I don't want to hard code anything, e.g. select * from person where startdate > 01042005. I want to try and use a store proc or function. The main requirement is to always retrieve data from source tables for the last two fiscal years (based on todays date - getdate). Our fiscal year starts the 1st April. Therefore, If todays date is 14 October 2007, I would like to select all persons whose start date was greater than 01 April 2005. Is there an easy way to do this in sql server 2005 without hard coding dates?
