|
Hi all,
I have table and inside table there are two columns named Amount and Balance i want to calculate the balanace as Professive like below
Balance 6000
Amount Balance
1500 4500
2000 2500
1000 1500
500 1000
Initially iam getting the balance from database table than have to perform the Progressive calculation .
May i have to use COALESCE function ?? Or how i can calculate the ProgressiveBalance like above any hint.
Any Idea ?
Best Regards
Rameez
|
|
|
|
|
|
One way to do this is with a subquery.
I assume an element number which is an int, that is numbered according to how you want to display this. However this is more sensible if you actually have dates and do the subquery comparison based on the date. Element also could be your customer order id (or invoice id, or whatever) if those are sequential.
SELECT [Element]
,[Amount]
,(select SUM(Amount) from AmountTabletb where tb.Element<=ta.Element )
NewBalance
FROM [yourdatabase].[dbo].[AmountTable] ta
And then adding in the original balance with another subquery.
SELECT [Element]
,[Amount]
,(select SUM(Amount) from AmountTabletb where tb.Element <= ta.Element )
+ (select top 1 balance from AmountTable tb where tb.Element=1 ) NewBalance
FROM [yourdatabase].[dbo].[AmountTable] ta
Remember; your subquery can return only a single value per row or you get an error so always use a 'top 1' clause or a summary function like SUM() or Max().
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Thanks for reply
Actually i have the below Table with multiple columns
Declare @Table Table
(
EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select Abc,07/03/2010,6000,0 UNION ALL
Select xyz, 07/03/2011,1500,0 UNION ALL
Select Rameez, 07/03/2011,2000,0 UNION All
Select Abhijit, 07/03/2011,1000,0 UNION ALL
I have the above table and have to calculate the progessive Balance for each row is it possible using subquery????
Regards
|
|
|
|
|
Yes. (BTW, this is not really a beginner's task you are attempting in case you are wondering.)
Yes, but life gets easier if you have a row number of some kind.
As your example shows there's no particular order to these entries; they all have the same date.
There is nothing indicating which ones you want first. I've added single quotes to your example entries.
If you added another column to indicate order you could have this query:
Declare @Table Table
(
row int, EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 1, 'Abc','07/03/2010',6000,0 UNION ALL
Select 2, 'xyz', '07/03/2011',1500,0 UNION ALL
Select 3, 'Rameez', '07/03/2011',2000,0 UNION All
Select 4, 'Abhijit', '07/03/2011',1000,0; --UNION ALL
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx
As you can see the number inserted with the information is working just fine.
This next version uses an identity column in the table to do the same job so if you have a lot of entries you won't have to pre-set all of their numbers.
Declare @Table Table
(
row int identity(1,1), EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
(
EmployeeName
,joiningDate
,Amount
,Balance
)
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0; --UNION ALL
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx
In this last example you can see I'm using row_number() to artificially create a row number on the fly.
Declare @Table Table
(
EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0;
with CTE01 as
(select *
,ROW_NUMBER() over (order by joiningdate) row
from @Table )
select xx.*
,(select sum(Amount) from CTE01 xy where xy.row <= xx.row ) RunningTotal
from CTE01 xx
I went ahead and used a common table expression (CTE) here because row_number can be a little intransigent when you use it directly in the subquery, but it works just fine in the CTE.
Hopefully this all makes enough sense that you can compare the examples to what you are trying to do and make something out of this.
You should be able to paste each of the above into it's own window in management studio and you can see how they work better. Don't put them in the same window, of course, but I'm guessing you know that one.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
I'm currently building a web-based store for my company that allows students register for different classes. In the administration section of the website there is page where a user can create a new class for students to register for. On this page there is multiple items that the person must fill out; date and time of class, the instructor, the course name, etc. Some of these items are stored in a database. For example, there is a table that has all the instructor's names and ID numbers, and there is another table with all the course names, price, etc.
My problem is that I want to populate several drop-down menus with information from different tables that have no relationship. For example I want to pull all the instructor's names (to be placed in one drop-down) and also select all the different course names (to be placed in a different drop-down). Now I could use multiple SQL statements to do this, but I was hoping there was a way to do it with a single SQL statement, however it cannot use JOINS. This is because, in the above example, the instructors table and the course table do not have any columns that are the same that the JOIN can be done on. I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load.
Any ideas or suggestions would be greatly appreciated, and thanks in advance for any help.
|
|
|
|
|
Try to use UNION ALL to get result which you want to have.
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.
www.cacttus.com
|
|
|
|
|
"I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load. "
If you look at the time it takes for a web page to load, the call to the database is the fastest part. If you want the page to load quickly, keep it as light as possible. No graphics, simple layout, limited javascript, etc.
You may be looking to optimize a portion that really doesn't need to be optimized. If you want a quick web-page, maybe consider using Ajax so that you are not pushing and pulling the entire page around like you do with a traditional postback.
Just something to think about.
|
|
|
|
|
Agree with Blue_Boy, Union All, just give a type, description, and ID to each subset you need like this:
Select 1 as nType, Instructor_Name as sName, Instructor_ID as nID
From Instructor
Where (optional condition)
Union All
Select 2 as nType, Class_Name as sName, Class_ID as nID
From Class
Where (optional condition)
...etc, etc,
Order By nType, sName
add to combo boxes based on nType
"Go forth into the source" - Neal Morse
|
|
|
|
|
Using UNION ALL as suggested by some folks would not be an optimal solution, because the time taken to access the tables would remain the same, you would just end up saving the time to open and close additional connections (which ASP.NET saves you anyway through connection pooling).
Since the Instructors and Courses do not change often, you can use the System.Web.Caching.Cache class to load the data from the database in the event and bind the controls from the Cache. There are mechanisms in SQL Server to hook on to Table change notifications and invalidate the Cache. So anytime the data in the base tables change, your Cache gets invalidated and the fresh data is loaded.
Here's an example on how to use the Cache and SqlCacheDependency classes: http://davidhayden.com/blog/dave/archive/2006/04/29/2929.aspx[^]
|
|
|
|
|
I'm not sure I understand what you want to do.
You have several different drop-downs which hold unrelated data from different tables in the database.
You want to populate all these different drop-downs from a single result set produced from a single database query.
How is that going to work?
|
|
|
|
|
I think you need to go back and look at your datadase design. You say there is no connection between Instructors and Courses but I think, in reality, there is a many-to-many relationship. You can achieve the connection with a linked (or intermediate) table which only contains the two primary keys - one from each table. Then create foreign keys on this table to the Instructors and Classes tables. The Primary Key of the linking table needs to be the composite of the two elements InstructorID and CourseID - see below.
Instructor Table: InstructorID, FirstName, LastName,...<br />
Course Table: CourseID, Subject, DateStart, DateEnd,.... <br />
InstructorCourse Table: InstructorID, CourseID
I presume you don't want Lecturer A being able to teach Geography when really his/her subject is History, but Lecturer B can teach Mathematics and Statistics - no problem.
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
AnnieMacD wrote: which only contains the two primary keys
Two primary keys? I presume you meant to say "a two column primary key".
|
|
|
|
|
No, she's correct. This isn't the primary key for the middle table but the elements from the two other tables that are primary keys in those tables.
Like so:
Table A
PKA
Instructor
Table B
PKB
Class
Table Middle
PKMiddle
PKA
PKB
The primary key for the middle table is neither PKA nor PKB in that case.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Then, it's no longer called Primary Key, we call it a Foreign key. A table can have one and only one Primary key.
|
|
|
|
|
But the Primary Key can be a composite.
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
AnnieMacD wrote: But the Primary Key can be a composite.
Yes, of course. I know what you were talking about. But a beginner who happens to see this thread might think that a table can have more than one Primary key.
|
|
|
|
|
Thanks for pointing that out. I supplied the script for creating the linking table to avoid any misunderstanding. 
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
Yes, and we all know that.
She was referring to them in explanatory fashion.
There was nothing wrong with her explanation.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
smcnulty2000 wrote: Yes, and we all know that.
I agree, but a beginner who happens to view this thread might get confused and misled.
|
|
|
|
|
Fair enough. I took your original use of the term as condescending.
Perhaps it wasn't meant that way, but, oh well.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
The Primary Key for the linking table is a composite of InstructorID + CourseID (in my example). This then makes it unique which a Primary Key has to be. There is NO other data in the linking table.
This then effectively creates a many-to-many relationship.
Here are the commands for creating the linking table assuming you have a Course table with Primary Key CourseID and an Instructor table with Primary Key InstructorID.
CREATE TABLE [dbo].[CourseInstructor](
[CourseID] [int] NOT NULL,
[InstructorID] [int] NOT NULL,
CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[InstructorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]
GO
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Instructor] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Instructor] ([InstructorID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Instructor]
GO
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
I think several good points have been made in this thread.
You could, if you are bent on doing this, use the row number function and join based on that.
select * from
(
select
name
,row_number() over (order by name) rowa
from instructor
) tableA
full outer join
(
select name Classname
,row_number() over (order by name ) rowb
from class
) tableb
on rowa=rowb
This should give you a listing of columns from table a, and table b as if they'd been put together into a spreadsheet. Then you just have to be sure you don't have a duplicate column name (as I showed in the second subquery).
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
So I've built a nice big Stored Procedure and call it from my SSIS package.
My SP uses a temp table since it uses nestes SP's.
Here's what I tries:
+------+-Table variable-+-Temp Table-+--Temp Table from SQL Server Management Studio--+
|Result|Failure | Failure | Success
It always fails unless I force a 'create table' from SQLSMS and keep SQLSMS open, the moment I close it the temp table gets dropped.
What on earth is going on?
I can only guess it's got to do with permission but I wouldn't really know where to look.
The first rule of CListCtrl is you do not talk about CListCtrl - kornman
|
|
|
|
|
Your question is not clear. Where do you create the temp tables? In your SP or in SSMS ? Would you show us some code?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
