Data Mining :: How To Get A Single Date Back To Each Item
Oct 5, 2015
I'm trying to get a list of items by there last invoice date, but I keep getting errors. I have to join two table to get all the information needed to match a date with an item number.
select i.[Invoice-date], l.[Item-no]
from [Invoice] as iÂ
join [invoice-line] as l
on i.[Seq-no] = l.[Seq-no]
 (Select MAX(I2.[Invoice-date]) as LastDate, l2.[ITEM-No]
  from [invoice] AS I2
  JOIN [invoice-line] AS L2
  on i2.[Seq-no] = l2.[Seq-no]
  GROUP BY L2.[Item-no]) ITEMNUMBER
WHERE i.[invoice-date] = lastdate.[invoice-date]
and l.[item-no] = itemnumber.[item-no]
However, I keep getting this..
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
I am wondering where can I store my mining results in data mining engine? For example, I got mining results like accuracy chart, decision trees, and other formats of results based on different mining algorithms I used for my data mining, so where can I actually store the results for reporting service use later? Is it possible to do that in SQL Server 2005?
Thanks a lot for any help and guidance in advance.
Hi all,I have been wrestling with this problem all morning with no success sofar where I have a need to bring back an excluded field.Basically I have a list of order numbers. Each order number can havemany order types attached one of which is a ‘P’ type. Most order typeshave an account number attached in its own field however when a ‘P’ typeis selected the account number is not brought back.Is there someway I can get this brought back for each P type or do Ihave to do some fancy insert in a data warehouse to get this done (i.e.insert account numbers into all P types)?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I would like to know if there is any way to migrate third-party data mining packages with SQL Server 2005 data mining algorithms together then we can have a comparison among all of them to get the best results for training models.
Hoping someone will have a solution for this error
Errors in the metadata manager. The data type of the '~CaseDetail ~MG-Fact Voic~6' measure must be the same as its source data type. This is because the aggregate function is not set to count or distinct count.
Is the problem due to the data type of the column used in the mining structure is Long, and the underlying field in the cube has a type of BigInt,or am I barking up the wrong tree?
I'm a beginner with SQL 2012 SSDT & SSMS. I get this error message when I try to deploy my project:Â
"Error 6 Error (Data mining): KEY SEQUENCE columns are not supported at the case level. The 'Customer Key' column of the 'TK448 Ch09 Cube Clustering' mining structure contains content that is not valid. 0 0 " I am finding it hard to locate the content that is not valid. I've been trying to find a answer for this problem but can't seem to find anything. How can I locate the content that is not valid and change or delete it so that I can deploy this solution?
- a data mining structure with about 80 columns. - a data mining model using Microsoft_Decision_Trees with 2 prediction columns.Â
I thought I would then explore the possibility of have more than 2 prediction columns, in this case 20.
I get an error message and I can't work out : a) if this is because there's a limit to the maximum number of prediction columns and where that maximum is stated. b) if something else has become corrupted c) there's a know bug and if the error message is either meaningful or not.
Either way, I'm unable to complete the data mining wizardÂ
The error message is :Errors in the metadata manager. Either the mining structure with the ID of '[my model Structure]' does not exist in the database with the ID of 'DMAddinsDB', or the user does not have permissions to access the object.
I am using Microsoft_Time_Series and have set HISTORIC_MODEL_GAP to various values (from 1 to 21). I always get this error: Error (Data mining): The 'HISTORIC_MODEL_GAP' data mining parameter is not valid for the 'My Time Series' model.
In Algorithm Parameters window, this parameters is not there by default, so I have to add it.
Implementing data mining Add-in in an academic setting? We need to handle over 150 new students a semester and have their connection to Analysis Services survive for their four years at the college. We are introducing data mining to every freshman business student as a unit within their Intro to Excel class (close to a month of work to give them a sense of what is possible). Other courses later in their curriculum will expand on that introduction.Â
Once implemented, we would have as many as 900 connections to manage (four years from now). It is possible that multiple sections will be running at the same time, so 40 students may be accessing the data mining tools concurrently. Â
Is there a way to "bulk establish" the access credentials and establish those databases?
With SASS Database i have created Data mining Structure Using Time series algorithm, while processing the SSAS db, Data mining  taking long time to process, so how we can  reduce processing time ???
Hi, I have just run a simple data set through a model to predict a simple true or false value (i.e. binary output) The Lift Chart/Mining Legend in Analysis Services shows three results €“ Score, Population Correct (%), and Predict Probability (%)
Population Correct I beleive is the percentage of predictions it got right out of the total number of predictions it tried to make. Is this correct?
However, I can€™t work out how the other two are derived in particular the 'SCORE'. To give a live example the scores were as follows:
Model Score Pop Correct Pred Probability Decision Trees 0.83 76.59% 54.28% Neural Network 0.75 67.63% 50.05% Ideal Model 100.00%
Can anyone help with this and give a detailed explanation?
I am trying to model data in analysis services with the Advance Create Mining Model function in the excel addin. I am having trouble creating an association model that works like the Associate button above the Advanced button.
The format of my data is like this
OrderID Product
100 Bike
100 Helmet
100 Shoes
200 Helmet
200 basketball
200 Bat
300 Shoes
300 Socks
The associate button works perfectly since it asks me which column is the transaction id (orderid) and which column I am trying to predict (product). The advanced create mining model asks me to determine what the columns are...
OrderID=key Product=Input+Predict?
When I run the advance create mining model associate, I get a browser that gives me no rules and the support for only one item itemset (each product but no combination of products).
Does anyone know what I have to do to get it to work like the associate button?
I perform data mining on all products and a specific product category. Do I need to create 2 data source views, one for all products and the other one for the specific product category? Afterward, I run the Data Mining Wizard 2 times to create 2 mining structures. I also need to add the same mining model (e.g. Bayes, Cluster) to each of these mining structures. Is there any simple way to do it?
Greetings,Our former SQL Server 2000 DBA backed up everything in a single diskfile. By everything I mean, full backup, differential backup andtransaction logs. See below for details of how the backup is currentlyset up. When I did 'view contents' of DBBackup, I saw it contained themixture of log, full and diff backups of verious dates. She has quit.Other coworkers said (in a not-sure tone) she was able to restore thedatabases from such a single file, although nobody ever saw or knew howshe did recovery. My knowledge about SQL Server, especially regardingits backup/restore is limited. But I've ever worked with otherdatabases, e.g. Oracle, MySQL and Postgres. I think log backups, fullbackups and differential backups should be completely separated. Also,in each type of backups, each database should have its own backup file.Please advise.=====Log backup:CREATE PROCEDURE sp_lbackup ASBackup log AGEP To DBBackupBackup log careerfairs To DBBackupBackup log CoEdocuments To DBBackupBackup log committee To DBBackupBackup log conference To DBBackupGOFull backup:CREATE PROCEDURE dbo.sp_fullbackup ASBackup database AGEP To DBBackup WITH INITBackup database CoEdocuments To DBBackupBackup database careerfairs To DBBackupBackup database committee To DBBackupBackup database conference To DBBackupGODiff backup:CREATE PROCEDURE sp_diffbackup ASBackup database AGEP To DBBackup with differentialBackup database careerfairs To DBBackup with differentialBackup database CoEdocuments To DBBackup with differentialBackup database committee To DBBackup with differentialBackup database conference To DBBackup with differentialGO=====Thanks in advance for any help,Bing
I have a table that contains the SKU and a period of time the SKU was available to purchase (StartDtKey and EndDtKey). I am trying to write a query determine which skus were available during a given date range. The DtKeys correspond to a date in our calendar table and are in order. So DtKey 5 is 4 days after DtKey 1.
Code: CREATE TABLE #SKU( [SKU] varchar(10) NULL, [StartDtKey] int NULL, [EndDtKey] int NULL); INSERT INTO #SKU VALUES ('Sku1',1,9) ,('Sku2',2,6) ,('Sku3',10,20) ,('Sku4',5,9) ,('Sku5',25,30)
Example: Which SKUs were available on at least one day between DtKey 7 and DtKey 9?
Item Date A 2015-01-02 A 2015-03-01 A 2015-02-17 B 2015-01-07 B 2015-03-20 C 2015-01-02 C 2015-03-17 C 2015-01-14 C 2015-02-02 D 2015-03-25 E 2015-02-09 E 2015-02-09
Is it possible to get a list with only one of each item and then the latest date possible?
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
Goofed up and ran an update query. It messed up all the data in a single table. I'm trying not to restore the table from a previous backup since the backup is more than 20 GB. It's going to take forever to restore it. Any advice would be much appreciated!
I am having a hard time creating a view with some complex math to chow availability for rental items. My tables are as follows:
OrderHeader
OrderID int, Site int, StartDate datetime, EndDate datetime
OrderDetail
OrderDetailID int, Site int, OrderID int, ItemName varchar(30), Qty int
ShipHeader
ShippingID int, Type int, ActionDate datetime
ShipDetail
ShipDetailID int , ShippingID int, OrderDetailID int, Qty int
Transaction
TransactionID int, Type int
PurchaseHeader
PurchaseHeaderID int, Site int PickupDate datetime, ReturnDate datetime,
PurchaseDetails
PurchaseDetailsID int, PurchaseHeaderID int, ItemName varchar(30), Qty int
I need a view that shows how many items will be available on a particular day for a specified item, date range and a specified €œSite€? (Office location. For example, NY, LA, CA, etc.)
The quantity actually owned is calculated from the Transaction table. Type, Qty are the columns. (type 1 is a purchase or addition and type 2 is a sale or subtraction. The quantity owned is the sum of all type 1s minus the sum of all type 2s.
The ShipHeader table also shows types. Type 1 is ship, Type 2 is return and Type 3 is lost.
Initially, all availability is calculated based on the FromDate and ToDate of our order headers. However, once the order has shipped, the availability should change to our ship and return dates in our ShipDetails table. Additionally, there is a PurchaseDetails table that shows items to be €œSub-Rented€? for a time frame. The PurchaseHeader table contains the FromDate and ToDate for these sub-rented items to be added to availability. If an item is kept beyond the due date set in the OrderHeader, then the item should show as unavailable from the FromDate through all future dates (as there is no way of telling when a late item will be returning. Once, the item is either returned or marked as lost, it should become available again as of that date.
The view should list dates in each row with the number of units available on that date.
Also, a second view needs to be created that shows the details for the dates listed in the first view. this is essentially a list of the orders or puchase orders that were used to calculate the number available.
I think this covers it.
As I said, this is a bit complicated. i understand what needs to be done but need assistance assembling the code to make it happen.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
I have a table in sql server, i need to import this table to another database in same sql server using DTS, In the table, we have a field called 'qualDate', I need to import the record that the qualDate is in the date of today and back off four years, for example, today is 8/10/2004, back off four year should be 8/10/2000, so i need only the record that qualDate is between 8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.
I am trying to pass a datename in to a database backup. The date is to be retrieved from the 'monthname' column which is stored in the 'tblbill' table. I have included my ridiculous bit of code below (which funnily enough does not work!!) just so that you can see what I am trying to do. Can anyone help?
DECLARE @name varchar(10) backup database Bill to Disk ='c:@name.dat' with init
@name =select distinct monthname from tblbill where monthname is not null
I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.
While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.
I cannot now reset that database to Multi User, even by executing the query
Hi, I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)
I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse
declare @WorkingDate as datetime
SELECT @WorkingDate=dt FROM tblCalendar AS c WHERE (@WorkingDays = (SELECT COUNT(*) AS Expr1 FROM tblCalendar AS c2 WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1) AND (IsHoliday = 0)
-- Return the result of the function RETURN convert(varchar(12),@WorkingDate,106)
