I am trying to build a query to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates. Â All of the information needed is in the same db. Â How do I do this?
Select db1.customer_id, db1.product_id, db1.purchase_date From db1
I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
I successfully used the query below to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates. Â Now I need to only pull the purchases from a particular region (client_cd = '593') that purchased a particular product on different dates. Â How can I do this?
select distinct T.* from db1 T1 where exists (select 1 from db1 T2 where T2.CustomerId = T1.CustomerId and T2.ProductId = T1.ProductId and T2.PurchaseDate <> T1.PurchaseDate) and T1.ProductId = '123'<o:p></o:p>
I have a sales order table that has customer, sales, product and sales date. Â I am looking to return the first sales date by customer for a specific product.
SalesTable:
Customer Product Sales Date Sales First Bike Date
Jon Bike 12/4/2011 Â $10.00
[Code] ....
I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer. Â Can I use function to complete? Â I have a datedim table as well related to the sales date.
I have a table Product2 as the attachment at the bottom. Now i want to create a Column "Purchasing rate" over Product and Region like this. I tried some Code but it gave me still Error.
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT) INSERT INTO @Hierarchy VALUES (123, 234) INSERT INTO @Hierarchy VALUES (234, 456) INSERT INTO @Hierarchy VALUES (456, 678) INSERT INTO @Hierarchy VALUES (678, 891) INSERT INTO @Hierarchy VALUES (891, NULL)
I installed the bike buyer example and i am learning the DMX language. Now i wrote the following query (using MS decision trees):
SELECT T.[Last Name], [Bike Buyer], PredictProbability(Predict([Bike Buyer])) AS [Probability] From [v Target Mail] PREDICTION JOIN OPENQUERY (....... And so on..)
Now the result is surprising to me. In the resulttabel all the probabilities are equal.
Bike Buyer Probability 1 0.99994590500919611 0 0.99994590500919611 0 0.99994590500919611 0 0.99994590500919611 0 0.99994590500919611 1 0.99994590500919611
and so on.
Now i am wondering what predictProbability means. I thought that PredictProbability meant the probability that the prediction is correct. Now all the probabilities are the same and the input is different. Can somebody tell me what PredictProbability means or am I using it wrong?
Can someone please assist? I have no problem using the provided Algorithms (NaiveBayes, Decision Tree, etc) from SQL Server 2005 Data Mining. For example: If I want to predict whether the customers want to buy bike from the following data, then I use Age, Salary, Gender as input/attribute/feature selection and BuyBike column as "Predict" column.
Table Age Salary Gender BuyBike ------------------------------------
However, say that I have 10,000 types of bikes to predict. How to do that? Age Salary Gender BuyBike1 BuyBike2 BuyBike3 ...... BuyBike10000 ------------------------------------------------------------------------------------
Are there any online resources discussing this issue? I am desperately try to solve this problem. Please assist!
Generally, what would be a good start to model and make predictions based on the following.
Even Dollar Transactions €“ Including transactions that end in $0 or $50 with a Total Transaction Amount between $400 and $5000.
I have a table called Transaction with a BillingAmount column.
I've gone through the SQL Server 2005 Data Mining Tutorial (which uses a discrete prediction BikeBuyer yes or no) and read a lot of Data Mining with SQL Server 2005. Neither of these give good, complete, methodical examples of selecting inputs and targets and making predictions; especially for continuous columns.
I think I'm generally struggling with the concepts of selecting (continuous?) predictable attributes and calculating predictions based on the results. Are there any examples of a scenario similar to the above that I can reference? or just some advise.
I want to query my msdb job and jobschedule related tables to generate a list of runtimes for each of these jobs for the next day or any future date. This query should output JobID, Run_Date(YYYYMMDD), and Run_Time(HHMMSS).
If I have 3 jobs with...
Job#1 scheduled to run once every 4 hours between 6 AM and 10 PM Job# 2 scheduled to run every 15 minutes between 11 AM and 1 PM Job# 3 scheduled to run every minute between 4 PM and 4:15 PM
I am trying to develop a custom algorithm. I have implemented and tested training methods, however I fail at prediction phase. When I try to run a prediction query against a model created with my algorithm I get:
Executing the query ... Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader COM error: COM error: DMPluginWrapper; Object reference not set to an instance of an object.. Execution complete
I know this is not very descriptive, but I have seen that algorith doesn't even executes my Predict(..) function (I can test this by logging to a text file) So the problem is this, when I run prediction query DMPluginWrapper gives exception -I think- even before calling my custom method.
As I said it is not a very descriptive message but I hope I have hit a general issue.
Hi, is there a way to modify the way an identity column counts? for example, I want the counter to reset every month, I store the number of the month in another colum, so I just need the identity to take the month in to account, so the first day of each month it would start from 1 again.
I have a customer table with the column "invoice_customer" (this is the customer_acccount of account where the bill is invoiced..)
so to get the invoice_customer address for the customer account - 13301
SELECT B1.address as InvoiceAddress from CUSTOMER AS B1 , CUSTOMER AS E1 WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'
i want to add the above InvoiceAddress to the query below:
select customer_account, order_no, date_req, del_address (InvoiceAddress) from CUSTOMERS INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account where status 'D'
I am aware of the problems with using identity. I would like a bitmore information on implementation. Concurency is a concern becausethis is a net application.Do I do this at the application level, in a trigger, or using anothermethod?I want to make sure that the method I choose is robust and maintainsthe integrity of the data.[color=blue]>From what I know, in this case not much, it would seem that the[/color]trigger is the best bet.If I were to do a table that stores the unique number counter, I'mthinking TableName and NextNumber fields could be created and thenused for all tables that need this requirement. Then my triggerwould look up the next number based on its table name, save thenumber as the ID and then increment the value by 1 or whatever stepI choose. Is this thread safe?Thanks,Greg
Hi there, I am a little confused with some data ideas in .NET 2.0. First, I now understand there are profiles for storing per-user information (such as address, etc). Now, each of my users will have say an inventory of equipment, that they can edit, add, etc. However I am not sure if using just regular tables would be better. Any suggestions? Also, I understand using the profiles does not let you use things such as the DetailsView control, which would allow for automatic editing, adding, deletion, etc. This would be very nice to have, rather than implementing it myself. If this is the case, how do I associate the new database with the ASPNETDB database? For example I will have a table in my new database that has columns [UserId, EquipmentName, Quantity, Description]. Now how do I get the logged in user's user ID to display only their equipment in a GridView say? Thank you very much! Tristan
If i want to replicate data only for a particular customer code using SQL 2012 replication - is it possible. I believe that either the entire database or few tables can be replicated using SQL 2012.
I have this table below with customers having loans. Nou I want to write a query which retrieves all customers with exactly the same set of Loans.
Rules:
If I give C1 then I should get C2 (same set of loans) If I give C2 then I should get C1 (same set of loans) If I give C3 then I should get C4 (same set of loans) If I give C4 then I should get C3 (same set of loans)
If I give C5 then I should NOT get any row because there is no other Customer with the same set of loans. If I give C6 then I should NOT get any row because there is no other Customer with the same set of loans. If I give C7 then I should NOT get any row because there is no other Customer with the same set of loans.
I tried this query below but it doesn't give me always the same right results.
SELECT t1.LOAN, t1.CUSTOMER FROM LoanCustomer t1 WHERE EXISTS ( SELECT t2.LOAN, t2.CUSTOMER FROM LoanCustomer t2 WHERE CUSTOMER= 'C1' and t1.CUSTOMER != t2.CUSTOMER and t1.LOAN = t2.LOAN )
Hello Guys, I really need you help to debug this query. OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW. CONDITION 1: RateReview field should have yesterday's date CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL ONe Customer can have many transwactions Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets. Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and do not send any email. This would be done by some tool known as StrongMail.
SELECT CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID, CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID, Film_id as MovieId, First_nm as FirstName, Last_nm as LastName, Email_nm as EmailAddress,
from ( select MIN(CAST(customer_guid AS varchar(36))) as Customer_GUID, Transaction_GUID from tblTransaction (nolock) where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm < GETDATE()
and Terminate_dm is null and customer_guid not in ( select CAST(customer_guid AS varchar(36)) as Customer_GUID from tblTransaction (nolock) where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm < GETDATE()
and Terminate_dm is null ) group by transaction_guid, customer_guid )z inner jointblTransaction a onz.Transaction_GUID = a.Transaction_GUID
I am fairly new to sql. Reading a table I need to show amount for each customer. Also I need to add amount for each customer that has more than one entry. What would the sql statement look like? Thank you.
Records in file: Company Amount Customer1 24.000 Customer2 36.000 Customer3 72.000 Customer1 20.000 Customer3 15.000
Desired results: Company Amount Customer1 44.000 Customer2 36.000 Customer3 87.000
I have two tables customers and orders. customerID is the foreign key for order table. If I pass customername I need to get information about each customer how many orders holding?
i have a database with a list of customers and goods that they have ordered. I want to send one email to each customer regardless of the number of products he has ordered. eg.
my source flat file has many rows per customer, but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups). I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database? As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
I have a contact table and a customer table. The two tables will contain columns like First name Last Name, Date of Birth Post Code, House Number Street Name etc.
I would like to find the different combinations in which I can relate the customer and contact data. Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me. From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?
I am very new to Data Mining and would like to have some direction as to how to progress with this.