June 19, 2013 at 5:37 pm
I am having problems figuring out how to add a running total. I initially tried to get my TempTable to show the different categories of transactions by reporting 1, 2, or 3. I could not get my program to do this. I ultimately want curItem compared to prevItem and if there is not a match, to populate the Trnd field with 1. If the curItem and prevItem match, I need the program to incrementally sum from the first mis-match to the end of the matched fields.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
curItemchar,
prevItemchar,
Trndint
)
INSERT INTO #mytable
(curItem, prevItem, Trnd)
SELECT'D','U','' UNION ALL
SELECT'D','U',''UNION ALL
SELECT'U','D',''UNION ALL
SELECT'U','U',''UNION ALL
SELECT'U','U',''
SELECT *
FROM #mytable
DECLARE @TempTable TABLE (curItem char(2), prevItem char(2), Trnd int);
DECLARE @curItem char(2), @prevItem char(2)
INSERT INTO @TempTable(curItem, prevItem, Trnd)
SELECTcurItem, prevItem, Trnd
FROM #mytable
BEGIN
SELECT@curItem=curItem,
@prevItem=prevItem
FROM @TempTable
IF @curItem = @prevItem
BEGIN
UPDATE EURUSD#1A SET Trnd = 1;
END
ELSE IF @curItem <> @prevItem
BEGIN
UPDATE EURUSD#1A SET Trnd = 2;
END
ELSE
BEGIN
UPDATE EURUSD#1A SET Trnd = 3;
END
SELECT *
FROM @TempTable
END
June 19, 2013 at 6:26 pm
It seems you have possibly over simplified your example - there is no way to ORDER the rows to give you the correct values.
As the solution will depend a lot on the actual situation you are trying to work in, perhaps you could create a sample that more accurately reflects you r real situation?
Otherwise we can only give you a general answer that will almost certainly not suit you.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 19, 2013 at 7:57 pm
Sorry,
This would have made it much easier to understand. The resulting report should look like:
curItem / prevItem / Trnd
D U 1
D U 1
U D 1
U U 1
U U 2
U U 3
U U 4
June 20, 2013 at 12:29 am
Well, no that doesn't help in this case.
I understand the required result, but the sample data is lacking anything to ORDER by to get that result.
You cannot rely on the rows being SELECTED in any particular ORDER without specifying the column or expression to ORDER by.
If you had a row identifier, for instance, we could use that.
Without it, who's to say the rows won't be SELECTED in this order:
curItem / prevItem / Trnd
U U 1
U U 2
D U 1
D U 1
U D 1
U U 1
U U 2
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 20, 2013 at 2:08 pm
There are a number of techniques to approach running totals in T-SQL, the most documented of which is the quirky update which is an article and long discussion afterwards by Jeff Moden. You'll find it around the site if you want to.
However, I agree with Magoo above. You can't do a running total without having a sort control. Now, there are a number of other steps you'll want to include to make sure that things won't go awry when you're not looking, but that's absolutely required.
If you can't ORDER BY the query to get you the sorting you want, there's just not enough to work with for a Running Totals format with any kind of repeatability.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2013 at 12:32 am
Here is a portion of the complete result set. I need the program to look at the curItem as compared to the prevItem and if there is a match, update "Trnd" column to counter = counter + 1. If there is not a match just apply counter which is equal to 1. For every record read, the counter needs to increment if there is a match. When there is not a match the counter needs to go back to 1. Though I know the structural elements needed, I do not know the best programming construct to apply it to. I explored CASE and IF, ELSE but do not know how to program it. I hope this makes more sense.
ConcatYearMonthDayHourMinuteSecondMilisecond curRN prevRN curBidprevBidTrndcurItemprevItem
201301012202021572013010122020215711101.320461.320411UU
201301012202150282013010122021502812111.320461.320461FU
201301012203016732013010122030167313121.320461.320461FF
201301012203017322013010122030173214131.320191.320461DF
201301012203017922013010122030179215141.320251.320191UD
201301012203020922013010122030209216151.320411.320251UU
201301012203023972013010122030239717161.320461.320412UU
201301012203072332013010122030723318171.320461.320461FU
201301012203142622013010122031426219181.320461.320461FF
201301012203145822013010122031458220191.320461.32046 2FF
201301012203146422013010122031464221201.320161.320461DF
201301012203152782013010122031527822211.320211.320161UD
201301012203190632013010122031906323221.320091.320211DU
201301012203191222013010122031912224231.320051.320091DD
June 21, 2013 at 10:44 am
mcertini (6/21/2013)
What you want isn't a "running total", it is simply a 1-n counter, partitioned by a particular set of fields and perhaps ordered by another set of fields. Investigate the ROW_NUMBER() TSQL construct, which will do exactly what is needed (assuming I am reading correctly). BOL has examples, and you can find many online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 21, 2013 at 9:42 pm
Thanks SQLGuru for pointing me in the right direction. Taking your direction I worked on Row_Number and Partition By. This gets me ever so close to what I need but as the old saying goes, "So close but yet so far away". Do you know how to get this to start the sequential numbering over again after an iteration sequence? What I noted is that Row_Number continues to iterate over the complete population. I need it to reset after the first continuous records. See below:
USE
EURUSD
GO
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
Yearvarchar(4),
Monthvarchar(2),
Dayvarchar(2),
Hourvarchar(2),
Minutevarchar(2),
Secondvarchar(2),
Milisecondvarchar(3),
curItem char(2)
)
INSERT INTO #mytable
(Year, Month, Day, Hour, Minute, Second, Milisecond, curItem)
SELECT'2013','05','04','10','12','45','200','F' UNION ALL
SELECT'2013','05','04','10','12','46','400','D'UNION ALL
SELECT'2013','05','04','10','12','46','500','U'UNION ALL
SELECT'2013','05','04','10','12','47','400','U'UNION ALL
SELECT'2013','05','04','10','12','49','400','U'UNION ALL
SELECT'2013','05','04','10','12','50','400','D'UNION ALL
SELECT'2013','05','04','10','12','50','400','D'UNION ALL
SELECT'2013','05','04','10','12','50','400','F'
SELECT Year, Month, Day, Hour, Minute, Second, Milisecond, curItem, ROW_NUMBER() OVER (PARTITION BY curItem ORDER BY Year, Month, Day, Hour, Minute, Second, Milisecond, curItem)
FROM #mytable
ORDER BY Year, Month, Day, Hour, Minute, Second, Milisecond, curItem
YearMonthDayHourMinuteSecondMilisecond curItem(No column name)
20130504101245200F 1
20130504101246400D 1
20130504101246500U 1
20130504101247400U 2
20130504101249400U 3
20130504101250400D 2 -> This number needs to be 1
20130504101250400D 3 -> This number needs to be 2
20130504101250400F 2 -> This number needs to be 1
June 22, 2013 at 1:51 am
I think it's crazy that someone split the date/time up like they did. If you want that same kind of output, then get rid of the first CTE in the following and replace all occurances of "DT" with the list of date/time columns you have. Using multiple DATEADDs would probably also make the code faster. I just didn't want to clutter the example with all of that.
Using your latest test data, the following will do as you wish.
WITH
cteSimplifyDateTime AS
(
SELECT DT = CAST(Year+Month+Day+' '+ Hour+':'+Minute+':'+Second+'.'+Milisecond AS DATETIME)
, curItem
FROM #MyTable
),
cteEnumerateGroups AS
(
SELECT DT
, curItem
, MyGroup = ROW_NUMBER() OVER (ORDER BY DT,curItem)
- ROW_NUMBER() OVER (PARTITION BY CurItem ORDER BY DT,curItem)
FROM cteSimplifyDateTime
)
SELECT DT
, curItem
, ConsecutiveCount = ROW_NUMBER() OVER (PARTITION BY MyGroup ORDER BY DT,curItem)
FROM cteEnumerateGroups
ORDER BY DT,curItem,ConsecutiveCount
;
Here's the output I get from that.
DT curItem ConsecutiveCount
----------------------- ------- --------------------
2013-05-04 10:12:01.200 F 1
2013-05-04 10:12:46.400 D 1
2013-05-04 10:12:46.500 U 1
2013-05-04 10:12:47.400 U 2
2013-05-04 10:12:49.400 U 3
2013-05-04 10:12:50.400 D 1
2013-05-04 10:12:50.400 D 2
2013-05-04 10:12:50.400 F 1
(8 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2013 at 10:09 am
Jeff, his first post with actual data in it had a "Concat" field, which seems to be a bit numeric with all the date-time values strung together. I think (if it exists already) that field could be used as the order by for the ROW_NUMBER() I was intending for him to use.
I concur that splitting date-time parts like this almost never brings any benefit...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2013 at 12:31 pm
Jeff & Kevin,
Thank you for your input, it is much appreciated. As I am a rookie in SQL programming, the included code is so beneficial to learning what is possible.
I took the most recent recommendation and applied it against my database. There appears to be some discrepancies though. Here is what I am seeing. Listed below in the code is the data for what is reflected here.
DT curItem ConsecutiveCount
2013-01-02 02:07:14.480U 2
2013-01-02 02:07:14.540U 3
2013-01-02 02:07:14.720D 1
2013-01-02 02:07:15.140D 2
2013-01-02 02:07:15.200F 2 -> Should be 1
2013-01-02 02:07:15.557D 1
2013-01-02 02:07:16.110U 1
2013-01-02 02:07:16.530F 1
2013-01-02 02:07:16.953D 1
2013-01-02 02:07:17.020F 2 -> Should be 1
2013-01-02 02:07:17.557F 3 -> Should be 2
2013-01-02 02:07:17.977U 1
2013-01-02 02:07:18.540F 3 -> Should be 1
2013-01-02 02:07:19.047F 4 -> Should be 2
2013-01-02 02:07:19.423D 1
2013-01-02 02:07:19.547F 2 -> Should be 1
2013-01-02 02:07:19.910U 1
2013-01-02 02:07:20.210F 2
2013-01-02 02:07:20.270D 1
2013-01-02 02:07:20.807U 1
2013-01-02 02:07:21.310F 2 ->Should be 1
2013-01-02 02:07:22.470F 3 ->Should be 2
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
Yearvarchar(4),
Monthvarchar(2),
Dayvarchar(2),
Hourvarchar(2),
Minutevarchar(2),
Secondvarchar(2),
Milisecondvarchar(3),
curItem char(2)
)
INSERT INTO #mytable
(Year, Month, Day, Hour, Minute, Second, Milisecond, curItem)
SELECT'2013','01','02','02','07','14','480', 'U' UNION ALL
SELECT'2013','01','02','02','07','14','540', 'U' UNION ALL
SELECT'2013','01','02','02','07','14','720', 'D' UNION ALL
SELECT'2013','01','02','02','07','15','140', 'D' UNION ALL
SELECT'2013','01','02','02','07','15','200', 'F' UNION ALL
SELECT'2013','01','02','02','07','15','557', 'D' UNION ALL
SELECT'2013','01','02','02','07','16','110', 'U' UNION ALL
SELECT'2013','01','02','02','07','16','530', 'F' UNION ALL
SELECT'2013','01','02','02','07','16','953', 'D' UNION ALL
SELECT'2013','01','02','02','07','17','020', 'F' UNION ALL
SELECT'2013','01','02','02','07','17','557', 'F' UNION ALL
SELECT'2013','01','02','02','07','17','977', 'U' UNION ALL
SELECT'2013','01','02','02','07','18','540', 'F' UNION ALL
SELECT'2013','01','02','02','07','19','047', 'F' UNION ALL
SELECT'2013','01','02','02','07','19','423', 'D' UNION ALL
SELECT'2013','01','02','02','07','19','547', 'F' UNION ALL
SELECT'2013','01','02','02','07','19','910', 'U' UNION ALL
SELECT'2013','01','02','02','07','20','210', 'F' UNION ALL
SELECT'2013','01','02','02','07','20','270', 'D' UNION ALL
SELECT'2013','01','02','02','07','20','807', 'U' UNION ALL
SELECT'2013','01','02','02','07','21','310', 'F' UNION ALL
SELECT'2013','01','02','02','07','22','470', 'F'
;WITH
cteSimplifyDateTime AS
(
SELECT DT = CAST(Year+Month+Day+' '+Hour+':'+Minute+':'+Second+'.'+Milisecond AS DATETIME), curItem
FROM #mytable
),
cteEnumerateGroups AS
(
SELECT DT, curItem, MyGroup = ROW_NUMBER() OVER (ORDER BY DT, curItem)-
ROW_NUMBER() OVER (PARTITION BY curItem ORDER BY DT, curItem)
FROM cteSimplifyDateTime
)
SELECT DT, curItem, ConsecutiveCount = ROW_NUMBER() OVER (PARTITION BY MyGroup ORDER BY DT, curItem)
FROM cteEnumerateGroups
ORDER BY DT, curItem, ConsecutiveCount
June 22, 2013 at 12:59 pm
Does this do the trick?
;WITH
cteSimplifyDateTime AS
(
SELECT DT = CAST(Year+Month+Day+' '+Hour+':'+Minute+':'+Second+'.'+Milisecond AS DATETIME), curItem
FROM #mytable
),
cteEnumerateGroups AS
(
SELECT DT, curItem, MyGroup = ROW_NUMBER() OVER (ORDER BY DT, curItem)-
ROW_NUMBER() OVER (PARTITION BY curItem ORDER BY DT, curItem)
FROM cteSimplifyDateTime
)
SELECT DT, curItem, ConsecutiveCount = ROW_NUMBER() OVER (PARTITION BY curItem, MyGroup ORDER BY MyGroup, DT, curItem)
FROM cteEnumerateGroups
ORDER BY DT, curItem, ConsecutiveCount
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2013 at 2:14 pm
Kevin,
Awsome! Thank you for the solution.
Mike
June 23, 2013 at 8:55 am
mcertini (6/22/2013)
Kevin,Awsome! Thank you for the solution.
Mike
Glad I could help! I got to stand on the shoulders of a TSQL Giant (hey Jeff!)! 🙂
BTW, please do use the numeric field from your first data-containing post if it exists. No sense in paying the cost to split and then recombine all those fields.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 23, 2013 at 3:39 pm
TheSQLGuru (6/23/2013)
I got to stand on the shoulders of a TSQL Giant (hey Jeff!)! 🙂
So that's what it was... I had a giant standing on my shoulders. 🙂
No pun intended but I had that sinking feeling that I was forgetting something. I didn't have the time to gen a wad o' test data like I normally do, though. Thank you VERY much for the cover, Kevin.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply