Finding Cases With All Children Closed
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
ADVERTISEMENT
Aug 18, 2015
I have made an SSRS report using the recursive parent functionality to show a hierarchical tree of values. The problem I have is that some children have more than one parent, but because (in order to use the recursive parent nicely) I need to group the results by Id, I only see distinct entries. This means that I only see each child once, even if it "should" appear in multiple locations in the report (under each of its parents).
View 6 Replies
View Related
Sep 10, 2007
hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I have save records in tree format...But not able to calculate the all childrens.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%')
This query works properly.Problem is that it is not working in the project because in the like I have to pass a variable.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%')
Please Suggest me...
View 1 Replies
View Related
Dec 30, 2004
Hi all, Im designing a product menu like the one seen here.
I need to be able to itterate through all product menu groups in a table and find all children of a specified parent. Sorry for the cryptic Explanation and crappy Diagram!
Please see my diagram
View 5 Replies
View Related
Nov 17, 2005
Hi,
I need to be able to identify if a parent has the same child names as another parent.
Have a look at my data (in pic)
Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?
So you would get something like:
Bob | 2
Bill | 1
Glen | 2
thanks,
david.
View 7 Replies
View Related
Jul 12, 2007
Hi
I am creating a SSRS report connecting to a cube , Which has geography as Dimension as Hirerarchy ( the Hirerachy consists of Region, Subregion) and a measure , Now this geography dimension is taken as Parameter
when i apply filters i am getting the filter based on Parameter selection , But how can i get the all children under the same parent
As i need to create a graph based on report parameter (ie i should get all the children under the Select parameter parent)
to be more specfic consider this
Region SubRegion
Asia India
Asia Pakistan
Asia Japan
Australia Melborne
Suppose my report parameter is India , I should get all the values under Asia(Parent of india)
Can any one help me ..
View 2 Replies
View Related
Dec 8, 1999
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another record. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
View 1 Replies
View Related
Sep 20, 2006
I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table). My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table. Thanks for any help
View 2 Replies
View Related
Apr 11, 2007
I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.
In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk
The external agency can only use a delimited file looking like
p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b
where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.
How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.
View 4 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
Apr 8, 2015
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent
**20-parent
- 9-parent
--250-child1
--210-child2
--240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of 9 with children
View 3 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
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
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
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
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
Jun 9, 2015
How to write a Query for multiple legal names that have the same CARE Number (same address) with difference of one Legal Name having a period in the name versus the other legal name that doesn't.
For example: Looking for cases of two of the same legal name one set off by period
All Season Equipment Ltd.
All Season Equipment Ltd
West End Housing, Inc.
West End Housing, Inc
Wellings, Norman L.
Wellings, Norman L
North Texas Boats, LLC
North Texas Boats, L.L.C.
Oktibbeha County Cooperative (A.A.L.)
Oktibbeha County Cooperative (AAL)
S & R Turf & Irrigation Equipment, L.L.C
S & R Turf & Irrigation Equipment, L.L.C.
Burke Equipment Company; Burke Equipment-Seaford, Inc.; Newark Kubota, Inc.
Burke Equipment Company
Burke Equipment-Seaford, Inc.
Pleasant Valley Outdoor Power, L.L.C.
Pleasant Valley Outdoor Power, LLC
J & D Lawn and Tractor Sales, Inc.
J&D Lawn & Tractor Sales, Inc"
View 2 Replies
View Related
May 27, 2007
Lets take the following example:
Movie train table:
ID Class
1 +
2 +
3 -
4 +
5 -
Actor train nested table:
ID MovieID Gender
1 1 F
2 1 M
3 1 F
4 1 F
5 2 M
6 2 M
7 2 F
8 3 F
9 3 F
10 4 M
11 4 M
12 4 F
13 4 F
14 5 F
15 5 M
We want to build a classifier model in order to predict the Class of a Movie based on the Gender of movie's actors. To deal with the nested table Analysis Services maps each record of the nested table to an attribute of the case table. These attributes are named Actor(n).Gender with n = 1..15, and so they are dependent on the nested table record numbers. Both Microsoft Decision Trees and Microsoft Naive Bayes algorihms use these attributes without any modification.
We are implementing a Relational Naive Bayes algorithm and we are planning to aggregate such attributes in order to make them independent of the nested table record numbers.
Next step we tried to predict some unseen cases and here we face with
a very huge problem.
Lets take more two tables of unseen cases:
Movie test table:
ID Class
6 +
7 NULL
8 NULL
Actor test nested table:
ID MovieID Gender
1 6 F
2 6 M
3 6 F
4 6 F
16 7 F
17 7 M
18 7 F
19 7 F
20 7 F
21 8 M
22 8 M
23 8 F
Predicting the movie 6 Class is not a problem since the movie actors were included in the training dataset and when the records are mapped to attributes because they already exist in the model. But when you
try to predict movies (7 an 8) with unseen actors all new attributes are simply ignored in the ALGORITHM:redict call (in_ulCaseValues is zero!) because they do not exist in the model!
What is the solution?
View 3 Replies
View Related
Jun 5, 2014
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
View 8 Replies
View Related
Sep 12, 2007
thx but this code not needed anymore
sry
View 1 Replies
View Related
