Quantity Balance And Sum

Aug 14, 2007

I have following table structure


CREATE TABLE [dbo].[EMPLOYEE] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJECT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[PPE_STOCK] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUANTITY] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PPE_ITEM_ISSUE] (
[PII_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE_REQUEST] [datetime] NULL ,
[QUANTITY_ISSUE] [int] NULL ,
[DATE_ISSUE] [datetime] NULL
) ON [PRIMARY]
GO


I want to calcalate the balance quantity avaiable in the stock.

PPE_STOCK => STOCK OF QUANTITY TABLE
PPE_ITEM_ISSUE => STOCK QUANTITY ISSUE TO EMPLOYEE TABLE

query retrieve like this.

Name....EMP_ID....PPE_NAME,..QUANTITY_ISSUE.Stock Qty.Balance Qty
-----------------------------------------------------------------
Martin..p0012456..Safety Gloves 1 .......100........99
Martin..p0012566..Safety Glass 1 .......100........99
Peter...p00123456.Safety Gloves 1........100........98
Jone....p00987654.Safety Helmet 1........100........99
Khan....p0012122..Safety Helmet 1........100........98
....

stock quanity minus the issue qauantity and retreive balance QTY
how ?

How can calcuate / retrieve the balance quantity by the query ?

Please us query calcuation.

regards
Martin

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Update Row Beginning Balance With Previous Row Trial Balance

Aug 1, 2014

I have a table with Million plus records. I have been able to calculate the Trial_Balance for all months.

Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I thought I could just do a self Join but this is not working.

UPDATE dbo.TrialBalance
SET Beginning_Balance_Debit = B.Trial_Balance_Debit
FROM (SELECT DATEADD(month, -1, Calendar_Month) AS PrevCalMonth,Trial_Balance_Debit
FROM dbo.TrialBalance) AS B INNER JOIN dbo.TrialBalance A
ON b.PrevCalMonth=A.Calendar_Month

[Code] ....

View 5 Replies View Related

Transact SQL :: Get Max Date From Table Where Quantity Require Big From Quantity Shipped?

May 8, 2015

I need sql query to select max date shipment where sum of quantity required > sum of quantity shipped from sales line table but i dont know how to make my sales line table as following

shipment date   quantity required  quantity shipped

07/05/2015          200                             100
07/05/2015          300                             300
07/05/2015          100                              50
06/05/2015          200                             100
06/05/2015          200                             200

Here the result of query i need is   max shipment date is 07/05/2015 and quantity required is  600 and quantity shipped is 450 so tat i must select this date because quantity Required is big from quantity shippedmy question How i write the query get result above in sql server 2005.

View 9 Replies View Related

Update Balance

Jul 25, 2006

I am trying to write a trigger which updates the customers balance when an invoice is deleted. The tables are created and I can post if needed, but I get a compilation error when I use the follow code.

create or replace trigger trg_updatecustbalance2
after delete on invoice
for each row
begin
update customer
set cust_balance = cust_balance - inv_amount
where customer.cust_num = invoice.cust_num;
end;
/

Can anyone help me figure out where my error?

View 8 Replies View Related

Balance Off 2 Records?

Feb 20, 2008

dear all experts please kindly provide ur help thanks.



suppose i hv 2 records in a table as follow:





customer_id status qty date

123456 install 24 2008-02-20

123456 disconnect -24 2008-02-20







how can i exclude these 2 records from my table? actually you can see the net gain is 0, but in my report reflects install qty is 24 and disconnect qty is -24. i dont want these 2 records reflect in my report, how can i achieve such result? thanks in advance!



View 8 Replies View Related

How To Balance The Database

Jan 30, 2007

Hello,

I started to use data mining sql server recently, and I have a little problem:

I'm trying to predict a boolean variable, but my database has only 1/4 of true results, because of that my predict probability is a little bit low in this kind of cases.

My question is: Is there a way of increasing the importance of a database entry in order to avoid editing a database with 40000 entries?

Thank you very much.

View 3 Replies View Related

SQL Clustering With Load Balance

Apr 18, 2006

For our application we are planning 4 Appserver/webservers with load balancing and 4 SQL servers in cluster with active/active setup. We are looking for some info on the active/active setup for SQL server 2000 Clustering.

1. Which cluster node replication method (2n, 2n +p or n+1) we should go for.
2. How operating system "windows 2003 server" should be configured and its role in failover.
3. How active/active failover setup for SQL Server 2000 really works and how much data loss happens in case of failover.
4. For web applications, how the session state is managed in SQL Clustering

View 4 Replies View Related

Balance Sheet Calculation

Apr 28, 2008

Hello Friends,

I have a doubt regarding balance sheet calculation in my Software. I am using a stored procedure for these calculations. I have some commands for calculation as given below:

/******************************************************************/
DECLARE @car_req FLOAT
DECLARE @amt_gen FLOAT
DECLARE @amt_shp FLOAT
DECLARE @amt_sho FLOAT
DECLARE @bal_gen FLOAT
DECLARE @bal_shp FLOAT
DECLARE @bal_sho FLOAT
DECLARE @tab_id INT

CREATE TABLE #Temp
(
rec_id INT IDENTITY NOT NULL
,cargo_requiredFLOAT
,amount_generalFLOAT
, amount_shipFLOAT
, amount_shoreFLOAT
,balance_generalFLOAT
, balance_shipFLOAT
, balance_shoreFLOAT
)

INSERT INTO #Temp (cargo_required, amount_general, amount_ship, amount_shore)
SELECT 5000, 1500, 1450, 1490
UNION ALL
SELECT 0, 3500, 3500, 3500
UNION ALL
SELECT 7000, 4500, 4550, 4560
UNION ALL
SELECT 0, 2500, 2000, 2000


DECLARE temp_cursor CURSOR FAST_FORWARD FOR
SELECT rec_id, cargo_required, amount_general, amount_ship, amount_shore
FROM #Temp

OPEN temp_cursor

FETCH NEXT FROM temp_cursor
INTO @tab_id, @car_req, @amt_gen, @amt_shp, @amt_sho

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@car_req > 0)
BEGIN
UPDATE #Temp
SET balance_general = @car_req - amount_general
,balance_ship= @car_req - amount_ship
,balance_shore= @car_req - amount_shore
WHERE rec_id = @tab_id
END
ELSE
BEGIN
UPDATE #Temp
SET balance_general = @bal_gen - amount_general
,balance_ship= @bal_shp - amount_ship
,balance_shore= @bal_sho - amount_shore
WHERE rec_id = @tab_id
END

SELECT @bal_gen = balance_general, @bal_shp = balance_ship, @bal_sho = balance_shore
FROM #Temp
WHERE rec_id = @tab_id

FETCH NEXT FROM temp_cursor
INTO @tab_id, @car_req, @amt_gen, @amt_shp, @amt_sho
END

SELECT * FROM #Temp

CLOSE temp_cursor
DEALLOCATE temp_cursor

DROP TABLE #Temp

/***************************************************************/

This is what acutally I am having in my stored procedure except the temporary table. The values which I am inserting here will get from my actual tables. What I need is to calculate balance. If you run this script you can see how the balance is calculating using the cursor.

I want an efficient way to calculate the same. I know cursor is not at all good option in stored procedures. Do you have any Idea to make this script in an efficient and simple way?

Thanks and Regards
Boney

View 2 Replies View Related

Running Balance Calculation - PLEASE HELP

Jun 23, 2008

Hi guys, I've really been struggling with this issue for quite a while and the solution still elludes me.
If anyone can help me I will worship you as a god for ever! [Wink]

The Scenario is as follows:
I have a table where several documents are linked to each other via a foreign key called ReconNum.
Basically this table is used to link Invoices, Credit Notes and Payments to each other.
Thus you get the following layout:

