Cases Supporting A Particular Association Rule
Jun 18, 2007
I haven't been able to find a DMX query which will spit out the cases which support a particular association rule. I was hoping it would work sort of like drillthrough but show only the cases supporting a particular rule. Am I missing something?
What I ended up doing was extracting the itemsets of the rule from the model's content then running a SQL query to retrieve the cases that contain both the left-hand and right-hand itemset of the rule. I'm hoping there's a better way.
View 1 Replies
ADVERTISEMENT
Nov 20, 2006
I have run into a .. somewhat of a "duh" question. I'm running association rule to run a basket analysis, and I'm trying to get probability of each prediction. I know this is wrong, but how do I go about running PredictProbability on each ProductPurchase prediction?
When I run the below DMX query, I get this error message...
Error (Data mining): the dot expression is not allowed in the context at line 5, column 25. Use sub-SELECT instead.
Thanks in advance...
-Young K
SELECT
t.[AgeGroupName]
, t.[ChildrenStatusName]
, (Predict([Basket Analysis AR].[Training Product], 3)) as [ProductPurchases]
, (PredictProbability([Basket Analysis AR].[Training Product].[ProductName])) as [ProductPurchases]
From
[Basket Analysis AR]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName]
, [ChildrenStatusName]
FROM
[dbo].[DM.BasketAnalysis.Contact]
WHERE isTrainingData = 0
') AS t
ON
[Basket Analysis AR].[Age Group Name] = t.[AgeGroupName]
AND [Basket Analysis AR].[Children Status Name] = t.[ChildrenStatusName]
View 4 Replies
View Related
Mar 6, 2006
Can anyone tell me, how the Business Ă?ntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.
Thanks!
View 12 Replies
View Related
Jul 13, 2006
I read somewhere that market basket analysis finds rules with substitutes as likely as rules with complements due to a consumer behavior called "horizontal variety seeking". This is when customers buy more than one product in the same category even though they are subsitutes. For example, when people go to the grocery store and buy soda, they buy coke and sprite at the same time even though they are substitutes of each other. I was wondering if anyone has experience with this anomaly and how they solved it. I found a time series model called the vector autoregressive model which is used to find the elasticity of prices over a time period. Does anyone have experience working with the VAR model? I am having trouble figuring out what some of the variables in the model are.
Below is the paper
http://www.feb.ugent.be/fac/research/WP/Papers/wp_04_262.pdf#search='VAR%20model%20market%20basket%20analysis'
View 1 Replies
View Related
Jul 11, 2006
What is the best practice in setting a minimum support threshold for market basket analysis? Is there a formula? Does it depend on ROI you predict?
View 4 Replies
View Related
Sep 22, 2006
Dear all,
I have a table containing call records, and made a mining model from that table only. The model has 3 columns : calling_number, called_number, and target_operator, using Association Rule algorithm. The key is calling_number, input was operator, and predicted column called_number.
The result shows no rule, but there are results with item-set size of 1 (column) and 2 (column). On the top record of the result, SQL Server says there are 1891 support for called_number = 1891 and operator = 'INDOSAT'.
I queried the table with this query
SELECT DISTINCT calling_number
FROM call_records
WHERE called_number = '07786000815'
AND target_operator = 'INDOSAT';
It returns 2162 records instead of 1891. If I removed the DISTINCT qualifier, SQL Server returns 2159 records. Why is this differences with the result of mining?
Thank you,
Bernaridho
View 8 Replies
View Related
Nov 30, 2006
Hi,
Could you please tell me?
Is SQL Everywhere supporting for Windows CE 4.2, Windows CE 5.0?
What processors are supported by SQL Everywhere?
Now we are using RC1 version SQL everywhere and when Release builds will be available?
What is the purpose of the following SQL everywhere runtime binary dlls.
· Sqlceca30.dll
· Sqlcecompact30.dll
· Sqlceer30en.dll
· Sqlceme30.dll
· Sqlceoledb30.dll
· Sqlceqp30.dll
· Sqlcese30.dll
· System.Data.SqlServerCe.dll
Thanks,
Rajendran.
View 3 Replies
View Related
Aug 30, 2006
I have a WinForms C#.net application in which I export data to MDB files. To create the initial database I use this code:
public static bool CreateDatabase(string fullFilename) { bool succeeded = false; try { string newDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilename; Type objClassType = Type.GetTypeFromProgID("ADOX.Catalog"); if (objClassType != null) { object obj = Activator.CreateInstance(objClassType); // Create MDB file obj.GetType().InvokeMember("Create", System.Reflection.BindingFlags.InvokeMethod, null, obj, new object[]{"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newDB + ";" }); succeeded = true; // Clean up System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } } catch (Exception ex) { Tools.ShowMessage("Could not create database file: " + fullFilename + "" + ex.Message, "Database Creation Error"); } return succeeded; }
This works perfectly fine on my system. But I've learned from one client that such MDB files cannot be opened using MS Access '97. Why they're still using this old version is a great question but the customer is always right, right?!
Anyhow, I thought that I could just alter the number "4.0" to "3.5" or "3.0" but it doesn't work on my machine. I got to wondering though if it might work on a client's machine if they had that version of OLEDB data access components installed.
So I'm wondering how I can programmatically test for which version(s) of OLEDB are available to use?
Robert Werner
http://PocketPollster.com
Vancouver, BC, Canada
View 5 Replies
View Related
Jul 1, 2015
update t1
    set t1.col1 = replace(t1.col1, t2.col1, t2.col2)
from table1 t1
inner join table2 t2 on t1.col3 = t2.col3
I am not able to replace some record which of t2.col1 contain like "[abc]def"
I have used "[][abc]def" and "/[abc/]def" for same but its not working.
View 4 Replies
View Related
Aug 17, 2015
When I try to open the Powerview report in the new Windows 10 Explorer (MS Edge), I am asked to install Silverlight.
When I try to install this, it says I already have it installed so I can’t proceed in opening the Powerview report.
View 2 Replies
View Related
Feb 4, 2008
We are setting up a new Reporting Services 2005 enterprise reporting tier that will support multiple developers, applications, and end users. We will have mirrored environments including development, test, and production each with their own database cluster, and reporting server.
We have multiple report developers who share a single Visual Studio solution which is saved in SourceSafe and is setup to have separate report projects for each business unit in the orgainzation. Each report project is mapped to a specific deployment folder matching the business unit. Using the Visual Studio Configuration Manager, we can simply flip to the envirnoment we want to deploy to and the reports are published to the correct environment and folder structure.
My problem lies with the common data sources. We are using a single master Common Data Sources folder to hold all of the data sources. The trick is that each and every reporting folder seems to have to have it's own copy of the data source in visual studio. There does not seem to be an easy way to change the data sources for the reports when you publish to various environment, i.e. development, test, production etc.
Ideally, we would have a single project for the common data sources that all reporting projects and associated folders would map to, and we would have a way to associate the appropriate data source for each environment when we deploy.
I'm looling for best practices on how to setup data sources for development and deployment in an enterprise environment that uses Visual Studio to develop and publish reports. We have 3 environments, and 6 data sources per environment and about 20 reporting folder / project in Visual Studio. That's 360 changes that have to be manged when deploying reports. Is there a best practices way to do this?
There has got to be a better way? Can anyone give me some insite into how to set this up?
Thanks!
View 8 Replies
View Related
Oct 11, 2005
Hi all,
I'm looking for some online resources here. Specifically, I'm interested in finding some case/project examples to learn more. I'm looking for any and all kinds in all areas...ADO, Security, Maintenance, etc. I've worn out google, but the most I seem to find is articles. I'm looking for actual Cases, like one you'd find in a text.
I have a text book from a couple of courses I took in school. Unfortunately it doesn't delve much into said areas. Any resources you could point me I will greatly appreciate it. I'd even be interested in some actual books if there's any that any of you have experience with that you think would help me out. Thanks for reading.
View 2 Replies
View Related
Jan 10, 2007
I recently added a nested table to a model that I had been using for a while. I noticed that after I added the nested table that the ClusterDistance() function returned 0 for every case. I went ahead and changed some of the keys for the nested table records so that the values would show up as missing and now the cases with a missing value have a non-zero ClusterDistance() value. Can anyone help me understand why this may be happening?
Thanks.
View 1 Replies
View Related
May 13, 2008
I have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a
case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4'
then 4,
else ..... then 3,
else ....... then 1,
end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks
Ludwig
CREATE TABLE #QTEMP(
[JobQueueID][int],
[JobScheduleID][int],
[JobID][int],
[JobName][varchar](50),
[JobDesc][varchar](50),
[JobStepID][int],
[JobStepName][varchar](50),
[JobStepDesc][varchar](50),
[JobStepExecutable][varchar](100),
[JobQueueStatus_ID][int],
[JobQueueStatusDesc][varchar](100),
[NextRunDateTime][datetime],
[LastRunDateTime][datetime],
[ProcessID][int]
)ON[PRIMARY]
Declare @sql nvarchar(4000)
Set @sql='INSERT INTO #QTEMP
SELECT [JobQueueID],
[JobScheduleID],
[JobID],
[JobName],
[JobDesc],
[JobStepID],
[JobStepName],
[JobStepDesc],
[JobStepExecutable],
[JobQueueStatus_ID],
[JobQueueStatusDesc],
[NextRunDateTime],
[LastRunDateTime],
[ProcessID]
FROM [FlexPort].[dbo].[FlexPortJobQueueView]
WHERE [JobID] IS NOT NULL
'
IF ISNull(@JobScheduleID,'')<>''
Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%'''
IF ISNull(@JobID,'')<>''
Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%'''
IF ISNull(@JOBName,'')<>''
Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%'''
IF ISNull(@Status,'')<>''
Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%'''
If IsNull(@LastRunDateTime, '') <>''
Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
Exec master.dbo.sp_ExecuteSql @sqlI have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a
case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4'
then 4,
else ..... then 3,
else ....... then 1,
end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks
Ludwig
CREATE TABLE #QTEMP(
[JobQueueID][int],
[JobScheduleID][int],
[JobID][int],
[JobName][varchar](50),
[JobDesc][varchar](50),
[JobStepID][int],
[JobStepName][varchar](50),
[JobStepDesc][varchar](50),
[JobStepExecutable][varchar](100),
[JobQueueStatus_ID][int],
[JobQueueStatusDesc][varchar](100),
[NextRunDateTime][datetime],
[LastRunDateTime][datetime],
[ProcessID][int]
)ON[PRIMARY]
Declare @sql nvarchar(4000)
Set @sql='INSERT INTO #QTEMP
SELECT [JobQueueID],
[JobScheduleID],
[JobID],
[JobName],
[JobDesc],
[JobStepID],
[JobStepName],
[JobStepDesc],
[JobStepExecutable],
[JobQueueStatus_ID],
[JobQueueStatusDesc],
[NextRunDateTime],
[LastRunDateTime],
[ProcessID]
FROM [FlexPort].[dbo].[FlexPortJobQueueView]
WHERE [JobID] IS NOT NULL
'
IF ISNull(@JobScheduleID,'')<>''
Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%'''
IF ISNull(@JobID,'')<>''
Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%'''
IF ISNull(@JOBName,'')<>''
Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%'''
IF ISNull(@Status,'')<>''
Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%'''
If IsNull(@LastRunDateTime, '') <>''
Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
Exec master.dbo.sp_ExecuteSql @sql
View 4 Replies
View Related
Jun 8, 2006
I need to add some cases to the select statment for cpeorderstatus:
Here is my Select statement:
"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD') AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER, OI.ITEM AS CPEORDERTYPE, OI.QTY, O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST, C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE, 'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND (C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"
I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP
View 1 Replies
View Related
Jun 8, 2006
I need to add some cases to the select statment for cpeorderstatus:
Here is my Select statement:
"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD')
AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER,
OI.ITEM AS CPEORDERTYPE, OI.QTY,
O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST,
C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE,
'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE
FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R
WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND
(C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"
I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP
View 1 Replies
View Related
Nov 28, 2012
I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.
A parameter in my stored procedure "@intUserType" may contain values 0-3
If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records
The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.
This is what I am working with:
Code:
SELECT * FROM tblEmployees
WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END)
OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END)
OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))
Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.
View 4 Replies
View Related
Oct 22, 2007
Hi everyeone,
do you know if I can do the following task through a single query
TableA(LocID,LocNAME)
TableB(ID,LocID,Amount)
What i need to do is to add sum amount having same locID from TableB and get LocIDs name through TableA.LocName. In the query there should be one thing more, if amount is less than zero put it into credit column, while if positive, puts in debit column
Thus Result(LocId, LocName, Debit, Credit) is the requied structure.
Can anyone help me out. I m not getting how to get the LocName if gets the sum by Groupby LocID also applying condition is confusing me:s
Looking forward for response
take care :)
View 4 Replies
View Related
Nov 27, 2007
For example I have
CASE (a.t_id)
WHEN (a.t_id in (22,23,27,30,38))
THEN t.desc
ELSE 'N/A'
END 'Column name..',
and that is giving me "incorrect syntax near 'in'" ??
View 20 Replies
View Related
Feb 7, 2005
I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.
I have this:
DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp]
JOIN
(select emailaddress, account_id, max(contact_id_tmp) max_cid
from [acctID_emailAddress_tmp]
group by emailaddress, account_id) AS tempImportTable
ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress]
WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid]
AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];
but it doesn't work since it's keeping the subset of the dupe row(s).
Can someone shed some light?
TIA
View 14 Replies
View Related
Jan 5, 2013
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table
CaseID,
CaseNumber,
CaseName
CaseChild Table
CaseID, FK to CaseInfo
ChildPartyID, FK to PartyID in Party table
ProgramClosureDate
Party Table
ID,
PartyID,
Firstname,
LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
FROM CaseInfo ci JOIN
CaseChild cc ON ci.CaseID = cc.CaseID JOIN
Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL
ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
View 5 Replies
View Related
Sep 9, 2013
I have a like clause like this:
WHERE COLUMN LIKE CAT1%
or
COLUMN LIKE CAT2%
or
COLUMN LIKE CAT3%
ETC..
I want to know if it is possible just have one like clause from 1-9:
CAT1, CAT3, ...., CAT9
View 3 Replies
View Related
Aug 29, 2007
Table "GprcAdj"
Code INT
StartDate SMALLDATETIME
EndDate SMALLDATETIME
Rate FLOAT
Factor FLOAT
________________________________________________
Querey
Select (Case
when @ITEM = 1 then GprcAdj.StartDate
when @ITEM = 2 then GprcAdj.Rate
when @ITEM = 3 then GprcAdj.Factor
end)
from GprcAdj
________________________________________________
When i use above querey and want to select any one of Attribute then it works right for @ITEM = 1 or 2 but for Item 3 it not shows the 'Factor' and show DateValues on option 3 which is wrong.
Also when i Change selection and select Code instead of Startdate then it works for all three Options.
so i guess that Startdate create a problem, but i dont know why it creating problem and how to resolve it
Plz give me some sugessions and solutions to resolve it.
View 3 Replies
View Related
May 10, 2007
I am testing a set of SSIS packages, In order to test my SSIS packages for errors I have two negative test cases
1) I didn't provide checkpoint file for the checkpoint enabled package.
2) I provide a wrong configuration file
Even though I am using a script task in my "on error" event of my SSIS package. It is not executed. (Perhaps because the package doesn't even execute).
My problem is that SSIS itself puts just a simple one liner in windows event log "Package Failure Error". It does not provide which package failed, why it failed etc. Therefore the admin who gets the ticket to resolve the issue has no clue of what is going wrong and where!
Since my custom logger doesn't even run, I don't know how can I put more details into the windows event log.
How can I resolve this?
regards,
Abhishek.
View 3 Replies
View Related
Sep 4, 2007
Configuration: MS SQL server 2005 SP2, and MS jdbc driver version: 1.1
The sendStringParameterAsUnicode has been set to false for performance reasons. However, when inserting unicode data, we would like to override the setting and send the data encoded in unicode, instead of defaulting the whole app to unicode=true and take a performance hit.
Any suggestions? We have tried the cast(? as nvarchar) function, but that did not help.
Sample code/output:
String text = "u0143u0144";
sendStringParametersAsUnicode=false
insert into unitable (_ntext) values (?)
Inserting into databse:
143 144 (printed hex values)
Read from database:
3f 3f (printed hex values)
View 6 Replies
View Related
Jun 18, 2007
Hi peoples,
I have created a custom CLR user define aggregate function based on the example that i found at http://msdn2.microsoft.com/en-us/library/ms131056(SQL.90).aspx.
It works great until i discovered that it will failed if i try to do either one of the following:
query a large records e.g: more than 4k records
has IS NULL in my where clause e.g: WHERE myConcatenatedFld IS NULL
Other than these two, it works perfectly fine.
Here is the error that i got:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have attached the Concatenate class that i used in my UDAGG.
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined, //use custom serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
private const int MAX_STRING_LEN = 4000;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
//Dont do anything if the input is null or empty
if (value.IsNull || value.Value.Length == 0)
{
return;
}
//Skip concatenation if the output exceed 4000 chars
if (this.intermediateResult.Length + value.Value.Length > MAX_STRING_LEN)
{
return;
}
//Concatenate output
if (!this.intermediateResult.ToString().Contains(value.Value.Trim()))
this.intermediateResult.Append(value.Value.Trim()).Append(", ");
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
//Skip concatenation if the output exceed 4000 chars
if (this.intermediateResult.Length + other.intermediateResult.Length > MAX_STRING_LEN)
{
return;
}
//Concatenate computed output
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 2);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
Anyone has any idea on how to solve this issue?. Thanks
View 5 Replies
View Related
Apr 25, 2008
Hello,
I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)
Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)
MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null
BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')
12-Jan
19-Jan
26-Jan
2-Feb
9-Feb
16-Feb
MTS
New Cases Received
85
84
79
98
79
95
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
44
29
26
35
55
54
CreditB
6
12
9
5
7
13
CreditS
-
-
-
-
3
-
CreditP
10
11
11
24
17
7
MTS Subtotal
140
125
110
144
151
150
BNB
New Cases Received
12
13
14
14
6
11
Sales
-
-
-
-
-
-
SalesD
-
-
-
-
-
-
CreditR
12
11
12
10
5
9
CreditB
8
13
9
17
16
6
CreditS
-
-
2
-
-
-
CreditP
1
1
1
1
4
3
BNB Subtotal
21
25
24
28
26
19
Total
New Cases Received
97
97
93
112
85
106
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
56
40
38
45
60
63
CreditB
14
25
18
22
23
19
CreditS
-
-
2
-
3
-
CreditP
11
12
12
25
21
10
Grand Total
161
150
134
172
177
169
This is just a very brief bit of code
SELECT MTS_new_cases_sales, mts_new_cases_salesd €¦€¦.
FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...
Can you please show me how to accomplish this?
Thank you in advance for your effort,
Rhonda
View 2 Replies
View Related
Jan 17, 2008
Hi,
is there any website or book that provide excellent database design examples to learn?
Appreciate for any help
Ricky.
View 1 Replies
View Related
Sep 4, 2006
Hi
I have developed a product
basket mining model as follows
DSV
SELECT C.CustomerId,C.CustomerIdName,P.ProductId,P.ProductIdName
FROM Customer INNER
JOIN CustomerProduct
ON C.OpportunityId
= P.OpportunityId
Mining Structure
CREATE MINING MODEL ProductBasket
(
CustomerId
TEXT KEY,
CustomerIdName
PREDICT,
ProductId
PREDICT,
ProductRecommend
TABLE PREDICT
(
ProductId TEXT KEY
ProductIdName PREDICTONLY
)
)
USING Microsoft_Association_Rules
Prediction Query
Since I want the output in the following format
Product
ID
Items (nested Table)
Product
A
product B
Product C
Product
B
Product A
Product C
I have written the prediction query as follows
SELECT
t.[ProductId],
PredictAssociation([Association].[ ProductId],3)
From
[Association]
PREDICTION JOIN
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT
[ProductId]
FROM
(SELECT ProductId FROM ProductBase)
as [Product]
') AS t
ON
[Association].[Product Id] = t.[ProductId]
The model is predicting the
same set of products for every case. Even changes in the algorithm parameter
value do not have any impact on the result.
What is the reason for this and
how can u rectify it?
View 5 Replies
View Related
Feb 2, 2008
Hi
I came across something like 3-4-5 rule while going through datamining book....but couldn't get from where that rule has been generalized and how it really works....
can anyone explain this rule ?
Thank you
View 1 Replies
View Related
Sep 21, 2015
I put this series of select statements to verify that the BETWEEN statement is working properly. I should always get “Between” below.
SELECT
CASEWHEN'1/1/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
SELECT
CASEWHEN'1/31/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
[Code] .....
View 4 Replies
View Related
Nov 3, 2006
Hi,
How do I extract rules and it's value from a database?
I can extract the rules through view(sys.objects) but where can I get it's content?
Regards
Marcelo Gamba
View 4 Replies
View Related
Jul 25, 2007
How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.
Thanks
View 2 Replies
View Related
