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


ADVERTISEMENT

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

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

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

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

Newbie, Sage Updating Invoice Prices With Current Prices

Sep 22, 2007

Dear all

I have a company that sells fruit and vegetables to the catering industry.
I take orders in the evening for the next day, and buy my fruit and vegetables from wholesale market to deliver to my customers on the next day.
I have sage simply accounting.
I have to enter invoices day before I print them, so I can get a list of items i should buy next day (and also sort them into different routes)
My question is, is there a way to update all the prices on the active invoices (i.e. not printed or posted) for the previous day, after I enter new prices for the fruit and vegetables???
I need this as at the moment I hava to go into individual invoices and enter the products and quantities again.

Many thanks

hh

View 4 Replies View Related

How To Calculate The MAX Value From A Range Of 48 Columns?

May 25, 2001

I have a table of data with values for every 30 mintues per day (i.e. 48 values per record per day.) I need to find the maximum value over the range of half-hourly time periods in any particular day and then calculate the MAX value for each month and year.

Is there a way of doing it? I am using SQL Server 7.0.

Many thanks.

View 1 Replies View Related

Moved Stock Minus In Item Table To Stock In Itemmoment Table

Sep 11, 2007

 helo all...,i want to make procedure like:examplei 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          2orderdetail tableno_order        itemid        quantity   1                  c1                5itemmoment tableitemid    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           2it'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           2and in itemmoment table became:itemid        itemname    stock  c1             coconut        3  p1             peanut          0my store procedure like:ALTER PROCEDURE [dbo].[orders](    @no_order as integer,    @itemid AS varchar(50),    @quantity AS INT)ASBEGIN    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 TRANSACTIONENDit'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 View Related

Tick Marks

Jul 25, 2000

I'd like some ideas on how to help out my VB staff when it comes to the tick ( ' ) mark being used in front end applications. When users enter data into the front end, and they include tick marks (eg: Sam's Auto Store), this causes trouble in how the app uses that data in a WHERE clause and other places. Does anyone have any slick ideas or experience in handling this. We have experimented with search & replace, double tick marks ( '' ), etc...

View 1 Replies View Related

Include Tick (') In Varchar?

Aug 15, 2007

Hi guys,

Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?

Address3 Varchar(100) is where I want to put it.

Many thanks,

Millicent.

View 7 Replies View Related

Tick Marks In Sql Statements

Feb 5, 2008



I am working on a project to import information from one database table to another. This info includes names, addresses, etc. I am running into a challenge whenever one of the names, such as O'Brien, causes problems in the sql statement. Is there a way for me to tell it to ignore the ' in O'Brien?

Thanks,
Brian

View 8 Replies View Related

SSRS : Number Of Categories Between Tick-mark Labels

Mar 7, 2007

I'm trying to do the equivalent of an Excel chart "Number of categories between tick-mark labels" on the X-Axis of a SSRS chart. Can't see anyway to do it. I can get it to display differently by doing Label = IIF(somethingistrue, onevalue, anothervalue), but can't see anyway to simply not show the label at all.

Any suggestions.
Thanks
Richard

View 7 Replies View Related

Summing Prices Day By Day

Oct 23, 2013

Let suppose that we have a table which look like this

BillDate Price
01.01.2013 2.00
01.01.2013 1.00
02.01.2013 3.00
02.01.2013 2.00
03.01.2013 1.00

I would like to sum a prices day by day and output to be like this

BillDate SumDaylyPrice
01.01.2013 3.00
02.01.2013 5.00
03.01.2013 1.00

To point I’ve reached myself is a query:

SELECT BillDate, (SELECT SUM( Price) FROM Table1 ) AS SumDaylyPrice
FROM Table1
WHERE BillDate BETWEEN
(SELECT Min(BillDate) FROM Table1)
AND
(SELECT Max(BillDate) FROM Table1)
GROUP BY BillDate

but this doesn’t work- summing everityng

I don’t know how to indicate in first row of query
SELECT BillDate, (SELECT SUM( Price) FROM Table1 WHERE DATE = ????) AS SumPrice
a WHERE clause for every day separately.

View 2 Replies View Related

Update Prices

Feb 5, 2008



I want to update price of products table from an imported table B.

Here is the code:




Code Snippet
update dbo.tbl_product
set product_wholesale_price = (SELECT dbo.B.WS FROM dbo.B
where dbo.tbl_product.product_id = dbo.B.product_code)






But I am getting the following error:

Msg 107, Level 16, State 3, Line 1

The column prefix 'dbo.B' does not match with a table name or alias name used in the query.

Msg 107, Level 16, State 3, Line 1

The column prefix 'dbo.B' does not match with a table name or alias name used in the query.


Please tell me where I am wrong..or is there a better way to do it?

View 5 Replies View Related

Reporting Services :: Possible To Remove Tick Marks On Horizontal Axis Legend

Jun 22, 2015

Is it possible to remove the tick marks that I have circled in red?The chart has data Week by week over a year, and the T1, T2 etc are equivalent to Quarter1, Quarter 2 etcI've got a 3 level grouping, with the top level being Year, then Quarter, then Week. The label for the "Week" grouping is set to blank, and that's why you don't see it here.Removing the Tick marks would make the chart legend more readable in my opinion.

View 2 Replies View Related

How To Get Prices From Table Where Date Not Exists

Feb 17, 2013

I have 2 test tables one for stock and one for prices.

I need to select all the rows from Stock but also the price on the stock but the price table doesn't always have the date, so I can not do stock date = price date.

What it needs to do is if the Stoc Date isn't in the price table use the price before... this would also have to be able to run on my rows...

-- Create Test Table (not sure if dates USA or UK format on your machine...

CREATE TABLE [dbo].[TheStockLedger](
[EntryID] [int] NULL,
[TheDate] [datetime] NULL,
[StoreCode] [nvarchar](50) NULL,
[Item] [nvarchar](50) NULL,
[ColorCode] [nvarchar](50) NULL,

[code]....

View 10 Replies View Related

Historical Prices With Data Gaps

Apr 24, 2008

Hi,

I have a SQL2005 db for tracking the prices of products at multiple retailers. The basic structure is, 'products' table lists individual products, 'retailer_products' table lists current prices of the products at multiple retailers, and 'price_history' table records when the price of a product changes at any retailer. The prices are checked from each retailer daily, but a row is added to the 'price_history' only when the price at the retailer changes.

Database create script:
http://www.boltfile.com/directdownload/db_create_script.sql

Full database backup:
http://www.boltfile.com/directdownload/database.bak

Database diagram:
http://www.boltfile.com/directdownload/diagram_0.pdf

I have the following query to retrieve the price history of a given product at multiple retailers:

SELECT
price_history.datetimeofchange, retailer.name, price_history.price
FROM
product, retailer, retailer_product, price_history
WHERE
product.id = 'b486ed47-4de4-417d-b77b-89819bc728cd'
AND
retailer_product.retailerid = retailer.id
AND
retailer_product.associatedproductid = product.id
AND
price_history.retailer_productid = retailer_product.id

This gives the following results:

2008-03-08 Example Retailer 22.3
2008-03-28 Example Retailer 11.8
2008-03-30 Example Retailer 22.1
2008-04-01 Example Retailer 11.43
2008-04-03 Example Retailer 11.4

The question(s) I have are how can I:

1 - Get the price of a product at a given retailer at a given date/time
For example, get the price of the product at Retailer 2 on 03/28/2008. Table only contains data for Retailer 1 for this date, the behaviour I want is when there is no data available for the query to find the last data at which there was data from that retailer, and use the price from that point - i.e. so for this example the query should result in 2.3 as the price, given that was the last recorded price change from that retailer (03/08/2008).

2 - Get the average price of a product at a given retailer at a given date/time
In this case we would need to perform (1) across all retailers, then average the results

I'd really appretiate anyone's help on this :)

many thanks in advance,

dg

View 17 Replies View Related

Table-How To Have Seasons In Headers With Prices In Columns??

Oct 12, 2006

 Hi,I would like to change my select to generate table that will resultsThat will haveService, Room, Season A, Season BWhere season names are saved in database and in their colums I would like to have pricing for rooms.How can I accomplish this - I dont know how to clearly explain - hope SQL will help:  String selectCommandBookings = "SELECT dbo.Service.Name AS Service, dbo.Room.Name AS Room, dbo.Season.Name AS Season, dbo.HSRSeason.Price"; selectCommandBookings += " FROM dbo.Season INNER JOIN"; selectCommandBookings += " dbo.HSRSeason ON dbo.Season.SeasonID = dbo.HSRSeason.SeasonID INNER JOIN"; selectCommandBookings += " dbo.HSR ON dbo.HSRSeason.HSRID = dbo.HSR.HSRID INNER JOIN"; selectCommandBookings += " dbo.Room ON dbo.HSR.RoomID = dbo.Room.RoomID INNER JOIN"; selectCommandBookings += " dbo.Service ON dbo.HSR.ServiceID = dbo.Service.ServiceID"; selectCommandBookings += " WHERE (dbo.HSR.HotelID = '" + hotelID + "')"; selectCommandBookings += " ORDER BY dbo.Service.Name, dbo.Room.Name, dbo.HSRSeason.Price"; 

View 2 Replies View Related

Find Models And Prices For All The Products By Maker B

Aug 9, 2013

Find out the models and prices for all the products (of any type) produced by maker B.

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

select product.type,
pc.price as pcprice,
laptop.price lapprice,
pc.model as pcmod,
laptop.model as lapmod
from product
join
pc on product.model=pc.model
join
laptop on laptop.model=product.model
where maker = 'B'

the syntex runs but its not displaying any results + I know that I have some extra columns there but its for some thing else I was trying

View 3 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

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

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

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







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