How Can I Get Oldest Record And Apply Only Part Of It If Necessary
Apr 9, 2008
I have a database that keeps track of pledges, scheduled reminders, and payments.
A user sets up a pledge and then sets up a schedule for reminders to be sent for each payment of the pledge.
Pledge table - PledgeID,PledgeDate,PledgeAmount,etc...
ScheduledReminders table - ScheduleID,PledgeID,DueDate,ReminderDate,ReminderPrintedDate,ReminderPaidDate
AppliedPayments table - ScheduleID,PaymentID,PaidAmount,PaidDate (Junction table between ScheduledReminders and PledgePayments
PledgePayments table PaymentID,PaymentAmount,PaymentDate
What I need to know is, when a payment is being Inserted I need the payment amount applied to the oldest scheduled reminder record that has not been paid. But if the payment is more than the amount shown in the schedule reminder record, I need the balance to be applied to the next oldest schedule reminder record, etc...
Can anyone offer some good suggestions or examples on how to do this?
View 18 Replies
ADVERTISEMENT
Feb 15, 2000
Is there a quick and dirty way to find the oldest record in a select query? There is a date column of type DateTime.
-Ringo
View 1 Replies
View Related
Nov 9, 2007
Hello all,
I need assistance in finding the oldest CDR record in Cisco Callmanager DB using sql query. The CCM version is 3.3(5).
Any assistance is needed. Thanks.
View 3 Replies
View Related
Mar 4, 2008
Hi,
I have a base query that will return the ID, StartDate and Code for all IDs. I SELECT only for Codes 5 and 9. For most of the IDs I return a record for both a Code 5 and Code 9. They will have different dates however. How could I select from this base query one record for each ID with the oldest date? The items in yellow are the ones that I would want to return to a report in SSRS. Is there a way to put this data in a temp table and read through it to compare IDs and grab the one with the older date?
ID
StartDate
Code
100
1/2/2000
5
100
4/6/2004
9
205
3/13/2002
5
205
9/10/2002
9
300
10/10/1999
9
407
2/12/2005
5
407
7/17/2007
9
Thanks,
rb
View 10 Replies
View Related
Oct 13, 2012
Table :
ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal
Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.
SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date
The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.
View 1 Replies
View Related
Oct 7, 2014
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
View 6 Replies
View Related
Apr 20, 2007
Hi,
Is it possible to select a record by just the date part of the data type TIMESTAMP?
date_time
2007-04-20 16:27:01
For example, a query to select all records added today.
Thanks very much,
dai.hop
View 2 Replies
View Related
Nov 27, 2007
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END
and then run a sample T-SQL statement
Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
View 8 Replies
View Related
Feb 3, 2008
Hello there,I have a problem when I'm trying to order by the date..I have tried this string:SelectCommand="SELECT TOP 5 [Date], [Id], [Navn], [ShortInfo] FROM [fest] ORDER BY [Date] DESC">
And I have these dates:
04/02/2008
06/02/2008
20/02/2008
29/02/2008
08/03/2008
28/03/2008They should be shown like this:
04/02/2008
06/02/2008
20/02/2008
29/02/2008
08/03/2008
But they dosn't, insted is they shown like this:
29/02/200828/03/200820/02/200808/03/200806/02/2008How can I do, so it work?Regards Jeppe Richardt
View 8 Replies
View Related
Jul 4, 2001
Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!
View 4 Replies
View Related
Jan 25, 2001
Is there a stored proc to tell details of the oldest active transaction ?
View 3 Replies
View Related
Jan 29, 2007
I may have one or several files in a folder that act as source files.
How do I loop through the folder, picking the oldest dated file according to its 'Date Modifield' attribute.
Do I have to use a Script Task or similar as I can't seem to find a way to do it with normal tasks.
If so, does anyone have example code.
Thanks
P R W.
View 1 Replies
View Related
Sep 17, 2014
I have a table that I need to delete the oldest date. I thought I could just do an update statement saying...
Delete T_BreakDown
where Date = MIN(Date)
But that doesn't work.
View 4 Replies
View Related
Oct 29, 2007
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
Thanks
Shankar
View 1 Replies
View Related
Dec 7, 2007
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
View 3 Replies
View Related
Jan 28, 2015
I have made the following Scalar-valued function:
CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)
[Code] ....
What it does is convert numbers to times
E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)
So far so good (function works perfectly)
My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.
This means that, e.g. with a time like 3.23.40 the last zero must be deleted.
My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,
is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))
There are 5 codes: 0 1 2 3 and 4
View 9 Replies
View Related
Jul 23, 2014
I have the following tables in my DB
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary
Here is a piece of code to generate sample data sets
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')
[Code] ....
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
[Code] ....
View 5 Replies
View Related
Mar 26, 2007
I have a sql server problem for which the solution is at,
http://support.microsoft.com/kb/889521
The article talks about a hot fix, but there is no link for download the hot fix.
Can anyone have a look and guide me.
Thanks
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
Apr 12, 2006
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config
The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config?
I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
--------------------------------------Part 1------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> --------------------------------------Part 1------------------------------------------------------------------------
--------------------------------------Part 2------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=CCWWSQLEXPRESS;Initial Catalog=ASPNETDB.MDF;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />--------------------------------------Part 2------------------------------------------------------------------------
View 2 Replies
View Related
May 23, 2007
I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.
Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?
View 1 Replies
View Related
Sep 7, 2007
I have a table called Courses with columns "courseCode" "courseName" and "TeacherName"Im passing in a value @prefixText ...I have the following...Select courseModuleCode from courses Where courseModuleCode like @prefixTextwhat I actually want to do is return one list, i.e. one column that will do the same for "courseName" and "TeacherName" aswell based on what the @prefix tag is...so i will end up with one column that may contains some or all of the following courseModuleCode, courseName, TeacherName. Im basically implementing a search on this table and want the users to search by any of the column nameshope this makes sense.
View 6 Replies
View Related
Jun 24, 2004
I am new to DTS.
In ExecuteSQLTask:
want the original sql statement of <<backup database databaeABC to backup_databaseABC with init>> to be
backup database ? to 'backup_' + ? with init
But it looks the parameter could not place a globalVariable to it and parse it with error.
One alternate way probably need to create a proc to pass a parm to it....
Also, how could pass a globalVariable to a FTP task?
thanks
-D
View 4 Replies
View Related
Aug 27, 2004
We have a Pub, Dist and Sub Server all running under SP2 with worm update and are looking to apply SP3a . Replication is transactional with heavy usage . What is the best sequence to apply SP3
can it be spread out in 3 weekends or has to be done all together ?
Thanks
View 5 Replies
View Related
Sep 12, 2013
I want to apply lock on a row, where by when another application is trying to access the locked row the application will automatically move to the next row instead of waiting for the locked row
View 3 Replies
View Related
Feb 26, 2004
I am trying to install sql server sp3a. I checked the version before and after the sp is applied, and it shows:
8.00.194
Now, according to the sp3a readme:
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp
the following versions should show:
SQL Server 2000 Original Release 8.00.194 RTM
Database Components SP1 or Desktop Engine SP1 8.00.384 SP1
Database Components SP2 or Desktop Engine SP2 8.00.534 SP2
Database Components SP3 or SP3a, or Desktop Engine SP3 or SP3a 8.00.760 SP3
This would imply that after installing sp3a and restarting, the version reported should now be 8.00.760. However, it still shows 8.00.194. So it appears to have not really installed, althought the sp3a installation ran without incident. So, how can I get the sp3a to not just act like it installed, but actually get it to install? Also, I have shut down all mssql services and rebooted, applied patch, and rebooted again. Doesn't help.
Also, if it is really installing, is there another way to tell?
Anyway, I really need to be able to install sp3a to see if it fixes another problem I'm having. Unfortunetly, since I can't even tell if the sp3a is installing, I can't tell if it really doesn't help or not. Thanks.
Tony F
View 2 Replies
View Related
May 22, 2008
What is Cross Apply, when it will be used ?
View 2 Replies
View Related
Jul 22, 2007
Hello everyone...
i am new to Structured Query Language. I am learning it for the past 6 months form a book by myself. I don't have a teacher available to me. Now when i have finished that with all of the exercises completed. i want to make sure what i've learned is i've really learned it. the problem is that there is no such organization here that i join and have some hand on practical. So now i am asking u guys to help me. Tell me some way so that i make sure of whatever knowledge i have about SQL.
don't worry be happy
View 1 Replies
View Related
Oct 14, 2007
We would like to apply a hotfix to solve the issue that brought our server down. The hotfix we are trying to apply is in Build 3175. But the latest Cumulative Update 3 Package is Build 3186. Should we apply 3175? Or we can go for the latest build 3186? Which is recommended?
View 6 Replies
View Related
Nov 20, 2007
I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.
Thanks
View 5 Replies
View Related
Feb 20, 2007
Should I apply SP2 even though I have already installed the community preview?
View 1 Replies
View Related
Apr 17, 2008
HI,
I am going to make a big web application. so im going to use sql connection with class for example i have 100 web pages and i will make a slq connection in class.vb
Class1.vb(see the below code)
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasicPublic Class Class1
Private conn As New SqlConnection("Data Source=DANISHSQLEXPRESS;Initial Catalog=ARBASHHR.MDF;Integrated Security=True")Private cmd As New SqlCommand()
Private da As New SqlDataAdapter(cmd)
End Class
default.aspx(here is asp.net page i want to use the sql connection from that class.)
but this below page saying that Name "cmd" is not declared and connection
Try
cmd.Connection = conn
conn.Open()Dim ds As New DataSet
cmd.CommandText = "SELECT * FROM Users where (UserName = N'" & TextBox1.Text & "')" 'And (Password = N '" & TextBox2.Text & "')"da.Fill(ds, "data")
conn.Close()
For Each dt As DataTable In ds.Tables
For Each dr As DataRow In dt.RowsTextBox3.Text = dr.Item("Password")
Next drNext dt
If TextBox2.Text = TextBox3.Text ThenResponse.Redirect("Welcome.aspx")
Else
Label3.Text = "Invalid Username/password"
End IfCatch ex As Exception
' MsgBox(ex.Message) '("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error")
End Try
End Sub
please let me know where im doing mistake.....
Thank in Advance
View 2 Replies
View Related
Aug 20, 1999
Is anyone else having trouble with the Apply button under the permissions tab?
I can change permissions, click apply, the changes are shown, but when I access the database, the changes arn't made. If I close the permissions windows and re open the changes arn't made.
Will SP1 fix this?(it's not listed in the fix text file)
View 2 Replies
View Related
Feb 7, 2004
HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
View 3 Replies
View Related
