Ordering Store Sales By All Items
Jan 18, 2008
Having tough time putting together an resultset that lists all potential items sold, wether or not they were actually sold, for each customer class, and store. I have two tables. The first table has the actual item sales data. The second table a list of the commodities that could be sold.
Table 1 - sales Table 2 - commodity
store id
contractor commodity
cust_class sort_com
Commodity active_flg
sell update_dt
cost
sort_com
update_dt,
What I need is to show in the output place holders all possible commodity items.
Existing results
Class
Agriculture
Agriculture
Business
Business
Business
Store
StoreA
StoreA
StoreB
StoreB
StoreB
commodity
Mouldings
Trinkets
Plants
Irrigation
Yard
sales
18.56
0.00
914.59
506.88
41.99
cost
8.90
0.00
630.08
340.64
26.36
sort_com
2
3
1
4
5
Desired Results
Class
Store
Commodity
Sales
Cost
sort_com
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Business
Business
Business
Business
Business
StoreA
StoreA
StoreA
StoreA
StoreA
StoreB
StoreB
StoreB
StoreB
StoreB
Plants
Mouldings
Trinkets
Irrigation
Yard
Plants
Mouldings
Trinkets
Irrigation
Yard
18.56
0.00
914.59
506.88
41.99
8.90
0.00
630.08
340.64
26.36
1
2
3
4
5
1
2
3
4
5
Greatly appreciate help in getting correct output - thanks.
View 11 Replies
ADVERTISEMENT
Jan 12, 2008
Hi There
This is probably realy simple but since im still a newbie some help would be appreciated. I have a listbox bound to an sqldatasource which has names of people from my sql database employeeDetails table. I simply want to allow someone to select to select multiple names and insert them into another field in my database.
I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out
Kind Regads
Dan
View 3 Replies
View Related
May 19, 2015
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children: Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 Replies
View Related
Apr 17, 2007
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies
View Related
Feb 25, 2015
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
View 8 Replies
View Related
Apr 10, 2015
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
View 6 Replies
View Related
May 12, 2006
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)
idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0
I want to DELETE each Row IN
SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1
and WHERE isOK = 0
in my exemple , after deleting, my table must look like
idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0
thank you for helping
View 10 Replies
View Related
Nov 26, 2007
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
View 3 Replies
View Related
Mar 3, 1999
Hi there!
We are experiencing a problem using order clause in the statement below:
SELECT
person.pkey
, first_name
FROM
person
, private_person
WHERE
fkey_person = person.pkey
AND first_name = 'A'
ORDER BY
first_name
, person.pkey
Where person table contains the following columns:
pkey, name etc
and private_person
pkey, fkey_person (indexed), first_name (indexed)
The output is:
STEP 1
The type of query is SELECT
FROM TABLE
private_person
Nested iteration
Index : i$private_person$first_name
FROM TABLE
person
Nested iteration
Using Clustered Index
pkey first_name
----------- ---------------------
2000512 A
10994 A
2299 A
1097 A
1218 A
5133 A
1329 A
1387 A
1465 A
7532 A
5513 A
1884 A
512 A
591 A
(14 row(s) affected)
STEP 1
The type of query is SETOFF
Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what?
If we are ordering by fkey_person everything is Ok. Can anybody help?
Thanks a lot!
Kind Regards,
Vladimir
View 4 Replies
View Related
Jun 18, 2007
I have a table named as C1_Messages with fields Id, Messages, Dates.
My Id filed looks like this:
1000
1001
1008
1009
1084
1093
1098 etc.
But here I need to make these Ids in a order, means it sould be in a consecutive order. Like
1000
1001
1002
1003
1004
1005 etc.
So is there anyway to do this method in SQL?
Regards
Shaji
View 5 Replies
View Related
Jun 27, 2006
Hello,
I have made sql table called costreallocation consists of two columsn:
RuleID varchar(10)
Type varchar(20)
I run the following three queries:
insert into costreallocation(RuleId,Type)values('aa','a')
insert into costreallocation(RuleId,Type)values('dd','d')
insert into costreallocation(RuleId,Type)values('cc','a')
then when i notice that these three records are not inserted as they are run:
In the table thn there are three records :
aa a
cc d
dd d
but i need to be filled in the table as they run as:
aa a
dd d
cc d
your help is highly appreciated
Best regards
View 2 Replies
View Related
Jan 26, 2007
I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields.
thanks for any suggestions you may have.
Jeff
View 1 Replies
View Related
Mar 5, 2001
Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:
[Books]
ID
Title
[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)
[Authors]
ID
Firstname
Lastname
AuthorOrder
As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.
Thanks for any help.
View 1 Replies
View Related
Mar 20, 2001
Hiya!
I've got a little stored procedure:
CREATE PROCEDURE rICDCode @param1 char(15) = 'Description'
AS
SELECT DiagCode, ICD9ID, Description
FROM ICDCodes
ORDER BY @param1
and SQL is returning error 1008 - "The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."
I want to be able to use the same stored procedure for several different functions which all need the same rowset sorted differently. Any way to do this?
Thanks,
Sarah
View 2 Replies
View Related
Jul 11, 2005
Hi guys.
I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'...
I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected.
is this plausible ?
Thanks
Tom
View 1 Replies
View Related
May 16, 2008
Is it possible without using CASE statements, to order a result set by one field (if the second field return value is null) or another (if the second field return value is not null)
This would be on datetime fields.
Let's say I have 5 records with 2 date fields
sched_dt and arriv_dt.
sched_dt will always have a value, arriv_dt may or may not have a value.
In a single result set I would like to have the records with an arriv_dt sorted by arriv_dt and the ones without an arriv_dt sorted by sched_dt.
*Hope that makes sense*
Thanks
View 1 Replies
View Related
Apr 7, 2005
I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it
might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders (ie the birthday could come first - or the anniverary could, etc).
In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table
I've also tried using various WHERE statements (ie do Calc1 using Birthday & Anniversary WHERE Birthday < Anniversary and ........ - but the code gets awfully long.
Is it possible to write my own SMALL function which uses a bubble sort on the 4 dates & then returns the smallest, next smallest, etc
Any thoughts ?
View 2 Replies
View Related
Jul 5, 2007
Hi all,
Ive got a table with a field called 'morder' which orders a menu based on the values in this field (1 for position 1, 2 for position 2 etc...) for example
1 - menu item 1
2 - menu item 2
3 - menu item 3
4 - menu item 4
If I want to add a record to this table and put it at number 2 in the list, i need to update the table to then read...
1 - menu item 1
2 - NEW menu item 2
3 - menu item 2
4 - menu item 3
5 - menu item 4
I want to use a mssql or php function to re-order this field... is it possible??
Hope you can help, and hope it makes sense...
Ash
View 2 Replies
View Related
Jul 12, 2004
Hi, I was wondering, I have several columns of data that are able to be sorted on - the user just clicks on the column name. However, we have 1 column called order status where we don't want the order to be alphabetical (and it is a text field - a couple current possible values are Processing...In Production...Waiting For Approval). This order is not good for logical sorting - we would want to be able to specify what value would get order. Does that make sense? So we would want to be able to say that Processing is first in the display, Waiting For Approval would be next, then In Production would be next, and so on. Is there any possible way of doing this in a SQL query? Or am I going to have to manually modify all the data adding numbers to the beginning of each data so that it will sort on the numbers? Thanks everyone.
View 4 Replies
View Related
Jan 2, 2007
i add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns...........
how to do so .............:)
View 6 Replies
View Related
Jun 7, 2007
Hi,I'm streaming data monitoring histories of components into a database table with the following three columns: Hour (DateTime), Id (Int64), and Value (Int32). The Value entry is an aggregate of all values sent from component Id during, and 59 minutes and 59 seconds after, the time listed in the Hour column.I had rather not have to sort the queries after pulling them from the database by date. So I tried to index the DB by the Hour column. Any column will inevitably have duplicates, since the uniqueness depends on a combination of Hour and PortId. But Indexing the Hour column doesn't result in INSERTs being in order as I had expected. Instead, every entry is listed in order of insertion.So. . .how can I keep such a table ordered by date on the disk? I'm afraid this will become very inefficient if this isn't nipped in the bud right now.Thanks so much for your help!-Brandon
View 4 Replies
View Related
May 19, 2004
I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..
January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22
here is my sample code
select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'
Thanks, Jeff
View 10 Replies
View Related
Jun 9, 2004
I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically.
select to_char(created,'yyyy-Mon'), matdesc, count(*)
from test
group by to_char(created,'yyyy-Mon'), matdesc
order by to_char(created,'yyyy-Mon') desc
any help would be greatly appreciated
Thank you
Stephen
View 11 Replies
View Related
Jul 25, 2007
I am trying to do a simple SQL query..the result will be sorted alphabetically by default, but i need to specify say, if there is a "Others" it will be placed last in the list. Is there any way to write the query using ORDER BY?
e.g
Australia
China
Others
USA
and i want it to be:
Australia
CHina
USA
OThers
View 1 Replies
View Related
Aug 7, 2007
Hi all,
Ive got a bit of a problem,
I have two tables:
CATEGORY
id
name
parent_CATEGORY_id
RECORD
id
CATEGORY_id
Stock_Held_Number
ps. Ive stripped out any non relevant fields
I also have the following query (again ive stripped out the non-relevant fields)
SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number
At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.
The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.
What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.
For example:
The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2
When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60
It's technically correct because I don't want the parent to calculate the total value of the children
What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get
Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40
Does anybody have any pointers as to how I can achieve this.
Many thanks
Mike
View 3 Replies
View Related
Feb 18, 2008
hi,
i have following data:
create table tb_customer (customer_id int, order_id varchar (12))
insert into tb_customer (customer_id, order_id) values (123, 2201)
insert into tb_customer (customer_id, order_id) values (123, 2246)
insert into tb_customer (customer_id, order_id) values (123, 2236)
insert into tb_customer (customer_id, order_id) values (123, 1253)
insert into tb_customer (customer_id, order_id) values (120, 1201)
insert into tb_customer (customer_id, order_id) values (127, 2251)
insert into tb_customer (customer_id, order_id) values (127, 2231)
insert into tb_customer (customer_id, order_id) values (122, 2121)
and i want to get the following output:
output:
Nu_order | customer_id| order_id
1 |123| 2201
2 |123| 2246
3 |123| 2236
4 |123| 1253
1 |120| 1201
1 |127| 2251
2 |127| 2231
1 |122| 2121
any idea?
thank you
View 11 Replies
View Related
Jul 23, 2005
Having recently had excellent service here (many thanks, Erland!),here's another wee problem.SQL 2000I need to get a report which will display the movement of people thus:ID DIRECTION NAME DATE VEHICLE REGISTRATIONThe value of DIRECTION can be either "Inbound" or "Outbound"I need to have the data grouped by ID, with the "Outbound" row first.For example1 "Outbound" Smith 10/02/2005 ABC1231 "Inbound" Smith 11/02/2005 ABC1235 "Outbound" Jones 14/02/2005 XYZ7895 "Inbound" Jones 15/02/2005 DEF456This is the SQL so far.SELECTfldPersonID,tblMovementType.fldType AS [MovementType],'Outbound' AS [Direction],fldMovementDate AS [Date],CASE WHEN tblPerson.fldForenames IS NULLTHEN fldSurnameELSE fldSurname + ', ' + fldForenames END AS [Name],'Outbound ' + fldVehicleOut AS VehicleRegistrationFROMtblPersonINNER JOIN tblMovementTypeON tblMovementType.fldMovementTypeID = tblPerson.fldMovementTypeWHERE((fldMovementDate BETWEEN @FromDate AND @ToDate) AND(fldStatus = 1))UNION ALLSELECTfldPersonID,tblMovementType.fldType AS [MovementType],'Inbound' AS [Direction],fldMovementDate AS [Date],CASE WHEN tblPerson.fldForenames IS NULLTHEN fldSurnameELSE fldSurname + ', ' + fldForenames END AS [Name],'Inbound '+ fldVehicleRtn AS VehicleRegistrationFROMtblPersonINNER JOIN tblMovementTypeON tblMovementType.fldMovementTypeID = tblPerson.fldMovementTypeWHERE((fldMovementDate BETWEEN @FromDate AND @ToDate) AND(fldStatus = 1))What I need is the way to Order or Group it so that I can just put therows into a report without any grouping on the report itself (CrystalReports aarrggh!) - if this is possible!Edward--The reading group's reading group:http://www.bookgroup.org.ukThanks
View 1 Replies
View Related
Jul 23, 2005
Hi everyone, I have a database (not designed by me) in sql server, I amhaving trouble ordering it correctly, and I would appreciate any help.Here is my structure, it's sort of hierarchical:TABLE_AID_COLSUB_ID_COLTABLE_BID_COL'NAMEThis could be thought of as an employee and supervisor structure. Wehave employees (TABLE_B) and their relationship (TABLE_A).I know I need to use a group and oder by, but I am drawing blankstoday. I need the result to be (if using the employee example):Bob-Ryan-SueMarkNick-George-Paul--Ringo--StarHere is my half-hearted stab at it:SELECT *FROM TABLE_A AINNER JOIN TABLE_B BGROUP BY A.SUB_ID_COLORDER BY NAME;Thank you very much for any help you can offer.Have a great night!Ryan
View 4 Replies
View Related
Sep 26, 2005
I've created a clustered two column index on a table where the secondcolumn is an integer value. When the first column is the same, insteadof ordering in numerical order it is ordering 1,10,100,2,20,200 etc.How can I change the behaviour to order the data in numerical order?Thanks,Rick
View 9 Replies
View Related
Jul 20, 2005
HOW TO ORDER THIS DATA.MONTH-YEAR10-200310-202011-20033 -20034 -20035 -20036 -20037 -20038 -20039 -2003THANX IN ADV.Join Bytes!
View 2 Replies
View Related
Oct 5, 2006
How I can ordering data in SQL EE by VB 2005 EE.
Thanks.
View 8 Replies
View Related