How to substract value from its own column in same tabl through condition basis
-
I have a table where i want to subtract the value from same column value through condition basis..
My table is like this
i have group the table through item_id through the query
SELECT Item_id, sum(qty) FROM [#TempSOR_mb] group by Item_id and get the result as per below :
i want to subtract subitem_id 203 and 299 from qty how can i do it.
thanks
Manoj
-
Please provide your sample data in the form of CREATE TABLE/INSERT statements and also provide your desired results.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Sorry... originally missed the meaning here and took the post down and replacing it with the following...
Just use a WHERE NOT IN. If something more sophisticated is desired, we'd need a bit more information and, like Phil said, some readily consumable data. Please see the article at the first link in my signature line below for one way to do that properly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Try this:
SELECT Item_id, sum(qty)
FROM [#TempSOR_mb]
WHERE subitem_id not in (203,299)
group by Item_idThe WHERE clause acts as a filter to exclude subitem_id.
=======================================================================
-
I have a GUI where the user has to fill in the data activity- and sub-activity-wise in the form of length, width, and height of the structure measurement as per the below image(Details of Measurement). When the user fills out the form and submits it, it will pass the value through the store procedure program and create a #temp table to store the data. I want to sum the quantity group by item_id and subtract some subactivity from the sum of the group item_id.
the result should be Abstract of Cost.
My Store procedure like this:
ALTER PROCEDURE [dbo].[GetSOR_Abstract] @SOR_subactivityid smallint,@SOR_activityid smallint,@SOR_Block_id smallint=null,@SOR_year_id smallint=null,
@jsontempdata Nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @tempdata Nvarchar(4000) =null
--create a temp table for insert measuarement data
CREATE TABLE #TempSOR_mb (
item_id Smallint,
subitem_id Smallint,
no Smallint,
lngt numeric(18, 2),
wid numeric(18, 2),
hd numeric(18, 2),
qty numeric(18, 2),
);
insert into #TempSOR_mb(item_id,subitem_id,no,lngt,wid,hd,qty) select item_id,subitem_id,no,lngt,wid,hd,qty from OPENJSON(@jsontempdata) with (item_id smallint,subitem_id smallint,no smallint,lngt numeric(18, 2),wid numeric(18, 2),hd numeric(18, 2),qty numeric(18, 2));
Set @tempdata = (select sum(qty) from #TempSOR_mb where subitem_id in(203,299))
print @tempdata
SELECT Item_id, sum(qty) FROM [#TempSOR_mb] group by Item_id
CREATE TABLE #TempSORitemsubitem (
SOR_Item_id Smallint,
SOR_Item_name NVARCHAR(255),
SOR_Unit NVARCHAR(200),
SOR_Item_rates numeric(18, 3),
SOR_year_id smallINT,
SOR_Block_id smallInt,
SOR_Subitem_id smallINT,
SOR_Subitem_name NVARCHAR(255),
SOR_Subactivity_id smallINT,
SOR_Activity_id smallINT,
);
INSERT INTO #TempSORitemsubitem(SOR_Item_id, SOR_Item_name,SOR_Unit,SOR_Item_rates,SOR_year_id,SOR_Block_id,SOR_Subitem_id, SOR_Subitem_name,SOR_Subactivity_id,SOR_Activity_id)
SELECT
I.SOR_Item_id,
I.SOR_Item_name,
I.SOR_Unit,
IR.SOR_Rates,
IR.SOR_Year_id,
IR.Block_id,
SI.SOR_Subitem_id,
SI.SOR_Subitem_name,
SI.SOR_Subactivity_id,
SI.SOR_Activity_id
FROM
[01_SOR_Item] I
LEFT JOIN
[02_SOR_Subitem] SI ON I.SOR_Item_id = SI.SOR_Item_id
LEFT JOIN
[06_SOR_Rates] IR ON I.SOR_Item_id = IR.SOR_Item_id;
-- Select data from the temporary table
if @SOR_Block_id =0
begin
SELECT SOR_Item_id, AVG(SOR_Item_rates) as SOR_Rates,SOR_Item_name,SOR_Unit FROM #TempSORitemsubitem where SOR_Block_id=@SOR_Block_id or @SOR_Block_id=@SOR_Block_id and SOR_Subactivity_id=@SOR_subactivityid and SOR_Activity_id=@SOR_activityid and SOR_year_id=@SOR_year_id group by SOR_Item_id,SOR_Item_name,SOR_Unit order by SOR_Item_id;
end
else
begin
SELECT SOR_Item_id, AVG(SOR_Item_rates) as SOR_Rates,SOR_Item_name,SOR_Unit FROM #TempSORitemsubitem where SOR_Block_id=@SOR_Block_id and SOR_Subactivity_id=@SOR_subactivityid and SOR_Activity_id=@SOR_activityid and SOR_year_id=@SOR_year_id group by SOR_Item_id,SOR_Item_name,SOR_Unit order by SOR_Item_id;
end
-- Drop the temporary table
DROP TABLE #TempSORitemsubitem;
END;you can see in measurement details S.No 3 subtract total value (6.97-1.43) = 5.54 m3 and 5.54 value is showing in abstract of cost table in S.No3
thanks
Manoj
-
Once again, if you want coding assistance, please provide consumable sample data for us to experiment with.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin wrote:
Once again, if you want coding assistance, please provide consumable sample data for us to experiment with.
In other words, do like I previously suggested... read'n'heed the article at the first link in my signature lines section below.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
table data is like this and out put of the data is mentioned in earlier post
-
I tried pasting that into SSMS and hitting F5, but got a parsing error.
You clearly have not taken the time to read the article to which Jeff directed you. Or, worse still, you did read it and decided you could not be bothered making the extra effort.
Yet you expect us to take the time to code you a solution. Can you see how this seems unfair?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - manojsemwal1 40990 wrote:
table data is like this and out put of the data is mentioned in earlier post
You didn't read the article at the link I provided for how to provide sample data. We need to be able to do tests and we can't do that with a graphic. Help us help you. Read the article and provide the data as code that inserts into the sample table, which should also be included.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry Jeff for the inconvenience. kindly see the below code
INSERT INTO [dbo].[10_SOR_MB]
([item_id]
,[subitem_id]
,[no]
,[lngt]
,[wid]
,[hd]
,[qty])
VALUES
(<item_id, smallint,>
,<subitem_id, smallint,>
,<no, smallint,>
,<lngt, decimal(18,2),>
,<wid, decimal(18,2),>
,<hd, decimal(18,2),>
,<qty, decimal(18,2),>)
GO
INSERT INTO "10_SOR_MB" ("hd", "item_id", "lngt", "no", "qty", "subitem_id", "wid")
VALUES
('0.4','101','0.1','1','0.01','290','0.2'),
('0.5','103','0.2','2','0.06','201','0.3'),
('0.6','103','0.3','3','0.22','202','0.4'),
('0.7','103','0.5','4','0.70','203','0.5'),
('0.3','104','0.5','5','0.45','299','0.6'),
('0.3','107','0.6','6','0.76','204','0.7'),
('0.7','107','0.7','7','2.74','205','.8')Manoj
-
Nice try, but there are a few issues.
- You have not included a CREATE TABLE, but I was able to get that from your proc definition a few posts earlier
- The code fails. The table contains numeric datatypes, yet you have set them all to be literal character strings in your VALUES clause.
Having fixed those, I got to the following code
DROP TABLE IF EXISTS #TempSOR_mb;
CREATE TABLE #TempSOR_mb
(
item_id SMALLINT
,subitem_id SMALLINT
,no SMALLINT
,lngt NUMERIC(18, 2)
,wid NUMERIC(18, 2)
,hd NUMERIC(18, 2)
,qty NUMERIC(18, 2),
);
INSERT #TempSOR_mb
(
item_id
,subitem_id
,no
,lngt
,wid
,hd
,qty
)
VALUES
(0.4, 101, 0.1, 1, 0.01, 290, 0.2)
,(0.5, 103, 0.2, 2, 0.06, 201, 0.3)
,(0.6, 103, 0.3, 3, 0.22, 202, 0.4)
,(0.7, 103, 0.5, 4, 0.70, 203, 0.5)
,(0.3, 104, 0.5, 5, 0.45, 299, 0.6)
,(0.3, 107, 0.6, 6, 0.76, 204, 0.7)
,(0.7, 107, 0.7, 7, 2.74, 205, .8);
SELECT *
FROM #TempSOR_mb tsm;This runs successfully, providing the following output:
Keen-eyed viewers of this thread will notice that this image bears only a passing resemblance to the image you provided originally:
How is anyone supposed to get to your desired results using the data you have provided here?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - manojsemwal1 40990 wrote:
Sorry Jeff for the inconvenience. kindly see the below code
INSERT INTO [dbo].[10_SOR_MB]
([item_id]
,[subitem_id]
,[no]
,[lngt]
,[wid]
,[hd]
,[qty])
VALUES
(<item_id, smallint,>
,<subitem_id, smallint,>
,<no, smallint,>
,<lngt, decimal(18,2),>
,<wid, decimal(18,2),>
,<hd, decimal(18,2),>
,<qty, decimal(18,2),>)
GO
INSERT INTO "10_SOR_MB" ("hd", "item_id", "lngt", "no", "qty", "subitem_id", "wid")
VALUES
('0.4','101','0.1','1','0.01','290','0.2'),
('0.5','103','0.2','2','0.06','201','0.3'),
('0.6','103','0.3','3','0.22','202','0.4'),
('0.7','103','0.5','4','0.70','203','0.5'),
('0.3','104','0.5','5','0.45','299','0.6'),
('0.3','107','0.6','6','0.76','204','0.7'),
('0.7','107','0.7','7','2.74','205','.8')Manoj
I guess I'm going to have to update that old article. Also, the use of quoted identifiers (") everywhere is a bit annoying and indicates that you might not be using SQL Server/T-SQL but maybe you are. Dunno. It's also a really bad idea (some would call it a "worst practice") to name tables that start with non-numeric characters except for properly created Temp Tables. It's also not a good idea to use something other that Temp Tables for example data on a forum.
Looking back at that old article, while it's still very pertinent, it could use an update to use VALUES instead of SELECT/UNION ALL.
That being said, I was able to salvage your efforts at making and populating a test table for this issue. Here's the tested code and Phil is correct... it doesn't produce the same data as what is in the graphic (but probably doesn't matter for this particular post) even when we use the same column order that you did in your INSERT/INTO (which Phil did not).
--===== Conditional drop the test table to make reruns easier.
-- Followed by a "GO" to make changes to the table easier.
DROP TABLE IF EXISTS #10_SOR_MB;
GO
--===== Define the test table.
-- This should include a PK if there is one.
-- Looking at the inluded data, It would appear that the
-- "no" column is the PK here might be the PK but not sure.
CREATE TABLE #10_SOR_MB
(
item_id smallint
,subitem_id smallint
,no smallint
,lngt decimal(18,2)
,wid decimal(18,2)
,hd decimal(18,2)
,qty decimal(18,2)
)
GO
--===== Populate the test table
INSERT INTO #10_SOR_MB
(hd, item_id, lngt, no, qty, subitem_id, wid)
VALUES ('0.4','101','0.1','1','0.01','290','0.2')
,('0.5','103','0.2','2','0.06','201','0.3')
,('0.6','103','0.3','3','0.22','202','0.4')
,('0.7','103','0.5','4','0.70','203','0.5')
,('0.3','104','0.5','5','0.45','299','0.6')
,('0.3','107','0.6','6','0.76','204','0.7')
,('0.7','107','0.7','7','2.74','205','0.8')
;
--===== View the content of the test table.
SELECT * FROM #10_SOR_MB
;
GOResults:
Here's the original graphic...
Like I said though, the differences in data to the right of the "no" column don't seem to matter for this particular problem. I'll be back later tonight unless someone beat's me to the answer, which would be fine by me. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Dear Phill,
Sorry for that, in my previous post column values are shifted .kindly see the revised values
INSERT #TempSOR_mb
(
item_id
,subitem_id
,no
,lngt
,wid
,hd
,qty
)
VALUES
(101,290,1,0.10,0.20,0.20,0.00),
(103,201,2,0.20,0.30,0.30,0.04),
(103,202, 3,0.30,0.40,0.40,0.14),
(103,203,4,0.40,0.50,0.50,0.40),
(104,299,5,0.50,0.60,0.70,1.05),
(107,204,6,0.50,0.70,0.80,1.68),
(107,205,7,0.60,0.40,0.80,1.34)Actually i want to do sum of qty in subitem_id 203 and 299 and subtract it form item_id 103 group by item_id.
i had tried but its subtract one by one.
SELECT Item_id,
(
CASE item_id
WHEN 103 THEN (qty-0.5)
ELSE qty
END
) AS Amount_Owed
FROM [#TempSOR_MB]Thanks
Manoj
-
Actually i want to do sum of qty in subitem_id 203 and 299 and subtract it form item_id 103 group by item_id.
OK, based on this description, try this
WITH aggs
AS (SELECT agg = SUM (tsm.qty)
FROM #TempSOR_mb tsm
WHERE tsm.subitem_id IN ( 203, 299 ))
SELECT tsm.item_id
,qty = CASE
WHEN tsm.item_id = 103 THEN
SUM (tsm.qty) - AVG (a.agg)
ELSE
SUM (tsm.qty)
END
FROM #TempSOR_mb tsm
CROSS JOIN aggs a
GROUP BY tsm.item_id;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply