Transact SQL :: Return Previous Row Value If It Is Negative In Current Row

Sep 25, 2015

I need to return the previous row value if it is negative in current row.  For example, in the below table for ID=7 i need the value 1305(ID=4) since 6,5 are negative values. 




View 9 Replies


Transact SQL :: Query To Compare Current Price With That Of Previous Value Of Item

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

Transact SQL :: Pull Records For Current And Previous Calendar Year

Jun 16, 2015

I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?

select id, list_date

from tableA

where list_date > DATEADD(year,-1,GETDATE())

View 5 Replies View Related

How To Subtract Value At Previous Row From Current Row

Oct 1, 2006

Hi all,

I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @ minute 2 plus sales @ minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.
would appreciate any help i can get. thanks

View 5 Replies View Related

Fetching Next Previous And Current Rows?

May 19, 2012

I am trying to create a stored procedure where i would like to fetch the next previous and current rows from a particular pagename which is stored in a table with sorting of dateadded

like my products table structure


my dummy records are like

1 13 'PCHDD' 'PCHDD' '2009-12-03 04:32:30.363' '2009-12-04 04:32:30.363'
2 30 'SDRAM' 'SD_Ram' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
3 12 'Pen Drive' 'Pendrive' '2009-12-03 04:32:30.363' '2009-12-05 04:32:30.363'
4 3 'Note Book' 'NoteBook' '2009-12-03 04:32:30.363' '2009-12-08 04:32:30.363'
5 15 'VIO' 'VIO' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
6 19 'PS2' 'PS_2' '2009-12-03 04:32:30.363' '2009-12-09 04:32:30.363'
7 31 'PS3' 'PS_3' '2009-12-03 04:32:30.363' '2009-12-02 04:32:30.363'
8 23 'WII' 'WII' '2009-12-03 04:32:30.363' '2009-12-10 04:32:30.363'
9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'
10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'

now if i query for a record like

i should get
Prv Detail I_pad row
11 23 'Ipad' 'I_pad' '2009-12-03 04:32:30.363' '2009-12-11 04:32:30.363'
Current Detail I_phone
10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'
Next Record Speakers
9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'

View 2 Replies View Related

T-SQL (SS2K8) :: Add Balance Of Previous Row To Current Row

Jul 2, 2014

I am novice to intermediate writer of T-SQL. Here is my current Query:

SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit,
FROM [dbo].[TB_Lookup]
ORDER BY acctperiod ASCand this is what is produces.

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

T-SQL (SS2K8) :: Set Current Row Using Values In Previous Row

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

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:

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

Get Current Month And Previous 2 Months

Feb 10, 2014

I have table 'Open_Months' as shown below

00FBG 42013

I need current month and previous 2 months, for this i have concatinated month and year column into date formate, the query for this as shown below

SELECT cast(CONVERT(datetime, CONVERT(varchar(4), OM_Year) + RIGHT('0' + CONVERT(varchar(2), OM_Month), 2) + '01', 112) AS Datetime) from Open_Months where OM_Emp_ID = '00FBG'


From the above date I need to find current month and its previous 2 months.

View 1 Replies View Related

Compare Previous To Current Record Row

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.

count(start_date) from table_A
group by acct_number, start_date
having(count(start_date) > 1)

View 5 Replies View Related

How To Compare The Previous Row With Current Row In SQL Server 2000

Nov 10, 2005


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

Transact SQL :: How To Multiply Against A Negative Value

Jun 11, 2015

what am I doing wrong here? I can multipley * 1.25, but not -1.25. Every google entry I find is how to convert positive to negative numbers... I want to use update to multiply and lower the price by 25% (I used the function a bit to liberally to multiply by 125% and now want to bring the values down to earth)

update production.product set listprice = (listprice * -1.25)
select max(listprice) from production.product

View 8 Replies View Related

Get Data For Previous Month In Table Based On Current Date

Jul 28, 2014

I need to get previous month data in the table based on current date.

In case of execution of each month, the data for previous month should come with date as between

create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)

insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')

if I execute in month July ( ie: today)

select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014


5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000

if I execute in month June

select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014


6Gopu2014-06-02 00:00:00.000
2Britto2014-06-12 00:00:00.000
4Selva2014-06-27 00:00:00.000

View 1 Replies View Related

Transact SQL :: Converting A Negative Varchar Decimal?

Jul 6, 2015

how to convert -2.0120 to -2 or 3,6789 as 3 ignoring the decimal places.

The input -2.0120 is a varchar.

View 8 Replies View Related

T-SQL (SS2K8) :: Table With Score Info For Groups - Ranking For Current And Previous Week

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
(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

Looking For How To Return The Previous Payment

Aug 21, 2007

I use the following sql in a view to return the last payment date and amount made by clients. What I need is a way to return the payment date and amount for the payment prior to the last one.
Any help is appreciated very much,
SELECT     dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate AS LastPayDate, SUM(dbo.tblPaymentReceipts.AmountPaid) AS LastPayAmtFROM         dbo.tblPaymentReceipts INNER JOIN                          (SELECT Client_ID, MAX(PaymentDate) AS LastPayDate                            FROM dbo.tblPaymentReceipts AS tblPaymentReceipts_1                            GROUP BY Client_ID) AS A ON dbo.tblPaymentReceipts.Client_ID = A.Client_ID AND dbo.tblPaymentReceipts.PaymentDate = A.LastPayDateGROUP BY dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate 

View 6 Replies View Related

How To Return Records Based On Previous Month

Jan 11, 2005

I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.

I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.

I've tried:
SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"

'SELECT TODAY'S MONTH and the last 2 months
SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"

These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).

Any ideas?

View 7 Replies View Related

T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours

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

