Classify Customer Order Method Preference Based On Their History?

Nov 5, 2013

I am trying to classify a customer's order method preference based on their history. My source table has every order, order method, date, etc.

The logic is:

If only one order method, that is their preference>1 order method, the majority is their preferenceif 50/50 split, the order method with the mose recent order is the preference

I then created a query to group by the customer, order method, and max(date):

[URL]

I am having trouble though, creating a query that applies the above logic and outputs this:[URL]

View 13 Replies


ADVERTISEMENT

SQL 2012 :: Order Of Preference In Case Statement

Sep 9, 2015

I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this

case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO

View 9 Replies View Related

Order ID For Latest Order For Every Customer

Jan 8, 2008

Hi!
For the Orders table (let's assume for the Northwind database), I'm trying
to get the order id of the latest order for every customer.
That means that the result should be one record per customer and that would
display CustomerID and OrderID.

Any ideas?

Thanks,
Assaf

View 1 Replies View Related

Select Customer Last Inserted Order Details

Sep 15, 2007

Hi,
Check this SQL


SELECT DISTINCT
TblOrder.CustomerUID,
TblOrder.OrderHiddenID,
TblPayment.PaymentAmount,
TblPayment.Result,
TblOrder.OrderID
FROM TblOrder
CROSS JOIN TblPayment
WHERE (TblOrder.CustomerUID = @IsCustomerID)
AND (TblOrder.OrderHiddenID = @IsHiddenID)
AND (TblPayment.Result = 'Pending')
AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))

one customer can have more than one orders.
So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this

View 4 Replies View Related

Power Pivot :: Get Order First Date For A Specific Product And Customer?

Nov 18, 2015

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".

View 2 Replies View Related

Transact SQL :: Merge Two Rows Into One Based On Same Customer Number

Oct 26, 2015

I have a table Customer with below column

CustomerNumber , FName,LName,DOB

I have either 2 or 1 row for each customer number  I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.

example:
CustNumber  Fanme   Lname   DOB
1  Sam tiller 08/26/1981
1  Joe   timy  01/02/1986
2  jack  niks   09/09/1990

Result I want:
CustNumber Fname1  Lname1 DOB1 Fname2 Lname2 DOB2
1  Sam  Tiller 08/26/1981 Joe  timy 01/02/1986
2  Jack  niks  09/09/1990 null null null

View 7 Replies View Related

T-SQL (SS2K8) :: Finding 5 Most Recent Records For Each Customer With Abnormal Order Amounts

Nov 5, 2014

The database consists of the following tables:

create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)

Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.

Possible result: [URL] ......

View 7 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Connecting Users From ASPNETDB.MDF To A Shopping Cart And Order History

Jan 2, 2007

I'm trying to figure out how to associate users in my ASPNETDB.MDF to create a shopping cart.IE: I have 3 tables, for a list of existing orders, list of products, and list of specific orders.The idea is for users who've signed up with the built-in user creation control, can then place orders from the shopping cart, and view their existing previous orders. This would normally be done with a unique UserID # to associate orders with specific users, however I don't BELIEVE there's anything like UserID's associated with each user in the ASPNETDB.MDF, it just uses usernames as the unique identifiers right?Anyway I'm not sure I'm explaining what I'm trying to do perfectly and I apologize, but I don't think it's an un-common problem I'm having. Basically I'm trying to avoid having to have existing ASPNETDB users create a sort of SECOND user that would associate them into a seperate Customers table, it seems like extra user steps that shouldn't be necessary.

View 1 Replies View Related

How To.... Classify

May 30, 2008



How to categorize the SQL Server database size? Like very large databases (VLDBs) etc
SQL Server 2000.
Thank you,
Gish

View 4 Replies View Related

How To Classify Hours By Ranges Of Time

Mar 31, 2008

Hi, I have the following query that is not working correctly

Basically I have a fields called TimeLeave (datetime) and I want to classify every record according to that field

The multiplication by the field b.Zulu is just to convert to local time, once converted I want to classify the local time with those numbers, but for example nothing falls in the first category and I get a bunch of records with a wrong ID

Any idea what should bet the correct sql statement to classify the records??

Thanks in Advance


CASE

WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '22:30' AND '05:59' THEN 1

WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '06:00' AND '08:59' THEN 2

WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '09:00' AND '11:59' THEN 3

WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '12:00' AND '14:59' THEN 4

WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '15:00' AND '16:59' THEN 5

WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '17:00' AND '19:59' THEN 6

WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '20:00' AND '22:29' THEN 7

END


View 3 Replies View Related

Storing Default Parameters As User Preference

Nov 29, 2007



Need some guidelines:

I wanted to create a report with parameters. The users will access the reports via
http://localhost/Reportserver/myReportName

Now, each user will have their own default parameters and would like to be stored(somewhere!!!) as "user preference", so nexttime when they access this report it should display data with the "user preference" parameters.

Can someone give me some advise to implement this.

Many thanks.

View 5 Replies View Related

SQL Server 2012 :: Set Based Method To Insert Missing Records Into Table - Right Join Not Work

Apr 24, 2014

I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)

Table A could look like the following where the multiple employees could have multiple types and multiple stages.

EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2

Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.

Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4

So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.

I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.

I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.

My count would look something like this:

EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2

So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.

The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.

View 9 Replies View Related

Ouput Based On Order Date

Jun 2, 2008

Hi all,
I have 2 tables, Order and Payment

Order
Order_NumberCust_NumberOrder_Date
10001 C1 23-May
10002 C2 24-May
10003 C1 25-May
10004 C3 28-May

Payment
Order_Number|Card_Type|Card_#|Merchant
10001 Gift Card1234null
10001 Gift Card1235null
10001 Gift Card 1236null
10001 Credit Cardxxxxprd
10002 Credit Cardxxxxprd
10003 Credit Card xxxxprd
10004 Credit Card xxxx prod

I have to populate th below table to track last gift card used for each cust_number.
1. last gift card used for each customer, each order
2. In a single order , if card used is gift card, last gift card used is gift card itself. if card used is a credit card, then the max gift card number from with in the order.
3. First time if a customer uses a credit card, then last gift card used is defaulted to 99 for merchant = prd and 88 for merchant = prod
4. In a new order, a past customer only uses a gift card, then last gift card used is gift card from his previous order.

[b]Last_Gift_Card
Cust_No|Order_number|card_number|last_gift_card

C11000112431234
C11000112351235
C11000112361236
C110001xxxx1236
C210002xxxx99
C110003xxxx1236
C310004xxxx88


Please help me with the sql.
I tried this using subqueries to find the max gift card for a customer for an order and could get the last gift card used correctly for credit cards for an order, but not able to insert the gift card from a previous order if the new order has only credit card as in for customer C1.

Thanks.

View 6 Replies View Related

SQL Question ( Retrieving Records Based On A Priority Order )

Feb 24, 2008

Hi all,I have a table with this structure:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/4/2007 | M1 | 1/2/2008 | R2 | 1//5/2007 | M2 | 1/6/2007 | RFor each customer ID, I need to retrive only the records with the mostrecent transaction date, and I did:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/2/2008 | R2 | 1/6/2007 | RThe problem is, each customer can make more than one transaction inthe same day (see above). In these cases, I need to retrieve only onerecord per customer, based on the importance of the transaction: F ismore "important" than R which is more "important" than M. So, in theexample above, I would only retrieve transaction F for customer 1 anddisregard transaction R: they were made on the same date, but I ammore interested in F than in R.Do you have any suggestions on how I could achieve this in SQL?I use Microsoft SQL server 2005.Thank you for your help!

View 1 Replies View Related

Shouldn't The Order Of Records Be Based On The Key Or Primary Index?

Jun 11, 2007

I upsized an access database with a key / index on ordernumber and linenumber.

However if I open the table in the Management Studio the records aren't ordered this way (same goes for select * from table) I get:










Ordernumber
Linenumber

200724001
37

200724004
3

200724006
33

200724001
3

200724011
19

200724014
5

200724006
37

200724011
19

200724006
28



Same goes for my crystal reports files, since the records aren't ordered by ordernumber / linenumber all my formulas go bezerk..



Am I wrong thinking the records should be ordered according to the prim. index?

Please help because I don't want to have to change all my 40+ reports to include an "ORDER BY"



Best regards,



Mike

View 7 Replies View Related

Transact SQL :: Retrieve Return Result With Chronological Order Based On Parameter

Jun 23, 2015

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?

Information: I'm using SQL server 2012

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values

[Code] .....

View 2 Replies View Related

Transact SQL :: Get List Of Items Present In Order Based On Confidentiality Code Of Product

Sep 29, 2015

I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.

I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.

whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.

If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.

Table 1:Order

Order_Id Order_No Customer_Id

