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.
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?
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
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.
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:
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?
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?
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 ;)
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.
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.
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,
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.
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.
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?
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?
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
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..
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.
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? :)
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 ***
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.
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
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.
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')
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.
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
