I am working on a project, which involves displaying trends of certain aggregate values over time. For example, suppose we want to display how the number of active and inactive users changed over time.
One issue is how to store historical data. First of all, should I create a separate database for each historical snapshot or should I use one database for all snapshots? Second, our database size is a couple of gigabytes and replicating the entire database on a daily basis is not feasible. An alternative solution is to back up aggregate values, but how do I back up results of aggregate queries, where the user can specify a date range in the WHERE-clause? Another solution is to create fact tables from our relational schema and back those up.
Another issue is how to query historical data. Using multiple databases to store historical snapshots makes it harder to query.
As you can see there are several design alternatives and I would like to know how this sort of problem is generally solved in the industry. Does SQL Server provide any support for solving this problem?
From what I've read this is called 'slowly changing dimensions'. Bassically the system I'm working on needs to store the history of certain data so that at any time a user can look up an old project and view it exactly as is, even though the associated parts might have had certain changes over time. From what I can tell Type 2 ( current and historical records are stored in the same table) seems to be the most popular. Type 4 (current records in one table and historical records in a seperate history table) seems like it would also work but I've been unable to find any articles comparing the two. Does anybody have any info on the dis/advantages of one v.s. the other?
I 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
Hi, all. I have a question about how you would design this. I have to return a list of projects in a stored procedure with a statement like this: SELECT ProjectID, ProjectName FROM PROJECTS; This populates a DropDownList with all possible projects. When the user selects one, I need to find out more information about that project, like this: SELECT a.ProjectID, a.ProjectName, b.OtherStuff FROM PROJECTS a INNER JOIN OTHERSTUFF b on a.ProjectID = b.ProjectID; In the old ASP world, we just filled the ProjectID parameter with NULL if we wanted to return all results, and the stored procedure was set up to dynamically return results based on whether that parameter was NULL or not. I'm looking for a more elegant way to do this. I can use two stored procedures, but I'm not sure what to name them. Obviously, I could call one 'SelectProjects' and the other 'SelectProject', but that just doesn't seem elegant enough. Anyone have any other ideas?
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
I'm new to database design and i need som advice concerning the design of my tables. What I want to accomplish is this: A attendace tracking system that allows tracking of meeting attendance. The project manager is setting up a meeting and after the meeting was held he/she reports who in the project team was at the meeting. It should then be possible to track the meeting attendance of the different team members. I have a table containing the data for the team members and i thought of adding an "attended meetings" field but the field is actually a number of fileds depending on how many meetings the person has attended so far. How do I accomplish the described functionality? Can someone pleas give me some advice Thanks
I need some advise on how to create a historical database.
What is the best way of doing this? For example, should I create a new row if a column is changed in a row and Time Stamp all record? What happens when I have child tables link to a Header table?
I have been looking on the NET for methods of creating a historical database, but I cant find any.
A general data design question:We have data which changes every week. We had considered seperatinghistorical records and current records into two different tables withthe same columns, but thought it might be simpler to have them alltogether in one table and just add a WeekID int column to indicatewhich week it represents (and perhaps an isCurrent bit column to makequerying easier). We have a number of tables like this, holding weeklydata, and we'll have to query for historical data often, but only backthrough the last year -- we have historical data going back to 1998 orso which we'll rarely if ever look at.Is the all-in-one-table approach better or the seperation of currentand historical data? Will there be a performance hit to organizing datathis way? I don't think the extra columns will make querying too muchmore awkward, but is there anything I'm overlooking in this?Thanks.
We have a database that adds over 100,000 records per day. This goes back to 2002 and I only need historical data for 6 months. Presently we can only can delete 1000 row at a time. Is there a faster way of deleting. We seem to continuely run out of disk space. Urgent!!!!!!!!!!!
I am trying to design a database for storing trule/false questions. I have no need to store any thing like chapters are topic. I want to just design a bare minimum tables for just storing questions and its responses. So does the following tables and fields suffice to achieve that?.
Questions_table(id(autogenerated),course_id,Questi on(text type)) id field uniquely identify each question, course_id(ex:CRS235) is used to identify which course a particular question belongs to, and Question filed store actual question.
Response_table(id,response) Response field is of int type, 1 for correct answer, 0 for wrong answer. I am not storing options for each question because every question has same options that is True/False.
So, will this work. Please advice and I would appreciate if any one can point me to a good resource on the web.
I have a SQL2005 db for tracking the prices of products at multiple retailers. The basic structure is, 'products' table lists individual products, 'retailer_products' table lists current prices of the products at multiple retailers, and 'price_history' table records when the price of a product changes at any retailer. The prices are checked from each retailer daily, but a row is added to the 'price_history' only when the price at the retailer changes.
I have the following query to retrieve the price history of a given product at multiple retailers:
SELECT price_history.datetimeofchange, retailer.name, price_history.price FROM product, retailer, retailer_product, price_history WHERE product.id = 'b486ed47-4de4-417d-b77b-89819bc728cd' AND retailer_product.retailerid = retailer.id AND retailer_product.associatedproductid = product.id AND price_history.retailer_productid = retailer_product.id
This gives the following results:
2008-03-08 Example Retailer 22.3 2008-03-28 Example Retailer 11.8 2008-03-30 Example Retailer 22.1 2008-04-01 Example Retailer 11.43 2008-04-03 Example Retailer 11.4
The question(s) I have are how can I:
1 - Get the price of a product at a given retailer at a given date/time For example, get the price of the product at Retailer 2 on 03/28/2008. Table only contains data for Retailer 1 for this date, the behaviour I want is when there is no data available for the query to find the last data at which there was data from that retailer, and use the price from that point - i.e. so for this example the query should result in 2.3 as the price, given that was the last recorded price change from that retailer (03/08/2008).
2 - Get the average price of a product at a given retailer at a given date/time In this case we would need to perform (1) across all retailers, then average the results
I've got a customer who wants reproducible/historical reporting. The problem is that the underlying data changes.
I tried to explain that this can't be done (can it?), but he doesn't understand.
To illustrate the situation - Let's say a teacher wants to track spelling test scores for her students. The below are scores for students A, B, and C (for January, February, March)
A: {70,80,85} B: {70,65, 80} C: {100,90,100}
So, I can generate a historical report that charts the class average and student trend - that's pretty easy.
Now, in April, we find that the school board has mandated that the British spelling of words is ok, so now the cumulative scores (for January, February, March, April)
He wants a report showing the January average as (70+70+100)/3 = 80, when really it is (90+80+100)/3 = 90.
Now imagine that there are actually thousands of data points changing like this... Now also imagine that we add and remove students on a regular basis...
He and his office manager get frustrated when I explain that the reports are not simple - in their mind it is. They have determined the solution is to get a report writer and buy Crystal Reports... I've tried to explain that the problem is that the report specification is unclear (basically - they don't understand what they want). The situation is ok for now, I'm just trying to plan for when they figure out that buying Crystal Reports won't change their situation (except they are done several thousand dollars)...
I have a database which contains time series data (historical stock prices) which I have to search for patterns on a day to day basis. But searching this historical data for patterns is very time consuming not only in writing the complex t-sql scripts but also executing them.
Table structure for one min data: [Date] [Time] [Open] [High], [Low], [Close], [Adjusted_Close], [MA], [DI]..... Tick Data: [Date] [Time] [Trade] Most time consuming queries are with lots of inner joins. So for example if I have to compare first few mins data then I have to do inner join like: With IntervalData AS ( SELECT [Date], Sum(CASE WHEN 1430 = [Time] THEN [PriceRange] END) AS '1430', Sum(CASE WHEN 1431 = [Time] THEN [PriceRange] END) AS '1431', Sum(CASE WHEN 1432 = [Time] THEN [PriceRange] END) AS '1432' FROM [INDU_1] GROUP BY [Date] ) SELECT [Date] ,[1430], [1431], [1432], [1431] - [1430] As 'Range' from IntervalData WHERE ([1430] > 0 AND [1431] < 0 AND [1432] < 0) OR ([1430] < 0 AND [1431] > 0 AND [1430] > 0) ------------------------------------------------------------------------ select ind1.[Time], ind1.PriceRange,ind2.[Time], ind2.PriceRange from INDU_1 ind1 INNER JOIN INDU_1 ind2 ON ind1.[Time] = ind2.[Time] - 1 AND ind1.[Date] = ind2.[Date] where (ind1.[Time] = 2058) AND ((ind1.PriceRange > 0 AND ind2.PriceRange >0) OR (ind2.PriceRange < 0 AND ind1.PriceRange < 0)) ORDER BY ind1.[Date] DESC; Is there anyway I can use Sql 2005 Data mining models to make this searching faster?
My application is to capture employee locations.Whenever an employee arrives at a location (whether it is arriving forwork, or at one of the company's other sites) they scan the barcode ontheir employee badge. This writes a record to the tblTSCollected table(DDL and dummy data below).The application needs to be able to display to staff in a control roomthe CURRENT location of each employee.[color=blue]>From the data I've provided, this would be:[/color]EMPLOYEE ID LOCATION CODE963 VB002964 VB003966 VB003968 VB004977 VB001982 VB001Note that, for example, Employee 963 had formerly been at VB001 but wasmore recently logged in at VB002, so therefore the application is notconcerned with the earlier record.What would also be particularly useful would be the NUMBER of staff ateach location - viz.LOCATION CODE NUM STAFFVB001 2VB002 1VB003 2VB004 1Can anyone help?Many thanks in advanceEdwardNOTES ON DDL:THE BARCODE IS CAPTURED BECAUSE THE COMPANY MAY RE-USE BARCODE NUMBERS(WHICH IS DERIVED FROM THE EMPLOYEE PIN), SO THEREFORE THE BARCODECANNOT BE RELIED UPON TO BE UNIQUE.THE COLUMN fldRuleAppliedID IS NULL BECAUSE THAT PARTICULAR ROW HAS NOTBEEN PROCESSED. THERE ARE BUSINESS RULES CONCERNING EMPLOYEE HOURSWHICH OPERATE ON THIS DATA. ONCE A ROW HAS BEEN PROCESSED FORUPLOADING TO THE PAYROLL APPLICATION, THE fldRuleAppliedID COLUMN WILLCONTAIN A VALUE. IN THE PRODUCTION SYSTEM, THEREFORE, ANY SQL ASREQUESTED ABOVE WILL CONTAIN IN ITS WHERE CLAUSE (fldRuleAppliedID IsNULL)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOINSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB001', '2005-10-18 11:59:27.383', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB002', '2005-10-18 12:06:17.833', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB001', '2005-10-18 12:56:20.690', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB002', '2005-10-18 15:30:35.117', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB003', '2005-10-18 16:05:05.880', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 11:52:28.307', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB002', '2005-10-18 13:59:34.807', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 14:04:55.820', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB003', '2005-10-18 16:10:01.943', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB001', '2005-10-18 11:59:34.307', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB002', '2005-10-18 12:04:56.037', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB004', '2005-10-18 12:10:02.723', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (977, 'VB001', '2005-10-18 12:05:06.630', 96879)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (982, 'VB001', '2005-10-18 12:06:13.787', 96697)
I want to load historical data from an old system into a new one.Thing is, that old system stored dates as Datetime and the new one uses DateTimeOffset.
All data was collected in the same Time Zone... but with the Daylight Saving Time (DST)
The offset is either +04:00 or +05:00, based on the calendar date. To add to the complexity, the rules for DST changed a couple of years ago.
To determine the offset, I'd need to know what was or would have been the server Timezone for each historical date.
Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.
I'm looking to get counts on historical data where the number of records exists on or after May 1 in any given year. I've got the total number of records for each year worked out, but now looking for the number of records exist after a specific date. Here's what I have so far.
SELECT p.FY10,p.FY11,p.FY12,p.FY13,p.FY14,p.FY15 FROM ( SELECT COUNT(recordID) AS S, CASE DateFY
I am getting ready to start a project where I am charged with moving out old data from production into a newly created historical DB. We have about 8 tables that are internal audit tables, that are big and full of old data. These tables are barely used and are taking up way too much space and time for maintenance.
I would like to create a way (SSIS?) to look at the date field in each of the 8 tables and copy out anything older than two years into my newly created history DB. Then deleting the older records from the source DB.
I don't know if SSIS is the best method to use. If it is, what containers to use to move over data, then how to do delete from source?
Can I do the mass deletes on my audit source tables without impacting performance/indexes/fragmentation?
Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.
A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.
SELECT MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10], MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10], MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11], MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11], MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],
[Code] ....
I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I click on Self Help while browsing, I want to see all items underneath Personal Development, Spiritual and Meditiation. So my question is is this a good way to store the product-category relationships, or should I put many entries into CategoryProducts to keep the queries simlpe and faster? Are they faster doing it this way? In this way there would be 4 entries for a product in meditation. My personal idea is that adding all entries up a tree arm of a category path will be cumbersome to manage, but it does solve the problem of clicking on Self Help and seeing all products that exist within sub-categories. I am sure an SQL query would be able to work this out, but I dont know if performance would be something to consider on an ecommerce site? Are there any patterns fo rthis stuff - seems a reasonably repeatable pattern for business sites?
Hey all. I have a query where I am basically querying an organizational chart. The table storing this information is basically a two column table of parent/child pairs. So you might have:
Querying this table should return all child columns that flow up to the top, so querying 1 would return 1, 2, 3, 4, 6, 7, 8, 10, 11.
I used a sample from MSDN (http://www.sqljunkies.com/Article/D7CAED46-CCAC-4FF7-B528-B2E9A274B71C.scuk) that does just this, except that when querying a value that returns more than 1000 records - I am experiencing way too long response times. The MSDN sample uses temp tables and inserts values into a temp table as it moves through the records and finds a match.
Anyone have an ideas on another way to accomplish the same thing? This is an important part of my security model as users that login should only have access to data that falls within their parent/child heirarchy.
Hi all, I've just started playing with data importing from Excel.
Since I know how to do linked servers, that's where I started. I created a link, querried the spreadsheet and got some strange results where data appearing in the spreadsheet returns as NULL in QA.
So, I noticed that I could use OPENDATASOURCE and OPENROWSET and tried each of them. They're returning the same data (in different column orders) also with strangely intermittant NULL values instead of the data that I see in the source spreadsheet.
I bet that this is something easy once you know what's going on. Anyone know what's going on?
Not that I think it'll matter, but the queries I'm using for these three cases are:
select * from EXCEL_A...EPIQ202$ where SUBSCRIBER_ID = '50664582103'
select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=e:EPIQ202.xls;Extended Properties=Excel 8.0')...EPIQ202$
select* from openrowset( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:EPIQ202.xls', 'SELECT * FROM [EPIQ202$]' )
Is there a way to query the name of the physical data file and/or log file associated with a given database. For example if I were to do an alter database: ALTER DATABASE X MODIFY FILE (NAME = xxxxx, size=10MB)
Is there something I can type in to extract the file name associated with 'X' or do I have to know it implicitly?
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!
Using a code snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by a snippet from the xml clob that I am pulling from.
select * from (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag from ws_transactions Where Wtr_Service_Tag In ('20458749610')
[Code] ....
This query is only able to pull the first value in the list.
I have two questions...
[1]How can I edit this query to pull all of the list items when there are more than 1? I have another field, in a separate table, that I can pull from to get that number.
[2]This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size." Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked' FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd)) AS VARCHAR) AS daysworked, ActivityId AS totalcalls FROM PhoneCall AS p WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb) AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error: Msg 7372, Level 16, State 4, Line 1 Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723 Linked Server Script: /****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true' I've tried searching the Internet for solution but yielded no results. Can anyone help?