Return Current Date

Apr 27, 2004


Can I write a code in SQL that return the current date? If so, how?



View 2 Replies View Related

Pls Help W/ A Query To Return Running Balances From The Previous Rolling 3 Month Period.

Oct 18, 2007

Please refer to this table in this discussion:


























What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:

If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)

I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:

Code Block

SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:


























Each date's charges are a culmination of 3 months worth of charges.

Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!

Thanks in advance!

View 6 Replies View Related

Power Pivot :: Cumulative Total For Current Week Days And Previous Week

Nov 30, 2015

We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week. 

I'm trying to produce a line chart with 2 axis:

- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of orders 

The chart needs two series:

Previous Week. The running count of orders placed that week. 
Current Week. The running count of orders placed this week. 

Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day. 

I have two tables:

Orders TableCalendar Table

The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc. 

My measure for counting orders is simply; # Orders: = countrows[orders].

How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:

- [URL] ...
- [URL] ...

But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.

View 3 Replies View Related

How To Return Current Hour Of Week??

Mar 7, 2004

One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
If week starts on system from Monday.

Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167



View 7 Replies View Related

Transact SQL :: First And Last Day Of Previous Month From Getdate

Dec 10, 2010

How to get First day of previous month and last day of previous month(From getdate()) using SQL..?

View 13 Replies View Related

Transact SQL :: Replacing Isnull With Previous Value

May 25, 2015

I am having table like below,

Period Pointvaluemonth area
201109 100 10Septembertn

[Code] ....

Then i want to copy september value for october and november and Jan value for feb & mar like next null values should get replaced with previous not null value ...

My desired output is ,

Expected Output is…..

Period Pointvaluemonth area
201109 100 10September tn

[Code] ....

View 7 Replies View Related

Transact SQL :: Copy Previous Non Null Value

Aug 10, 2015

I have a table with data like below

SampleDateTime Value

2015-08-08 01:00 AM 0.5
2015-08-08 02:00 AM Null
2015-08-08 03:00 AM Null
2015-08-08 04:00 AM Null


Using subquery I thought of a couple of ways but that didn't work.

View 6 Replies View Related

Transact SQL :: Previous Log Date For Each Record

Sep 22, 2015

I have one table :

file _target

which has below records..

file_target_ID is identity column which will repeat per files_ID

Now, i just shown Target log  for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date  column

Like this ..

Target Date                     New Column
2015-09-09 00:00:00.000   2015-09-16 00:00:00.000
2015-09-16 00:00:00.000   2015-09-25 00:00:00.000
2015-09-25 00:00:00.000   New Target date after 25-9-2015

how to do this in SQL Server ??

using Cross Apply ?? Row_Number() ??

View 3 Replies View Related

Transact SQL :: First Of November Previous Year

Oct 9, 2015

How I can setup the first of November of the previous year dynamically? So running now it should be 01-11-2014 but if I run in 2th of January 2016 it should be 01-11-2015.

View 17 Replies View Related

Compare Given Period In Current Year / Previous Year

Mar 15, 2006

HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan

View 3 Replies View Related

Transact SQL :: Update Column Based On Row Number For Previous Row

Jul 25, 2015

Below is the resultset I got using the following SQL statement

,NULL AS end_date
FROM dbo.Table_1


RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL

[Code] ....

Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18

[Code] ....

View 4 Replies View Related

Transact SQL :: Retrieve Customers Invoiced Twice Or More In Previous Month

Oct 29, 2015

I am trying to pick up the customers invoiced twice or more within a month. In the case below Pepsi Cola and Jack Daniel were invoiced twice in October. The query need to pickup the previous month, se being in October I need to pick up the invoice of September.

create table #forum (Customer varchar(20),Invoiced date)
insert into #forum values ('Pepsi Cola','2015-09-01') ,('Pepsi Cola','2015-09-06') ,
('Pepsi Cola','2015-10-01') ,('Pepsi Cola','2015-10-02') ,('Pepsi Cola','2015-11-01') ,
('Ferrarelle','2015-09-01') ,('Ferrarelle','2015-10-01') ,('Ferrarelle','2015-11-16') ,('Ferrarelle','2015-11-01') ,
('Jack Daniel','2015-09-01') ,('Jack Daniel','2015-09-04') ,('Jack Daniel','2015-09-06') ,('Jack Daniel','2015-09-30') ,
('Jack Daniel','2015-10-01') ,('Jack Daniel','2015-10-18') ,('Jack Daniel','2015-11-01') ,
('Bud','2015-09-01') ,('Bud','2015-10-01') ,('Bud','2015-11-01')
select * from #forum

View 5 Replies View Related

Transact SQL :: Comparing Previous Records Based On Each ID Column

Aug 21, 2015

I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1

We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID

My tables look like

Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)

Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)

select * from @tab1

1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only

2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column 

3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column 

My expected result set should be like ....

View 10 Replies View Related

Transact SQL :: Get Counts Including Previous Years / Months?

May 28, 2015

I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'

How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from  05/16 and 05/09 and so on...

View 13 Replies View Related

Comparing The Previous Time With The Current Time

Dec 21, 2005

Manish writes "Hi there, any help will be greatly appreciated as i am struggling with this problem :)

I have a field in a database called time which also has the value time in it.

What i want to do is to have a sql query which will display all the records in that table where 5 mins have gone after that time."

View 4 Replies View Related

How To Subtract Previous Value Of One Column From Current Value Of Another Column

Sep 7, 2006

I have a table like this:

Bottom Top

0 2

4 6.5

9 14

15 17

In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.

So, the result would be 0, 2, 2.5, 1

I must return the value in a table. Thank you all for your help!


View 9 Replies View Related

Copyrights 2005-15, All rights reserved