2401 1234567 23
2402 1246001 24
2403 1246002 25

Table 2 : OrderedItems

OrderItem_Id Order_Id Item_Id Sequence

1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1

Table 3: ItemMaster

Item_Id Item_Name confidentCode

1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8

Out put for 1st grid 

**Note :** Logged-in user have confidentiality code 6

Order No Customer
1234567 23
1246001 24

3rd order is not displayed in the grid

After user selects the 1st order in the grid then the items present in that 1st order should be displayed as 

1001     Rice

the second item not displayed because that having confidentiality code greater than user.

After user selects the 2nd order in the grid then the items present in that order should displays

1005 Badham
1007 Oil

I need the query to display the order details in 1st grid.

View 3 Replies View Related

SQL Server 2012 :: List Of Order Numbers Based On Stock Availability - Filter Results?

Dec 23, 2014

Trying to build a list of order numbers based on stock availability.

The data looks something like this:

OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10

In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.

View 1 Replies View Related

Send Request To Stored Procedure From A Method And Receive The Resposne Back To The Method

May 10, 2007

Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString)    {      //call stored procedure  } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.  

View 3 Replies View Related

Update Method Is Not Finding A Nongeneric Method!!! Please Help

Jan 29, 2008

Hi,
 I just have a Dataset with my tables and thats it
 I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
 Please help if anyone has a solution
 
Thanks

View 7 Replies View Related

Add Calculated Field In Order Table Based On Price Column In Product Table

Nov 18, 2014

I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?

this query returns the values i want in the table.

select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID

View 17 Replies View Related

Help Getting Top 1 Row For Each Customer

Oct 26, 2007



Hi ALl,
I am using SQl server 2005,
I have the following data

customer order_id order_code complete
1328004 3462 18 1
1328004 3463 18 1
1554477 3689 18 1
1554477 4123 18 0

Here I need results like this,

customer order_id order_code complete
1328004 3462 18 1
1554477 3689 18 1

the first row for each customer and order_code, so far I haven't been able to come up with the correct query.

select top 1 a.customer,a.order_id,a.order_code,a.complete
from order a
order by a.customer,a.order_id

My query only returns the top 1 row .Please point me in the right direction.

Thanks in advance

View 4 Replies View Related

Customer Identity

Apr 13, 2008

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. 

View 4 Replies View Related

Customer Table

Feb 13, 2007

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'

How would I put the 2 togtheer...

Thanks in advance!!!

View 2 Replies View Related

Pro / Con Of Different Databases Per Customer

Jul 18, 2007

Hello everyone -

Is there a pro / con reason why a corporation would create a new database for each customer??

I can understand the obvious reason - to keep the companies apart,
but other than that single reason are there any other ones?

thanks
tony

View 7 Replies View Related

Last 3 Orders Per Customer

May 10, 2007

Good day.
Am new with SQL server and i use SQL Server 2005

i have 3 tables (1 = customers, 2 = orders, 3 = keys) both these tables are linked by the key table

I wanna be able to "view" (as am only allowed to view) the last three orders for every customer.

I tried using the top predicate/ clause with a group by & count but unfortunately am getting no where.

Please kindly help

View 14 Replies View Related

Need Most Recent Customer

Mar 12, 2008

What statements can be used to grab the most recent customer record?

View 3 Replies View Related

Customer IDs Without Identity

Jul 23, 2005

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

View 4 Replies View Related

Implementing A Customer System

Mar 4, 2006

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

View 3 Replies View Related

Problem With Mdf File For Customer

Jan 26, 2007

I have given a Zip file with mdf + ldf file to a customer and he cannot attach it to his server
what can be the problem ?
any rights ?

thank you

View 4 Replies View Related

SQL 2012 :: Replication Only For A Customer?

Dec 17, 2014

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.

View 2 Replies View Related

Finding Orders Per Customer

Jan 22, 2014

I have three SQL tables that I am trying to join. One is Called Customer and the other Orders.

Customer contains these fields that I need

CustomerID,Email,FirstName,LastName,

Orders has these that i need

OrderNumber,CustomerID,OrderTotal,OrderSubTotal

OrdersShoppingCart has these that I need

OrderNumber,CustomerID,OrderedProductSKU,OrderedProductName,OrderedProductPrice

What I would like to try to export is this

CustomerID
FirstName
LastName
Order ID
SKU
Order Total

This would be grouped for each customer for all total orders. Do you think this is possible?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved