Moved Stock Minus In Item Table To Stock In Itemmoment Table

Sep 11, 2007

 helo all...,

i want to make procedure like:
example

i have table:
item (itemid,itemname,stock)
orderdetail(no_order,itemid,quantity)
itemmoment(itemid,itemname,stock)
item table
itemid    itemname    stock
  c1        coconut         2
  p1         peanut          2

orderdetail table
no_order        itemid        quantity
   1                  c1                5

itemmoment table
itemid    itemname    stock
  c1       coconut          0
  p1       peanut            0
 
when customer paid, his quantity in orderdetail decrease stock in item table..
so stock in item table became:
itemid        itemname    stock
  c1            coconut         -3
  p1            peanut           2

it's not good, because stock may not minus...
so i want to move -3 to itemmoment table..
so stock in item table became:
itemid        itemname    stock
  c1            coconut          0
  p1            peanut           2

and in itemmoment table became:
itemid        itemname    stock
  c1             coconut        3
  p1             peanut          0


my store procedure like:
ALTER PROCEDURE [dbo].[orders]
(
    @no_order as integer,
    @itemid AS varchar(50),
    @quantity AS INT
)
AS
BEGIN
    BEGIN TRANSACTION
    
        DECLARE @currentStock AS INT

        
        SET @currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @itemid)

        UPDATE [item]
        SET
            [Stock] = @currentStock - @quantity
        WHERE
            [itemid] = @itemid


    COMMIT TRANSACTION
END

it's only decrease stock with quantity. i want move stock minus from item to itemmoment..
can anyone add code to my store procedure?

plss.. helpp.
thxx....

View 2 Replies


ADVERTISEMENT

Item Master And Office Stock Details (was Query Problem)

Sep 15, 2006

Hi,

I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:

ITEM MASTER
--------------
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E

STOCKDETAILS
-----------------------------------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3

I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1

--------------------------------------------
FOR OFFICE - 1
--------------------------------------------
ITEMCODE ITEMNAME OFFICEID
--------------------------------------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL

i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.

Thanks in advance

View 8 Replies View Related

Stock Inventory Diagram

Mar 4, 2007

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 Related

Complex Stock Query

Jun 17, 2008

Well probably not that complex for some of you out there!

I need to work out the amount of stock which was sold between @datefrom and @dateto and how much we currently have on hand (to work out if we are over ordering etc). That's the pretty easy part but I also need to include a column which works out how many items have been sold 3 months prior to @datefrom (from invoiceline). The proc I have so far works out the items sold between 2 dates so basically what I need is another column which is the amount sold (QtySold) in the 3 months prior to datefrom

This is the basic part I have so far:


ALTER PROCEDURE [dbo].[rptstockholdinglevel]
-- Add the parameters for the stored procedure here
@datefrom datetime,
@dateto datetime,
@periodname varchar(50),
@percentage int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT
products.productname,
@periodname AS periodname,
isnull(SUM(invoiceline.qty),0) AS QtySold,
products.qtyonhand AS OnHand,
nominals.nominalname,
productcategories.categoryname,
productmanufacturers.manufacturername
FROM
productmanufacturers RIGHT OUTER JOIN
invoices INNER JOIN
invoiceline ON invoices.invoiceid = invoiceline.invoiceid RIGHT OUTER JOIN
products ON invoiceline.productid = products.productid ON productmanufacturers.manufacturerid = products.manufacturerid LEFT OUTER JOIN
nominals INNER JOIN
productcategories ON nominals.nominalid = productcategories.salesnominal ON products.categoryid = productcategories.productcategoryid
WHERE
(invoices.invoicedate BETWEEN @datefrom AND @dateto)
OR
(invoices.invoicedate is null)
GROUP BY
products.productname,
products.qtyonhand,
productcategories.categoryname,
nominals.nominalname,
productmanufacturers.manufacturername
HAVING (SUM(isnull(invoiceline.qty,0)) < products.qtyonhand)
ORDER BY
nominals.nominalname,
productcategories.categoryname,
productmanufacturers.manufacturername
END


I'd be really grateful for any pointers as I'm just going round in circles on this one.

Thanks in advance as usual all :)

Stephen.

View 6 Replies View Related

Stock Control, Procedures

May 7, 2007

hello,I have a table like this:thing, size, color, type_mov, vary1, s, red, sell, 11, s, red, buy, 21, m, green, return, 10....and the question is how I can see the total number of products by sizeand color having in mind that some type of movement are + and otherare -.in other words, like stock control.how I can control this in sql server?code, procedures?thanks!

View 2 Replies View Related

Make Procedure For Stock-quantity

Sep 3, 2007

 helo alll...,this is my data:my table is item(productid,stock) ,order(customerid,no_order), and orderdetail (no_order,productid,quantity) example: order and orderdetail displayed in gridview....order is displayed like this:                 customerid            no_orderdetail                  A                        1detail                  B                        2  when i click detail in row 2, it's display orderdetail:no_order               productid              quantity       2                            c1                       2  2                            p1                       3 i have make all this is ok. but i want to decrease stok in item with quantity in orderdetail.my code in procedure like:CREATE PROCEDURE [dbo].[order_item](@productid AS varchar,@quantity AS INT)ASBEGINBEGIN TRANSACTIONDECLARE @no_order AS INTDECLARE @stock AS INTINSERT INTO [Orderdetail]([ProductId],[Quantity])VALUES(@productId,@quantity)SET @no_order = SCOPE_IDENTITY()SET @Stock = (SELECT [Stock] FROM [item] WHERE [ProductId] = @productId)UPDATE [item]SET[Stock] = @Stock - @quantityWHERE[ProductId] = @productIdCOMMIT TRANSACTIONENDreturn it can't work..how about his true code in store procedure?ok..., thx.. 

View 8 Replies View Related

Inventory Stock, Triggers Vs Views/SP

Jan 22, 2007

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.

View 3 Replies View Related

Transact SQL :: How To Calculate A Running Stock Value

Oct 8, 2015

I have the following store procedure :

SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,

[code]....

What I am trying to get is a RunningStock level column which is able to display stock level as describe below :

If ItemStatus value is 0, that means that the item has been taken out from stock.

So based on that the first row running Stock level is calclulated as

(ProductQuantity * ItemUnitWeight)-ItemQuantity=9...

For the second record, ItemsStatus=1 which means the item return to stock, at the time the running stock value calculation should be the previous row Running Stok value (=9 ) +(ItemQuantity*ItemUnitWeight)When the ItemStatus=2, that means the item is definitely out and will be never back to current stock. Is there a way to get that calculation field ?

View 6 Replies View Related

Conditional Predictions On Stock Prices?

Mar 13, 2007

I would like to use analysis services to analyze stock prices.

I want to find conditional probabilities:
P (YpriceChg >= 10% s.t. Ydate between A and B| X Price Chg >= 20%)?

€¦ Like given a price change of X percent or greater, predict the probability of a price change of Y percent or greater, within a specified time window (like 2 days, 3 months etc.).

I also want to add a support filter, like:

N > 30 cases (i.e., there have been at least 10 instances of a 10% or greater price change, for the chosen time window)

I have a database of prices, monthly, daily, etc. I also have a number of cols that compute statistics such as pChg1M, pChg-1M, vChg1d. Like price chg 1 month forward, price change 1 month backward, volumeChg1d forward. Ideally, I would like to minimize the column flags necessary for the experiment. Can you offer some hints, as far as setting up appropriate columns/flags and choosing a algorithm (maybe decision trees, association rules, or NB)?

View 1 Replies View Related

Changing Timeframes With Stock Data

Apr 21, 2006

I have stock data in 1 min intervals and would like to convert it into other timeframes (e.g., 10 min, daily, monthly).

Here's is some sample data and my final goal:

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.87 3.87 10
10-Feb-05 12:11:00 3.87 3.87 3.87 3.87 2
10-Feb-05 12:12:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:13:00 3.85 3.87 3.84 3.85 23
10-Feb-05 12:14:00 3.85 3.85 3.85 3.85 6
10-Feb-05 12:15:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:16:00 3.85 3.85 3.85 3.85 1
10-Feb-05 12:18:00 3.85 3.85 3.85 3.85 3
10-Feb-05 12:19:00 3.85 3.85 3.85 3.85 3

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.84 3.85 50 *

*sum

View 8 Replies View Related

The Logic Of Sample Notification Service(Stock)

