Query Data Based On Moving Datetime.
I'm trying to query data from a database for a report that looks for the last 2 weeks starting at 10pm, taking a value once every 24 hours. Using
AND DateTime >= DateAdd(wk,-2,GETDATE())
AND DateTime <= GetDate()")
I easily get the last two weeks but the query obviously only grabs the data at the time the query runs. I need to be able to run it any time of the day but only grab the data at 10pm. I'm very new at this so please excuse my ignorance but I could really use some help with this. Thanks very much.
View Complete Forum Thread with Replies
Related Forum Messages:
Select Query Based On Datetime
I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today. In Oracle I would use: select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24. This would start at 8am yesterday and end at 7:59am today. How would I do this with T-SQL? thank you, Mark Fiesta_donald@email.com
View Replies !
Report Based On Filtered Query OR Based On Custom Query
adp on SLQ7 What would be wise to do. I'm creating a report based on two inner joined tables and i've got a total sum field for each line in the report. (Price * Ordered) This results in the following sql statement: Code: sqlStr = "SELECT *, [Table1].Ordered * [Table1].Price AS LineTotal FROM [Table1] INNER JOIN [Table2] ON [Table1].RecieptNumber = [Table2].Number WHERE ([Table1].RecieptNumber = " & varNumber & ")" I think I have a few options now. 1. Leave the varNumber out of the query, and save it as a regular query. And now filter the report on varNumber. 2. Write the constructed query with createquerydef to eg. "TmpQueryForReport" And set the reports recordsource fixed to TmpQueryForReport. 3. Set the recordsource of the report to constructed sqlStr on Report_open() --------------------- Option 1, I got this working but when the database grows (and it will) this might get awfully slow. Option 2 I had this working before i switched to using MSSQL server 7.00 After the switch i thought, this might be a problem with giving db access rights cause the users might need write rights to the db. Option 3. Fast and easy??? Option 4 Stored procedures???
View Replies !
Web Based Data Mining Query... Installation Issue..
Hey gang, I have ran into an installation issue that has been bugging me all weekend. I'm trying to run the preditive analytics query from a web based application (ASP.NET). I have installed a data pump as directed by this article "Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003" because this isn't set up under a domain. I also set appropriate roles in Data Mining Database (set it to everyone for testing purpose). ConnectionString is "MSOLAP.3;Persist Security Info=True;Data Source=http://1.0.0.1/olap/msmdpump.dll;Initial Catalog=DataMiningDB" Try Dim connection As AdomdConnection = Me.Connect Dim command As New AdomdCommand(txtQuery, connection) Dim adapter As New AdomdDataAdapter(command) Dim dataset As New DataSet ' Fill the dataset adapter.Fill(dataset) ' Bind to grid Return dataset Catch ex As Exception Throw ex End Try Return Nothing Exception Details: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. OLE DB error: OLE DB or ODBC error: Login failed for user 'VMSQL2006IUSR_VMSQL2006'.; 28000. It happens on the adapter.Fill(dataset). Any ideas? I have also tried to set the olap application pool to use different identity instead of "Network Services", but I always get the same error message (but with user I specified instead of I_USR). -Young K
View Replies !
Moving From Access To A Web Based Approach
Hi all. I am not a programer but managed to piece together an Access database that is the backbone of my company. My IT group advised me to move to an SQL server back end and move to a web based front end. My question. I am very comfortible with Access and modify the databases regularly. What front end is most like Access for modifying forms, reports, macros, etc. Thanks!
View Replies !
Sql Query To Retrieve Comments Based On Selected Month And Data
hi, i am generating report for my project. my table goes like ID VASID VASSID JanMail JanVisit JanPhone JanComment FebMail FebVisit FebPhone FebComment................. DecPhone 1 25 4 True False False Mail me False True False Visit me My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively.. once user check on Mail and select a month suppose January in my report,the report should generate only January Comments. here user can select any number of months.if user selects january,february and march, report should generate respective months Comments. Please help me in writing SQL Query to get the comments....... if any one know the solution for the above problem,Please help me Thanks & Regards,
View Replies !
Query To Select Data Based On Alphanumeric (surname) Information.
Hello, I am trying to write a query that will be able to select different segments of data based on spelling of the last name. For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ' then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on... I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way? Thanks
View Replies !
Sql Server Express Problem When Moving From VWD File Based To IIS
Hi All, We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test. However, when we get to a page that tries to access our Sql Sever Express database, we get the following error: An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Does anyone know how to overcome this problem? Any help appreciated. Claude.
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
SQL Query - Use Date Format Data And Match It To MS-SQL's Datetime Datatype?
Need some quick help here.. The data I got from the text file use the "04/11/2007" date format and the StockDate in MS-SQL use the datetime datatype. My understanding is that the "04/11/2007" will default to the 04/11/2007 12:00 am" format in MS-SQL. So, when I use the sql query, how do search only for the date part of the data in MS-SQL and match it to the data from text file? I tried this SQL Query below. --snip-- SELECT RawID FROM tblPurchaseRaw WHERE VIN = '" & sVin.ToString.Trim & "' AND StockDate = '" & dStockDate.ToString.Trim & "' --snip-- That way, if I get a row then I know the data is there. If I don't get a row then I know the data is not there. Bold: I get it now. It is all automatic as MS-SQL does it automatically...
View Replies !
How To Update Only The Year (yyyy) In The Datetime Data Type Using SQL Query?
Hi I have an SQL table that contains RowDateTime column and the data type is datetime the date looks like :- 2006-01-01 20:00:00.000 2006-01-01 20:01:00.000 2006-01-01 20:02:00.000 2006-01-01 20:03:00.000 2006-01-01 20:04:00.000 2006-01-01 20:01:00.000 2006-01-01 20:01:00.000 2006-01-01 20:01:00.000 I want to update the year to look like :- 2004-01-01 20:00:00.000 2004-01-01 20:01:00.000 2004-01-01 20:02:00.000 2004-01-01 20:03:00.000 2004-01-01 20:04:00.000 2004-01-01 20:01:00.000 2004-01-01 20:01:00.000 2004-01-01 20:01:00.000 How can I do it using the SQL Query
View Replies !
Datetime-Based Operations
I'm new to SQL Server and relatively new to database design and I have a specific problem I'm trying to resolve. I have a collection of records in a table (let's call them 'tasks') which represent some list of things that needs to be completed. Each task has an associated datetime on which this action is to commence, and my applications is responsible for executing these tasks. My first instinct is to regularly poll the 'tasks' table to determine if there are any tasks which have not been processed and are past their schedule start datetime. But, I'm wondering if there is some sort of database feature that would recognize that a task's starttime has arrived, and could somehow communicate this to my application without my application having to constantly poll the database. Thanks.
View Replies !
Implicit Conversion From Data Type Datetime To Int Is Not Allowed. Use The CONVERT Function To Run This Query.
Hey im trying to store a category name and the date into a database. For some reason i keep getting this error Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. This error is the error im getting back from the database. the datetime field in the database is a datatype (DateTime) so what exactly is going on ?protected void InsertNewCat_Click(object sender, EventArgs e) { string insertSql = "INSERT into Category (CategoryName,Date) VALUES (@Category, @Date)"; string conString = WebConfigurationManager.ConnectionStrings["ProCo"].ConnectionString; SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand(insertSql, con); cmd.Parameters.AddWithValue("@Category", NewCat.Text); cmd.Parameters.AddWithValue("@Date",DateTime.Now); try { con.Open(); int update = cmd.ExecuteNonQuery(); CatInsertStatus.Text = update.ToString() + " record updated."; }catch (Exception Err) { CatInsertStatus.Text = Err.Message; } finally { con.Close(); } }
View Replies !
Problem Updating A Record Based On A Datetime.
Hi People, hope someone can help me out here with a little problem. Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58" Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control. Below is how i get the string from the listbox control Dim except_time As DateTime except_time = DropDownList1.SelectedValue The expect_time is then passed to store procedure along with some other vars, which looks like this -- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData] -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- Insert statements for procedure here UPDATE exceptions SET validated = @validated, update_time = @update_time WHERE (except_time = @except_time)END So validated and update_time should be updated when except_time is equal to @except_time My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match. can anyone see any silly mistakes i'm doing here ????
View Replies !
SELECT From DateTime Field Based On Current Date
I am trying to match records that are >= the current date. I have tried using: SELECT DISTINCT name FROM table WHERE datefield >= DATEPART(month, GETDATE()) AND datefield >= DATEPART(day, GETDATE()) AND datefield >= DATEPART(year, GETDATE()) ORDER BY name but this is not giving me the result that I am looking for. What is the best way to match a DateTime field type using the current date without the time?
View Replies !
SQL DateTime Limit = Enemy Of Historical Based Websites
I'm trying to create a website based on interesting historical facts, Searchable by date. I need each fact entry to have a datetime field in the database, but unfortunately this method only lets me go as far back as the year 1753 (for an inexplicable reason, I can go up to the year 9999).Is there something I'm overlooking here, or did Microsoft goof this one? Will I have to store my dates as 3 separate custom month/day/year fields? If anybody has a solution for this, please let me know (a B.C./A.D enabled solution isn't at all needed, but I'm open to that too).
View Replies !
How To Convert UTC Based Datetime Values To Local Setting?
Hello: I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST). So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function. I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC. Any suggestions or links to code will be greatly appreciated! Thanks, Pete
View Replies !
SQL Query: Finding Records Between Datetime Inside Datetime
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table: ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time. Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View Replies !
Insert Adjusted Datetime Into Sql Field Based On User Locale
Is there a way to adjust a date and time depending on the users locale before inserting into the database? Users login in and their country of residence is stored in the database on signup. My server is in the US and I want to reset part of the users db record when it's the end of the day in their country so I need to enter the date and time into the db plus or minus the time difference, is there a function written to do this already or would i need to write my own?
View Replies !
X-Axis Scalar Mode Based On DateTime Field - Not Working
I have a chart showing quantity against time. I had been using X-Axis in category mode, but wanted to include all dates, so changed it to Scalar mode by checking "Numeric or time-scale values". However instead of displaying dates it simply shows integers, and to make things worse, Y-axis values have all come out as zero. It appears that the integer values are simply sequential values for each datapoint (i.e. 1,2,3,4 etc). The category field for the X-Axis is a field called "Date" which comes from a Date Type field in the cube based on a datetime column in the DSV. The properties of the category in the chart are : Group On =Fields!Date.Value and Label =Fields!Date.Value. Am I missing something obvious?
View Replies !
The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
View Replies !
The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
View Replies !
Stored Proc To Get Single Person From A Table Based On Earliest Datetime
Hi, I'm having problems with a stored procedure, that i'm hoping someone can help me with. I have a table with 2 columns - Username (varchar), LastAllocation (datetime) The Username column will always have values, LastAllocation may have NULL values. Example Username | LastAllocation ------------------------ Greg | 02 October 2005 15:30 John | 02 October 2005 18:00 Mike | <NULL> My stored procedure needs to pull back a user name with the following criteria: If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation Then update the LastAllocation column with GETDate() for that username. This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln? Thanks Greg ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ CREATE PROCEDURE STP_GetNextSalesPerson AS DECLARE @NextSalesPerson varchar(100) BEGIN TRAN IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0 BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC END ELSE BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC END SELECT @NextSalesPerson UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson COMMIT TRAN GO
View Replies !
Datetime Data Type Resulted In An Out-of-range Datetime Value. Please Help
Hi, I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date '31/08/2006 23:28:59' I get the error "...datetime data type resulted in an out-of-range datetime value" I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be? cheers, Ernest
View Replies !
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View Replies !
How To Use Value Calcuated In Query In Subsequent Query, All Based On Value In Querystring?
I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string. But, I don't know how to get a value returned from one sql statement into a second. Here's what I'm trying to do... From the QueryString, we know that the MatterID = 4 ( xxx.aspx?MatterID=4) Knowing that the Matterid=4, I query the database to get the OfficeId for that MID (Select OfficeID from tMatter where Mid=4) ~This returns an OfficeID of 6 So, then I need to do another query to get the employees where OfficeID = 6 (Select EmployeeID from tEmployees where OfficeID = 6) How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement?
View Replies !
&&"Slipt&&" Rows Based On Datetime
Hello! I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table: row1: initial_time: 2006-05-24 8:00:00 final_time: 2006-05-24 8:30:00 row2: initial_time: 2006-05-24 8:35:00 final_time: 2006-05-24 9:15:00 I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into: row2_a: initial_time: 2006-05-24 8:35:00 initial_time: 2006-05-24 8:59:59 row2_b: initial_time: 2006-05-24 9:00:00 initial_time: 2006-05-24 9:15:00 Is it possible to do it in a query, I mean, without using procedures? Thank you!
View Replies !
How To Optimize Data Import With Huge Volumes And Joins Across Data Sources Not All SQL Server Based?
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios: Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL. Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical. I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN. The result of this query would be exactly what I need to import. How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet. In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server. I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do. Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible! Thanks in advance for your suggestions!
View Replies !
Taking Data From A Different Databases/Data Warehouses Using A Sequel Server 2005 Based Database.
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back. Stephen
View Replies !
Need To Sort Group Based On Data From A Different Data Source.
Hello all, I have a matrix report in which the columns are selected and should be sorted based on criteria from a data source other than the one being used to generate the report. Let me explain that in a little more detail. A query into the first data source gives me a list of operations and a sequence number associated with each one. The operation list is used to select information from a second data source which is used to generate a matrix report where the operation is used as the column group. I do this by creating a multivalue report parameter with values from the first query, which I then use to constuct the second query in the second data source. Works pretty slick! Now I want to make sure that the matrix columns are sorted by the sequence numbers associated with the operatations retrieved from the first query. I set up a second report parameter with those sequence numbers, but I can't figure out how to set up the operation group sort so that it sorts on the sequence numbers that correspond to each operation. This shouldn't be too difficult, but my VB skills are just not up to the task. If you want a more challenging problem, the users would like the operation columns to appear in the matrix report even if there is no report data returned for that operation in the second query. I've written that off as impossible, but if someone really wants a challenge and can figure out how to do that trick, I would really be appreciative. Thanks in advance....
View Replies !
Problem With SQL Query Not Moving Through Every Record
I was wondering if anyone could quickly identify why the query is not parsing through each line in the temp table? I am sure its something stupid and easy, but if anyone has an idea, I would greatly appreciate the help! My results are the same row repeated exactly the same for the number of rows in the temp table. Declare @Topic varchar(150) Declare @CustomTitle varchar(150) Declare @FullName varchar(100) Declare @starttime datetime Declare @endtime datetime select TOPIC = t.TopicName, CustomTitle = e.ssCustomTitle, StartTime = v.StartDateTime, eFirstName = FirstName, eLastName = LastName, eEndTime = ssEndTime INTO #tmpwork FROM brSession e INNER JOIN v_SessionStartDateTime v on v.ssSessionId=e.ssSessionId LEFT OUTER JOIN Topic t on t.TopicId=e.ssTopicId LEFT JOIN brssPresenter ep ON (e.ssSessionID = ep.prSessionId) LEFT OUTER JOIN Personnel p on p.PersonnelNbr=ep.prPerNbr LEFT OUTER JOIN brVirtualRoom vr ON vr.vrBriefingId=e.ssBriefingId AND vr.vrVirtualRoomId=e.ssVirtualRoomId LEFT OUTER JOIN brLocation bl ON bl.loBriefingId=vr.vrBriefingId AND bl.loVirtualRoomId=vr.vrVirtualRoomId LEFT OUTER JOIN location BR ON BR.LocationId=bl.loLocationId LEFT OUTER JOIN brssDetail1 dt on dt.dt1SessionId=e.ssSessionId LEFT OUTER JOIN Competitor c on CompetitorId=dt.dt1CompetitorId LEFT OUTER JOIN PresentationStyle ps ON ps.PresentationStyleId=dt.dt1PresentationStyleId WHERE e.ssBriefingID = 11749 and((not prConfirmModeId = 0) or (prPerNbr is null)) ORDER BY ssStartTime SELECT @Topic = Topic, @CustomTitle = CustomTitle, @FullName = eFirstname + ' ' + eLastName, @StartTime = starttime, @EndTime = eEndTime From #tmpwork IF (@CustomTitle is not null) IF (not @CustomTitle = '') --correct problem of ZLS Begin set @Topic = @CustomTitle End SELECT Topic = @Topic, StartTime = @StartTime, FullName = @FullName, EndTime = @endtime INTO #Final FROM #tmpwork select * from #Final drop table #tmpwork drop table #Final
View Replies !
How Do I Exclude Data From One Table Based On Data From Another?
I have a table called MasterSkillList which is a list of skills and attributes, eg: [Appraise, INT], [Bluff, CHA] etc I have a table called Classes, which is a list of all classes available (and some details which are irrelevant), eg: [Fighter], [Assassin] etc. I also have a table called ClassSkills which holds a list of classes and their applicable skills, eg: [Assassin, Bluff], [Assassin, Open Lock], [Fighter, Appraise], [Fighter, Bluff] etc. What I have is a gridview which shows all my classes from the class table. i want to be able to select a class on that gridview and create a checkbox list of all available skills that are NOT allready associated with that class. Eg: assassin has bluff and open lock, so those two skills shouldn't appear on my checkbox list. So i want to show all the skill from the master skills list, excluding all the skills the selected class allready has. Alternatively, It would be better if there was a way to display all the skills in existance on my checkboxlist and the ones that class allready has to be checked. Any suggestions? Here's the query I have: SELECT MasterSkillsList.SkillFROM ClassSkills INNER JOIN MasterSkillsList ON ClassSkills.Skill = MasterSkillsList.SkillWHERE (MasterSkillsList.Skill <> ClassSkill.Skill) Edit: I just added the following sql query, but when i run it i get no results even though it should show everything except 2 skills. Have I written it wrong? SELECT SkillFROM MasterSkillsListWHERE (NOT EXISTS (SELECT Skill FROM ClassSkills WHERE (ClassName = @ClassName)))
View Replies !
Data Updation Based On Previous Data
Hi guys, Here's something that I need to do. Might be pretty simple for you guys. :) I have a table of Employees. All the employees work in some departments. So, I have a table of Department too. Employee table consists of details like EmpID, FirstName, LastName, SAP etc. Dept table consists of TeamID, TeamNo. Now, I have another table called as Emp-Team. This table basically maps the employees to the department by taking EmpID and TeamID. There's one more column in this table which is date. This date is required because when some person resigns (say today) then he won't feature in the headcount for July 08 but till June 08 he was there and this is how I maintain my history. e.g All the employees in the Emp_Team table have date as 01/06/2008 for this month. So, in future if I query for the employees who worked in June I will get this list. Now, I want to copy all this data in the same table again and want to remove any people who have resigned. Their resignation status is in the Employee table, where you have their last working date as well. So, when I add all this data with date 01/07/2008 I want to remove any employees whose last working date is before that. Can this be done or I have to change my design? In case it can be - How? Thanks a lot! :)
View Replies !
Moving Data
I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments.
View Replies !
Moving Data
what would be the best way to move 59 million rows from one table to another. The table has no constraint, but has has three indexes. The table has only four columns. It will be going from SQL 2000 to SQL 2000. Thank You
View Replies !
Moving Data From 6.5 To 7.0
Hello, I'm having problems using the update wizard to move data from 6.5 sql server (on another machine) to a 7.0 server sitting out a PDC. The wizard dies (and passes me over to Dr Watson) when login fails for the 6.5 machine. I am sure I have the right pasword (I've tried variations as well) and have updated the hosts file so that the machine is known by it's name (I think one of the FAQ answers suggested that).... What other possibilities are there for moving the data ? I looked at bcp, but that seems a rather long winded route (the data contains time stamps so I suppose there will be a problem reading them in on the 7.0 side) ... Many Thanks, Paul.
View Replies !
Moving SQL 6.5 Data To SQL 7
Any suggestions on the best way to get my SQL 6.5 live data to the SQL 7 test server on a separate network? I have disk dumps that I can copy and restore, if that is possible. Any other suggestions would be appreciated. Thanks, Christi Fischer
View Replies !
Moving Data Between 6.5 And 7
I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.
View Replies !
Best Way For A Set-based Query
I have a database where each row has a set of keywords separated bycommas eg .ROW 1 ID,<other columns>,"green,hat,man,boat"ROW 2 ID,<other columns>,"green,boat,seaside"ROW 3 ID,<other columns>,"red,hat"I want users to be able to find similar records to the one they arelooking it (this is art BTW). ie if they are looking at row 1 andclick "Find Similar Works of Art" I want to do a query where I findrecords that have a commonality of keywords and obviously the onesthat match the most keywords should be shown first. The keywords arecurrently just stored comma-separated in a single column but I am notattached to this. Nor are they guaranteed to come in any order (iethey are not alpha sorted) but they could be.Number of records is around 15000 on a hosted server.Any tips for best query/algorithm/data storage to achieve this? orreferences to useful sources?Thanks,RB
View Replies !
Moving Data Across Networks
I am wondering the best way to go about a task I have been assigned. We have two similar websites but each is located on a different network. One network is secure so it cannot be accessed on the normal WWW. The secure network will contain the master database. I need to write a program or do something with SQL server to retrieve all records from the WWW site and get them onto the secure database. I also in the future will need to update records from the WWW site if they have been updated. What is the easiest way to move data from one network to the other when I cannot connect to both databases simultaneously? Thanks, Matt
View Replies !
Moving Data From One Database To Another
Hi all, I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates. Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table. My Ms access table Name Id jas 100 meena 101 viji 102 My Ms sql table Name Id jas 100 meena 101 viji 102 After 1 min, say 2 records are added to my Ms access table like, Name Id jas 100 meena 101 viji 102 bhuvana 103 pinky 104 Now i want to insert only the latest records from Ms access to Ms sql like, Name Id jas 100 meena 101 viji 102 bhuvana 103 pinky 104 how to do this? thanx in advance. Jasmeeta.
View Replies !
Moving Data Between Databases
I have a web app that has been regulated to a disconnected PC. It's runing IIS and 2.0 with sql server express, but no connectivity. I have changes that are made to some of the data in the db (data, bot schema). There is one particular table that I cannot overwrite, and must extract the data. What methods are available to do this swap of data between databases? I was thinking of doing something like this: Track last date that remote db was updated. Upload updated database into data directory, loop through records for all affected tables, any date that was past logged date then update the record if it exists or insert new record, and then loop through the remote db and delete this records that dont exist in the updated db. This seems intensive and slow - especially as the tables get bigger but I can't think of another solution that can be done by a user using sometime of web interface.
View Replies !
Moving Data For Reporting
Hi all,I have 5 databases, each about 20GB in size. I need to copy the data toa new server for reporting purposes. Initially I wanted to setuptransactional replication; however, the database schemas often change(adding or modifying tables) and in order to accomodate those changesthrough replication I would need to send a new snapshot each time achange occurs. This is very time consuming.So I decided instead to use log shipping, until I realized thereporting databases would be unavailable during the period when thetransaction log was being applied. That also will not work.So what does everyone else use? My only requirements are that thereporting databases be available during business hours and there bevery little administration when it comes to changing the databaseschema.Thanks,Josh
View Replies !
|
TRACKER