ReconNum LineID DocID DocType ReconAmount
111 0 101 Payment 20 000
111 1 202 Credit Note 12 0000
111 2 303 Payment 5500
111 3 404 Invoice 10 000
111 4 505 Credit Note 22500
111 5 606 Invoice 30 000
111 6 607 Invoice 20 000

What I need to do is assign values from each of the Payment/Credit Note documents to the Invoices. Thus getting the following result:

Inv# DocID DocType AmountApplied DocBal InvBal
404 101 Payment 10 000 10 000 0
606 101 Payment 10 000 0 20 000
606 202 Credit Note 12 000 0 8 000
606 303 Payment 5500 0 2 500
606 505 Credit Note 2500 20 000 0
607 505 Credit Note 20 000 0 0

I've come close, but it seems to be the old chicken or the egg problem.i.e. I need one field to calculate the other and vica-versa.
(Amount Applied, DocBal and Inv Bal are all dependant on each other)

NOTE: The only field I'm really interested in is the AmountApplied field, but amount applied depends on the DocBal and InvBal fields.

I need a running balance on both the Amount Due on the Invoice, and the Amount Availble on the document being assigned to the invoices. The problem is that I'm trhowing the data into a temporary table and cant use fields in the temp table for my calculation.
i.e. AppliedSum = if (Doc.Available amount on Doc < Invoice Balance) then Doc.Available Amount
elseif (Doc.Available amount on Doc > Invoice Balance) then Invoice Balance

Invoice Balance = Invoice Tot - sum(Applied Amounts)

Any help would be breatly appreciated!

View 15 Replies View Related

Balance Sheet Calculation

Apr 28, 2008

Hello Friends,

I have a doubt regarding balance sheet calculation in my Software. I am using a stored procedure for these calculations. I have some commands for calculation as given below:DECLARE @bal_gen FLOAT
DECLARE @bal_shp FLOAT
DECLARE @bal_sho FLOAT
DECLARE @tab_id INT

CREATE TABLE #Temp
(
rec_id INT IDENTITY NOT NULL
, cargo_required FLOAT
, amount_general FLOAT
, amount_ship FLOAT
, amount_shore FLOAT
, balance_general FLOAT
, balance_ship FLOAT
, balance_shore FLOAT
)

INSERT INTO #Temp (cargo_required, amount_general, amount_ship, amount_shore)
SELECT 5000, 1500, 1450, 1490
UNION ALL
SELECT 0, 3500, 3500, 3500
UNION ALL
SELECT 7000, 4500, 4550, 4560
UNION ALL
SELECT 0, 2500, 2000, 2000



SELECT @bal_gen=0,@bal_shp=0,@bal_sho=0
SELECT @tab_id=MIN(rec_id)
FROM #Temp

WHILE @tab_id IS NOT NULL
BEGIN
UPDATE #Temp
SET @bal_gen=balance_general =CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_gen END - amount_general
, @bal_shp=balance_ship = CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_shp END - amount_ship
, @bal_sho=balance_shore = CASE WHEN cargo_required > 0 THEN cargo_required ELSE @bal_sho END - amount_shore
WHERE rec_id = @tab_id

SELECT @tab_id=MIN(rec_id)
FROM #Temp
WHERE rec_id >@tab_id
END

Select * FROM #Temp
This is what acutally I am having in my stored procedure except the temporary table. The values which I am inserting here will get from my actual tables. What I need is to calculate balance. If you run this script you can see how the balance is calculating using the WHILE loop.

I want an efficient way to calculate the same. Is there any way to calculate it without using loop methods.
Thanks and Regards
Boney

View 3 Replies View Related

T-SQL (SS2K8) :: Add Balance Of Previous Row To Current Row

Jul 2, 2014

I am novice to intermediate writer of T-SQL. Here is my current Query:

