T-SQL (SS2K8) :: Compare With Previous Records
Oct 20, 2014
I am having a table which contains data of students like:
StudentID,StudentName,Term,RESult.
Sample data :
StudentID,StudentName,Term,RESult.
1,ABC,Term1,Pass
1,ABC,Term2,Fail
1,ABC,Term3,Pass
1,ABC,Term4,Pass
1,ABC,Term5,Pass
Now i want to compare Result and dislay prevterm where student fail:
Now my output would be as: Now I want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:
studentID PrevFailTerm, CurrentTerm
1,Term2,Term5
View 1 Replies
ADVERTISEMENT
Sep 16, 2014
I need to compare the next row with the previous row of same table and produce nonidentical column.for eg... say my table has
Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180
then my output would be as below:
StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150
i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.
View 9 Replies
View Related
Mar 3, 2014
There are four tables
1. Matter
MID, CID, RType
001, a, m
002, a, m
003, b, m
004, c, m
2. Category
CID. RType
a, T
b, T
c, T
3. Security assignmnet
RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04
4. Group
GID
g01
g02
g03
g04
I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"
In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"
How can I create qury to find all the possible record in the table #2?
View 9 Replies
View Related
Mar 18, 2014
I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.
SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)
My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2
View 7 Replies
View Related
Mar 3, 2014
My current code returns account_number with multiple start_date regardless of the value is same or not. However, I would like to get only the account number when the value on start_date is different within same account_number.
select
acct_number
count(start_date) from table_A
group by acct_number, start_date
having(count(start_date) > 1)
View 5 Replies
View Related
Nov 10, 2005
Hi,
I have to compare the previous row with current row in a table in SQL Server 2000. Please help me how to do this in a optimized way. Table contains nearly 30 columns and rows count is more than 10 digits.
View 7 Replies
View Related
Nov 18, 2015
There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date
Price Status
1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore
3 11/12/2015 100 High Value
create table testmaster
(
ID int,
Threshold int
)
create table testdetail
(
ID int,
Date varchar(20),
Price float
)
[Code] ...
View 15 Replies
View Related
Apr 11, 2014
I am trying to code a rule at the moment which sets a value based on the value of the previous row. This is as far as I have got so far. I am trying to run it as a set based command and I believe I may need to make use of a numbers table.
Anyway the code is....
declare @data table
(ID int identity(1,1),
DeviceID int,
EventTypeID int,
EventID int)
[Code] ....
So the output is currently incorrect. Each DeviceID needs to have a ID assigned to the MIN(ID) which isn't yet in the code above. This ID has to be sequential across the full table and not dependent on ID.
Next the rule is coded in the case statement above.
So for each DeviceID, when the EventType goes from 1, 2 or 4, to 0 or 3, the following record after the 0 or 3 will have a new EventID. And conversely when the EventType goes from 0 or 3 to 1,2, or 4, the record that is the 1, 2 or 4 will have a new EventID.
View 6 Replies
View Related
Jul 2, 2014
I am novice to intermediate writer of T-SQL. Here is my current Query:
SELECT [FISCALYEAR],
[ACCTPERIOD],
SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit,
[POSTINGTYPE]
FROM [dbo].[TB_Lookup]
WHERE [POSTINGTYPE]='Profit & Loss'
GROUP BY [FISCALYEAR],[ACCTPERIOD], [POSTINGTYPE]
ORDER BY acctperiod ASCand this is what is produces.
FISCALYEARACCTPERIODTrialBalanceDebitPOSTINGTYPE
2014 201401 282361372.13000 Profit & Loss
2014 201402 227246272.86000 Profit & Loss
2014 201403 315489534.33000 Profit & Loss
2014 201404 287423793.76150 Profit & Loss
2014 201405 256521290.76000 Profit & Loss
2014 201406 65582951.30000 Profit & Loss
Now I need a way to add another field that takes the TrialBalanceDebit from current ACCTPERIOD and adds it to the Previous ACCTPERIOD TrialBalanceDebit.
View 9 Replies
View Related
Feb 25, 2015
I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.
Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl
ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5
I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored.
My expected output would be:
ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305
View 7 Replies
View Related
Aug 22, 2014
How do I get first day of last month of previous quarter from today's date? I know my question is little confusing. I need to get 06/01/2014 using t-sql.
View 2 Replies
View Related
Nov 4, 2015
I want to find previous date from selected date. below is the sample data.
DECLARE @StartDate SMALLDATETIME = '1/11/2016'
declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')
Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.
if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.
View 4 Replies
View Related
Mar 4, 2014
How can I calculate and return the previous Date at 18:00 Hours?
Here is a miserable attempt:
DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()
SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate
-- Desired Result is the following:
-- 2014-03-03 18:00
View 9 Replies
View Related
Mar 23, 2014
I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.
Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.
If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.
Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.
Here is the sample table structure and data :
CREATE TABLE [dbo].[tblRedeems]
(
[Mobileno] [varchar](50) NOT NULL,
[TripNo] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
[TripEndTime] DATETIME NOT NULL,
[Code] .....
View 5 Replies
View Related
Sep 29, 2014
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
View 6 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
Mar 16, 2015
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
View 1 Replies
View Related
Jan 14, 2015
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
[code]...
View 2 Replies
View Related
Jan 9, 2008
I have got a table with GUID (PK), COMPANY, CONTACT. There are going to be instances where the company name is the same on multiple records.
What i am trying to do is work out what is the next and previous record
The data is going to be sorted, by COMPANY then GUID.
I am think a stored procedure would be the best to combact this, but very usure how to go about writing it, so i passed the present company and GUID values to it.
Any help would be appreciated, and thanks in advance.
View 20 Replies
View Related
Jan 21, 2015
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
View 3 Replies
View Related
Jun 27, 2014
I have the following recordset:
cmdBatchNbPdsLbsZONE
817159644 1.55320031
817159652 9.09590031
817159679 2.5891806
817159687 5.7123006
817159709 2.3903006
817159733 2.2792006
817159741 2.0647007
817159768 1.2430007
817159784 4.1547006
817159792 3.56576013
I need to extract the corresponding price from the following table:
Zone MaxWeight Price
---------------------- ---------------------------------------
31 1.70 7.14
31 2.20 8.76
31 3.30 9.47
31 4.40 9.69
31 5.50 10.61
31 6.60 11.05
31 7.70 11.49
31 8.80 11.93
31 9.90 12.37
31 11.00 12.81
31 12.10 13.23
In this case, the 2 first rows should give a price of
1) 7.14 (weight between 0 - 1.70)
2) 11.93 (weight between 8.80 - 9.90)
How can I do that with a query?
View 4 Replies
View Related
Aug 7, 2014
We have a table setup to track changes that are made to another table, for auditing purposes. How do we compare the most recent record in the change table with the previous record in the change table? Particularly, we have a column named DUE_DATE in the change table and want to identify when the most recent change has a different DUE_DATE than the previous change made.
View 8 Replies
View Related
Oct 15, 2014
I am having below two tables:
1) TableA : Which contains 5 columns(Column1,..........Column5)
2)TableB : Which contains 10 columns(Column1,..........Column10)
TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:
select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )
But query is taking almost 5 minutes. Is there is another approach?
View 4 Replies
View Related
Oct 22, 2014
I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.
How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.
SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',
[Code] ....
View 6 Replies
View Related
Oct 28, 2014
I am asked to compare the address fields (three columns of nvarchar(100) ) of a customer database (around 10,000 records) and find any duplicates. If it is a character by character match, I could have just GROUPed and get the result.
But, I am expected to produce a list with similar addresses which the guys who entered may have use slightly different spelling or more or less characters, or a "." here and there.
is there any way to create a query for this?
View 5 Replies
View Related
Dec 3, 2014
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
View 5 Replies
View Related
Jul 13, 2005
On a webform, I have three button ... [7 days] [15 days] [30 days]When the user clicks one of the buttons, I want to return their orders for the past X days. The WHERE clause would include something like this (for 7 days):WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102))How do I parameterize the number of days?Thanks,Tim
View 2 Replies
View Related
May 15, 2008
I have an sub-period end-date column, in a row set of (let's say) 2 records.
How Can I grab a previous record and use it's SUB_PD_END_DT
to add a day and get the current record's sub period beginning date?
i.e.
row 1 = col1, col2, SUB_PD_END_DT = (2008-05-02)
row 2 = col1, col2, SUB_PD_END_DT = (2008-05-09)
therefore, row 2 = SUB_PD_start_DT = (2008-05-03)
Any help would be great.
Thanks
JDA
View 9 Replies
View Related
Oct 15, 2007
I am in the middle of taking course 2073B �� Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
View 1 Replies
View Related
Mar 26, 2014
I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.
The problem is that when I run my compare I get false matches. Example:
CREATE TABLE #ABC (Acct VARCHAR(10), Que INT);
INSERT INTO #ABC VALUES
('2310947',110),
('2310947',245);
[Code] ....
Which gives me two records when I really do not want any as the tables are identical.
View 2 Replies
View Related
Jul 2, 2014
--drop table #temp
create table #temp (id int, idvalue int)
insert into #temp(id,idvalue)
select 1095,75
[code]...
I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.
View 3 Replies
View Related
Aug 14, 2014
I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.
'test1', 'need this record', 25, {d '1901-01-01'}
'test3', 'some longer value', 23, {d '1900-01-01'}
declare @table1 table (
col1 varchar(10) not null,
col2 varchar(200) null,
col3 int not null,
[code]....
View 2 Replies
View Related
Sep 17, 2015
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
View 4 Replies
View Related
