Calculating A Field Value Based On Other Records
May 20, 2008
I have two tables and they have a foreign key relationship. It will be a 1 to Many realationship.
Table1- the primary key table has the following columns
ContentID - int identity field
DateAdded- datetime
Table2 - The foreign key table, has the following relevant fields
FK - int foreign key
version - int
When an initial record is added there is a record added to each table. The ContentID in Table1 will match the FK field in Table2. Then a user will be able to "edit" the record in Table2, but instead of writing over the record that is already there, a new record will be added to Table2. I would like to calculate the version field where each time a record is added for the particular ContentID/FK it adds 1 to the last version that was added to that particular ContentID/FK.
Does that make sense. I am trying to implement some type of revision tracking for my CMS that I am building and this is the only way I can come up with because my client is worried that somebody will go in and make incorrect changes to their site and they want to be able to roll it back to a previous version.
I would appreciate any ideas.
Thanks,
laura
View 5 Replies
ADVERTISEMENT
Apr 2, 2007
Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?
View 6 Replies
View Related
Feb 18, 2014
I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate field that i use in the subtraction for calculating the number of months.
Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate
SET NOCOUNT ON
GO
declare @num_of_times int
declare @count int
declare @frequency varchar(10)
declare @num_of_times1 int
[Code] ....
View 6 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Aug 2, 2007
Hey all! First post-- I apologize for the newbness of the question, but I'm having an issue with a basic query... here goes:
I want to calculate the percentages of statuses found from two tables. Let say T1 is my list of statuses:
T1
Passed
Failed
Not Completed
and T2 is the table where I derive my total # of statues for each id and how many in each status.
T2
ID, Desc, Status , numtimesinstatus
1, Test1, Failed, 3
1, Test1, Passed, 1
3, Blah, Failed, 5
3, Blah, Not Completed, 1
What I'd like to do is get the percentages for each available status in T1, from T2-- I can do this; however, I'm having trouble in the cases where a particular ID hasn't been in one of the status (in other words, calculating a 0%). What I'd like to get from these two tables is:
Result
ID, Desc, Status, %age
1, Test1, Failed, 75.0
1, Test1, Passed, 25.0
1, Test1, Not Completed, 0.0
3, Blah, Failed, 83.3
3, Blah, Passed, 16.67
3, Blah, Not Completed, 0.0
Can anyone shed some light? Thanks!
View 5 Replies
View Related
Sep 14, 2013
I have created an SQL report that returns order data from a shop DB.
How can you get the output to total the number of order instances and the total value of those orders, both per user_id? Is this possible?
View 3 Replies
View Related
Jul 21, 2001
Does anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies
View Related
Oct 2, 2014
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
Category Example
IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice
1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00
Product Example
1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99
Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
I use Microsoft SQL 2008
View 2 Replies
View Related
Jun 14, 2006
I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point. It doesn't need to account for elevation, but assumes a flat plane with lat and long.
Does anyone have any algorithms in T-SQL to do this?
View 5 Replies
View Related
May 1, 2007
Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas :)
Basically, I have the following table structure
Employee(employee_number, continuous_start_date, ...)
The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+).
I can work out the length of service in months with the following code
SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months'
FROM employee
So the first stage is to summarise the length of service into groups as mentioned above.
Then the final stage is working out how many people are in each group!
Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result ;)
Thanks later, and in advance :p
-GeorgeV
View 14 Replies
View Related
Aug 19, 2014
I need a Query for calculating the fiscal_week based on the input dates (start_date and end_date), though I got a query from this forum, it is not giving me exact result.
the sample is in the excel file with the attachment.
In the excel:
First tab tells you the raw_data what I am using to find the Fiscal_week
Second tab tell you the data where i found the mistake, and how I am expecting the output.
I also have attached the query I have got from this forum, query I have modified for fiscal week.
View 4 Replies
View Related
Dec 21, 2005
I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.
View 4 Replies
View Related
Oct 29, 2015
I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,
emp_idstart_date end_date
1001 1998-01-01 2003-06-21
1002 1999-05-23 2008-03-28
1001 2004-08-19 NULL
1003 2004-10-12 2006-07-25
1004 2005-04-28 NULL
1002 2008-11-02 NULL
1003 2009-05-17 NULL
The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it.
View 9 Replies
View Related
Jul 23, 2005
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies
View Related
Jul 20, 2005
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies
View Related
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Oct 10, 2005
Hey all.
I'm looking around for a solution on how to pull records back based on legnth.
So, if field A is more then 2 characters, return that row.
Thanks
Caden
View 2 Replies
View Related
Apr 18, 2008
Hi All,
For one of my reports, I need a expression smthing like this.
=IIf(Fields!Category.Value = "Decisioned" , THEN SHOW SUMMATION OF TOTAL APPLICATIONS WHERE Catergory = "Decisioned ," ")
Can anyone help me to write this expression.
Thanks
View 3 Replies
View Related
May 29, 2008
I have a report. I want to display title according to the following combination of two parameter values.
Line of Business
Product Type
Report Title
Total
Line, Loan
13 Month Home Equity Portfolio - Total
Cbank
Line, Loan
13 Month Regional Bank Portfolio - Total
DTC
Line, Loan
13 Month Direct to Consumer Portfolio - Total
Edirect
Line, Loan
13 Month Equity Direct Portfolio - Total
InstLend
Line, Loan
13 Month Institutional Lending Portfolio - Total
Total
Line
13 Month Home Equity Portfolio - Lines
Cbank
Line
13 Month Regional Bank Portfolio - Lines
etc
Line of Business and product type are two parameters.
Product type is a muliple parameter. i.e you can select Line, Loand or Line and Loan.
can any one tell me how to write a proceedure to get this result(i.e report title)
Thanks
View 1 Replies
View Related
Oct 25, 2007
Hi! I have a table Tbl1 has to columns:
A B
_________
Ibm Me
Sony Me
Me Bob
Me Frank
I'd like to select all rows where B=ME and A=Me Thanks for the help
View 2 Replies
View Related
Oct 8, 2003
Hello
I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..
Thanks!!!<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat=server>
void Page_Load(Object sender , EventArgs e)
{
SqlConnection conPubs;
string strDelete;
SqlCommand cmdDelete;
conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" );
strDelete = "Delete tblbook Where Posted_Date =???????????";
cmdDelete = new SqlCommand( strDelete, conPubs );
conPubs.Open();
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Write("Records Deleted!");
}
</script>
//
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<!-- Insert content here -->
</form>
</body>
</html>
View 6 Replies
View Related
Aug 12, 2013
I am trying to create a report where the rows of the database have fields containing customer names and dates associated with the names. I want to know for each customer how many records the customer has for specified date ranges.
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate Received1]
FROM [DL]
WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#))
GROUP BY [DL].[Customer];
The idea is a list of the number of records for each customer in 2013.
Ultimately my goal is to show the customer activity over multiple years.
So,
Customer Name 201120122013
Customer A123
Customer B246
Customer C543
I am trying to go down the path of a Union:
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate 2013]
FROM [DL]
WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#))
GROUP BY [DL].[Customer]
[Code] ....
This returns 2 columns only not the four I am looking for.
View 1 Replies
View Related
Oct 10, 2014
I have a set of records that will always be the same. A template if you wanna call it that.
"SELECT ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7 FROM TABLE WHERE Field2 = 000"
This returns a 20 record set. The user will click a button and create another set of 20 records, where they can make changes accordingly.
View 8 Replies
View Related
Mar 8, 2006
I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this:
select * from table where emailaddress count > 1
Can someone provide me with the correct syntax? :)
View 2 Replies
View Related
Mar 15, 2007
I have a table that has a DateTime column which uses a DataTimedatatype. How do I retrieve a range of records based on the month andyear using ms sql?Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies
View Related
Oct 15, 2007
Newbie alrt...
I am trying to create an asp page that will update an Access 2000 database. I need to update records if the user exists and create a new record if the user does not exist. Most of the variables are pulled from a separate "post" form.
I am using 2 pieces of info to find duplicates, as employee numbers can be assigned to multiple employees. Right now I have the page check for a duplicate employee id number then check for a duplicate last name. Unfortunately it is running each check separately, so if the last name is duplicated anywhere, it is sending a duplicated value.
here is the chunk of code in question... all RF_variables are request.form variables
cnt="SELECT COUNT(emp_id) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE emp_id='" & RF_emp_id & "'"
set again=conn.Execute(cnt)
dup=again("Xnum")
if dup>=1 then
cnt="SELECT COUNT(lname) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE lname='" & RF_lname & "'"
set again=conn.Execute(cnt)
dupl=again("Xnum")
if dupl=1 then
upd="UPDATE " & RF_course & " SET "
upd=upd & "section" & RF_section & "='" & RF_score & "'"
upd=upd & " WHERE emp_id='" & RF_emp_id & "'"
upd=upd & " AND lname='" & RF_lname & "'"
on error resume next
conn.Execute upd
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
Hopefully this is understandable.
If anyone can offer any help I would greatly appreciate it.
Thanks
View 1 Replies
View Related
Oct 30, 2007
I have a spreadsheet that we download from one of our customers that contains Sales Order Release information. The spreadsheets contains 1-n releases per part #.
I have a SQL 2000 view that groups this information into a smaller table (after importing into SQL).
I need to take this view and create 1-n release records based on our production run qty and put it in a new table. I have a field in my view that contains the desired # of records to create.
Since I never used TSQL to loop through table/view, how do I do this. Following is what the output should look like:
From SQL view
---------------------------------------
Part #: 11124A1
Qty Due:175
Run Qty: 50
# Release to Generate: 4
Output to Table
----------------------
Part # QtyDue RunQty
----------------------------------------------------
11124A1 50 50
11124A1 50 50
11124A1 50 50
11124A1 25 50
Note QtyDue is the production run qty, with the last record containing the remaining QtyDue from view's Qty Due
View 4 Replies
View Related
Sep 4, 2007
I have a table of Sales Order Releases. Following is what the table looks like:
SMIPartNo
QtyType
PO
POLine
QtyDue
UOM
DateDue
DateDueType
fsono
fcustno
finumber
frelease
fspq
JI-117933A1
Firm
N40136001
234
200
EA
7/2/2007 0:00
SH Ship Date
E00001
20
001
1
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/9/2007 0:00
SH Ship Date
E00001
20
001
2
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/30/2007 0:00
SH Ship Date
E00001
20
001
3
1800
JI-117933A1
Firm
N40136001
234
400
EA
8/6/2007 0:00
SH Ship Date
E00001
20
001
4
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/13/2007 0:00
SH Ship Date
E00001
20
001
5
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
8/20/2007 0:00
SH Ship Date
E00001
20
001
6
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/24/2007 0:00
SH Ship Date
E00001
20
001
7
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
9/3/2007 0:00
SH Ship Date
E00001
20
001
8
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/10/2007 0:00
SH Ship Date
E00001
20
001
9
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/17/2007 0:00
SH Ship Date
E00001
20
001
10
1800
See 2nd post for rest of question
View 1 Replies
View Related
Dec 28, 2006
I need to update the status of a client when they make a payment of a certine amount. My problem is this, the two pieces of information needed to do this are comming from two tables. For example;
@ClientID Int,
@PmtAmt Money
IF @PmtAmt >= tblSettings.TopAmt THEN
Update tblClients
SET
ClientStatus='High'
WHERE ClientID=@ClientID
ELSE
Update tblClients
SET
ClientStatus='Medium'
WHERE ClientID=@ClientID
ENDIF
How do I do this in a stored procedure? I need to select the TopAmt from the table tblSettings and then update the table tblClients.
View 3 Replies
View Related
May 29, 2008
Hi I hop i make sense with this, i have a page where you can update a products price based on the year, so for instance some products will still have prices set in 2007, while some may have prices which will be set in 2008, what i am trying to do is if the price has been set in 2008 it shows this value. The fields in the table which control the values are Rate(controls 2007), and Rate2007Period2. What i am trying to do is if Rate2007Period2 is null or 0, then display the 2007 rate, and if Rate2007Period2 is greater than 0 then display that value. Here is the view which displays the rates;
SELECT dbo.tblRateSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblRateSchedule.DESCRIPTION, dbo.tblRateSchedule.SWT, dbo.tblRateSchedule.Rate, dbo.tblRateSchedule.Tstamp, dbo.tblRateSchedule.RateSchedule_ID, dbo.tblRateSchedule.RateScheduleUnit_ID, dbo.tblRateSchedule.RateScheduleType_ID, dbo.tblRateSchedule.WorkType_ID, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblRateSchedule.Work_Type_Description AS Expr1, dbo.tblRateSchedule.Rate_Active, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2FROM dbo.tblRateSchedule INNER JOIN dbo.tblHistoricalRateSchedule ON dbo.tblRateSchedule.MA_Code = dbo.tblHistoricalRateSchedule.MA_Code LEFT OUTER JOIN dbo.tblRateScheduleType ON dbo.tblRateSchedule.RateScheduleType_ID = dbo.tblRateScheduleType.RateScheduleType_ID LEFT OUTER JOIN dbo.tblWorkType ON dbo.tblRateSchedule.WorkType_ID = dbo.tblWorkType.WorkType_ID LEFT OUTER JOIN dbo.tblRateScheduleUnit ON dbo.tblRateSchedule.RateScheduleUnit_ID = dbo.tblRateScheduleUnit.RateScheduleUnit_IDWHERE (dbo.tblRateSchedule.Rate_Active = '1')
View 6 Replies
View Related
Nov 9, 2005
Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.
View 5 Replies
View Related
Feb 27, 2014
I have one table with text column(varchar50) and 2 value columns(INT)
it looks like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text3 ext4
I have tried to use CASE with no luck.
edit:
output like this is also fine
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
5, null, text3 ext4
View 6 Replies
View Related
Jan 11, 2008
Do triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
View 3 Replies
View Related