SELECT [FISCALYEAR],
[ACCTPERIOD],
SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit,
[POSTINGTYPE]
FROM [dbo].[TB_Lookup]
WHERE [POSTINGTYPE]='Profit & Loss'
GROUP BY [FISCALYEAR],[ACCTPERIOD], [POSTINGTYPE]
ORDER BY acctperiod ASCand this is what is produces.

FISCALYEARACCTPERIODTrialBalanceDebitPOSTINGTYPE
2014 201401 282361372.13000 Profit & Loss
2014 201402 227246272.86000 Profit & Loss
2014 201403 315489534.33000 Profit & Loss
2014 201404 287423793.76150 Profit & Loss
2014 201405 256521290.76000 Profit & Loss
2014 201406 65582951.30000 Profit & Loss

Now I need a way to add another field that takes the TrialBalanceDebit from current ACCTPERIOD and adds it to the Previous ACCTPERIOD TrialBalanceDebit.

View 9 Replies View Related

Load Balance Clustering On 2005?

Jul 8, 2005

My company is planning on using a datacenter for our customers.  These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)

View 6 Replies View Related

Make Procedure To Check Balance &> Or &< Totalcost

Sep 9, 2007

 helo  all...,i have create procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo 
 in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill            balance
111222            200$
222444            10$ 
this is a list of order table 
user_id            no_order            date            totalcost
    a                     1                  1/1/07             50$
when customer insert no_bill(111222) in page and click a button,  then bill table became
no_bill            balance
111222            150$
222444            10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough" 
mystore procedure like:ALTER PROCEDURE [dbo].[pay](    @no_bill AS INT,    @no_order AS int,    @totalcost AS money)ASBEGIN    BEGIN TRANSACTION            DECLARE @balanc AS money                SET @balanc= (SELECT [balance] FROM Bank.dbo.bill WHERE [no_bill] = @no_bill)        UPDATE [bank.dbo.bill]        SET            [balance] = @balanc - @totalcost        WHERE            [no_bill] = @no_bill    COMMIT TRANSACTIONEND it can decrease money in bank, but i want it ceck money if balance > totalcost, so balance-totalcost,if balance<totalcost,so error message"sorry, your balance not enough"is it can make in procedure?thx... 

View 2 Replies View Related

Running Sum Is A Unique ID Field - Balance On Certain Date?

Dec 20, 2012

I am working on a software primarily related to accounting with visual basic

Many of the problems and proposed several solutions for running sum, but I have read almost all of these interpretations,

have applied when running sum is a unique ID field. but almost all of the functioning of accounting reports are in chronological order. So he questioned what was the balance on 05.05.2012.

ID of the process is done, if the user enters a process backward a few things since then, the transaction date is old, but the ID is new, are experiencing problems. eg

ID DATE DEBIT CREDIT BALANCE

1 02.02.2012 100.00 0.00 100.00

6 04.04.2012 0.00 150.00 -50.00

3 02.05.2012 70.00 0.00 20.00

4 02.05.2012 80.00 0.00 100.00

2 06.06.2012 120.00 0.00 220.00

must balance the figure above.

if ID = 2 record, the record date 06/06/2012 and if the ID = 6, date 04/04/2012, the date the order is made if the listing is experiencing trouble getting balance. 2 records in the same day when the balance at worst formula breaks down. The big point is that I hang out in my project.

the only solution is to use a temporary table or writing about it in Visual Basic seems to calculate. the use of temporary table is not a solution, but unfortunately also very fast.

View 1 Replies View Related

Clustering, Security, Performance, Load Balance

Nov 27, 2007

I think this question has been asked number of times. However, I amlooking for some specific information. Perhaps some of you can helpclose the gap. Or perhaps you can point me towards right direction.Perhaps this group can help me fill in ms-sqlserver related followingquestions.1. Do this database have data Clustering capabilities?1a. If yes, what mechanism is used such as shared disk, share nothing,etc.2. Do these dB have Security features?2a. If yes, what security features are supported? For instance do theysupport encryption or SSL connection?3. How does the database perform and what is the criteria for theperformance matrix?4. Do they have inbuilt load balance capabilities?I want to thank everyone for taking your time to read thiscorrespondence. I will also greatly appreciate your efforts in sharingyour thoughts.Regards,Manish

View 3 Replies View Related

Want To Load Balance And Cluster 3 SQL 2005 Servers

Oct 9, 2007

Currently we have a single SQL server. It went down and the higher ups were none to happy. Lots of money lost, down time, unhappy customers....the whole nine yards. They want to throw all sorts of money at the problem and want a solution that is high availability and provides load balancing. I think I came up with a solution.

3x Windows 2003 Enterprise Edition servers running SQL 2005 Standard in a cluster all connected to a SAN.

I'm guessing I need to set up Network Load Balancing in order to load balance the SQL database. What we would love to have happen is if one of the servers goes down, everything else just picks up. That coupled with our database that is increasing in size and transactions gets load balanced.

Am I going down the right path? Something else I should look at? Or that I am missing?

Thanks!

View 5 Replies View Related

Want To Load Balance And Cluster 3 SQL 2005 Servers

Oct 9, 2007

Currently we have a single SQL server. It went down and the higher ups were none to happy. Lots of money lost, down time, unhappy customers....the whole nine yards. They want to throw all sorts of money at the problem and want a solution that is high availability and provides load balancing. I think I came up with a solution.

3x Windows 2003 Enterprise Edition servers running SQL 2005 Standard in a cluster all connected to a SAN.

I'm guessing I need to set up Network Load Balancing in order to load balance the SQL database. What we would love to have happen is if one of the servers goes down, everything else just picks up. That coupled with our database that is increasing in size and transactions gets load balanced.

Am I going down the right path? Something else I should look at? Or that I am missing?

Thanks and sorry for posting this in multiple places!

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

SQL Server 2012 :: Recurrent Balance That Resets When Hitting A Value

Oct 15, 2014

I have query that calculate a running balance, but I need to reset that balance when it reaches 240. Then start calculating the balance again from that value. For instance, let's see we have the following table:

YearAmmount
200049.95
200179.92
200279.92
2003114.99
2004120.00

[Code] ....

If I run the following query to get the running balance:

SELECT Year,
Ammount,
SUM(Ammount) OVER(ORDER BY Year ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM _YearlyTotals

I get:

YearAmmountRunningTotal
200049.9549.95
200179.92129.87
200279.92209.79
2003114.99324.78
2004120.00444.78

[Code] ....

I'm looking to reset the Running Total if it reaches 240, and continue calculating from there: Something like this:

YearAmmountRunningTotalAdjustment
200049.9549.9549.95
200179.92129.870.00
200279.92209.790.00
2003114.99240.00-84.78
2004120.00240.00-120.00

[Code] ....

Here is the code to create the table and insert data:

CREATE TABLE _YearlyTotals(
[Year] int NULL,
Amount numeric(18, 2) NULL
) ON [PRIMARY]
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2000,49.95)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2001,79.92)

[Code] .....

View 4 Replies View Related

Power Pivot :: Determine Opening And Closing Balance

Jun 3, 2015

I am preparing a monthly cash flow statement and for doing so, I need to determine the monthly opening and closing cash balance.  To simplify, this is what I did.

1. Dragged months to the columns labels

2. Created a slicer for selecting the Financial Year.  In this slicer, I chose 2015-16 i.e. April 1, 2015 to March 31, 2016.

Since I have data only for two months of this Financial Year i.e. April and May, only these two months show up in the column labels.

To determine the monthly opening cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),PREVIOUSMONTH(calendar[Date]))

To determine the monthly closing cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),calendar[Date])

Much to my surprise, I got the result as seen in the image below.  Cells B5, C6 and D5 are blank.  On going through my Bank Book, I realised that

1. There is no figure in cell B5 (Opening balance of April) because there was no transaction on the last day of March i.e. March 31.  The last transaction was on March 28

2. There is no figure in cell C6 (Closing balance of May) because there was no transaction on the last day of May i.e. May 31.  The last transaction was on May 30

3. There is no figure in cell D5 (Opening balance of June) - same reason as mentioned in point 2 above.

As seen in the image, I have also computed the monthly "Last date of previous month" and "last date of current month" but do not know how to make use of them in computing the opening and closing Cash balances.

Please also note that there can be multiple transactions on the last day of any month.  For e.g., let's say the last day of transaction in May 2015 was May 30 (not may 31) but there were multiple transactions on this last day (both inflow and outflow). 

So I cannot simply determine the last day of transaction and take MAX/MIN/SUM that day.  I have to take the final balance on that day.

How to solve this problem i.e. in determining the opening and closing cash balances.

View 10 Replies View Related

Need Help Updating A Quantity In Db Via A Loop

May 16, 2008

When a user orders an amount of a given product, I want to update the available quantity in my db.  I use a loop to insert each ordered product item and the needed quantity into an ORDERED_ITEMS table (these items are tied to an order record in another table).  The available_quantity is in the STOCK table.  Here is my code.  Can you help me out with how I can achieve this? (I know my queries should be in SP's, but for now while I'm getting everything the way I want it, it's easier to write it directly in my code.)
 Thanks!
 //insert the individual items
string items_to_ship = "";

foreach (GridViewRow rw in GridView1.Rows)
{
if (rw.RowType == DataControlRowType.DataRow)
{
if (((TextBox)rw.Cells[4].FindControl("qtyneeded")).Text != "")
{
int qty = Convert.ToInt32(((TextBox)rw.Cells[4].FindControl("qtyneeded")).Text);
int itm = Convert.ToInt32((rw.Cells[0].Text));

SqlConnection mycn2 = new SqlConnection(myConnectionStr);
SqlCommand myCommand4;
SqlDataReader rdr = null;
int new_qty = 0;
myCommand4 = new SqlCommand
("INSERT INTO SHP_ORDERED_ITEMS (ship_id, item_id, quantity) values(@ship_id,@item_id,@quantity);" + "SELECT item_description, qty_available from shp_stock where item_id = @item_id;", mycn2);

myCommand4.Parameters.Add("@ship_id", SqlDbType.UniqueIdentifier).Value = shipmentID; myCommand4.Parameters.Add("@item_id", SqlDbType.Int).Value = itm;
myCommand4.Parameters.Add("@quantity", SqlDbType.Int).Value = qty;

myCommand4.Connection.Open();
rdr = myCommand4.ExecuteReader();
while (rdr.Read())
{
// create a list of item descriptions and quantities of each, for use in the email
items_to_ship += (string)rdr["item_description"] + " (" + qty.ToString() + ")<br>";


}
myCommand4.Connection.Close();

}
}

View 1 Replies View Related

Quantity Of Entrys From Record Set

May 18, 2004

Quantity of entrys from record set
Hi All!

In a database (Access) i have many lines. In one of colomnes (Label) i have entrys "AAA", "BBB", "CCC", ... . I would like to know how many "AAA" and

how many "BBB" we have in database.

Is this possible to achieve this by using i = RecordSet.Fields (Label). ... mixad with other functions where i is an integer and RecordSet a record set?

what a SQL query cal deliver a i as
Integer, where i is quantity of lines
labeled with "AAA".

How can i do this with VBA?

Thank You all!
Alexander

View 1 Replies View Related

Deduct Quantity Of SKU From One DB To Next Database

Jan 3, 2013

currently we have some shipping software that has a mqsql database locally.There are filters on the program and when an item is shipped from our warehouse it goes into a filter "Shipped".We have a SKU and also quantity for the product.

Now we have an inventory program that also has a mqsql database,this has the same SKU as the shipping software database.what we hope to do is when an SKU is shipped in our software program(ie it Goes into the "Shipped" folder),it will deduct the quantity from the adjacent SKU in the inventory program database.

Note that the 2 databases are independent from each other but would like them to be in effect linked to each other.

View 1 Replies View Related

MAX Date Dependent On Quantity

Aug 20, 2013

I am trying to query only the Max date dependant on quantity

Create Table dbo.TestParts
(Part char(30), Desc1 char(50), Desc2 char(50));
Create Table dbo.TestStructure
(Model char(30), Part char(30), EDATE smalldatetime, QtyPer float);
GO
Insert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'),

[Code] ....

Looking for a return of:

PART Description EDATE QtyPer
101112 Widget B ..... 2012-12-03 3
101113 Widget C ..... 2012-12-03 5
101114 Widget D ..... 2012-12-01 1

Widget A should not show because the last date the qty was changed to Zero

Closest that I have come..........(which is pulling the part 101111 which should be incorrect)

Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description,
ts.EDATE, ts.QtyPer
FROM testing.dbo.TestStructure ts
Inner Join (Select Part,MAX(EDATE) as Date
FROM testing.dbo.TestStructure
WHERE QTYPER <> '0'

[Code] .....

View 3 Replies View Related

Sum Quantity With Like Employee And Itemname

Nov 8, 2014

I have a table Item_used like this

Itemname Employee Quantity
pencil samlopez 10
pencil samlopez 5

All I want is to make a report that sum all the quantity of the same items with the same employee like this

Itemname Employee Quantity
pencil samlopez 15

View 1 Replies View Related

Query Quantity Of Records

Nov 20, 2014

I need to query SQL Server Express 2012 records to find 6 serial numbers that are all assigned to one common unique number. Normally in this use case, 12 serial numbers are assigned to one common unique number, so I'm trying to find the odd entry.

View 2 Replies View Related

Group By Adding The Quantity

Dec 22, 2014

I have the following query

Select FullItemName,
Region, IssuedQuantity
from Transactions.TransactionBaseMain
where EnvironmentID = 34
and ModeID=2 and UnitOfIssueID=73 AND itemid=5605 and TransactionType in ('Issue')
and DATEDIFF(DD,TransactionDate,GETDATE())<30
Group by FullItemName,Region,IssuedQuantity
Order by FullItemName,Region,IssuedQuantity

I need to group the IssuedQuantity by region. (Add up the IssuedQuantity for the region).

View 1 Replies View Related

Breakdown Quantity Field

Sep 20, 2007

I'll give a basic example of my table.
OrderID, ProductID, Quantity
1,1,4

I would like to display the above row 4x (hence qty of 4)
1,1,"item 1"
1,1,"item 2"
1,1,"item 3"
1,1,"item 4"

Any suggestions? Thanks.

View 7 Replies View Related

On Insert Need To Calculate A Quantity

Jan 31, 2008

In my Insert into #TempTable I need to calculate the 'qty_wasted' as difference between 'qty_received' and 'qty_used' Where would I put the calc statement?


CREATE TABLE#TempTable
(
job_date datetime,
job_number char(15),
cost_code char(15),
qty_received decimal(8,2),
qty_used decimal(8,2),
qty_wasted decimal(8,2),
productId char(25),
plant_id char(10)
)


INSERT INTO #TempTable (job_date, job_number, cost_code, qty_received, qty_used, qty_wasted, productId, plant_id)
SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as productId, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id,
qty_received = CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END,
qty_used = CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN.....

View 1 Replies View Related

Calculate Price For Quantity

Feb 20, 2008



Hello all, I have an interesting question about calculating a price in my database for a quantity entered. I have a join table that I store ProductID, SizeID, Quantity and Price. The price for a particular product changes based most often on the quantity ordered. For example, if you order one unit of a widget the price is 10.00, however if you order one dozen units of the same widget the price drop to 9.75 and so on.

Here is a sample of my table....columns are in the order I specified above.

1, 1, 1, 10.00
1, 2, 1, 10.00
1, 3, 1, 10.00
1, 1, 6, 9.90
1, 2, 6, 9.90
1, 3, 6, 9.90
1, 1, 12, 9.75
1, 2, 12, 9.75
1, 3, 12, 9.75
1, 1, 24, 9.50
1, 2, 24, 9.50
1, 3, 24, 9.50
1, 3, 36, 9.30
1, 2, 36, 9.30
1, 1, 36, 9.30


So depending on if my widget is available in certain sizes, the second column, then each product has an price for the size id and quantity at which the price break occurs.

I use this stored procedure to return the price or price range based on the input parameters entered.

CREATE PROCEDURE dbo.sp_GetPrice
@ProductID INT,
@QuantityID INT = NULL,
@SizeID INT = NULL
AS
BEGIN
SET NOCOUNT ON
IF @QuantityID IS NOT NULL AND @SizeID IS NULL -- WE ARE L0OKING FOR A SPECIFIC PRICE BUT DO NOT HAVE A SIZE SPECIFIED
SELECT DISTINCT Price AS 'Price'
FROM join_ProductSizeQuantityPrice
WHERE ProductID = @ProductID
AND Quantity = @QuantityID

IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL -- WE WANT THE EXACT PRICE FOR THE SIZE AND QUANTITY SPECIFIED
SELECT Price AS 'Price'
FROM join_ProductSizeQuantityPrice
WHERE ProductID = @ProductID
AND SizeID = @SizeID
AND Quantity = @QuantityID
IF @SizeID IS NULL AND @QuantityID IS NULL
SELECT MIN(Price) AS 'Price Range' -- WE ARE LOOKING FOR A PRICE RANGE
FROM join_ProductSizeQuantityPrice
WHERE ProductID = @ProductID
UNION ALL
SELECT MAX(Price)
FROM join_ProductSizeQuantityPrice
WHERE ProductId = @ProductID

END
GO

So everything works great, however, when a user orders an quantity amount like 7, 13, 26 - which is not part of the table - I want to give them the discount available to them for the price break appropriate.

For example if a customer orders 16 widgets of size 2 the price break threshold they have crossed is one dozen, however they have not yet reached the next one which is two dozen. Therefore I want to offer the price associated with one dozen widgets of size id which is: $9.75. Once I have this a simple calculation of this price * 16 units would give me a total but my question is, how do I elegantly design this quantity / right price per unit calculation?

-Brian

View 9 Replies View Related

Use Datatableadapter To Decrease Item Quantity

Sep 3, 2007

 my friend said that to decrease quantity of item must use store procedure or trigger.. is there can use datatableadapter to decrease quantity? if yes.., so which the easy to use? procedure or trigger or tableadapter?ok, thx....

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

How To Reduce Quantity ? [shopping Cart]

Feb 5, 2008

protected void Buy_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
try
{String connStr = ConfigurationManager.ConnectionStrings["project_DataFile"].ConnectionString;
conn.ConnectionString = connStr;
conn.Open();
cmd.Connection = conn;ArrayList cart = (ArrayList)Session["Cart"];
{String oldSel = "SELECT Qty_warehouse FROM Producttable";
Int32 sel = 0;Int32.TryParse(oldSel, out sel);
string num = cart.Count.ToString();Int32 numQty = 0;
Int32.TryParse(num, out numQty);int nQty_warehouse = 0;
nQty_warehouse = sel - numQty;String sql = "UPDATE Producttable" + " SET Qty_warehouse =" + "'"+ nQty_warehouse +"'";
cmd.CommandText = sql;}
}catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
Response.Write("end.aspx");
}
}
 
from this code, i want to reduce product's quantity in database.
when I click buy button,it reduce old quantity and new quantity (quantity from cart when I want to buy it) in auto. 
please advise me.

View 3 Replies View Related







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