January 30, 2009 at 12:01 am
hi, i am having table in which data is coming in below format
Tid datetime
103/12/2006 11:56
103/12/2006 11:59
103/12/2006 11:45
103/12/2006 12:30
103/12/2006 14:00
203/12/2006 08:30
203/12/2006 10:00
and i want to insert the data in another table as below mentioned table i.e 1st transaction for that date will be inserted in trans1 and second transaction is greater than 1st but less than any other transaction will be inserted in trans2 and along with that transdate and totaltrans will be the difference in tran2 and tran1 in minutes.
Tidtrans1 trans2 Transdatetotaltrans
1 03/12/2006 11:45 03/12/2006 11:56 12/03/060:11
1 03/12/2006 11:59 03/12/2006 12:30 12/03/060:31
1 03/12/2006 14:00 NULL 12/03/06NULL
2 03/12/2006 08:30 03/12/2006 10:00 12/03/061:30
January 30, 2009 at 1:25 am
Hello,
from your post I understood that if date changes, you start from the beginning - meaning that if transaction starts before midnight and ends on the next day, it should be considered as separate entries - not one row.
I'm not sure whether this is the best solution, and you'll have to test it in your environment for performance, but as far as I can tell, it works as required.
The solution has 3 logical steps, that I have preserved in the query to make it more readable.
First : number the rows for each tid and day using ROW_NUMBER function (query Q)
Second : from these row numbers, mark odd and even rows (to know into which column the datetime value belongs), and number each pair of rows to be merged into one result row (query Y)
Third : create the resultset
/*create test data*/
CREATE TABLE #tran(tid INT, tdate DATETIME)
INSERT INTO #tran(tid, tdate)
SELECT 1, '20061203 11:56'
UNION SELECT 1, '20061203 11:59'
UNION SELECT 1, '20061203 11:45'
UNION SELECT 1, '20061203 12:30'
UNION SELECT 1, '20061203 14:00'
UNION SELECT 2, '20061203 08:30'
UNION SELECT 2, '20061203 10:00'
UNION SELECT 3, '20061204 23:56'
UNION SELECT 3, '20061205 00:10'
/*produce the result*/
SELECTY.tid,
MAX(CASE WHEN Y.col = 1 THEN Y.tdate ELSE NULL END) as trans1,
MAX(CASE WHEN Y.col = 2 THEN Y.tdate ELSE NULL END) as trans2,
Y.tday as transdate,
CONVERT(VARCHAR(5), MAX(CASE WHEN Y.col = 2 THEN Y.tdate ELSE NULL END) - MAX(CASE WHEN Y.col = 1 THEN Y.tdate ELSE NULL END), 8)
FROM
(SELECT Q.tid, Q.tdate, Q.torder,
DATEADD(d, DATEDIFF(d, 0, Q.tdate), 0) as tday,
CASE WHEN Q.torder%2 = 1 THEN 1 ELSE 2 END as col,
(Q.torder+1)/2 as resultrow
FROM
(SELECTtid, tdate,
ROW_NUMBER() OVER(PARTITION BY tid, DATEADD(d, DATEDIFF(d, 0, tdate), 0) ORDER BY tdate) as torder
FROM #tran) as Q
) as Y
GROUP BY Y.resultrow, Y.tid, Y.tday
ORDER BY Y.tid, trans1
/*cleanup*/
DROP TABLE #tran
Please, make helping you easier next time by posting CREATE TABLE and INSERT INTO statements (as above) next time. It save lots of time, and ensures that all people work with the same structure and data. Thanks.
PS: Sorry for no comments in the SQL itself, but I really didn't have more time.... hope you'll understand what I'm doing there.
January 30, 2009 at 4:50 am
no it wont work each time new record gets added which wont solve the problem.
i will expalin again i had prepared trigger on table TRAN_TABLE which is having column Tid and Tdatetime which will execute the stored procedure by taking Tid and Tdatetime from inserted as variable and the result will be insert in another table which is having clomn id,trans1,trans2,transdate by doing the rest calculation as mentioned. but before prcessing it will check for existing record for same id and same date if the rcord already exist i.e tran1 and tran2 than it will insert new record.
January 30, 2009 at 4:57 am
Please show me why it won't work - find some example where my code produces an error and explain why that isn't correct. Also please explain a bit about the circumstances - why do you need to insert those rows into another table, when do you need to do that, how many rows in the tables and how often new rows are added. Maybe we can find some better solution - what I posted was beased on the very limited info in your first post.
January 30, 2009 at 5:04 am
sandy (1/30/2009)
i had prepared trigger on table TRAN_TABLE which is having column Tid and Tdatetime which will execute the stored procedure
Better think again about that. Trigger that calls a stored procedure is a VERY bad option for performance. If you need to use a trigger, the code should be inside the trigger, and work with multiple rows - otherwise you are enforcing row-by-row processing.
I think I begin to understand your reply... what you wanted to say is that my solution won't work, because you want to run the code every time something is inserted into the original table, and that would require checking whether to insert or update. Is that what you were trying to tell?
January 30, 2009 at 5:19 am
exactly Vladan,
thats what i need,the basic idea to do that is to trace the transactionand count for how many hrs the actual transaction was there
i.e suppose i had 1st transaction at 08:00(trans1) for todays date and other trnsaction is on 08:10(trans2) so the difeerence is of 10mins and now again i had done at 10:00(trans1) and after that 10:45(trans2) and differnce is off 45mins,again i had done transaction 14:25 but that has been not reflecting due to some network reason and again i had transaction at 15:00 but later on transaction which i had at 14:25 had dowloaded so that senario alos 14:25(trans1) and 15:00(trans2) and difference is 35 mins the total excat transtion duration will of 01:30 hrs for that id on same date.i think i might have cleared my idea.
January 30, 2009 at 5:57 am
Ok, now it seems to be clear. Well, but it also seems that trigger will not work as you wish - trigger will fire on insert. If you have situations where some of the times can be missing and appear in the table later (order of insert can be different than the real order of events), then the trigger would have to find previous row, reopen it, close it with the correct time and insert the time that originally was used as tran2 into tran1 of a new row.
Trigger would be useful if order of inserts is always correct - but you say some rows can be delayed.
For this situation, I would suggest using dynamic dataset (view based e.g. on the code I posted) instead of a table. Is there any reason why the data must be in a table?
There could also be a solution in using the view for data from the current day only (or week, or month..), and store the previous, unchanging days in your permanent table.
That would mean adding a time condition when selecting form the view, and inserting rows into the table using job that runs every 24 hours (or, of course, you could decide to archive the data once a week or whatever seems best).
January 30, 2009 at 10:17 pm
Yes it is neccessary to insert into another table because based on that many more further processes & calculation will be done and i will be using into my application for pulling the various types of report. can pls help me.
January 31, 2009 at 7:47 pm
Vladan, check your code... I don't believe it's coming up with the correct result set. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2009 at 8:09 pm
sandy (1/30/2009)
Yes it is neccessary to insert into another table because based on that many more further processes & calculation will be done and i will be using into my application for pulling the various types of report. can pls help me.
Insert what? Don't you mean "insert any new rows and update any existing rows"?
Vladan asked you before... please post the CREATE TABLE statements for both the source data and the target table. This isn't a difficult problem, but you have to help us help you. 😉
For future posts, please follow the instructions in the link in my signature below.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2009 at 1:31 am
Jeff Moden (1/31/2009)
Vladan, check your code... I don't believe it's coming up with the correct result set.
I checked it and either I'm blind (quite possible, I'm still only half awake) or we understand the requirements differently. What do you think is wrong?
The fact that ID 3 is treated as 2 "starts" is on purpose - I don't know why they want it, but that's how I understood the question. I agree absolutely that it is logically incorrect, but it is what was required and I mentioned it in the post with code.
Or do I have something in the cose that works only on my system because of some setting and produces different result on yours?
tid trans1 trans2 transdate
----------- ----------------------- ----------------------- ----------------------- -----
1 2006-12-03 11:45:00.000 2006-12-03 11:56:00.000 2006-12-03 00:00:00.000 00:11
1 2006-12-03 11:59:00.000 2006-12-03 12:30:00.000 2006-12-03 00:00:00.000 00:31
1 2006-12-03 14:00:00.000 NULL 2006-12-03 00:00:00.000 NULL
2 2006-12-03 08:30:00.000 2006-12-03 10:00:00.000 2006-12-03 00:00:00.000 01:30
3 2006-12-04 23:56:00.000 NULL 2006-12-04 00:00:00.000 NULL
3 2006-12-05 00:10:00.000 NULL 2006-12-05 00:00:00.000 NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
(6 row(s) affected)
February 2, 2009 at 3:43 am
Hi Vladan,
When i run this code is sql 2000 i get an error of
Server: Msg 195, Level 15, State 10, Line 27
'ROW_NUMBER' is not a recognized function name. but it runs perfectly in sql 2005 is there any way i can run it sql 2000
/*create test data*/
CREATE TABLE #tran(tid INT, tdate DATETIME)
INSERT INTO #tran(tid, tdate)
SELECT 1, '20061203 11:56'
UNION SELECT 1, '20061203 11:59'
UNION SELECT 1, '20061203 11:45'
UNION SELECT 1, '20061203 12:30'
UNION SELECT 1, '20061203 14:00'
UNION SELECT 2, '20061203 08:30'
UNION SELECT 2, '20061203 10:00'
UNION SELECT 3, '20061204 23:56'
UNION SELECT 3, '20061205 00:10'
/*produce the result*/
SELECT Y.tid,
MAX(CASE WHEN Y.col = 1 THEN Y.tdate ELSE NULL END) as trans1,
MAX(CASE WHEN Y.col = 2 THEN Y.tdate ELSE NULL END) as trans2,
Y.tday as transdate,
CONVERT(VARCHAR(5), MAX(CASE WHEN Y.col = 2 THEN Y.tdate ELSE NULL END) - MAX(CASE WHEN Y.col = 1 THEN Y.tdate ELSE NULL END), 8)
FROM
( SELECT Q.tid, Q.tdate, Q.torder,
DATEADD(d, DATEDIFF(d, 0, Q.tdate), 0) as tday,
CASE WHEN Q.torder%2 = 1 THEN 1 ELSE 2 END as col,
(Q.torder+1)/2 as resultrow
FROM
(SELECT tid, tdate,
ROW_NUMBER() OVER(PARTITION BY tid, DATEADD(d, DATEDIFF(d, 0, tdate), 0) ORDER BY tdate) as torder
FROM #tran) as Q
) as Y
GROUP BY Y.resultrow, Y.tid, Y.tday
ORDER BY Y.tid, trans1
/*cleanup*/
DROP TABLE #tran
February 2, 2009 at 4:08 am
Vladan (2/2/2009)
Jeff Moden (1/31/2009)
Vladan, check your code... I don't believe it's coming up with the correct result set.I checked it and either I'm blind (quite possible, I'm still only half awake) or we understand the requirements differently. What do you think is wrong?
The fact that ID 3 is treated as 2 "starts" is on purpose - I don't know why they want it, but that's how I understood the question. I agree absolutely that it is logically incorrect, but it is what was required and I mentioned it in the post with code.
Or do I have something in the cose that works only on my system because of some setting and produces different result on yours?
tid trans1 trans2 transdate
----------- ----------------------- ----------------------- ----------------------- -----
1 2006-12-03 11:45:00.000 2006-12-03 11:56:00.000 2006-12-03 00:00:00.000 00:11
1 2006-12-03 11:59:00.000 2006-12-03 12:30:00.000 2006-12-03 00:00:00.000 00:31
1 2006-12-03 14:00:00.000 NULL 2006-12-03 00:00:00.000 NULL
2 2006-12-03 08:30:00.000 2006-12-03 10:00:00.000 2006-12-03 00:00:00.000 01:30
3 2006-12-04 23:56:00.000 NULL 2006-12-04 00:00:00.000 NULL
3 2006-12-05 00:10:00.000 NULL 2006-12-05 00:00:00.000 NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
(6 row(s) affected)
Nah... Maybe it's just an interpretation on my part, but you do a split at midnight and I believe you only need to split by transaction regardless of midnight to match the op's requirements in the original post.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2009 at 4:13 am
It's kind of a moot point now because the op said they're using SQL Server 2000, but here's the 2005 rendition I came up with. The output is the same as what the op requested in the first post.
CREATE TABLE #tran(tid INT, tdate DATETIME)
INSERT INTO #tran(tid, tdate)
SELECT 1, '20061203 11:56'
UNION SELECT 1, '20061203 11:59'
UNION SELECT 1, '20061203 11:45'
UNION SELECT 1, '20061203 12:30'
UNION SELECT 1, '20061203 14:00'
UNION SELECT 2, '20061203 08:30'
UNION SELECT 2, '20061203 10:00'
UNION SELECT 3, '20061204 23:56'
UNION SELECT 3, '20061205 00:10'
;WITH cteRowNum AS
(
SELECT Tid,TDate,
ROW_NUMBER() OVER (PARTITION BY Tid ORDER BY TDate)-1 AS RowNum
FROM #Tran
)
,
ctePair AS
(
SELECT Tid,TDate,RowNum,RowNum/2 AS Pair
FROM cteRowNum
)
,
ctePaired AS
(
SELECT Tid,
MAX(CASE WHEN RowNum%2=0 THEN TDate END) AS Trans1,
MAX(CASE WHEN RowNum%2=1 THEN TDate END) AS Trans2,
MIN(CONVERT(CHAR(8),TDate,1)) AS TransDate
FROM ctePair
GROUP BY Tid, Pair
)
SELECT Tid,Trans1,Trans2,TransDate,CONVERT(CHAR(5),Trans2-Trans1,108) AS TotalTrans
FROM ctePaired
ORDER BY TID,Trans1
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2009 at 4:17 am
sandy (2/2/2009)
When i run this code is sql 2000 i get an error ofServer: Msg 195, Level 15, State 10, Line 27
'ROW_NUMBER' is not a recognized function name. but it runs perfectly in sql 2005 is there any way i can run it sql 2000
Ok... looks like I was wrong about Vladan's code v.s. the Ops request. My applogies.
And, although this is an honest mistake, it's still bloody annoying when someone wants help and doesn't tell you it has to work on both versions from the git.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply