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.
Author |
Topic |
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-02-27 : 23:50:16
|
I have the following 3 tablesSelect * from #iID EventID EventExtraDataID Path FileName CameraNumber CameraName1 1014318 1091745 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg 0001014318_0_1.jpg 1 TV CH 12 1014318 1091746 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg 0001014318_0_2.jpg 1 TV CH 13 1014318 1091747 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg 0001014318_0_3.jpg 1 TV CH 14 1014318 1091754 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg 0001014318_1_1.jpg 2 Cam 25 1014318 1091755 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg 0001014318_1_2.jpg 2 Cam 26 1014318 1091756 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpg 0001014318_1_3.jpg 2 Cam 2Select * from #ibID FileName Image EventID EventExtraDataID CameraNumber1 0001014318_0_1.jpg 0xFFD 1014318 1091745 12 0001014318_0_2.jpg 0xFFD 1014318 1091746 13 0001014318_0_3.jpg 0xFFD 1014318 1091747 14 0001014318_1_1.jpg 0xFFD 1014318 1091754 25 0001014318_1_2.jpg 0xFFD 1014318 1091755 26 0001014318_1_3.jpg 0xFFD 1014318 1091756 2Select * from #tSiteID SiteName EventID EventType Description Cause SourceID SourceType LocalStartTime EventStartTime LocalEndTime EventEndTime Indicator ZoneNumber EventCode ZoneText DetectorText RID Media Format YearPath MonthPath DayPath EventExtraDataID FileName Type Format StartTime EndTime CameraNumber CameraName LoginName Comments CommentedTime21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091745 0001014318_0_1.jpg Image HYIMAGES 2013-02-22 09:50:45.000 2013-02-22 09:50:45.000 1 TV CH 1 NULL NULL NULL21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091746 0001014318_0_2.jpg Image HYIMAGES 2013-02-22 09:50:50.000 2013-02-22 09:50:50.000 1 TV CH 1 NULL NULL NULL21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091747 0001014318_0_3.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 1 TV CH 1 NULL NULL NULL21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091754 0001014318_1_1.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 2 Cam 2 NULL NULL NULL21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091755 0001014318_1_2.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 2 Cam 2 NULL NULL NULL21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091756 0001014318_1_3.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 2 Cam 2 NULL NULL NULLI am looking for the following result. Basically there is only ever one event (EventID) but the event has two associated cameras in this example (there could be upto a max of 16 cameras, so in this case there would be 48 images, 16x3=48) and each camera has 3 images. I need the 3 images (image, filename and path fields) for each CameraNumber to be outer applied or something. The last 9 fields are the ones that need to be joined.EventExtraDataID EventID FileName CameraNumber CameraName SiteID SiteName EventType Description Cause SourceID SourceType LocalStartTime EventStartTime LocalEndTime EventEndTime Indicator ZoneNumber EventCode ZoneText DetectorText RID Media Format YearPath MonthPath DayPath Type Format StartTime EndTime LoginName Comments CommentedTime FileName1 Image1 FileName2 Image2 FileName3 Image3 Path1 Path2 Path31091745 1014318 0001014318_0_1.jpg 1 TV CH 1 21 Delta CAM01 MOTION1 Quad Alarm CAM01 MOTION1 1 Cameras 2013-02-22 09:36:39 2013-02-22 09:50:45 2013-02-22 09:36:39 2013-02-22 09:50:45 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 Image HYIMAGES 2013-02-22 09:50:45 2013-02-22 09:50:45 NULL NULL NULL 0001014318_0_1.jpg 0xFFD8 0001014318_0_2.jpg 0xFFD8 0001014318_0_3.jpg 0xFFD8 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg1091747 1014318 0001014318_1_1.jpg 2 Cam 2 21 Delta CAM01 MOTION1 Quad Alarm CAM01 MOTION1 1 Cameras 2013-02-22 09:36:39 2013-02-22 09:50:45 2013-02-22 09:36:39 2013-02-22 09:50:45 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 Image HYIMAGES 2013-02-22 09:50:45 2013-02-22 09:50:45 NULL NULL NULL 0001014318_1_1.jpg 0xFFD8 0001014318_1_2.jpg 0xFFD8 0001014318_1_3.jpg 0xFFD8 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpgI have the following code that worked prior when there was only ever one camera and therefore 3 images. Can this be modified to provide the 2 required rows?Select DISTINCT #t.EventExtraDataID, #t.EventID, #t.FileName, #t.[CameraNumber], #t.[CameraName], [SiteID], [SiteName], dbo.EventTypeConv ([EventType]) As [EventType], [Description], [Cause], [SourceID], dbo.SourceTypeConv ([SourceType]) As [SourceType], CONVERT(VARCHAR(19), [LocalStartTime], 120) AS [LocalStartTime], CONVERT(VARCHAR(19), [EventStartTime], 120) AS [EventStartTime], CONVERT(VARCHAR(19), [LocalEndTime], 120) AS [LocalEndTime], CONVERT(VARCHAR(19), [EventEndTime], 120) AS [EventEndTime], [Indicator], [ZoneNumber], [EventCode], [ZoneText], [DetectorText], [RID], [Media Format], [YearPath], [MonthPath], [DayPath], [Type], [Format], CONVERT(VARCHAR(19), [StartTime], 120) AS [StartTime], CONVERT(VARCHAR(19), [EndTime], 120) AS [EndTime], [LoginName], [Comments], CONVERT(VARCHAR(19), [CommentedTime], 120) AS [CommentedTime],i1.FileName AS FileName1, i1.Image AS Image1,i2.FileName AS FileName2, i2.Image AS Image2,i3.FileName AS FileName3, i3.Image AS Image3,i4.Path As Path1,i5.Path As Path2,i6.Path As Path3From #t LEFT JOIN#ib AS i1 ON #t.FileName = i1.FileNameOUTER APPLY (Select FileName, Image From #ib Where ID = 2) AS i2OUTER APPLY (Select FileName, Image From #ib Where ID = 3) AS i3OUTER APPLY (Select Path From #i Where ID = 1) AS i4OUTER APPLY (Select Path From #i Where ID = 2) AS i5OUTER APPLY (Select Path From #i Where ID = 3) AS i6Where ID = 1ORDER BY #t.EventID |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 00:23:48
|
post the sample data between code tags and properly formatted. also provide it as insert statements so that its easier for us to create it and see for ourselves. then give sample output you desire.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-02-28 : 01:38:20
|
This case has 2 cameras associated with the event and each camera will always have 3 images.The number of cameras associated with an event could be upto 16. In this case there would be 16 rows required (one for each camera) each with the 3 FileName, Image and Path fields. CREATE TABLE #i (ID int IDENTITY(1,1) NOT NULL, [EventID] int, [EventExtraDataID] int, [Path] nvarchar(255), [FileName] nvarchar(max), [CameraNumber] int, [CameraName] nvarchar(max))INSERT INTO #i ([EventID], [EventExtraDataID], [Path], [FileName], [CameraNumber], [CameraName])Values('1014318', '1091745', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg', '0001014318_0_1.jpg', '0', 'TV CH 1'),('1014318', '1091746', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg', '0001014318_0_2.jpg', '0', 'TV CH 1'),('1014318', '1091747', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg', '0001014318_0_3.jpg', '0', 'TV CH 1'),('1014318', '1091754', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg', '0001014318_1_1.jpg', '1', 'Cam 2'),('1014318', '1091755', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg', '0001014318_0_2.jpg', '1', 'Cam 2'),('1014318', '1091756', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpg', '0001014318_0_3.jpg', '1', 'Cam 2')CREATE TABLE #ib (ID int IDENTITY(1,1) NOT NULL, [FileName] nvarchar(255) NOT NULL, [Image] nvarchar(255), [EventID] int, [EventExtraDataID] int)INSERT INTO #ib ([FileName], [Image], [EventID], [EventExtraDataID])Values('0001014318_0_1.jpg', '0xFFD', '1014318', '1091745'),('0001014318_0_2.jpg', '0xFFD', '1014318', '1091746'),('0001014318_0_3.jpg', '0xFFD', '1014318', '1091747'),('0001014318_1_1.jpg', '0xFFD', '1014318', '1091754'),('0001014318_1_2.jpg', '0xFFD', '1014318', '1091755'),('0001014318_1_3.jpg', '0xFFD', '1014318', '1091756')Create Table #t (SiteName nvarchar(50), EventType nvarchar(50), EventID int, EventExtraDataID int, [FileName] nvarchar(50), CameraNumber int, CameraName nvarchar(50))INSERT INTO #t ([SiteName], [EventType], [EventID], [EventExtraDataID], [FileName], [CameraNumber], [CameraName])Values('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091745', '0001014318_0_1.jpg', '0', 'TV CH 1'),('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091746', '0001014318_0_2.jpg', '0', 'TV CH 1'),('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091747', '0001014318_0_3.jpg', '0', 'TV CH 1'),('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091754', '0001014318_1_1.jpg', '1', 'Cam 2'),('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091755', '0001014318_1_2.jpg', '1', 'Cam 2'),('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091756', '0001014318_1_3.jpg', '1', 'Cam 2')--Expected Result *** Note Image is normally varbinary(max) and I reduce the fields in #t to make it a little cleaner.***Create Table #Expected_Result (SiteName nvarchar(50), EventType nvarchar(50), EventID int, CameraNumber int, CameraName nvarchar(50), FileName1 nvarchar(255), Image1 nvarchar(255), Path1 nvarchar(255), FileName2 nvarchar(255), Image2 nvarchar(255), Path2 nvarchar(255), FileName3 nvarchar(255), Image3 nvarchar(255), Path3 nvarchar(255))INSERT INTO #Expected_ResultValues('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '0', 'TV CH 1', '0001014318_0_1.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg', '0001014318_0_2.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg', '0001014318_0_3.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg'),('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1', 'Cam 2', '0001014318_1_1.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg', '0001014318_1_2.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg', '0001014318_1_3.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpg')Select * From #iSelect * From #ibSelect * From #tSelect * From #Expected_ResultDrop Table #iDrop Table #ibDrop Table #tDrop Table #Expected_Result |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 23:31:21
|
[code]select SiteName,EventType,EventID,CameraNumber,CameraName,MAX(CASE WHEN Seq=1 THEN FileName END) AS Filename1,MAX(CASE WHEN Seq=1 THEN Image END) AS Image1,MAX(CASE WHEN Seq=1 THEN Path END) AS Path1,MAX(CASE WHEN Seq=2 THEN FileName END) AS Filename2,MAX(CASE WHEN Seq=2 THEN Image END) AS Image2,MAX(CASE WHEN Seq=2 THEN Path END) AS Path2,MAX(CASE WHEN Seq=3 THEN FileName END) AS Filename3,MAX(CASE WHEN Seq=3 THEN Image END) AS Image3,MAX(CASE WHEN Seq=3 THEN Path END) AS Path3from(select ROW_NUMBER() OVER (PARTITION BY i.EventID,i.CameraNumber,i.CameraName ORDER BY i.EventExtraDataID) AS Seq,SiteName,EventType,i.EventID,i.CameraNumber,i.CameraName,ib.[FileName],ib.Image,i.Pathfrom #i iinner join #ib ibON ib.EventID = i.EventIDand ib.EventExtraDataID = i.EventExtraDataIDinner join #t ton t.EventExtraDataID = i.EventExtraDataIDand t.EventID = i.EventID)tgroup by SiteName,EventType,EventID,CameraNumber,CameraName[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-03-03 : 18:28:01
|
Thanks visakh16. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-03 : 22:25:07
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|