Analysis :: How To Insert New Members In Quantity
Jul 10, 2015Enterprise 2014 SQL Server - SSAS
I have a need to insert 50 - 100 new members each week.
Is there an SSIS procedure that will do this?
ADVERTISEMENT
Analysis :: Calculate Members At Certain Level In One Hierarchy As Sum Of Corresponding Members At A Certain Level
Jun 4, 2015Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008.
Ex.:
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states. Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
Analysis :: Querying Dimension Members In MDX?
Sep 17, 2015The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.
Is there any way i can get the Member_key in a set in rows section.
Like
With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube
I want the value as just like
Value
Employees, UniqueName
Analysis :: Find Date Ranges On Members And SUM
Jul 24, 2015I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] AS
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
Analysis :: Exclude Members From A Scope - MDX SSAS?
Sep 27, 2013I'm trying to do a currency conversion with an MDX statement in my Cube SSAS 2012.
Here is my script :
SCOPE( LEAVES([Entity]) );
SCOPE( LEAVES([Time]) );
SCOPE( LEAVES([Currency]));
SCOPE( [Account].[Account].[Total ACCOUNT].members)
THIS = ([Measures].[Value],[Currency].[Currency].[Local])*([Measures].[Value],[Account].[Account].[Fx Rate]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
The problem is I want to exclude frome the scope Currency my local Currency in order to make the conversion only if a currency (€,$, £) is selected. I tried the following syntax but it always return a "MDX script is not valid" :
SCOPE( LEAVES([Currency], Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except ([Currency].[Currency].members,[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( LEAVES([Currency] - [Currency].[Currency].[Local]);
Analysis :: Sorting Dimension Members From Fact
Dec 2, 2015I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:
The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.
Analysis :: Limit Date Dimension Members Dynamically?
Jul 7, 2015We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.
View 4 Replies View RelatedAnalysis :: SSAS Calculated Members For Each Week Of Month
Jul 6, 2015I have a date dimension with structure/hierarchy (year - month - wkofmonth - day) & measure count
This works fine when I insert wkofmonth in columns & count in values but when I add another measure it duplicates per week
Product CountWk1(jan) CountWk2(jan) CountWk3(jan) CountWk4(jan) SalesVolume
1 1 2 0 0 1000
Is there something I can do on the structure & do 4 calculated members pending how many weeks in a month, is there a simpler way where the next measure does not calculate by columns field.
If calculated member for each week, how do I do this, any example code?
Analysis :: Statistical Function Results In Calculated Members
Jul 8, 2015I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value]) returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])
Caching (storing) Calculated Members In Analysis Services 2005
Mar 2, 2006Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?
There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.
I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .
Thanks!
Analysis :: How To Give Permission To Read Dimensions Members In Hierarchy
Jun 9, 2015I am using a "Client" dimension that includes a "Holding - Client" hierarchy. I have to make sure, that only the appropriate roles may access appropriate members from this dimension, but I only have the information which role may access which ClientID - I do not have the information which HoldingID should be accessible. Also, "Client" is used as the key column of the dimension with "ClientID" and "HoldingID" as key columns. The hierarchy is strict, no client may belong to multiple holdings.
I cannot seem to find the right MDX for the allowed member set. My MDX expression would need to look like this:
[Client].[Holding - Client].[Client].&[*]&[123]
In this example I want to give access for client &123, no matter the holding, so &1&123 and &2&123 would be allowed.
Is this doable?
Analysis :: Attribute Relationships Impacts On Members Full Unique Name
Aug 17, 2015Our SSAS integration didn't initially use attribute relationships.Now that our system has been running for a few years and we have bigger databases, we think we need to add them to improve performance. So we're in the process of adding them but we found out that, when attribute relationships are added, the full unique name of our members all go from something like:
[DIM].[HIERARCHY].[LEVEL].&[GRANDPARENT].&[PARENT].&[MEMBER]
to something like:
[DIM].[HIERARCHY].[LEVEL].&[MEMBER]
It looks nice and SSAS will accept the longer names fine but it will return the short ones in response to 'discovery' requests and in the XMLA response of MDX queries. This is causing problems in our low level XMLA-based modules that assume the long names in and out. is there any clean way to use attribute relationships and still have SSAS generate the long member names. We fiddled with the various documented dim/attribute properties but to no avail. It also appears that some switches are obsolete.
Analysis :: Setup Multiple Default Members For A Dimension Attribute In SSAS?
Jun 2, 2015I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.
I want to set more than one default members for dimension report.
I've read all about how dimensions can have only one default member, but I need to set more than 1.
Transact SQL :: Get Max Date From Table Where Quantity Require Big From Quantity Shipped?
May 8, 2015I 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.
On Insert Need To Calculate A Quantity
Jan 31, 2008In 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.....
Reporting Services - Analysis Services - Displaying Dimension Members As Columns
Dec 3, 2006I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -
I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:
"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"
Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).
Here's what my query looks like -
SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]
Any ideas?
Thanks,
Arjun
Only Members Of The Sysadmin And Bulkadmin Fixed Server Roles Can Execute BULK INSERT
Aug 29, 2007We would like to use the bulk insert function to import large CSV files into a SSE database however we have serious concerns regarding giving all our users these high privleges. Is there some way around this can we give them the privleges temporarily do the insert and take it away again or some other solution.
Quantity Balance And Sum
Aug 14, 2007I 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
Need Help Updating A Quantity In Db Via A Loop
May 16, 2008When 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();
}
}
}
Quantity Of Entrys From Record Set
May 18, 2004Quantity 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
Deduct Quantity Of SKU From One DB To Next Database
Jan 3, 2013currently 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.
MAX Date Dependent On Quantity
Aug 20, 2013I 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] .....
Sum Quantity With Like Employee And Itemname
Nov 8, 2014I 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
Query Quantity Of Records
Nov 20, 2014I 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 RelatedGroup By Adding The Quantity
Dec 22, 2014I 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).
Breakdown Quantity Field
Sep 20, 2007I'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.
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
Use Datatableadapter To Decrease Item Quantity
Sep 3, 2007my 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 RelatedMake Procedure For Stock-quantity
Sep 3, 2007helo 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 RelatedHow To Reduce Quantity ? [shopping Cart]
Feb 5, 2008protected 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.
How To Get Quantity From Output Of Query Command
Jun 7, 2012how to get the quantity from output of sql query command for example, I could get the container name by below command
select container_name from sysibmadm.snapcontainer
get the container number by
select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART
now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.
Getting Total Quantity Without Using Aggregate Function
Nov 7, 2014I'm trying to write sum function in SQL with only basic operators without using aggregation function, but I don't know how or if it's possible or not!? I've searched but can't find anything on the internet
For example we have table order:
OrderID ProductID Quantity
----------------------------
1001 15 5
1002 35 7
1002 10 10
1003 50 30
1004 47 15
We can sum up the quantity with sum function in sql
SELECT SUM(Quantity) FROM OrderTable
How can I get the total quantity without using any aggregate functions?
Default Value On Quantity And Price Field
Jun 1, 2006should I set to "0" as a default value on a quantity and a price fieldor set to "null"?if a user enter nothing on the quantity or price field on a webbrowser, should I treat it as null or "0"? I am so confused about thisconcept. please advise me. thank you.
View 5 Replies View Related