Oct 4, 2007

 hi allin the sample of sqlserver2005(notification service(stock))this is code sample in :appADF.xml(stock) -------------------------- --       Update value in the chronicle if event price greater than value in the chronicle                        UPDATE    StockEventsChron                        SET        StockPrice = E.StockPrice                        FROM    StockEvents E, StockEventsChron C                        WHERE    E.StockSymbol = C.StockSymbol AND E.StockPrice &gt; C.StockPrice.for scenario: +  the first SubscriberId(Stephanie)  set :StockTriggerValue=20, and stockPrice changge =50  -> one notification for Stephanie and in the table StockEventsChron the value field :StockPrice = 50,  + the next :  stockPrice changge  = 25. the field value StockPrice  of table StockEventsChron is still 50 +  then second  SubscriberId(Scott) set StockTriggerValue=30 but---------------- Insert Into StockNotifications    (    S.SubscriberId,                        S.DeviceName,                        S.SubscriberLocale,                        E.StockSymbol,                        E.StockPrice                        )                        SELECT                                S.SubscriberId,                        S.DeviceName,                        S.SubscriberLocale,                        E.StockSymbol,                        E.StockPrice                        FROM   StockSubscriptions S JOIN StockEvents E                        ON   S.StockSymbol = E.StockSymbol                        LEFT OUTER JOIN StockEventsChron c                        ON   S.StockSymbol = c.StockSymbol                        WHERE  S.StockTriggerValue &lt;= E.StockPrice                        AND    (S.StockTriggerValue &gt; c.StockPrice OR c.StockPrice IS NULL)   here :StockTriggerValue=30 &lt; c.StockPrice=50 so no notification for Scott,  why ?can anyone help me?   

View 1 Replies View Related

Urgent Stock Options/org Chart Query

Jan 9, 2001

Okay, here's an algorithm question for you TSQL gurus out there...

Due to circumstances beyond our control, our group has been tasked with a massive project and a very short timeline. And of course, timely completion is needed because our STOCK OPTIONS grants depend on this! And of course board meetings are always scheduled sooner than you expect.

Here's one of the killer questions we're trying to solve...

Given a table of employee ID's, associated supervisor ID's, and the amount of stock options given, how would you write a stored procedure to return, for any branch of the organizational tree, the sum of all the stock options in a particular branch?

Example:

EMPID SUPERVISORID STOCKOPTIONS
1 2 100
2 30 500
3 2 150
30 50 1000
50 60 5000

What we need is something like : "sp_StockOptionsPerDepartment @SUPERVISORID=30"
with a result : "1750".

Basically we're building an organizational chart of our company from this table, on the fly, and also counting up for certain branches of the org chart, the total stock options.

If a manager has two managers under him, and each sub-manager has three employees, then we want to know the total stock options that all 3+3+2+1 = 9 people possess. Basically it's the total pool of stock options for a department, or work group, or division, etc.

Got this to work for a small set of employees, but when we begin to scale up to entire departments, the query times out because it takes tooooo long...

Any ideas? ANY ideas at ALL would be helpful...

Comments
(1) This is basically a tree-traversal algorithm, but conversion into SQL is not always so straightforward. Starting from an arbitrary root node, we must visit every child node underneath, walking all the way down to the leaves.

(2) We tried a brute force algorithm which is fast for smaller sets, but impossibly long for sets where we're dealing with hundreds of employees. Any cheats? Caching results as we go? Any ideas out there?

Thanks,
Dan
dantan@pobox.com

View 6 Replies View Related

Calculate A Range Bar From (stock) Tick Prices?

Dec 30, 2011

Assume you have a table called Tick with 2 columns
(
tickId bigint IDENTITY(1,1)
, price int -- usually money data type, making it int for simplicity
)

I am tasked with creating bars that are 10 units long.

Now the catch is I'm not looking for the tickId where price is >= t1(price) + 10 where t1(price) is the price for the first row where tickId = 1. (it could also be where price <= t1(price) - 10)

Here is sample data:

1, 25
2, 26
3, 23
4, 26
5, 27
6, 30
7, 34
8, 32
9, 30
10, 33

What I am looking for are rows 3 (23) and 7 (34)

Currently I have:

Code:
DECLARE @tickDiff int
SET @tickDiff = 10
DECLARE @r1TickId bigint

[Code].....

This seems to work but it is taking multiple minutes to run for about 50k rows of data (which I created off of the 24 million row table I have just looking at data from today). So it takes ~5 minutes to create the first bar which is not acceptible.

If my logic above seems acceptable are there any indexes you could recommend. Database engine tuning advisor didn't find any.

View 2 Replies View Related

Identify Genuine Stock Quantity Discrepancies Between ERP And WMS?

Dec 11, 2014

I have been asked to report on missing Stock in my works Warehouses. My work uses SAP Business One for ERP, and Accellos for Warehouse Management. Both SAP / Accellos maintain stock levels, and whilst they do talk to each other (in real time), nothing is perfect and stock counts (within each system) sometimes develop discrepancies.

Here is the code that I developed to show stock discrepancies -

Code:
SELECT
Tx.[Item Code]
, ISNULL(Ty.Qty, 0) AS 'A1 Qty'

[Code]....

View 1 Replies View Related

Transact SQL :: Create Query For Last Stock Quantity?

Sep 10, 2014

I need to create query for last stock quantity.

I have 3 tables. Stores, Dates and Transactions. I want to combine all Stores with all Dates in one table and then calculate Last Stock Quantity.

Stores
London
Paris
Prague

Dates
1.1.2014
2.1.2014
3.1.2014

Transactions
1.1.2014 London 1000
1.1.2014 Paris 1300
1.1.2014 Prague 1500
2.1.2014 London 800
3.1.2014 Prague 1200

And result should look like this Last_Quantity should be Quantity for last date in Transactions table.

1.1.2014 London 1000
1.1.2014 Paris 1300
1.1.2014 Prague 1500
2.1.2014 London 800
2.1.2014 Paris 1300
2.1.2014 Prague 1500
3.1.2014 London 800
3.1.2014 Paris 1300
3.1.2014 Prague 1200

View 8 Replies View Related

Power Pivot :: Stock Balance - Last Date

Oct 23, 2015

I am struggling with the Lastdate function. I have got stock balance data and want to show the number of products/models that are on stock at the latest date of the stock balance table.

My DAX formula is as follows:

=CALCULATE(DISTINCTCOUNT('3S-StockData'[Article Model]);LASTDATE('3S-StockData'[Date]))

I get the wanted results for all aggregated product groups, on product/model level however the formula does not give me the information wanted (see screenshot).

Basically, the formula calculates correct, but I want in my example only models shown with the date 2015-10-21.

View 2 Replies View Related

Stock Age Query To Accept Multiple Rows As A Result

Jan 22, 2014

I worked with someone else to create a query that gives us the age of a stock. How long it has been in the warehouse since the Purchase order date (without completely selling out). It does exactly what I want, the problem is that it only accepts 1 row as a result.

The error message I get is:

quote:Msg 512, Level 16, State 1, Line 4

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So my question is; can this code be modified to pass it multiple SKUS and run a report on every item currently in stock?

SELECT TOP 1
@skuVar, CAST(GETDATE()-ReceivedOn AS INT) AS 'Age'
FROM
(SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn
,(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp

[code].....

I use Microsoft SQL 2008

View 1 Replies View Related

SSRS :Generate Live Data Report (Example Stock)

Feb 21, 2008


Hello,


I have requirement to cache report server as soon as data get refreshed in datbase.My database get refreshed every 10 minutes.
I am working in stock and bond domain were data changes very frequently.My user want to see almost live data whenever accessing report in report manager.they also want to cache some data for better performance.Can any one tell me step's to improve performance of reporting services .
Kindly suggest me.

Thanks.
Monika Singh

View 11 Replies View Related

Shopping Cart, How Do I Subtract The Quantity Purchased From The Stock In Database?

Dec 11, 2007

Im making a shopping cart website for a school project in ASP.net with VB. I need help subtracting the quantity purchased (its saved in a session) from the stock number saved in a database.I know this:UPDATE inventory SET stock = stock - <quantity_purchased> WHERE id = <inventory_id>But I dont understand how to get the quantity purchased from the session to <quantity_purchased>. I tried putting the name of the session there and I got an error, i tried saving the session into a dim didnt work either.
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [stock] FROM [product]" InsertCommand="INSERT INTO [product] ([stock]) VALUES (@stock)" UpdateCommand="UPDATE product SET stock = (stock - @Quantity) WHERE (productID = @productID)">
<InsertParameters>
<asp:Parameter Name="stock" Type="Int16" />
</InsertParameters>
<UpdateParameters>
<asp:SessionParameter Name="Quantity" SessionField="Quantity" Type="Int32" />
<asp:SessionParameter Name="productID" SessionField="productID" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
 and I have than in my VB code on submit : SqlDataSource1.Update()

View 1 Replies View Related

SQL 2012 :: Calculate Stock Aging Based On Hand Quantity

Jan 18, 2015

I want to calculate stock aging (qty, cost) based on the on hand quantity.

Currently I am recording only in/out transaction only.

For ex: Item A i have 115 pieces (Balance stock) as on to day.

Transaction History
---------------------
Lot 1 - 01/01/2015 - 50
Lot 2 - 10/02/2015 - 50
Lot 3 - 11/03/2015 - 50
Lot 4 - 15/04/2014 - 50

I want to calculate cost of balance qty as shown below.

Jan -
Feb - 15 @ 1.1
Mar - 50 @ 0.90
Apr - 50 @ 1.2

Database schema
--------------------
CREATE TABLE [dbo].[StockManagement](
[Uniid] [int] IDENTITY(1,1) NOT NULL,
[StockCode] [int] NULL,
[TransactionDate] [datetime] NULL,
[TransactionTime] [time](0) NULL,

[Code] .....

View 0 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

Developing Custom Components By Extending The SSIS Stock Data Flow Components

Sep 7, 2006

Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.

But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.

View 1 Replies View Related

Convert Tick-by-tick Stock Price Time Series Data Grouped As High, Low, Open And Close Prices

Dec 3, 2006

I am working on a stock price analysis project. Stock prices are stored in SQL Server as tick by tick data (that means in a high frequency) and need to be grouped by minutes in order to display them as as high, low, open and close prices in a candlestick or similar chart.

The fields in the table are:

Time (for every stock price tick)
Price (stock price)
Symbol (of the stock)

A sample time series would look like this:

11:45:01 $22.11 MSFT
11:45:02 $22.10 MSFT
11:45:04 $22.25 MSFT
...
...
...
11:45:58 $22.45 MSFT
11:46:03 $22.22 MSFT
11:46:08 $22.25 MSFT
...
...
...
11:46:59 $22.45 MSFT

The result of the SQL query should be:

Symbol Time Open High Low Close
MSFT 11:45 $22.11 $22.45 $22.10 $22.45
MSFT 11:46 $22.22 $22.45 $22.22 $22.45

This is the SQL statement I came up with but obviously doesn't work:

SELECT DATEPART(Minute,Time), MIN(Price), MAX(Price) FROM dbo.TimeSales WHERE Symbol='MSFT' GROUP BY DATEPART(Minute,Time)


I would appreciate any suggestions.

Thank you!


View 10 Replies View Related

How To Select The Last Identical Item# Until A Different Item# In The Table

Jul 12, 2007

Hello,Basically, I have a table with 2 fieldsId     item#1      33332      33333      22224      22225      22226      33337      33338      3333I would like to only select the last identical Item# which in this case would be  the id 6,7 and 8Any idea how could I do that?Thanks

View 1 Replies View Related

DB Design :: Can Field Be Moved Within Same Table?

Nov 19, 2015

I have a table named [Customers] which has about 100 fields and tens of thousands of records. These fields have been created over a couple years, but now they are so disorganized its getting crazy. I want to move the fields using click and drag on the record selectors the GUI in design view of SQL Server Management Studio, but I am worried about possible dangers. Is there anything I should be worried about by doing this before saving the changes? I don't want to make a view because the front end program is pretty big and would require way too much reprogramming to point all its parts to a new view on a table used so much already.

The question arises because when I try, it wants to also save all tables that are FKs to this table, which are many(and have cascade updates on). So I decided to abort and get info I should know first. Although the data shouldn't change when moving the field, I don't want to just assume that nothing will go wrong.

Also, the system is live, and it isn't easy to shut down to do this change. Is it essential that the system be offline, or at least has no users connected to the tables?

View 3 Replies View Related

DBCC SHRINKFILE: Page 4:11283400 Could Not Be Moved Because It Is A Work Table Page.

Jul 20, 2007



Hi,



I issued this command on Tempdb but it doesnot shrink the file.



dbcc shrinkfile (tempdev_3,1)

go



Messages:

-----------------------------------------------------------------------------------------------------------

DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.

-----------------------------------------------------------------------------------------------------------------------------------------------



I have checked that there are no tables associated with any user in tempdb. Any help is appreciated.



Regards,

Razi

View 15 Replies View Related

How To Check There Is No Item In Table?

Apr 1, 2008

Hi,I want to check that Is there row exists in table or not.Please correct me.          Dim conn As SqlConnection        Dim comm As SqlCommand        Dim reader As SqlDataReader        Dim connstring As String        connstring = ConfigurationManager.ConnectionStrings("iharyana").ConnectionString        conn = New SqlConnection(connstring)        comm = New SqlCommand("select * from test where username=@username", conn)        comm.Parameters.Add("@username", Data.SqlDbType.VarChar, 20)        comm.Parameters("@username").Value = uname        conn.Open()        reader = comm.ExecuteReader        While reader.Read            If reader.Item("username").ToString = "" Then                Response.Redirect("http://www.iharyana.com")            End If                End While        reader.Close()        conn.Close()

View 1 Replies View Related

How Do You Receive The Last Item In A Table

Jan 26, 2004

Thats it
How do you receive the last item(row) in a table.
Thanks

View 11 Replies View Related

Locate Item In A Table

Aug 2, 2006

I have a sql table containing names of departments. Is there a way after a user has typed a department in a textbox on a web page I can search for it in the sql table and if it isn't there then add it. I am using asp.net for the web page.

View 3 Replies View Related

Cannot Find An Item In A Big Table

Nov 27, 2006

Hello people,

I have a table of 30,000 records on my handheld which runs windows 4.2 and sql me. When the item I am searching for is at the top of the table, the search finds it easily. When the item is in the middle or lower, it doesnt find it. I am not running out of memory and my database is on the local memory. Here is my code:



String query = "SELECT * FROM Products WHERE Barcode = " + TheReaderData.Text;

DataSet ds = GetDescription(query);

label2.Text = TheReaderData.Text;

label1.Text = "No description";

if (ds.Tables[0].Rows.Count != 0)

{

label2.Text = (ds.Tables[0].Rows[0].ItemArray[0]).ToString();

label1.Text = (ds.Tables[0].Rows[0].ItemArray[1]).ToString();

}



---------------------------------------------------------------------------------------

private DataSet GetDescription(string str)

{

DataSet ds = new DataSet();

try

{

System.Data.SqlServerCe.SqlCeEngine theEngine = new SqlCeEngine(CONN_STRING);



if (!System.IO.File.Exists(DBFILE))

{

theEngine.CreateDatabase();

}



SqlCeDataAdapter da = new SqlCeDataAdapter(str, localDB);

da.SelectCommand.CommandTimeout = 6000;

da.SelectCommand.CommandType = CommandType.Text;



da.Fill(ds);

}

catch (Exception exce)

{

string total = exce.ToString();

}

return ds;

}



I hope there is some way to find the item in the object. Any ideas?

Thanks in advance, John.

View 9 Replies View Related

Find How An Item Ranks In A Table?

Feb 7, 2007

Is it possible to find the position of an item without retrieving all the records in the table?
I have a products table, and want to find out how its sales are doing within a particular category.
My table consists of the following columns of interest:
ProductID, CategoryID, ItemsSold.
So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?SELECT * FROM dbo.Products
WHERE CategoryID = 10
ORDER BY ItemsSoldAlso, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?Any help would be very much appreciated.Thanks.

View 3 Replies View Related

Selecting The 3rd Highests Item From A Table?

Feb 5, 2004

I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.

I have 2 tables. One table with schedules and one with attended time.

What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?

Example scheduled records
student1 2/4/2004 7 hours scheduled
student2 2/4/2004 8 hours scheduled
student1 2/3/2004 8 hours scheduled
student1 2/2/2004 6 hours scheduled <---Need this one
student2 2/2/2004 8 hours scheduled
student2 1/30/2004 4 hours scheduled <---Need this one
...thousands more...

It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.

View 3 Replies View Related

Totaling More Than One Line Item In Table

Jan 16, 2008

I need to figure out hours based on StartTime and EndTime (year does not matter) from the first table and totaled for the entire week based on the effective date in the second table.

I need it to return the follwing:
WorkHourGroup TotalWorkHours
TJOHNSO 24 Hours




Table 1 -'workhour'
WorkHourGroup DayIDStatrtTimeEndTime
TJOHNSO0NULLNULL
TJOHNSO11899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO2NULLNULL
TJOHNSO31899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO4NULLNULL
TJOHNSO51899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO6NULLNULL


Table 2 -'workhourgroup'
WorkHourGroupWorkHourDescEffective Date
SMBSMB Work Week2007-09-11 00:00:00.000
SMITHBSTANDARD2008-01-12 00:00:00.000
TJOHNSOJohnson12008-01-11 00:00:00.000

Any ideas on how to accomplish this?

Thanks,
DZ

View 2 Replies View Related







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