hi,
I need to write a SQL Query in which I have time1 and time2.
Now I need to find the difference between the time1 and time2 and if the difference is less then 15 minutes then I have to raise an Exception in SQL SERVER 2000.
Anyone know how I can do this.
Hi,I am currently updating a column in my database table with the value typed into a text box. But it is updating with the new value, rather than taking away the value typed in from the present value of the column. 1. How would i subtract the value typed in away from the current column value?2. I guess I need to check the column value is above 0 before updating, then rebind the control to show new values after the update has happened, incase two people are using it at once. How would this be achived? Thanks for any help!!Jon P.S.My code is:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable = @qty WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); con.Close(); return true;} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@User)"+ "INSERT INTO Transactions (Itemid)VALUES (@productID)"+ "INSERT INTO Transactions (itemname)VALUES (@Itemsname)"+ "INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+ "INSERT INTO Transactions (Qty)VALUES (@qty)"+ command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.Parameters.Add("@qty", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); return true; }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Update") { SqlDataSource1.Insert(); } }}
View 7 Replies View RelatedI'm in trouble. I have a table with some fields, one of them is an amount of money.
One field indicates whether the amount is negetive or positive. This fields has only two possible values, 'N' and 'R'. 'N' means the amount is negative and 'R' means the amount is positive.
The problem I have is that I have to sum these amounts to give a total for each customer and report them as
customer1 total amount1
customer2 total amount2
I don't know how to make a query to bring these results. I know that sum would bring me the total amount for each customer but I don't know how to specify which fields are supposed to be negative so that it subtracts them instead of adding them.
By the way I'm not allowed to change the structure of the table.
Could you help me try to create this query?
hi all
I want to find the set of rows that are present in result returned by first select statement & that are not there in the result of second select statement.
Both select statement operate on tables having similar columns.
Is there any way by which i can find such difference in results?
Is this possible using EXCEPT function?
plz help me.
I'd like to take two DateTimes, subtract them and insert the result as HH:MM:SS,
I think you use datediff but I'm not clear on the datediff formatting to get back HH:MM:SS...
Thanks for your help!
--PhB
I would like to know the best method or the simplest method to do the following. Take a datetime value and subtract a given number of hours. I am taking a UTC time and subtracting a given number or hours in a query. I seem to be getting mixed results.
Thanks
One of the following selected fields is a sum. I need to subtract ISNULL(R.CMS_AMOUNT,0) where R.Waived = '1
from that amount.
Pretty much just subtract those amounts where the waived field is 1. I am having trouble with the syntax. Can anyone help? Thanks.'
SELECT P.Efs_Network_ID
, F.DBA_Name
, F.ACCOUNT_STATUS
, SUM(ISNULL(R.CMS_Amount,0)) - isnull(RA.Amount,0) AS Amount -- ?? isNULL(R.CMS_AMOUNT,0) WHERE
R.Waived = '1' ??????
, isnull(SUM(RA.Pending),0) AS Pending
, F.Salesman_no
FROM tblPimsFDSLookup P
INNER JOIN ACH_MerDemo F
ON P.Efs_Network_ID = F.Efs_Network_ID
LEFT OUTER JOIN tblrecovery R
ON P.IntMid = R.IntMid
LEFT OUTER JOIN(
SELECT sum(isnull(Amount,0)) as amount
, intmid
, SUM(CASE WHEN RA.Status = 'P' THEN 1 ELSE 0 END) AS Pending
FROM tblACHRecoveryAmounts RA
--WHERE IntMid = 641046
WHERE RA.Status IN ('A','P','M','S')
Group by intmid
) AS RA
ON P.IntMid = RA.IntMid
WHERE P.INTMID = @INTMID
AND R.Waived = 0
AND Upgraded IS NULL
GROUP BY
P.Efs_Network_ID
, F.DBA_Name
, F.ACCOUNT_STATUS
, RA.Amount
,F.Salesman_no
If I have the total amount of 673000 that is passed as a parameter in my stored proc then, I need to do this:
Declare @TotalAmount money
@TotalAmount = 673000
Col1 Col2 Col3
Test1 45 672955 --(I want to subtract 673000 from Col2 data) 673000-45
Test2 30 672925 --(I want to subtract 30 from the remaining amount of col3)
Test3 100 672825 --(I want to subtract 100 from the remaining amount of col3)
Hello forum.
How do I go about subtracting adjacent Rows? Let's say each day I entered one row entry into a table (tblFlowerHeight)showing the new height of a flower in one column(Height)...say if I had a second second column (ChangeFromLastHeight), how would I go about showing the difference between current height and the last recorded height?
Please assist.
I have the following fields in my report
Year
2008 2007 Net Change
Val1 10 6
Val2 7 5
Val3 15 7
The Matrix Column is grouped by Year.
I need to calculate Net Change as Year 1 - Year 2 for each row.
How do I access the contents of the first and second column of the matrix ?
I have a table for which I need a €śspecial€? running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
Credit
Debit
Balance credit
Balance debit
6
0
6
0
5
0
11
0
0
4
7
0
0
9
0
2
3
0
1
0
0
5
0
4
0
2
0
6
I have to point out that this is done in a grouped way where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Credit,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID
I have a table CombinedSales script below (CreateCombSalesTable.sql) which contains the details of a sale.
A row in the table has two flags online/offline
SalesAmount is the financial amount,
online = 1 (online sales)
online = 0 (combined sales off and online).
The table also contains the product and the client etc.,
What I am trying to do is subtract onlinesales from combinedsales within the query or outside I don't mind, to return the result as (below)
HERE is HOW I want the results to appear
skuid(No column name) onlineClientName
200437215183.350Asda
20043716816.651Asda
I query the table using:
select skuid,SUM(SalesAmount),online,ClientName from t_CombinedSalesDetails
where ClientName ='Asda'
and SkuId = 200437
group by Skuid,Online,ClientName
order by skuid
THIS is what I get below the online = 0 is the total sales of this product.
skuid(No column name) onlineClientName
200437232000.000Asda
20043716816.651Asda
How I could achieve the required results
/****** Object: Table [dbo].[t_CombinedSalesDetails] Script Date: 27/08/2014 13:23:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
[Code] ....
I have a Column in my data that gives a financial period value in the YYYYMM format. i.e. an asset was re-valued in a particular period for example 201301. I need to find out the number(count) of periods(months) between another given period for example current period (201403) and the period provided in the table i.e. 201301.
Is this possible in the SQL Database?
Hi,
I have a matrix which looks as follows:
----- Actuals | Budget
ItemA 20 | 50
ItemB 45 | 30
ItemC 20 | 15
I would like to add a column with the difference between actuals and budget.
Actuals/budget is 1 group in the matrix being the dimensionname.
How can I calculate the difference (subtract actuals and budget), because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?
Please help...
Hi all,I encountered this small problemI have two tables A and B with two columns 1 and 2 each, I would likethe first column of each table when match the first in the second tableis to subtract the second columnso the result would look as followsColumn 1 | Columnn 2where A1=B1 | A2-B2Now this is no problem so far ..But if there was no corresponding value in column 1 in either tables ..i.e. field A1 doesnt exist in Table B column 1, IT SHALL DO A2 - 0; or0-B2 ..NOW How can that be achieved ?Thanks all for your help
View 3 Replies View RelatedI have two date fields, start_date and end_date.I'd like to subtract the two dates, and come up with a number (thenumber of difference between the two dates).What function is there to do this? I haven't been able to find anythingin BOL.Start_date = 6/1/03End_date = 6/8/03End_date - start_date = 7*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI am trying to get a query that subtracts a month and a day from current date in SQL Server.
Currently I have SELECT Â DATEADD("MM", -1,GETDATE()) which subtracts one month from the date but I want to subtract a month and a day from date.
I have a table that has two smalldatetime columns. like say a shift schedule.
Fname Lname Timein TimeOut Dept
What I need to find out is how to construct a SQL Query in BIDS so I can get a report containing names of people who have not worked in last 24 hours.
This query returns values but it seems it is not correct.
Select * FROM Table
WHERE (Timeout < { fn NOW() } - '23:59:59')
Hi there,I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).I was duration in the format of hours:minutes:seconds.The stored procedure is pasted below. I am getting the following error. Syntax error converting datetime from character string. Any ideas? ============================== CREATE PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List ( @websiteID AS int = 0, @SelectedDateFrom AS dateTime, @SelectedDateTo AS dateTime) ASIF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0begin set @SelectedDateFrom=null endIF ISNULL(@SelectedDateTo, '') = ''begin SET @SelectedDateTo = @SelectedDateFromendSET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions WHERE UserID != 'Anonymous'GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitleHAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)GO============================== <Columns> <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn> <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn> <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn> <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn> <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn> <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn> </Columns> ============================
View 1 Replies View RelatedHello. How do I increment or decrement a date by adding/subtracting days? For example I want to add 4 days to the date today. I should get 07/31/2005 since today is 07/27/2005. And if I add 5 days then I should get 08/01/2005. Thank you.
View 5 Replies View Related=Sum(Fields!balance_due.Value)
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total $137.84
It should be like this:
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total 112.5
How can I get SSRS to add and subtracted numbers.
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
Sample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies View RelatedI am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?Â
I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
SELECTÂ
  CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
  CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate   04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies View RelatedI need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
Hi all,
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?