Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get current row data + next row?

Author  Topic 

tech_1
Posting Yak Master

129 Posts
Posted - 2012-04-17 : 09:57:26
I am trying to obtain, for a selected data set/result set, the current Row value for a field + the next row value for the field.

is it possible to do this in a SELECT [col1], [col2], [col3].... statement?

if not, what are the alternatives and how would I do this?

I have data in a temp table of say "dates" (daily dates)
I then need to find quarterly data for data in the dates temp table. The quarterly data is another select statement with a between clause for the dates and is bringing that dataset fine but within this, I need to get the current row Date + the next row Date field.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-17 : 10:20:10
It can be done - would be easier if you can provide the DDL for the tables. In any case, if you have consecutive dates in a table, the following example shows you one way to get the data for the next date. If the dates are not consecutive, we will need to do something more.
-- create a test table.
CREATE TABLE #tmp (datestamp DATE, col1 FLOAT);
INSERT INTO #tmp VALUES
('20120101',32.7),
('20120102',22.8),
('20120103',76.8),
('20120104',51.8),
('20120105',45.8);

-- Get each day's and next day's data.
SELECT
	a.DateStamp,
	a.col1,
	b.DateStamp AS nextDate,
	b.Col1 AS NextDateCol1
FROM
	#tmp a
	LEFT JOIN #tmp b ON b.Datestamp = DATEADD(dd,1,a.datestamp);
	
-- cleanup
DROP TABLE #tmp;
Go to Top of Page

tech_1
Posting Yak Master

129 Posts
Posted - 2012-04-17 : 10:31:39
dates will not be consecutive for the quarterly data.

DDL? Data structure? well there are quite alot of columns but I will state the important ones really.

DateVal (DateTime)
Calc1 (float)
FacValue (Float)
FacType (nvarchar(100))

these are the rows being returned eventually.

I have a temp table which holds Date (datetime) and value(nvarchar(200)) for temp data, data for each and every day between a provided start and end date

what I then want to do is that the final result of data being returned, have a column which I want to do a SUM of value (in the temp table) for the current record DateVal + the next record DateVal

2011/01/01
2011/04/01
2011/07/01
2011/10/01


above are quarterly's. Then what I mean by having a column in there to do the SUM I need the date values which basically from the snippet is dates between 2011/01/01 and 2011/04/01 then for the next row: 2011/04/01 and 2011/07/01 and so on
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-04-17 : 10:40:09
I'm not 100% sure I'm following your logic. Here is a guess (using suntabecks data):
;WITH cte
AS (
SELECT 
	*,
	ROW_NUMBER() OVER (ORDER BY datestamp) AS RowNum
FROM 
	#tmp
)

SELECT *
FROM 
 cte AS A
LEFT OUTER JOIN
 cte AS B	
 ON A.RowNum = B.RowNum + 1
If that is nto right, please see the following links for how to provide DDL, DML and expected output in a consumable format so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tech_1
Posting Yak Master

129 Posts
Posted - 2012-04-17 : 10:56:53
Sure, thanks.

its a bit tricky because there are large amounts of fields with custom data types and relational tables, I was trying to simplify it down :-)

let me try to simplify it down if I can....

quote:

CREATE TABLE [dbo].[QuarterData](
[QuarterVal] datetime NOT NULL,
[P_Total] float NULL,
[Period_Total_Pulses] float NULL,
[Period_Value_C02e] [CO2e] NULL,
[Period_Value_Money] float NOT NULL,

CONSTRAINT [PK_QuarterData_1] PRIMARY KEY CLUSTERED
(
[QuarterVal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


I am returning data from that table based upon a start and end date given.

the data typically is:

quote:


INSERT INTO QuarterData (QuarterVal, P_Total, Period_Total_Pulses, Period_Value_C02e, Period_Value_Money)
VALUES ('2011-01-01', 100, 10, 10, 10)


INSERT INTO QuarterData (QuarterVal, P_Total, Period_Total_Pulses, Period_Value_C02e, Period_Value_Money)
VALUES ('2011-03-01', 100, 10, 10, 10)


INSERT INTO QuarterData (QuarterVal, P_Total, Period_Total_Pulses, Period_Value_C02e, Period_Value_Money)
VALUES ('2011-06-01', 100, 10, 10, 10)


INSERT INTO QuarterData (QuarterVal, P_Total, Period_Total_Pulses, Period_Value_C02e, Period_Value_Money)
VALUES ('2011-09-01', 100, 10, 10, 10)


INSERT INTO QuarterData (QuarterVal, P_Total, Period_Total_Pulses, Period_Value_C02e, Period_Value_Money)
VALUES ('2011-12-01', 100, 10, 10, 10)


INSERT INTO QuarterData (QuarterVal, P_Total, Period_Total_Pulses, Period_Value_C02e, Period_Value_Money)
VALUES ('2012-01-01', 100, 10, 10, 10)


INSERT INTO QuarterData (QuarterVal, P_Total, Period_Total_Pulses, Period_Value_C02e, Period_Value_Money)
VALUES ('2012-03-01', 100, 10, 10, 10)


in the SPROC, it also calls out to a SQL CLR to obtain data. This data is then put into a temp table:

quote:

CREATE TABLE #tmpDegreeDaysData (
date DATETIME,
[value] NVARCHAR(200)
)



The data returned from this SQL CLR will be of each and every single day from the start and end date (specified by the SPROC parameters) i.e:

2011-01-01 12
2011-01-02 14
2011-01-03 20.1
..
..

I want to add a column at the end of the select statement for selecting data from the quarterly table (lets call it tempSum), which does the following:

SELECT *, tempSum = SUM(something here) FROM Quarterly WHERE QuarterVal BETWEEN @startDate AND @endDate

tempSum = For the current QuarterVal, get me the date for current Row + the next row and do a Sum on the temp table for the Value column
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-04-17 : 11:28:33
Does the sample data even make sense?

At any rate, I tried one mroe time.
;WITH cte
AS (
SELECT 
	*,
	ROW_NUMBER() OVER (ORDER BY [Date]) AS RowNum
FROM 
	#tmpDegreeDaysData
)

SELECT
	*
FROM 
 dbo.QuarterData AS QD
OUTER APPLY
(
	SELECT
		CAST(A.Value AS FLOAT) + COALESCE(CAST(B.Value AS FLOAT), 0.0) AS TempSum
	FROM
		cte AS A
	LEFT OUTER JOIN	
		cte AS B
		ON A.RowNum = B.RowNum + 1
	WHERE
		A.Date  = QD.QuarterVal
) AS T
Go to Top of Page

tech_1
Posting Yak Master

129 Posts
Posted - 2012-04-17 : 11:57:40
thats the thing, I dont know if the sample data makes sense (I wasnt involved in the whole thing, nor do I know what the data is meant to really look like but I know what it should be getting!)

ill give your suggestion a try :-)
Go to Top of Page

tech_1
Posting Yak Master

129 Posts
Posted - 2012-04-17 : 12:01:31
deleted
Go to Top of Page

tech_1
Posting Yak Master

129 Posts
Posted - 2012-04-17 : 12:19:25
hmmm. almost I think. its difficult explaining at the same time :-)

Currently as it stands, here is the final result set being returned:

quote:

SELECT
QuarterData.QuarterVal as Datim,
QuarterData.QuarterVal,
@Factor_Name AS [Factor_Name],
Factor_Value =
CASE
WHEN @Factor_Name = 'A' THEN CAST(QuarterData.A AS float)
WHEN @Factor_Name = 'B' THEN CAST(QuarterData.B AS float)
WHEN @Factor_Name = 'C' OR @Factor_Name = 'D' THEN
(SELECT [value] FROM
#tmpDegreeDaysData
WHERE [date] = QuarterData.QuarterVal)
ELSE 0
END
FROM QuarterData

WHERE QuarterData.QuarterVal BETWEEN @DateFromDay AND @DateToDay

ORDER BY QuarterData.QuarterVal


so in the Factor_Value, I want to be able to do a SUM(value) on the #tmpDegreeDays where the Date in #tmpDegreeDays is from the current QuarterVal to the next row QuarterVal.


if it makes it any easier, can a subquery be applied here?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site