Count Of Orders For Each Customer
Mar 3, 2008Hi,
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?
ADVERTISEMENT
Last 3 Orders Per Customer
May 10, 2007Good 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
Finding Orders Per Customer
Jan 22, 2014I 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?
Updating Orders From Customer Table
Sep 26, 2012I want to update the orders that has invoice number empty when doing a change in the customer table Nm.
My order table is named order and customer is named customer, they both have a column named CustId and Nm (name).
This works fine and shows in my case column Nm in 4 rows (for orders);
select
Order.Nm
from
Order
Join Customer
on Order.CustId=Customer.CustId
where
Order.CustId=@variable and InvoNo=''
But the update statement gets error;
Update
Order
set
Order.Nm=Customer.Nm
from
Order
inner join Customer
on
Order.CustId=Customer.CustId
where
Ord.CustId=@Variable and InvoNo=''
I use SQL 2008....
Two Count Queries - Calculate % Change In Number Of Orders
Mar 12, 2015I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.
I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.
Case Statement - Count Orders That Fall Into Specific Dollar Buckets
Aug 2, 2013I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!
For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:
Code:
CASE
WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?
Orders And Orderitems
Feb 26, 2006Hello all, have a temp table where I put data from a shopping cart. I need to take the data from this temp table and put it into an orders table and an orderItems table. The table structure is as follows: The cstb_orders.orderId has a one to many relationship with the cstb_orderItems.orderId cstb_orderItemsTemp id --pk invtId descr location quantity username shiptoaddress shiptocity shiptostate shiptozip shiptophone shiptofax cstb_orders orderId --pk username shiptoaddress shiptocity shiptostate shiptozip shiptocity shiptostate shiptozip shiptophone orderdate --auto date shipdate --null cstb_orderItems id --pk orderId --fk invtId descr location quantity What is the best way to accomplish this?
View 1 Replies View RelatedSort Orders
Oct 5, 1999Is there a query you can run against a 7.0 server to return the chracter set and sort order.
View 1 Replies View RelatedDifferent Sort Orders ??? Please Help
Feb 12, 2008Hi There i have two windows 2000 servers which are both running SQL and i would like to restore a backup from one server to the other. Which in my opinion should be an easy task but when i go into the restore option and point it at the file i would like to restore i get the follwoing error
"The database you are attempting to restore was backed up under a different sort order ID (52) than the one you are currently using on this server (50) and at least one of them is a non binary sort order. Backup or restore operation operation terminating abnormally."
The server that i am trying to restore to already has databases on this so i cannot just reinstall SQL and change the sort order not that id know how to do that but this is what i have read.
Is htere anyway that i can put insome script for the database to fix this ???
Im using Enterprise manager with SQL server 7
Thanks in advance
Max Orders Placed By Hour
May 22, 2007I have a pretty straight forward fact table that contains order information like this:
DateTimeStamp Price OrderID
I have a measure that does a simple row count for orders placed. How can I get the Max Orders Placed grouped by Hour? Something similar to this:
0:00 15
1:00 25
2:00 50
.....
23:00 75
I tried to do a max function like max([time].[hour], [measures].[Order Row Count]) but ended up with only one value. I know I'm doing it wrong.
I appreciate any help on this.
Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
Aug 6, 2006With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
Selecting The Last 3 Orders Made
Apr 25, 2007Hi guys
Am new in SQL server. i use SQL server 2003 ans am trying to retieve the last three orders made. Can you please help me with my Select statement so that it will only select the last three coz @ the moment it selects everything
CODE:
SELECT dbo.Invoice.InvoiceNo, dbo.Sales.UnitsSold, dbo.Sales.QuantitySold, dbo.Sales.CostAmount, dbo.Item.ItemCode, dbo.Item.ItemDescription,
dbo.Item.Item, dbo.PurchaseOrder.OrderNumber, dbo.PurchaseOrder.OrderDate, dbo.PurchaseOrder.OrderStatus, dbo.PurchaseOrder.QuantityOrdered,
dbo.PurchaseOrder.QuantityReceived, dbo.Customer.CustomerCode
FROM dbo.Invoice INNER JOIN
dbo.Sales ON dbo.Invoice.InvoiceKey = dbo.Sales.InvoiceKey INNER JOIN
dbo.Item ON dbo.Sales.ItemKey = dbo.Item.ItemKey INNER JOIN
dbo.PurchaseOrder ON dbo.Sales.VendorKey = dbo.PurchaseOrder.VendorKey INNER JOIN
dbo.Customer ON dbo.Sales.CustomerKey = dbo.Customer.CustomerKey
Thanks
Noks
Listing Orders Without A Date
Apr 25, 2012I'm suppose to List all orders where there is no shippedDate (just give their orderNumbers).
I have this:
select orderNumber from orders where shippedDate= null;
I'm not sure what to put in place of the "= null" as that is clearly wrong
(Note: If I use "select orderNumber from orders where shippedDate;" it prints out all the orders, but I only want the orders where there are no ShippedDate...
Daily Invoicing Of Orders
Jun 10, 2008I am trying to Invoice all of the records in my 'Orders' table. After each of the records has been invoiced I would like SQL to flag that record as having been completed so that when I run my query the next day it will ignore those having been completed already. Any feedback would be greatly appreciated.
This is the query I wrote to invoice one Order at a time by specifying each Order_Num seperately. As you can tell...I'm a n00b. Thanks all.
select convert(varchar, getdate(), 107) as Date
select order_num as 'Invoice No.' from orders
where order_num = '20009'
select c.cust_name as Customer, c.cust_address as 'Street Address',
c.cust_city as City, c.cust_state as State, c.cust_zip as 'Zip Code'
from customers as c, orders as o
where c.cust_id = o.cust_id and order_num = '20009'
select oi.order_item as 'Line Item', oi.quantity as QTY, p.prod_name as 'Product Name',
oi.item_price as 'Sale Price', oi.quantity*oi.item_price as Total
from orderitems as oi, products as p
where oi.prod_id = p.prod_id and order_num = '20009'
order by oi.order_item
select sum(quantity*item_price) as Subtotal, sum(quantity*item_price*.089) as 'Tax 8.9%', sum(quantity*item_price)+ sum(quantity*item_price*.089) as Total
from orderitems
where order_num = '20009'
Related Customers To Orders
Sep 26, 2007I am trying to link my ORDERS table to my CUSTOMERS table.
The Primary key in the CUSTOMERS table is a UNIQUEIDENT.
The foreign key it is linking to is an INT.
When I run a query in ACCESS, it says type mismatch....
Any solutions?
Inconsistent Sort Orders
Jan 11, 2008Hello all-
Maybe someone has some insight on this... I currently have some hash values (SHA512 through Enterprise Library) that I'm trying to compare to data in our database. When I sort the values on the pipeline (DT_STR, 1252) with a Sort and compare them against values coming out of an OLE DB Source (SQL_Latin_1_General_CP1_CI_AS) with a Merge Join, any hash that has a '/' in it is treated as inequal to the one in the database.
For even more fun, when I change the sort/merge join to a Lookup, everything (seems) to check out. Is the 1252 code page not the same as SQL_Latin_1_General_CP1_CI_AS? Any other reasons this might be happening?
Cheers,
-Brandon Tucker
Retrieve Only New Customers With Orders
Jan 22, 2007I have a table with orders and customer info. I want to retrieve only customers with new orders only. These are the available fields: customer Id, Order Id, Order Date, and Order Subtotal. I need help with the "where" section of the query.
ORDER BY, Only Orders By The First Character?
Feb 22, 2007Hi,
I am using a stored procedure that is databound to a dropdownlist. The stored procedure works fine, aside from the fact that the ORDER BY only sorts the list of items using the first character in the cell. E.g. The numbers 1, 20, 100 and 200 would be ordered as follows:
1
100
20
200
clearly i am doing something wrong and i apologise if it is a stupid question, but could anyone please offer me some help?
Thank you,
Shep
Attach Database Between Different Sort Orders
Feb 27, 2004Hi,
Just wanted to check. I've got a SQL 7.0 box on NT4 with about 40 databases (each about 500Mb-1Gb) which I want to move to SQL2000.
The main problem is, the 7.0 box was built with a sort order not officially supported by the finance software that uses the databases, so I want to build the SQL2000 box with the default sort orders and character sets.
I've done this in a test environment by copying the .mdf files accross and attaching them to the SQL2000 box, and this all seems to work. But is this advisable, and will there be any problems?
Thanks for the help!!
SQL Server 2012 :: Last Orders For Each CustomerID
Feb 27, 2015I want to select last order's details for each customer
[URL] ...
I have one table. id is primary key. No Foreign Key
I could select last date and customerid but I want to see quantity, productid, total in last date for each customerid
[URL] .....
Current Date Orders Query
May 7, 2015How to get the result for current date orders but I can get the query working for due date.
"
SalesOrder.DateEntered,
SalesLine.OrderQuantity, (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) AS Value,
(SalesLine.SellingPrice / SalesOrder.ExchangeRate) AS Price
FROM Company INNER JOIN
CustomerAccount ON Company.CompanyID = CustomerAccount.CompanyID INNER JOIN
SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID INNER JOIN
SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID
WHERE (SalesOrder.DateEntered = { fn CURDATE() })
"
How could I get the current date orders?
How To Get Average Number Of Days Between Orders
Nov 5, 2013SQL 2012
I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.
SELECT custId, AVG(invDate - priorDate)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders)
Group BY custId
Combined Orders Grand Total?
Apr 20, 2007Hi,
Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms:
take all order totals from same client and add together order totals to produce a grand client spend total.
Anyone help!?
thanks
Reporting/querying For Orders That Contain Certain Items?
Mar 25, 2008I'm trying to create a couple of reports and I'm having trouble coming up with a way to do what I need to do. I already have several SQL views that I use for other reports, and they are geared toward either order-based or product-based reporting. For order-based reporting, I'm grouping the detail order lines by the order number. For product-based reporting, I'm not worrying about the order numbers necessarily.
Here's my problem. I need to report on orders that have a particular product line in them (the product codes in this case start with the same letter, unique from other product lines), and separate the dollars coming from that product line and what sold with it. Something like:
Orders sold with these (A) products in them:
Aproduct1 total orders $1000, $600 of which was Aproduct1 and $400 of which was Bproduct1 and Bproduct2
Aproduct2 total orders $500, $500 of which was Aproduct2
Aproduct3 total orders $700, $400 of which was Aproduct3 and $300 of which was Cproduct4
etc.
The data looks like this:
(Order#) 12345 (ProductCode) Aproduct1 (quantity) 1 (dollars) $200
12345 Bproduct1 1 $100
12348 Cproduct2 2 $500
12350 Aproduct2 1 $500
12350 Cproduct1 1 $50
12350 Bproduct2 1 $300
etc.
So I'd need to pick out orders 12345 and 12350 and separate the dollars. The thing is the three products in 12350 don't know about each other.
Any ideas?
Definitions For Sort Orders And Chracter Sets
Jul 20, 2000Other than the error log is there an easy way to find the sort order and
chracter set of an installed SQL Server. Also after finding the numbers
is there a good reference to tell you what these numbers mean.
Thanks
T-SQL (SS2K8) :: Average Number Of Days Between Orders
Apr 7, 2015I need to calculate the average number of days between customer orders.
My table has customer number and order number and the order date.
I would like to end with customer number last, order date and the average number of days between orders for each customer.
cust_idorder_numorder_date
HOLLGCAB 119482 02/27/2015
JILCO 119484 02/27/2015
KEY 119491 02/27/2015
TURNER 119496 02/27/2015
KEY 119499 02/27/2015
[Code] .....
ORDER BY Question: Splitting String Into 2 Orders?
Jul 20, 2005I have a column named "LIST" in a table with strings like the following:151231-1002-02-1001151231-1001-02-1001151231-1002-02-1002151231-1003-02-1001etc....What I'd like to do is include an ORDER BY statement that splits thestring, so that the order would be by the second set of four numbers(i.e. between the first and second - marks), followed by the third setof two numbers, and then by the last set of four numbers.How would I do something like this?--Sugapablo - Join Bytes!http://www.sugapablo.com | ICQ: 902845
View 1 Replies View RelatedEDB On WM. Do Do Sort Orders Generate Index Tables?
Aug 6, 2007Hi,
I am developing database support for my application and I am using EDB. I would like to use sort orders to make the code easier but I was wondering if sort orders generate index tables. I mean, do they only sort the records during addition or they also generate index tables to speed up queries. In other words, do they make the database file bigger?
In the MSDN documentation I can not find anything on this.
Thanks,
Giulio
Select Customers Who Havnt Made Orders
Sep 14, 2006Using the northwind database, how do I select the cutomerID of customers who havnt placed an order.
is it something like this?
select Customers.CustomerID
FROM Customers INNER JOIN Orders ON Customers.CustomerID <> Orders.CustomerID
and how can I display the customers who HAVE made orders alongside customers who havnt made orders.
Thanks in advance.
Multiple Stores Orders To Sink Up With Main Server
May 4, 2007Hello,
Say there are 10 retail store locations that need to push their daily sales to the main server at the head office.
What techniques are used to accomplish this? I'd imagine the transaction rows would have to be some sort of a GUID.
What options are there? What techniques are used to ensure each stores orders have indeed been pushed to the main server?
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
T-SQL (SS2K8) :: Select Query - Unique Orders For Date Range?
Aug 27, 2014I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
Script File - Get Total Number Of Orders In Month (July)
May 5, 2015I am trying to create a script file that will get me the total number of orders in july. How exactly would i say july because i know my syntax is wrong and I would be using sum instead of count right?
What i tried
use Cis11101_Northwind
Declare @Julycount int
Set @Julycount= (Select sum(*) From orders Where OrderDate = 'july')
print 'The total orders for july is ' + Cast(@JulyCount as varchar)