|
I have following two tables
1st table is Fee:
PK ---- payable
1 ---- 1500
2 ---- 1200
3 ---- 1900
2nd table is Fee_Detail:
pk ---- paid ---- fk_fee
1 ---- 700 ---- 1
2 ---- 400 ---- 1
3 ---- 400 ---- 1
4 ---- 800 ---- 2
5 ---- 300 ---- 2
Result should be
payable ---- paid
1500 ---- 1500
1200 ---- 1100
1900 ---- 0
what is sql query
modified on Wednesday, September 2, 2009 2:39 AM
|
|
|
|
|
You need and inner join a group by and a sum
Select FeeID, sum(payable), sum(paid)
from Fee
iner join Fee_detail FD on FD.fk_fee = fee.FeeID
group by
FeeID
|
|
|
|
|
I want to fetch all records of left hand side
and only sum of 'paid amount' matching records of child table(right hand side) according to fk_fee(foreign key column)..
for example
Fee table:
Pk ---- payable
1 ---- 1500
2 --- 700
Fee Detail:
Pk ---- Paid ---- fk_fee
1 ---- 1100 ----1
2 ---- 200 ----1
3 ---- 100 ----1
Result should be
payable ---- paid
1500 ---- 1400
700 ---- 0
|
|
|
|
|
Then you need to use a left join and deal with null values
Select FeeID, sum(payable), sum(ISNULL(paid,0))
from Fee
left join Fee_detail FD on FD.fk_fee = fee.FeeID
group by
FeeID
search here for articles on joins, there is a good one around, sorry I don't have a link
|
|
|
|
|
Below is the query for your requirement. You need to group by each field that you want in the select statement.
SELECT Fee.Payable, ISNULL( SUM(Fee_Detail.Paid) ,0)
FROM Fee Left Outer Join Fee_Detail ON Fee.PK_Id = Fee_Detail.FK_Id
Group By FK_Id, Fee.Payable
Hope it helps you.
|
|
|
|
|
|
You need to be a little more specific and generous with information when asking a question. I would be nice to know what type of control you are using, I will assume a combobox.
Have you tried searching for a solution - this is a VERY common requirement and there are plenty of examples. Basically you bind filter of the city combo to the selected value of the country combo, repopulating the city combo in the onselected index change event.
|
|
|
|
|
Something like this:
select * from cities where stateid in (select stateid from states where statename='statename')
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.aktualiteti.com
|
|
|
|
|
Hi dear all,
I am new to database design and database structure, In my internal test i faced one database design problem, tried to solve this but i am still having doubts so please experts give me clear solution for this
Problem is
Media bank is a web application for storing and retrieval of Media files. In Media bank, files are stored in folders (just like in the case of windows). A folder can have sub-folders to any level of depth. A file in a folder can exist either in published or un-published state.
Every folder will have the following information associated with it: folder name and folder description.
Every file will have the following information associated with: file name, file description, physical location in server, size in kbs, type of file.
1. Write a normalized database design for the above mentioned scenario. Also show primary keys, foreign keys, and also what kind of relationship (1-M , M-1 or M-M) exists between them.
2. We add a new feature to Media bank: A file can contain different versions, but only one version will be active at a time. Any version of a file can be set to active by the administrative user.
Modify the database deign above to incorporate this new feature.
3. Write SQL Query to retrieve
a. All files of type ‘images’ with the following information:
File name, File description, state of file (unpublished/published), Folder name, Folder description.
b. All folders with following information:
Folder name, Number of files in published state in that folder
c. All files with following information
File name, complete folder path (all parents until root folder)
Thanks in advance
|
|
|
|
|
Please leave fill out the following to get a valid response.
Educational Institution:
Course Enrolled:
Instructor Name:
Contact Number:
|
|
|
|
|
Hello all!
This seems pretty simple, but I'm still stuck. Probably making it more complicated than it is. I have a tblPot, with Monday, Tuesday, etc as Columns. I need to return the Pot of the week based on todays date.
I'm getting the day of the week from SQL with the DATENAME function. But how can I pass that to match the column name. Basicly I need this simple statement.
SELECT Potname FROM tblPot WHERE Monday = 'true'
How can I get it so it grabs the current day automaticly. Thanks!
Rudy 
here is the code
ALTER PROCEDURE [dbo].[SchedPod]
-- Add the parameters for the stored procedure here
@Day nvarchar(50) Output,
@PotList nvarchar(50) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select @Day = DATENAME(dw , getDate())
SELECT @Potlist = PotName
FROM tblPot
WHERE (@Day = 'true')
END
|
|
|
|
|
Unfortunately the table definition you have makes it quite difficult to get the result you want based on the current day.
However, after saying that there is still a solution to use UNPIVOT.
The idea is to change your day columns (Monday, Tuesday) to a row value, then query the result of the UNPIVOT.
See UNPIVOT[^]
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
Use a case statement
Select @PotName = Case DATENAME(dw , getDate()) When 1 then MondayCol
when 2 then TusedayCol End
from tblPot
|
|
|
|
|
Hi all,
I am trying to hide a table in my report if all the text values are null. I am running the expression =iif(IsNothing(Fields!LoanType.Value), true, false) but this seems to hide the table no matter what. I have also tried the Len function but this doesn't work either. The Table in the database is set to allow nulls for this attribute. Here is an example of two tables I have in the report.
Example dataset1:
Conventional
FHA
VA
null
Expression used:=iif(IsNothing(Fields!LoanType.Value), true, false)
Example Dataset2:
null
Expression used:=iif(IsNothing(Fields!Assumable.Value), true, false)
When I apply the above functions both tables are hidden. However, the table holding example datset1 shouldn't be hidden and example dataset2 should be.
|
|
|
|
|
Are you using SQL Reporting Services by any chance?
Use IsDBNull function instead of IsNothing. The null value returned by database is not the same as NOTHING or NULL.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
Hi everyone,
I try to create Cubes with Analysis Services in Visual Studio (C#) 2005, but my problem is...
... I created the datasource, datasourcesight, the cube and dimensions, but if I try to compile it and add it to SQL Servermanagement Studio it means, that it can't be added to the server cause the name might be wrong...
Sorry I am from germany and so I only have the german errorlog:
Fehler 1 Das Projekt konnte aufgrund der folgenden Konnektivitätsprobleme nicht auf dem Server 'test' bereitgestellt werden: Es kann keine Verbindung hergestellt werden. Stellen Sie sicher, dass der Server ausgeführt wird.. Überprüfen oder aktualisieren Sie den Namen des Zielservers, indem Sie mit der rechten Maustaste im Projektmappen-Explorer auf das Projekt klicken. Wählen Sie 'Projekteigenschaften' aus, klicken Sie auf die Registerkarte 'Bereitstellung', und geben Sie dann den Namen des Servers ein.
-> the server 'test' is just for this errormessage.
... my knowledge: it just means that the connection with the server failed, because I typed the wrong servername into the project
greetz
|
|
|
|
|
Hello Sir
I had tried All This link,
But it is not working.
My Sqlserver is on local machine which is connected in a netork. On which internet connection is through D-link router.
i am passing user id and password also.
I want to access sqlserver 2005 through internet (i.e Web application)
it gives following eror
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
|
|
|
|
|
1. Is your connection string correct? (the server name, credentials etc.)
2. Is the server running? (Check the related services)
3. Have you enabled remote connectivity? (It is disabled by default and can be enable using Surface Area Configuration)
Hope this helps.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
Hi,
Yes My Connection Sting Is Correct.
And My Server is Also Running,
Yes I enable Remote Connectivity,
I created A Website ,
When i run it from local network
it execute properly.
When i run it from
Internet it gives following Error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Thank You
|
|
|
|
|
Well, to connect to remote SQL server, all you need is IP address of the server, port number(1433 by default) and credentials.
Is your server behind firewall? See this[^] link.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
|
Is the SQL browser service on? Seriously, if these things are correct then you should be able to connect to the server.
If you are using ASP.Net, check out the membership provider and role provider properties as well.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
Yes SQL browser service is On
|
|
|
|
|
Try to ping that server with its name from different locations and see if you can see it
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Try to do this we use to do connect to remote servers and uses odbc connection your port address should be 1433
goto SQL Server Configuration Manager ----> Network configuration ------> Protocols for your Server
and see Named Pipes and TCPIP should be enable , you can use either one of them at a time
also make sure you to check aliases and your SQL Browser is running and in connection string try server name
like ".\SQLServer" not "machineName\instanceName"
and are you using sql express then also see your configuration file located in
C:\WINDOWS\Microsoft.NET\Framework\yourFrameWOrkVersion
I hope so it will solve the problem
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
