|
|
Hello,
I have two columns, a category and a value. Below is the data:
| Category | | Total | | Devices | | 38111 | | Plant | | 6063 | | Improvements | | 2266 |
I want to add two more columns: Zero and Sum
So the columns would be Category, Zero, Total, Sum
Is there any way to take the addition of Zero and Total and have the result in Sum, THEN take the result of Sum, and make it the answer in the next row of Zero, then repeat by adding zero and Total?
The reason why I want to do this is b/c I am trying to create a waterfall graph and this is the only way I can think to do it with the data that I have. Thanks for your time.
JM
I would be forever indebt to you if you could help out. Thanks!
|
|
|
|
|
After thinking about this for a bit, I think I would do this by creating a TEMP table and cursor to loop through the main table.
The TEMP table would be something like this:
Category Zero Total Sum
Devices 0 38111 38111
Plan 38111 6063 44147
Improvements 44174 2266 4640
Use the following code snipet to get you going with the logic ...
DECLARE myCURSOR CURSOR FOR
SELECT CATEGORY, TOTAL
FROM myTABLE
OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
SET @PBAL = 0.0
WHILE(@@FETCH_STATUS=0) BEGIN
SET @GTOTAL = @PBAL + @XTOTAL
SET @MYSQL = 'INSERT INTO #MYTEMP'
SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
SET @MYSQL = @MYSQL + @GTOTAL + ')'
PRINT @MYSQL
EXEC(@MYSQL)
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
END
CLOSE myCURSOR
DEALLOCATE myCURSOR
GO
Hope this helps you !
|
|
|
|
|
I will give that a shot, thank you for your help!
|
|
|
|
|
I am trying to update records in a table in sequence by setting up a Cursor and using Fetch like so..
DECLARE VPres_Cursor CURSOR FOR
select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID
OPEN VPres_Cursor;
FETCH NEXT FROM VPres_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM VPres_Cursor;
update dbo.VicePresident
set dbo.VicePresident.LastName = LastName
END;
CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;
While I am able to see the records in the Results window in SQL Management Studio, I am not able to see the actual update in the table.
47 Burr
48 Clinton
...
..
81 Humphrey
Can anyone help??
Thanks in advance..
Gymnast.
|
|
|
|
|
Do like this
DECLARE @LASTNAME VARCHAR(50)
DECLARE VPres_Cursor CURSOR FOR
select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID
OPEN VPres_Cursor;
FETCH VPres_Cursor INTO @LASTNAME
WHILE @@FETCH_STATUS = 0
BEGIN
update dbo.VicePresident
set dbo.VicePresident.LastName = @LASTNAME
FETCH VPres_Cursor INTO @LASTNAME
END;
CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;
I have only highlighted those lines which I think to be changed.
And it is working fine in my system.
Hope this helps.
Let me know in case of any concern
Niladri Biswas
modified on Thursday, July 2, 2009 2:45 AM
|
|
|
|
|
Using the update in a cursor I thought you needed Where Current of cursorname otherwise you set all the names to @Lastname
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Mycroft,
Actually I was not able to fathom your statement.
Did you mean that, the update will be based on some condition!
But actually, in the original post, the author of the code didn't specify any such thing by which I can use the Where Current of cursorname.
So I thought that I should do that in the way I did!.
You are always welcome to give a better solution so that I can also improve myself.
Niladri Biswas
|
|
|
|
|
When you do an update on a table if you do not apply a filter you will update every name to the variable. This updates the current record being accessed by the cursor.
Declare csrPV Cursor For
SELECT ColName_0
FROM stg.IRsCap
WHERE ID > @HeadRow
OPEN csrPV
FETCH NEXT FROM csrPV INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Value != ''
SET @LastValue = @Value
IF @Value = ''
BEGIN
UPDATE STG.IRsCap SET ColName_0 = @LastValue
WHERE CURRENT OF csrPV
END
FETCH NEXT FROM csrPV INTO @Value
END
CLOSE csrPV
DEALLOCATE csrPV
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Niladri Biswas
|
|
|
|
|
Thanks Mycroft! But I am yet to understand portions of the code you suggested:
SELECT ColName_0
FROM stg.IRsCap
I have a resultSet from a join that I would liek to use.. I assume I can still use that.
WHERE ID > @HeadRow
I assume that @HEADERROW is the value of the first IDin the table I am updating (which is 33 in my case).
IF @Value != ''
SET @LastValue = @Value
Here I assume that @Value is declared to hold the Value of the LastName
Your input is appreciated.
Thanks,
Rosh
|
|
|
|
|
Hello friends I have ha table like below
Amount GLID
2000 6523
4000 2356
4500 5623
I need to write a query that will show this table like below
6523 2356 5623
2000 4000 4500
How can I do this? Any help would be helpful.
Thanking in advance
Johnny
|
|
|
|
|
I believe what you need is PIVOT[^]
only two letters away from being an asset
|
|
|
|
|
|
WRONG - you can use MAX or MIN as the aggregate when you want to pivot on 1 value. It is still using an aggregate but it allws you to achieve a solution.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I was not arguing with your solution, just your statement that a pivot would not do the job because of the aggregate requirement. You can see it in this article[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this, assuming your table name is (TBL_COLROW_TRANSPOSE)
SELECT
CASE FINALRESULT.RECORDNUMBER
WHEN 1 THEN R1.GLID
WHEN 2 THEN R1.AMOUNT
END AS '1ST_COLUMN',
CASE FINALRESULT.RECORDNUMBER
WHEN 1 THEN R2.GLID
WHEN 2 THEN R2.AMOUNT
END AS '2ND_COLUMN',
CASE FINALRESULT.RECORDNUMBER
WHEN 1 THEN R3.GLID
WHEN 2 THEN R3.AMOUNT
END AS '3RD_COLUMN'
FROM
(SELECT AMOUNT, GLID, ROW_NUMBER() OVER(ORDER BY AMOUNT) AS 'RECORDNUMBER' FROM TBL_COLROW_TRANSPOSE) R1
INNER JOIN
(SELECT AMOUNT, GLID, ROW_NUMBER() OVER(ORDER BY AMOUNT) AS 'RECORDNUMBER' FROM TBL_COLROW_TRANSPOSE) R2
ON (R1.RECORDNUMBER = R2.RECORDNUMBER - 1)
INNER JOIN
(SELECT AMOUNT, GLID, ROW_NUMBER() OVER(ORDER BY AMOUNT) AS 'RECORDNUMBER' FROM TBL_COLROW_TRANSPOSE) R3
ON (R2.RECORDNUMBER = R3.RECORDNUMBER - 1)
CROSS JOIN
(SELECT 1 AS 'RECORDNUMBER' UNION ALL SELECT 2) FINALRESULT
Hope this helps
Vote me please
Niladri Biswas
|
|
|
|
|
Hi
I am using SQL 2005. I have a trigger for insert on a table. In the script I read everything from the inserted table and write it into a User Log table. It seems as if the trigger is not fireing when the insert happens, but if I copy the records, delete it from the table and paste them back in, it fires the trigger.
Have anyone experience this problem before, and if you have, what can I do to fix this?
Your help on this will be greatly appreciated.
Kind Regards,
Elizma
|
|
|
|
|
Instead using triggers I suggest you doing insert in your Log Table.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
show us your code please.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi
Here is my code.
CREATE Trigger [trg_MovementsLog]
ON [dbo].[GtrDetail]
FOR INSERT
AS
INSERT INTO tblMovementsLog(GtrReference,Line,StockCode, QtyMoved, MovedBy,TransactionDate)
SELECT inserted.GtrReference,inserted.Line, inserted.StockCode, inserted.GtrQuantity AS QtyMoved,
SUBSTRING(LotTransactions.Narration,9,Len(LotTransactions.Narration)-8) AS MovedBy, inserted.TransactionDate
FROM inserted INNER JOIN
GtrLot ON inserted.GtrReference = GtrLot.GtrReference AND inserted.Line = GtrLot.Line INNER JOIN
LotTransactions ON GtrLot.Lot = LotTransactions.LotJob AND inserted.GtrReference = LotTransactions.Reference AND
inserted.StockCode = LotTransactions.StockCode
WHERE (inserted.TargetWarehouse = 'HS') AND (NOT (inserted.TransferComplete = 'Y')) AND (LotTransactions.Narration LIKE 'JOB_TRF_%')
I know however that my code does work. The trigger use to work fine until they re-installed the server. Also it runs if I manually insert a record into the GtrDetail table. It just does not run when it's basically inserted via a script (Well, so it seems).
Thanx for your assistance.
Elizma
modified on Thursday, July 2, 2009 2:55 AM
|
|
|
|
|
Hy!
I have problem enetring data from my application to the database datable. Basicly this is the table in MS Access 2003 desgin:
table name: details
table fields:
acountNum (Text, primary key)
itemId (Integer, primary key)
price(Decimal, format: 0,000)
quantity(Long integer)
The problem is when i use my insert method which has a line that goes like this:
komanda.CommandText = "INSERT INTO details(acountNum, itemId, price, quantity) VALUES ('" + par1 + "', " + par2 + ", " + par3 + ", " + par4 + ")";
I use step by stet debuging in studio and check that all the values are correct. But what happens is a problem on a third field of the table. It is formated to represent data in 0,000. That means if you enetr just 3 it wil show like 3,000 in the database. Or if you enetr 2,98 it will show as 2,980 etc..
So let us say that in above comand i pased in these values for variables that store values:
par1 = "98bg", par2 = 12, par3 = 4.7 and part4 = 10. When you execute this command in step by step debuger and check the CommandText property it will look like this:
komanda.CommandText = "INSERT INTO details(acountNum, itemId, price, quantity) VALUES ('98bg', 12, 4,700, 10)";
We see that the command (which has its connection field already set to a opened connection prior to this) immediatly applied the format rule of price field and turned 4.7 into 4,700. Now of course when i execute the DataAdapter.Update method it will thorow an exception because now we have only 4 fields to insert values and actually 5 values ('98bg', 12, 4,700, 10). 4.7 became 4,700. Of course if i remove the format for the price field in my databse the command works perfectly. But in this situation i ned to have this format of numbers for the price.
Does anyone have an idea how to over come this small problem?
Cheers!
|
|
|
|
|
I think parameters are working with ms access, this would remove all you troubles related to filling inside decimal values, dates, etc... mostly on computers that have different culture set. Parameters protect you also from sql injection.
Or you could use culture info to get decimal seperator and do a replace on string.
And i would write variable names on english, it has many benefits.
More on parameters here[^].
Edit: You can also write decimal number inside this way ...12, '4,700', 10...
But as i wrote, how do you know what is decimal seperator? Different culture, dufferent decimal seperator. Just use parameters.
|
|
|
|
|
Thank you! That was helpfull!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
