June 11, 2013 at 11:18 am
I am trying to extract data from multiple table joins into a temporary table, so that I will be able to count Occupancy and Vacancy parking transactions based on starttime.
The problem I am having is the sequence of occurrences for each transaction in not accurate in the database. My solution is to create a temporary table and generate my own sequence numbers. I did generate my own sequence numbers, however the numbers are not in sequence and I am also getting duplicates.
See sample code below as well as data output (Excel Spreadsheet attached) with inconsistent sequence numbers. If anyone knows how to fix this issue, please provide an example. As you can see, the SN numbers keep starting over at 1 as soon as it gets to a certain point.
Any help would be greatly appreciated.
SAMPLE CODE
IF OBJECT_ID ('tempdb..#Tmp1') IS NOT NULL DROP TABLE #Tmp1 --all transactions + SN
CREATE TABLE #Tmp1(
[SN] [int] ,
[ParkingSpaceId] [int],
[MeterId] [int],
[BlockFaceID] [int],
[occupancystatus] [int],
[StartTime_PT] [datetime],
[LastSensorEvent_PT] [datetime],
[State] [nvarchar](50)
)
insert #Tmp1
SELECT ROW_NUMBER() OVER (PARTITION BY ST.ParkingSpaceId ORDER BY StartTime) AS SN,
ST.ParkingSpaceId,
MeterId,
[BlockFaceID] ,
[occupancystatus],
st.StartTime as StartTime_PT,
PS.LastSensorEvent as [LastSensorEvent_PT],
ST.[State]
FROM SensorTransactions ST
join ParkingSpaces ps on ps.ParkingSpaceId=ST.ParkingSpaceId
WHERE BlockfaceId = 996
AND dateadd(HH,-7,Starttime) between '2013-05-28 08:00:00.000' and '2013-05-28 20:00:00.000'
AND state in('Occupied','vacant')
ORDER by starttime
select * from #Tmp1
OUTPUT - Excel Spreadsheet is also attached.
SNParkingSpaceIdMeterIdBlockFaceIDoccupancystatusStartTime_PTLastSensorEvent_PTState
16366577599615/28/13 10:216/10/13 19:57OCCUPIED
26366577599615/28/13 10:226/10/13 19:57VACANT
36366577599615/28/13 10:516/10/13 19:57OCCUPIED
16367577699615/28/13 8:446/10/13 16:44OCCUPIED
26367577699615/28/13 8:506/10/13 16:44VACANT
36367577699615/28/13 11:016/10/13 16:44OCCUPIED
16370577999615/28/13 8:126/10/13 19:37OCCUPIED
26370577999615/28/13 8:146/10/13 19:37VACANT
36370577999615/28/13 9:456/10/13 19:37OCCUPIED
46370577999615/28/13 9:456/10/13 19:37VACANT
56370577999615/28/13 9:566/10/13 19:37OCCUPIED
16371578099615/28/13 8:196/10/13 16:39OCCUPIED
26371578099615/28/13 17:596/10/13 16:39VACANT
36371578099615/28/13 18:026/10/13 16:39OCCUPIED
16372578199615/28/13 10:386/10/13 19:21VACANT
26372578199615/28/13 10:546/10/13 19:21OCCUPIED
36372578199615/28/13 12:536/10/13 19:21UNKNOWN
46372578199615/28/13 13:196/10/13 19:21OCCUPIED
16373578299615/28/13 8:276/10/13 19:31OCCUPIED
26373578299615/28/13 8:306/10/13 19:31VACANT
36373578299615/28/13 8:546/10/13 19:31OCCUPIED
June 11, 2013 at 12:06 pm
You should read up on how windowed functions work. You have PARTITION BY in your ROW_NUMBER.
insert #Tmp1
SELECT ROW_NUMBER() OVER (PARTITION BY ST.ParkingSpaceId ORDER BY StartTime) AS SN,
When you partition the ROW_NUMBER windowed function it will reset when the value of ST.ParkingSpaceId changes. In other words you will get duplicates. Either remove the partition by OR use an identity in your temp table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2013 at 1:18 pm
Sean,
I removed the partition and it worked. I will definitely take a look at this article.
Thanks so much for your help.
June 11, 2013 at 1:31 pm
Sean,
I removed the partition and it worked - see revised table below:
IF OBJECT_ID ('tempdb..#Tmp1') IS NOT NULL DROP TABLE #Tmp1 --all transactions + SN
CREATE TABLE #Tmp1(
[SN] [int] identity ,
[ParkingSpaceId] [int],
[MeterId] [int],
[BlockFaceID] [int],
[occupancystatus] [int],
[StartTime_PT] [datetime],
[LastSensorEvent_PT] [datetime],
[State] [nvarchar](50)
)
insert #Tmp1
SELECT
ST.ParkingSpaceId,
MeterId,
[BlockFaceID] ,
[occupancystatus],
st.StartTime as StartTime_PT,
PS.LastSensorEvent as [LastSensorEvent_PT],
ST.[State]
FROM SensorTransactions ST
join ParkingSpaces ps on ps.ParkingSpaceId=ST.ParkingSpaceId
WHERE BlockfaceId = 996
AND dateadd(HH,-7,Starttime) between '2013-05-28 08:00:00.000' and '2013-05-28 20:00:00.000'
AND state in('Occupied','vacant')
ORDER by starttime
select * from #Tmp1
Thanks so much for your help.
June 11, 2013 at 1:39 pm
Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply