June 28, 2012 at 12:08 pm
Hi Friends,
let me explain with example..
i have a table having three columns called
ID Unit SysCC
100 38142 1
100 65982 6
100 46413 8
100 64685 9
101 46551 3
101 64651 8
102 46465 2
102 56465 9
what i need to do is..i have select the records based on SysCC column.........
for example if i select records having ID = 100 , it must show all the 3 records except SysCC having value 9...if SysCC has value 8 then ID = 100 must not show the record where SysCC having 9..if 8 is not there, then it must show 9 with the other two records...
how can i achive this?
help me friends...
please let me know if i am not clear...
Thanks,
Charmer
June 28, 2012 at 12:19 pm
not sure if you are looking some generic or just something along the lines of
select *
from table
where ID = 100
and ( SysCC != 9 OR NOT EXISTS ( SELECT 'x'
From table2
where table2.ID=100 and table2.SysCC=8)
(you might want to double-check my logic 🙂 )
June 28, 2012 at 12:47 pm
what's the logic? From what you are telling me you are saying this:
If sysccc = 8 is not there, show syscc = 9
Else do not show record where syscc = 9
Something tells me it is more involved than that. Please state the actual logic, and THEN follow up with an example.
Jared
CE - Microsoft
June 28, 2012 at 12:50 pm
Do you intend to show the first 3 values on SysCC for every ID?
June 28, 2012 at 12:56 pm
Will something like this apply? (I'm learning how to use CROSS APPLY :blush: )
SELECT DISTINCT y.*
FROM Table x
CROSS APPLY (SELECT TOP 3 * FROM Table y WHERE x.id = y.id) y
June 28, 2012 at 12:59 pm
Luis Cazares (6/28/2012)
Will something like this apply? (I'm learning how to use CROSS APPLY :blush: )
SELECT DISTINCT y.*FROM Table x
CROSS APPLY (SELECT TOP 3 * FROM Table y WHERE x.id = y.id) y
How do you know what TOP 3 refers to on the last query without an ORDER BY? 🙂 The answer is that it could be any 3 rows with the matching id. Better put an ORDER BY in there...
Jared
CE - Microsoft
June 28, 2012 at 1:01 pm
Also, Charmer, you have been around these forums long enough to know how to post DDL, sample data, and expected results. You really need to start putting more effort into your questions or you may find less effort in answering them.
Please take a close look at how I put together the following. It looks like David posted an aswer that meets your initial requirements.
create table #TestData (
ID int,
Unit int,
SysCC int
);
go
insert into #TestData(ID, Unit, SysCC)
values(100,38142,1),
(100,65982,6),
(100,46413,8),
(100,64685,9),
(101,46551,3),
(101,64651,8),
(102,46465,2),
(102,56465,9);
go
select
td.ID,
td.Unit,
td.SysCC
from
#TestData td
where
td.SysCC <> 9
or not exists(select
1
from
#TestData td1
where
td1.ID = td.ID
and td1.SysCC = 8);
go
drop table #TestData;
go
June 28, 2012 at 1:06 pm
SQLKnowItAll (6/28/2012)
Luis Cazares (6/28/2012)
Will something like this apply? (I'm learning how to use CROSS APPLY :blush: )
SELECT DISTINCT y.*FROM Table x
CROSS APPLY (SELECT TOP 3 * FROM Table y WHERE x.id = y.id) y
How do you know what TOP 3 refers to on the last query without an ORDER BY? 🙂 The answer is that it could be any 3 rows with the matching id. Better put an ORDER BY in there...
You're right, I just gave in a solution without thinking too much. However, I'm sure he still has to work with it to make it work for the real deal.
June 29, 2012 at 2:38 am
Sorry Friends, i was not able to post my DDL and DML last night....I am sorry Lynn.....
USE [MyDB]
GO
/****** Object: Table [dbo].[CADInc] Script Date: 6/29/2012 2:04:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CADInc](
[CADNo] [bigint] NULL,
[RptDttm] [datetime] NULL,
[District] [char](4) NULL,
[IncidentID] [bigint] NOT NULL,
[ProductID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [MyDB]
GO
/****** Object: Table [dbo].[CADUnitHist] Script Date: 6/29/2012 2:04:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CADUnitHist](
[UserCC] [char](2) NULL,
[ActDttm] [datetime] NULL,
[SysCC] [smallint] NULL,
[IncidentID] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000058, CAST(0x00009C0300B78525 AS DateTime), NULL, 100040000685027, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000023, CAST(0x00009C0300B78525 AS DateTime), NULL, 100040000685030, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000059, CAST(0x00009C0300BEBCB8 AS DateTime), NULL, 100040000685123, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000024, CAST(0x00009C0300BEBCB8 AS DateTime), NULL, 100040000685126, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000025, CAST(0x00009C0300BF98C6 AS DateTime), NULL, 100040000685158, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000060, CAST(0x00009C0300C2D86C AS DateTime), NULL, 100040000685249, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000026, CAST(0x00009C0300C2D86C AS DateTime), N'1 ', 100040000685251, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000061, CAST(0x00009C0300C3A37C AS DateTime), NULL, 100040000685270, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000064, CAST(0x00009C0600B53458 AS DateTime), NULL, 100040000687366, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000065, CAST(0x00009C0600B5EA96 AS DateTime), NULL, 100040000687375, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000066, CAST(0x00009C0600BC98AF AS DateTime), NULL, 100040000687580, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000067, CAST(0x00009C0600BD972A AS DateTime), NULL, 100040000687643, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000068, CAST(0x00009C3000A41E8A AS DateTime), NULL, 100040000689600, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000027, CAST(0x00009C3000A41E8A AS DateTime), NULL, 100040000689603, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000028, CAST(0x00009C3000A48C17 AS DateTime), N'1 ', 100040000689620, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000069, CAST(0x00009C3000A64573 AS DateTime), NULL, 100040000689683, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000070, CAST(0x00009C3000E0E730 AS DateTime), NULL, 100040000690311, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000029, CAST(0x00009C3000E0E730 AS DateTime), N'1 ', 100040000690314, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000071, CAST(0x00009C4800F6A528 AS DateTime), NULL, 100040000693374, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000080, CAST(0x00009C4C00B6A7E6 AS DateTime), NULL, 100040000694835, 1)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D4B6B AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D4B6B AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D4B6F AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D58D2 AS DateTime), 105, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D6765 AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D676A AS DateTime), 9, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D8EC8 AS DateTime), 105, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D9604 AS DateTime), 9, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010DB812 AS DateTime), 105, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010DB817 AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OD', CAST(0x000098430110A5FB AS DateTime), 1, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OD', CAST(0x000098430111300B AS DateTime), 1, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009843011A1B8D AS DateTime), 3, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009843011A1B8D AS DateTime), 3, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009843011AC8D3 AS DateTime), 6, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x00009843012EA7AD AS DateTime), 71, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'CT', CAST(0x00009843012F5D9C AS DateTime), 9, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x00009843012F8A78 AS DateTime), 71, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x00009843012F92AC AS DateTime), 71, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x000098430130142A AS DateTime), 8, NULL)
GO
SELECT top(100) percent substring(convert(varchar,dbo .CADInc .CADNo),3,2)+ substring(convert(varchar,dbo .CADInc .CADNo),6,5) as CaseNo,
CONVERT(VARCHAR(10),dbo.CADInc.RptDttm,101) as [Reported Date],CONVERT(VARCHAR(10),dbo.CADInc.RptDttm,108) as [Reported Time],
dbo.CADUnitHist.SysCC, case when dbo.CADUnitHist.UserCC='IN' then 'RE' else dbo.CADUnitHist .UserCC end as UserCC
,CONVERT(VARCHAR(10), dbo.CADUnitHist.ActDttm,101) as [ACT Date],CONVERT(VARCHAR(10),
dbo.CADUnitHist.ActDttm,108) as [ReportTime],dbo.CADInc.District
FROM dbo.CADUnitHist
INNER JOIN dbo.CADInc ON dbo.CADUnitHist.IncidentID = dbo.CADInc.IncidentID
WHERE CAdinc.ProductID = 2
and dbo.CADInc.RptDttm >='01/1/2012 12:00:00 AM' And dbo.CADInc.RptDttm <='5/31/2012 11:59:59 PM'
and dbo.CADUnitHist.UserCC in('DI','OS','RE','IN') ORDER BY sysCC, CaseNo,
dbo.CADInc.CADNo, dbo.CADUnitHist.ActDttm ,dbo.CADUnitHist.UserCC
Thanks,
Charmer
June 29, 2012 at 2:43 am
The above is the SQL query that i use in here......
For each caseno it must show the all the records excpet SysCC column has a value 9...for a CaseNo, if SysCC column has both 8 and 9, then it must not show the row that contains 9....if SysCC column does not have 8, then only the row having 9 must come..
Thanks,
Charmer
June 29, 2012 at 4:32 am
Charmer (6/29/2012)
Sorry Friends, i was not able to post my DDL and DML last night....I am sorry Lynn.....
USE [MyDB]
GO
/****** Object: Table [dbo].[CADInc] Script Date: 6/29/2012 2:04:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CADInc](
[CADNo] [bigint] NULL,
[RptDttm] [datetime] NULL,
[District] [char](4) NULL,
[IncidentID] [bigint] NOT NULL,
[ProductID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [MyDB]
GO
/****** Object: Table [dbo].[CADUnitHist] Script Date: 6/29/2012 2:04:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CADUnitHist](
[UserCC] [char](2) NULL,
[ActDttm] [datetime] NULL,
[SysCC] [smallint] NULL,
[IncidentID] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000058, CAST(0x00009C0300B78525 AS DateTime), NULL, 100040000685027, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000023, CAST(0x00009C0300B78525 AS DateTime), NULL, 100040000685030, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000059, CAST(0x00009C0300BEBCB8 AS DateTime), NULL, 100040000685123, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000024, CAST(0x00009C0300BEBCB8 AS DateTime), NULL, 100040000685126, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000025, CAST(0x00009C0300BF98C6 AS DateTime), NULL, 100040000685158, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000060, CAST(0x00009C0300C2D86C AS DateTime), NULL, 100040000685249, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000026, CAST(0x00009C0300C2D86C AS DateTime), N'1 ', 100040000685251, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000061, CAST(0x00009C0300C3A37C AS DateTime), NULL, 100040000685270, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000064, CAST(0x00009C0600B53458 AS DateTime), NULL, 100040000687366, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000065, CAST(0x00009C0600B5EA96 AS DateTime), NULL, 100040000687375, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000066, CAST(0x00009C0600BC98AF AS DateTime), NULL, 100040000687580, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000067, CAST(0x00009C0600BD972A AS DateTime), NULL, 100040000687643, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000068, CAST(0x00009C3000A41E8A AS DateTime), NULL, 100040000689600, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000027, CAST(0x00009C3000A41E8A AS DateTime), NULL, 100040000689603, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000028, CAST(0x00009C3000A48C17 AS DateTime), N'1 ', 100040000689620, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000069, CAST(0x00009C3000A64573 AS DateTime), NULL, 100040000689683, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000070, CAST(0x00009C3000E0E730 AS DateTime), NULL, 100040000690311, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000029, CAST(0x00009C3000E0E730 AS DateTime), N'1 ', 100040000690314, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000071, CAST(0x00009C4800F6A528 AS DateTime), NULL, 100040000693374, 1)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2009000080, CAST(0x00009C4C00B6A7E6 AS DateTime), NULL, 100040000694835, 1)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D4B6B AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D4B6B AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D4B6F AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D58D2 AS DateTime), 105, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D6765 AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D676A AS DateTime), 9, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D8EC8 AS DateTime), 105, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010D9604 AS DateTime), 9, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010DB812 AS DateTime), 105, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'TU', CAST(0x00009843010DB817 AS DateTime), 8, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OD', CAST(0x000098430110A5FB AS DateTime), 1, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OD', CAST(0x000098430111300B AS DateTime), 1, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009843011A1B8D AS DateTime), 3, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009843011A1B8D AS DateTime), 3, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009843011AC8D3 AS DateTime), 6, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x00009843012EA7AD AS DateTime), 71, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'CT', CAST(0x00009843012F5D9C AS DateTime), 9, 100040000588033)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x00009843012F8A78 AS DateTime), 71, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x00009843012F92AC AS DateTime), 71, NULL)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OK', CAST(0x000098430130142A AS DateTime), 8, NULL)
GO
SELECT top(100) percent substring(convert(varchar,dbo .CADInc .CADNo),3,2)+ substring(convert(varchar,dbo .CADInc .CADNo),6,5) as CaseNo,
CONVERT(VARCHAR(10),dbo.CADInc.RptDttm,101) as [Reported Date],CONVERT(VARCHAR(10),dbo.CADInc.RptDttm,108) as [Reported Time],
dbo.CADUnitHist.SysCC, case when dbo.CADUnitHist.UserCC='IN' then 'RE' else dbo.CADUnitHist .UserCC end as UserCC
,CONVERT(VARCHAR(10), dbo.CADUnitHist.ActDttm,101) as [ACT Date],CONVERT(VARCHAR(10),
dbo.CADUnitHist.ActDttm,108) as [ReportTime],dbo.CADInc.District
FROM dbo.CADUnitHist
INNER JOIN dbo.CADInc ON dbo.CADUnitHist.IncidentID = dbo.CADInc.IncidentID
WHERE CAdinc.ProductID = 2
and dbo.CADInc.RptDttm >='01/1/2012 12:00:00 AM' And dbo.CADInc.RptDttm <='5/31/2012 11:59:59 PM'
and dbo.CADUnitHist.UserCC in('DI','OS','RE','IN') ORDER BY sysCC, CaseNo,
dbo.CADInc.CADNo, dbo.CADUnitHist.ActDttm ,dbo.CADUnitHist.UserCC
You really should check your code works before posting it. The query at the end of code block above returns nothing as the sample data provided has no data within the data range coded into the query.
June 29, 2012 at 5:42 am
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000003, CAST(0x00009FCC01571284 AS DateTime), NULL, 100040002665107, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000003, CAST(0x00009FCC01571284 AS DateTime), NULL, 100040002665107, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000008, CAST(0x00009FCE01184C14 AS DateTime), NULL, 100040002673135, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000008, CAST(0x00009FCE01184C14 AS DateTime), NULL, 100040002673135, 2)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCC01001126 AS DateTime), 3, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCC010096B1 AS DateTime), 6, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCC01009B6D AS DateTime), 1, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCC0100F327 AS DateTime), 9, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCD00CB9EA7 AS DateTime), 8, 100040002665107)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCD00CD03B0 AS DateTime), 3, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCD00CD35A7 AS DateTime), 6, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCD00CD3B32 AS DateTime), 8, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCD00CD79DF AS DateTime), 9, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCD012678DF AS DateTime), 9, 100040002665107)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCE010E3824 AS DateTime), 3, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCE010E46F3 AS DateTime), 6, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCE010E4CD6 AS DateTime), 8, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCE010F3338 AS DateTime), 9, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCE010F461B AS DateTime), 3, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCE010F4D69 AS DateTime), 6, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCE010F50E3 AS DateTime), 8, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCE010F54C6 AS DateTime), 9, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCE011873DF AS DateTime), 3, 100040002673135)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCE0118A354 AS DateTime), 6, 100040002673135)
GO
Please try with this DMl, Lynn....i hope you will get some result to test...
Thanks,
Charmer
June 29, 2012 at 5:51 am
Charmer (6/29/2012)
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000001, CAST(0x00009FCC00FFD238 AS DateTime), NULL, 100040002662851, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000003, CAST(0x00009FCC01571284 AS DateTime), NULL, 100040002665107, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000004, CAST(0x00009FCD00CCBAE7 AS DateTime), NULL, 100040002666868, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000003, CAST(0x00009FCC01571284 AS DateTime), NULL, 100040002665107, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000005, CAST(0x00009FCE010E171B AS DateTime), NULL, 100040002672754, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000006, CAST(0x00009FCE010EADB0 AS DateTime), NULL, 100040002672779, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000008, CAST(0x00009FCE01184C14 AS DateTime), NULL, 100040002673135, 2)
GO
INSERT [dbo].[CADInc] ([CADNo], [RptDttm], [District], [IncidentID], [ProductID]) VALUES (2012000008, CAST(0x00009FCE01184C14 AS DateTime), NULL, 100040002673135, 2)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCC01001126 AS DateTime), 3, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCC010096B1 AS DateTime), 6, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCC01009B6D AS DateTime), 1, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCC0100F327 AS DateTime), 9, 100040002662851)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCD00CB9EA7 AS DateTime), 8, 100040002665107)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCD00CD03B0 AS DateTime), 3, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCD00CD35A7 AS DateTime), 6, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCD00CD3B32 AS DateTime), 8, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCD00CD79DF AS DateTime), 9, 100040002666868)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCD012678DF AS DateTime), 9, 100040002665107)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCE010E3824 AS DateTime), 3, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCE010E46F3 AS DateTime), 6, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCE010E4CD6 AS DateTime), 8, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCE010F3338 AS DateTime), 9, 100040002672754)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCE010F461B AS DateTime), 3, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCE010F4D69 AS DateTime), 6, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'RE', CAST(0x00009FCE010F50E3 AS DateTime), 8, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'IN', CAST(0x00009FCE010F54C6 AS DateTime), 9, 100040002672779)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'DI', CAST(0x00009FCE011873DF AS DateTime), 3, 100040002673135)
GO
INSERT [dbo].[CADUnitHist] ([UserCC], [ActDttm], [SysCC], [IncidentID]) VALUES (N'OS', CAST(0x00009FCE0118A354 AS DateTime), 6, 100040002673135)
GO
Please try with this DMl, Lynn....i hope you will get some result to test...
Don't like the "I hope." Did you test your sample data against your query?
June 29, 2012 at 6:00 am
I am sorry Lynn. yes,it will work for sure..i tested it...
i have attached the screen shot of the result...
for example
in caseNo 120005, we got rows having SysCC column as 6,8,9.......
so we need to show, only the records that not having SysCC column value 9 (only 3,6,8 must be shown) if 8 is existed for the same CaseNo....if 8 is not there , then we must show all the records for the CaseNo having SysCC having (3,6,9)
i believe you will be understanding my requirement Lynn....
Thanks,
Charmer
June 29, 2012 at 8:58 am
Now, did you take the code that I posted based on David's post to see if that did what you wanted? If so, did it based on your initial post? If so, have you tried to modify it to work with your new requirements?
Here is the code again:
create table #TestData (
ID int,
Unit int,
SysCC int
);
go
insert into #TestData(ID, Unit, SysCC)
values(100,38142,1),
(100,65982,6),
(100,46413,8),
(100,64685,9),
(101,46551,3),
(101,64651,8),
(102,46465,2),
(102,56465,9);
go
select
td.ID,
td.Unit,
td.SysCC
from
#TestData td
where
td.SysCC <> 9
or not exists(select
1
from
#TestData td1
where
td1.ID = td.ID
and td1.SysCC = 8);
go
drop table #TestData;
go
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply