i want to make business portal intellegence with sql2000 as my final project but i don't have datasets. i need iventory datasets like northwind or foodmart or middlewest. somebody help me please!!
View 1 RepliesHi! I'm kinda stuck in getting a particularly tricky qurey...
SQL Code:
Original
- SQL Code
SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.ShippedDate) Between #1/1/1995# And #12/31/1995#))
GROUP BY Categories.CategoryName, Products.ProductName;
SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, SUM(CCur([ORDER Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSalesFROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN [ORDER Details] ON Orders.OrderID = [ORDER Details].OrderID) ON Products.ProductID = [ORDER Details].ProductIDWHERE (((Orders.ShippedDate) BETWEEN #1/1/1995# And #12/31/1995#))GROUP BY Categories.CategoryName, Products.ProductName;
The above SQL statement is from the famous Northwind DB and it is called the "Product Sales for 1995" query...
I have Transactions instead of Orders and TransactionDetails instead of [Order Details]
Now, I want something similar... I want to render Dead Stock and Fast Stock... I'm using ComponentOne's Reporting solution and it pretty much like Access.
Anyways, I want to supply a date for TransactionDetail.TransactionDetailDetail as starting date (ending date is always Today()) and within this range, i want to get a list of products that are sorted ascending on Products.ProductUnitSold. This should return a list of products (grouped by category name) showing SubCategoryName, ProductName, ProductUnitPrice, TotalSold...
If total sold is ascending = Dead Stock
If total sold is descending = Fast Stock
Hi all, i don't know where to post these, and so i posted in here about sql stuff... I want to do a stock inventory for my restaurant, and i don't know how to start building the database, so, I want to ask if anyone knows if they have a database diagram for stock inventory... any kind of database diagram will helps, so I get and Idea how to start... thanks...
View 2 Replies View RelatedI am designing a BI solution for a large manufacturer. They process big ticket items. They track their inventory by each piece. I need to know where each item is located, where it came from, when it arrived, has it been processed, ect. Along with that I have to be able to run reports by day/week/month/year that shows current inventory levels at any point in time.
What I arrived at for table design is each item will get a row which will include a column for date and another for Add_Remove. When the item is added to inventory it gets a date and a +1 for Add_Remove. When it is removed it gets a new row with a date and a -1 in the Add_Remove column. I should be able to add the Add_Remove column for the selected rows (by location, type, etc) from the beginning of time to any point in time and get a current total. If I'm reporting by month for the year I should be able to add from time(0) to lastitem in January to get January levels, then the same for each successive month.
The problem I can't figure out the MDX code to do that. My basic data set is as follows:
SELECT NON EMPTY { [Measures].[Total], [Measures].[Add Remove], [Measures].[Inventory Weight] } ON COLUMNS, NON EMPTY { ([Ship From].[Ship From Region].[Ship From Region].ALLMEMBERS * [Ship From].[Ship From].[Ship From].ALLMEMBERS * [Date Received].[Date].[Date].ALLMEMBERS * [Date Received].[Year].[Year].ALLMEMBERS * [Date Received].[Month].[Month].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( STRTOSET(@Region, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOMEMBER(@FromDateReceivedMonth, CONSTRAINED) : STRTOMEMBER(@ToDateReceivedMonth, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@InventoryType, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW]))))
WHERE
( IIF( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ),
IIF( STRTOSET(@InventoryType, CONSTRAINED).Count = 1, STRTOSET(@InventoryType, CONSTRAINED), [Inventory Type].[Inv Type].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Any ideas? I've tried adding ordered sets to the query with no success. I've tried calculate measures in the cube with less success. My time dimension includes every day from 1/1/1980 to 12/31/2050.
The one calculated memeber i tried - AGGREGATE ([Date Shipped].[Date].[Date].Item(0):Tail(existing [Date Shipped].[Date].[Date]).Item(0)) took 20 minutes to run in VS Browser and produced an error for every date in the time dimension......
I have this script which pulls in all products from a product inventory with a sku range starting in 9 and ending in BK
select Product.ProductID,Product.Name,Product.SKU,ProductVariant.Inventory
From Product
Inner join ProductVariant
on Product.ProductID=ProductVariant.ProductID
where Product.SKU like '9%BK'
If I wanted to add something to this script where the inventory for all those products in the results above was changed from 0 to 20 how would I add this? Just changing those who had a a SKU starting with 9 and ending in BK
Hello, I'm looking for a little input on this situation.I'm working on an inventory system and was thinking that I'd like tobuild it similiar to the way a bank keeps track of your funds.If you look at at bank statement, for each line item, you'll normallysee at least date/time, description, amount (plus or minus value), andbalance.In my inventory system, the amount and balance would be a count of theitems used or transferred instead of money.Does this sound reasonable? Or is there a better design? I am planningto connect the inventory system to a few different ordering interfaces,and I think it would work well if I can dump transactions into theinventory instead of incrementing and decrementing counts.The only problem I see with this, is if I want to implement the balancepart of it with each record....I can't think of a way to do that currently.I can certainly use a trigger in the database to retrieve the mostrecent transaction and update the balance field for one record insertedat a time, but if a list of transactions is dumped into the inventorysystem at once, I'd have to compensate for that in the trigger.Ideas?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedHi,
I would like to know how does CMS stores content inventory in SQLserver.?
Example-If I create or migrate an application to CMS in sqlDB how does information is stored ?
Regards
T.Gunaseelan
Hello..
I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?
1st Design
PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..
SALES TABLE
SalesID
Date
...etc...
SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
PURCHASE TABLE
PurchaseID
Date
...etc...
PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...
and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE
2nd Design
PRODUCT TABLE
ItemID
ItemName
Price
...etc...
INVENTORY TABLE
ItemID
QtyBegin
...etc...
SALES TABLE
SalesID
Date
...etc...
SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...
and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:
QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ........
And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.
According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?
Thanks a lot.
I have to caculate the fifo inventory aging from purchase date, for example I have one item with 100 units in stock, and purchase this month 20 units, and 200 units three month ago, this item has 20 units this month and 80 units from 3 month ago.
Data is as follows:
<<<<< ORIGINAL DATA >>>>>>>>>>>>>>>>
Code:
Code StockPO Date PO_Qty
ao0-123100OC001010-08-2014 8
ao0-123100OC001110-08-2014 4
ao0-123100OC002011-10-2014 50
ao0-123100OC002111-10-2014 2
ao0-123100OC002211-10-2014 20
ao0-123100OC003012-11-2014 10
ao0-123100OC003112-11-2014 1
ao0-123100OC003212-11-2014 20
And the reuslt must be something like this:
<<<<< ORIGINAL DATA >>>>>>>>>>>>>>>> <<<<< CALCULATED DATA >>>>>>
Code:
Code Stock PO Date PO_QtyDays_Old Qty_From_PO Residue
ao0-123100OC001010-08-2014 8 135 8 92
ao0-123100OC001110-08-2014 4 135 4 88
ao0-123100OC002011-10-2014 50 73 50 38
ao0-123100OC002111-10-2014 2 73 2 36
ao0-123100OC002211-10-2014 20 73 20 16
ao0-123100OC003012-11-2014 10 41 10 6
ao0-123100OC003112-11-2014 1 41 1 5
ao0-123100OC003212-11-2014 20 41 5 0
I need to built a select to make this calculation, not using cursors, and build a view, only a basic select.
I am developing an inventory database in SQL Server. I realize there aremany commercial (as well as some non-commercial) inventory offerings, but myclient has specific requirements that would necessitate significantcustomization of any off-the-shelf application. In the end, we decided itwould be more feasible to build one to our specifications.What I am looking for are a list of best practices/recommendations for thearchitecture and modeling of an inventory database. This inventoryapplication will be for managing a wholesale products operation. It mustmanage purchase orders to the manufacturer of the products, sales invoicesfor the retail customers of the products, as well as manage product stocklevels in the warehouse. We will need a number of reports, including:1. What products are in stock.2. What products are on order from the manufacturer.3. What products are needed to fulfill outstanding sales.4. Warehouse operations (e.g., receipt of delivery, inspection, add toinventory, pull from inventory, packing lists, invoices, return toinventory, etc.)5. Sales analysis (e.g., product velocity, sales by associate, etc.)My main question is regarding the managing of products in stock. As I see itthere are three ways of accomplishing this:1. Track only transactions (products received, products shipped, etc.) andcalculate the stock based on the sum of all transactions. But as transactionvolume accumulates over time, this would get very slow and cumbersome.2. Have a table of "units in stock" and add and subtract to it astransactions occur. This has the advantage of always providing an instantsnapshot of inventory levels. But it makes it more difficult to managechanges or corrections to a transaction once it has been entered.3. A combination of the above two concepts. Choose a point in time (forexample, when the warehouse inventory is audited) and capture that in a(semi) "static" table. After that, transactions are summed as needed againstthose static numbers. The next time the warehouse is audited, the "static"table is updated and all prior transactions are archived. New transactionsare once again summed against the most recent audit numbers. Of course, thiswould mean halting operations at the time of the audit (probably not anissue for my client's business).Recommendations? Suggestions? Comments?(Btw, I've tried to find this info in books and online, but so far have notfound anything that makes specific inventory system design recommendations.If you know of a good reference, please let me know.)Thanks in advance!Michael
View 4 Replies View RelatedWhat is a good simple way to scan the servers in your network for SQLServers?Does anyone have any scripts or code that would show me how to dothis?I think WMI might be able to do this but don't know where to start.If possible I'd like to be able to get the version (2000 or 2005) ifpossible.Thanks,Kelly GreerJoin Bytes!change nospam to yahoo
View 1 Replies View RelatedI would like to know how I could code so that I can get the ending inventory of each day. The following is my data
CREATE TABLE #TEMP
(
DATERCVD DATETIME
,ACCTID INT
,DATESENT DATETIME
)
INSERT INTO #TEMP VALUES ('01/01/2015', 1 , '01/05/2015')
[Code] ....
So my query results should be something like this:
InventoryDate Received Sent Ending
1/1/2015 4 1 3
1/2/2015 5 0 8
1/3/2015 3 2 9
1/4/2015 0 4 5
1/5/2015 0 5 0
TOTAL 12 12
Hello
I would appreciate If any one could help me in this matter.
problem is : how to maintain perpetual inventory transaction table order in batch mode updation ?.
I have designed a table to hold all inventory transactions. The table order is perfectly maintained in online system of updation. But if I go with batch updation then the order of the transaction is collapsing. For example consider the following table design. (note I used auto number to maintain the order).
version used : SQL Server 2000 with service pack updates.
LINEID TRANS_TYPE TRANS_ID TRAN_DATE STOCKNO QUANTITY RATE
1 Receipts 2 01/01/2000 xxxxx1 2 2.0
2. Issue 1 01/01/2000 yyyyy1 1
3 Receipts 3 02/01/2000 xxxxx2 4 3.0
4 Issue 2 02/01/2000 yyyyy2 1
the above is well in online mode. But in batch mode if i update issue 2 before receipts 3 then i am getting order like below
1 Receipts 2 01/01/2000 xxxxx1 2 2.0
2. Issue 1 01/01/2000 yyyyy1 1
3 Issue 2 02/01/2000 yyyyy2 1
4 Receipts 3 02/01/2000 xxxxx2 4 3.0
ofcourse if the order collapse means costing can not be accurate. so please any one could help me to solve this problem. because many software packages are not posting in sequence if we choose in batch mode.
So I have been struggling with a way to perform inventory turn calculations in SQL. I'll start off with the formula. Basically the formula is Cost of Goods Sold over last 12 months / Average Inventory Value over the last 12 months. Short hand as COGS/AvgInventoryValue = Turns
In order to obtain the COGS value I take the Avg(UnitCost) * Sum(InvoicedQty) in the last 12 Months for Sales documents. This is represented as DOCUMENTTYPE = 2
Then divide AvgInventoryValue
To obtain the AvgInventoryValue I need to know the Quantity purchased in the 12 month period * Avg(UnitCost) + QuantityOnHand at the start of the period * Avg(UnitCost)
The purchased documents are represented with DOCUMENTTYPE = 6
The purchases will be positive since we are putting value in. The sales are negative since value is going out. I'll start with a simple example where the product is at zero Quantity On Hand at the beginning of the period.
--CREAT THE TEMP TABLE
create table #VALUEENTRY
(
ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATEDATETIME NOT NULL
, DOCUMENTTYPEINT NOT NULL
[code]....
Is there another approach I should take to obtain the inventory turns?Also how do I account for periods earlier than 12 months where inventory is left over?In the sample data this item happened to be at zero inventory on hand at the start of the period. Also I believe it is my subquery's which are throwing the numbers off but I'm not sure how to isolate the subquery to just perform the result for a specific item. It is aggregating the entire table. How can I make the subquery more specific?
Hi everyone,
I am a novice programmer. I want to develop a Inventory software using
C# 2005, which will run on a LAN environment. There will be three machine
which will store and retrive data from a common database. Which version
of SQL server is appropiate for this ? Is it possible to provide the
networking facilily using MS Access ? I don't know how to configure SQL
Server for this. Please help me.
Thank You.
how to measure a change in inventory over various stores. My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order. When the new stock arrives it is added to the "OnHand" count. I want to measure the delta change per day,per store. I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
TimeStamp Store
OnHand OnOrder
2015/04/22 18 1 - Concord
12
0
2015/04/23 11 1 - Concord
11
[code]....
To setup some data in some new tables in t-sql 2012, I need to create a relationship between 2 tables were data is obtained from an inventory file. The inventory file will be loaded to sql server 2012 from an excel spreadsheet.
1. Columns that will be loaded to the first table are:
1. Inventory category number,
2. Inventory date,
3. Inventory Category total amount, and
4. Inventory category quantity on hand.
This table will have a column called Item number. This will need to be the value of item number that is in the second table.
2. Columns that will be loaded to the second table are:
1. Item Number,
2. item date,
3. Item price, Item quantity on hand.
Thus can you show me the sql that will load the table into both tables? This sql would also need to show how to obtain the values of item number from the second table and place that value into the first table.
We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it. The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number. Below is what I have so far but it only matches exact part numbers. One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match. Both PartNumbers are varchar(30).
SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake
FROM dbo.PartsInventory INNER JOIN
dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN
dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN
dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake
WHERE (dbo.RepairOrderLines.RecordID = 46001)
Hi,
My tables:
Product
- productID
- name
- price
Inventory
- productID
- stockCount
- timestamp
So each day the Inventory table has a new row for each productID with its stock count.
How can I create a report for the total products sold from one day to another? Or from what a dateStart from a dateEnd (i.e. a range)
Example:
ProductID StockCount TimeStamp
1 10 2008/03/07
1 7 2008/03/08
So you can see that 3 products were sold in the last day.
I'm trying to create an inventory report for service trucks by adding all the transactions that were used to restock the truck and subtract the transactions where the parts were used on an invoice and removed from the truck. All the transactions are in the same table. The fields that would be relevant are PartID, QTY, WhsTo and WhsFrom. If I wanted to calculated stock levels for truck 16 I would select all transactions that have a value of 16 in either WhsTo or WhsFrom. If WhsTo contained 16 then I would want to add QTY. If WhsFrom value was 16 then I would want to subtract QTY. I would want it grouped by distinct PartID. I don't know how to structure the Select statement to decide whether to add or subtract.
View 6 Replies View RelatedI have an inventory table with the following columns
[Item], [Quantity], [Date]
I have a sales table with the following columns
[Item], [Customer], [Quantity], [Date]
In addition to this I have a normal date table.
I want to create a measure that calculates
CurrentInventory:=LastInvenoryCountInTable-SalesQuantitySinceThen
I don't know where to start. Is this possible to do with DAX?
I have a sharepoint on a windows internal database server (some version of sql express 2005). When I run the app, it says that I need SQL server reporting services 2005. I dont think that this component is bundled with "windows internal database server". Is there anyway I can download this component or otherwise make this work?
Seems a bit silly that they would make a tool requiring a component that doesnt need to be installed for sharepoint to run.
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
I typically use DataAdapters and Datasets from the toolbox when connecting to a SQL database. Since switching to VS 2005, I am not able to find (or add) these items to the tool box for web site development.
Any ideas on how I can get them back? Or is there a better alternative to retrieving the data so I can process it? Any help or suggestions would be appreciated.
Thank you
Can anyone advise me on how to view the contents of a dataset in VS dotnet 2003..
I had filled a dataset from a query...But i don know what happens after filling the qry results.It might be helpful if someone tells me to view the contents of the dataset
hi
i am using older ado datasets in a borland 6 program.
i need to retrieve the data per record count....
example is that i need all 1000 records but i only want to retrieve them 100 at a time....
how will i accomplish this?
I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?
View 5 Replies View RelatedHi,
Is there any possibility of combining data from two datasets to a single report item. If there is any thing that we can do, can any one please tell me what to do, so that my problem will be solved.
Thanks
Rajeev
Hi,
I have a table which has over 450,000 records in it, I have now split this into 4 so each table has around 100,000 records in it but I'm still having the problem of the data being returned really slowly.
What I need to do to this data is group it by a code and show the total for each code for every month of the year (this is currently based on one column and selecting the data accordingly). I have created views and put some indexes onto my table but the results are still being returned slowly. Does anyone have any suggestions of how I can speed this up?
Thanks
Gemma
Hello experts,
I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...
Code Snippet
=iif(Left(Fields!TestName.Value,4)="VTAM","VTAM Logon",
iif(Left(Fields!TestName.Value,4)="CICS","VTAM Logon",
iif(Left(Fields!TestName.Value,4)="Shaw","SHAW Main Menu",
iif(Left(Fields!TestName.Value,7)="Inquiry","Inquiry Menu",
iif(Left(Fields!TestName.Value,6)="Search","Search Menu",
iif(Left(Fields!TestName.Value,6)="SEAX A","SEAX Auxiliary Search Menu",
iif(Left(Fields!TestName.Value,8)="SEAX - B","SEAX",
iif(Left(Fields!TestName.Value,4)="STLN","STLN","Other Value"))))))))
Not sure where I put the dataset name in this.
Thanks,
C
How do you join two datasets in reporting services... one from sql and another from oracle?
Thanks
Hello all,
I have a report with a table and a chart. It uses dataset1 as the data source.
All works fine.
I create a new dataset called dataset2.
The queries are exactly the same. The only differences between the 2 datasets is the database server and the fact that one of the columns is a smallint (in dataset2) and an int(in Dataset1)
I change the datasetName property of both the table and the chart to use dataset2.
When I run the report I get a conversion error stating that there was an overflow of int2 while using dataset1. I have verified the report is not using dataset1 anywhere. If I delete dataset1 and run the report the error goes away. If I add it back, I get the error again. Why is the report looking at dataset1 if it is not referenced at all in the report? Does SQL RS cache the datasets and verify each when it compiles?
regards,
Bill
If I have a dataset that has ID and CODE, and another dataset that has CODE and DESCRIPTION, is it possible to relate these two datasets to one another so I put the ID and Code in columns 1 and 2 and then put the related DESCRIPTION in column 3 on the report table layout? In other words, can you "JOIN" two datasets, or must all that be done in the query and create a unified dataset for the report contents?
View 6 Replies View Related