|
hi,
I know this may look easy for some,but I need help with it. I have three tables "inhabitants","Payement" and "postponedPayement". I need to select from inhabitants those that didn't pay their loan and store their Ids along with the unpayed period into table "postponedPayement". The table "inhabitant" gathers all the inhabitants of a bulding while "Payement" table gathers all the payement made by an speific inhabitant for a specific month.
I wonder if someone know how I can do it automaticaly by the end of each month using SQL.
thanks in advance
It's not shame to ask if we don't know but it's shame to pretend you know when u don't
-- modified at 11:53 Thursday 22nd September, 2005
|
|
|
|
|
From your post I think your table structure looks something like this (I've tried to fill in the gaps with information that seems to fit the description - if this is not the case then a more accurate description would be more helpful)
Inhabitants
-----------
ID
Payment
-------
InhabitantsID (Foreign key join to Inhabitants.ID)
Date
PaymentAmount
PostponedPayment
----------------
InhabitantsID (Foreign key join to Inhabitants.ID)
UnpaidPeriod
Try this query
INSERT INTO PostponedPayment(InhabitantsID, UnpaidPeriod)
SELECT InhabitantsID, COUNT(*) AS UnpaidPeriod
WHERE p.PaymentAmount = 0
GROUP BY InhabitantsID
It will count all the periods unpaid for each inhabitant and insert it in the PostponedPayment table.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
thank u Mr Mackay,
my tables look like this
inhabitant
----------
inhabitantId
... info about inhabitant
Appartment
----------
AppartementId
inhabitantId
Amount //the amount that the inhabitant shoul pay monthly
Payement
---------
inhabitantId
AmountPayed //the amount payed is what's paid each month if done
date //date the the paid amount
PostponedPayement
-----------------
InhabitantId
amountdue
Referencedate //the reference of the unpaid month
I need to get the inhabitants that didn't pay for each month and store these in my PostponedPayement table.
for example
Appartement Inhabitant Payement
--------------------| ----------------| ----------------------------
ApptId inhId Amount| InhID | InhaName| inhId |paidAmount|Date
--------------------| ------|---------| -------|----------|---------
177/R1 inh1 150 | inh1 | James | inh1 | 150 |01/08/2005
177/R2 inh2 180 | inh2 | Jone | inh4 | 100 |05/08/2005
177/R3 inh3 150 | inh3 | Greg |
177/R4 inh4 100 | inh4 | mike |
in my PostponedPayment table I need to get this for august payments plus what is left from other months
PostponedPayement
------------------------------
InhabitantId|AmountDue|Refdate|
------------|---------|-------|
inh2 | 180 |08/2005|
inh3 | 150 |08/2005|
inh1 | 150 |06/2005|
I did the following query to get all those who paid and those who didn't :
select Appartement.ApptId, date, Payement.Amount from Appartement left join Payement on Payement.ApptId=Appartement.ApptId
I get the following
---------------------------------
InhabitantId|Amount | date |
------------|---------|----------|
inh1 | 150 |01/08/2005|
inh4 | 150 |05/08/2005|
inh2 | Null | null |
inh3 | Null | null |
all I need to pik up are the 2 last values in the example and insert them into PostponedPayment
Hope it's clearer now!excuse my English
But I thank yu very much
Thanks to all those who help and those who try to help
|
|
|
|
|
I have a report that will come back as 20 pages.
What the user wants is on the beginning of another unique ID, which causes a new page to be started, to have the page count restart to 1.
So, if I have 3 UniqueIDs for all 20 pages, and the first, IDAA, is 10 pages long, the second, IDBB, is 4 pages long, and the third, IDCC, is 6 pages long, then the page numbering would be as:
1-10 for IDAA
1-4 for IDBB
1-6 for IDCC
I've been told to set a cursor in a SP that determines what page I'm on and then passses this to the result set as a column. This is then set in a group to control page breaks. How could I do this? I'm not very familiar with SPROC cursors.
I know how to do this scenario in Crystal Reports, but am trying to figure it out here with either a page count formula, or some type of distinct count().
Thanks!
|
|
|
|
|
This is what I've tried so far, but it resets on every page.
So, each page is 1 even if the Unique ID spans 2-3 pages.
I only need it to reset on a new field grouping of the list object.
Here's the VB code for the report:
Shared offset As Integer
Public Function GetPN(reset As Boolean, pagenumber As Integer) As Integer
If reset
offset = pagenumber - 1
End If
Return pagenumber - offset
End Function
***************************
In the header, I have a textbox named "tag"
In the footer, I've got the following expression:
="Unique ID Page: " & Code.GetPN(Not(ReportItems!tag.Value Is Nothing),Globals!PageNumber)
|
|
|
|
|
My fault...I had the textbox "tag" in the page header and not in the list object itself.
It works!
Thanks!
-- modified at 17:02 Thursday 22nd September, 2005
|
|
|
|
|
this query is perfect in Ms-Access but its not working in Sql Server
UPDATE EmpDept INNER JOIN clockings ON EmpDept.clockno=clockings.clockno SET clockings.deptid = empDept.deptid;
it shows following error msg.
Incorrect syntax near the keyword 'INNER'.
Naveed Kamboh
|
|
|
|
|
The basic syntax of UPDATE statement in SQL Server is
UPDATE {table_name | view_name}
SET column_name1 = {expression1 | NULL | DEFAULT | (SELECT)}
[, column_name2 = {expression2 | NULL | DEFAULT | (SELECT)}
[ ,...n ]
WHERE {search_conditions}
Try this.
UPDATE clockings SET clockings.deptid = ( SELECT EmpDept.deptid from EmpDept WHERE clockings.clockno = EmpDept.clockno )
|
|
|
|
|
I have used UPDATEs with JOINs before, and it works in SQL Server. I believe the SET clause should come before the JOIN, and also, that you need a FROM clause before any JOIN.
Try this:
UPDATE clockings SET clockings.deptid = empDept.deptid
FROM EmpDept
WHERE EmpDept.clockno = clockings.clockno I hope this helps!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Thanks LuisR
Your query affected 46311 while i have 48410 rows but query worked  cheers
Naveed Kamboh
|
|
|
|
|
I would like to know how to check for 'NO RECORD FOUND' after a Select statement.
I am not sure about 'SQLCODE or SQLSTATE'. Please Help!!
Norm;P
|
|
|
|
|
Use COUNT to see if any records are returned.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
If this is inside a stored procedure check for
select @@rowcount.
If you want to check this in ADO.NET, then
SqlCommand.ExecuteNonQuery() will return the no of rows selected.
|
|
|
|
|
I am trying to prevent duplicate records from being appended to my Access database. What I did was created two tables in Access. The first one "NewResultsTable" is populated with new records. I created a Query in Access "Query1" which appends the first database to the second database. The Table "NewResultsTable" is then cleared of all of its records.
The table "TestResultsTable" has three primary keys:TestDate, TestTime, and SerialNumber. So when I run "Query1" while in Access, if all three of those primary keys are the same as any other three primary keys I will get an error message similar to the following.
"Microsoft Access can't append all the records in the append query
Microsoft Access didn't add (Number of records) to the table due to key violations
To ignore the error and run the query, click Yes"
I click yes the records that aren't duplicates are appended to "TestResultsTable". I wrote a small test program in C# and called the "Query1".
When there where no duplicates the "TestResultsTable" is appended to no problem. When there are duplicates I get the following error.
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
I don't know how to tell Access to continue and run the query. How can I do this? Or would it be easier to write a SQL statement? What would the SQL statement be that would do this same thing?
Thanks to anyone who can help.
|
|
|
|
|
Why don't you write a query that doesn't cause the error ? Ideally you should not send duplicate data to the database, but your proc for doing an insert should first check if the unique ID exists, and perhaps if it does, do an update instead.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi,
Need some help. Say I have 5 rows in my table and I filled in 4 rows and submit it, now I want to add data to the 5th row by using an update statement.
It looks like this:
MainID | EntryDate | TechName | Software | Application | Problem | Solution
The Main ID is an int and the entrydate is a datetime. I fill in a form to add a problem. Now someone goes and click on the entry in a datagrid and then adds a solution.
Thanks for the HELP!!!
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
You can iterate over the datagrid to find out which rows are changed, then you just fire an update statement for each row, or use OPENXML or something like that to pass all the rows in for one transaction.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Good Evening,
I'm working with C# code which presents a dataset with an id and name column, it also has an added expression column to concatentate the two as follows:
DataColumn aggregateCol = new DataColumn();
aggregateCol.DataType = System.Type.GetType("System.String");
aggregateCol.ColumnName = "strConcat";
aggregateCol.Expression = "Convert(id, System.String) + ' ' + name";
concatTable.Columns.Add(aggregateCol);
concatTable is passed to this code as: this.downtimeData.Tables["Equipment"]
to Test the result I printed the contents of the Datatable after it had been merged with a populated dataset as follows:
this.downtimeData.Merge(downtimeData);
System.Diagnostics.Debug.WriteLine(this.downtimeData.Tables["Equipment"].Rows.Count);
System.Diagnostics.Debug.WriteLine(this.downtimeData.Tables["Equipment"].Rows[0].ItemArray[0] + " " +
this.downtimeData.Tables["Equipment"].Rows[0].ItemArray[1] + " " +
this.downtimeData.Tables["Equipment"].Rows[0].ItemArray[2]);
And the perplexing print out reads:
3
1 spoon
 Why does the strConcat field not contain a value?
Thanks in advance
A chicken is just an eggs was of making more eggs
-- modified at 6:20 Wednesday 21st September, 2005
A chicken is just an eggs way of making more eggs
|
|
|
|
|
Do you have a data table with Data Or Want to create and fill data table on fly ?
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Hi,
I have a typed dataset with multiple tables, from this I obtain a reference to a table using a DataTable type. I use this reference to add an aditional column to the table/schema/dataset on the fly. I then merge this dataset with another of the same schema (without the aditional column) that is populated with data. I assume that the additional column should provide a value as it is an expression of other columns specified in the typed schema.
Cheers.
...
|
|
|
|
|
I figured out the solution... After I merged the populated dataset with my local dataset that has the extra column I had to set that columns expression again. I find this odd that my function added the new column and set the expression, but when the data is updated I have to respecify the expression for that column, but not re-add the column .
Could anyone tell me why things work this way?
Cheers.
|
|
|
|
|
is there a limit on the size of the "in memory" database it can hold?
"there is no spoon"
biz stuff about me
|
|
|
|
|
It is limited by the amount of memory it can allocate through .net. I've seen datasets with 300,000 rows lockup processes on servers with 4Gigs of memory. The asp.net process only hit 1.5GB before the admins shut it down, but it wasn't doing anything other then paging at that point.
DataSets are not the most efficient structure on the planet. They have lots and lots of features and it shows. It appears they use roughly an object per cell and sometimes more if you keep track of changes to the data. They do serialize to xml well. Just don't expect them to load schemas fast. I just did a profile of a simple database application using typed datasets and 25% of the time was spent in the DataSet.LoadXml method, which was longer then the time spent actually getting data from the database. 
It should be easy to code, just don't expect it to be blazing fast.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
not sure if this the right forum but here goes...
im trying to convert a source database into an xml file ... simple enough u say ... the problems are:
1. the target schema is totally different from the source database schema so some remapping of fields to different tables has to happen ... not sure if a built in solution exists for this or if i have to define my own remapping file and processing code?
2. the output has been given as an xsd file that contains about 7 or more tables ... when i try to read that into a dataset (using readxmlschema()) i get errors ... can a dataset schema only contain one table? if so how do i break the xsd file into single table xsd files?
3. im doing this in vb.net cos thats what the client wants ... are there any technologies in vb.net that can help me with this?
thnx in advance and i hope i didnt completely obfuscate my questions
"there is no spoon"
biz stuff about me
|
|
|
|
|
1. If you can write a sql statement per output table you can put them in a data adapter and you should be able to just fill the dataset.
2. As long as it is a valid XSD schema, readXmlSchema should work fine. DataSets support multiple tables and relationships between them.
3. Vb.net, I'm sorry.  You should be able to load the .xsd file into a vs.net project and generate a typed dataset. This should validate the xsd schema as well.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
