August 5, 2011 at 5:23 am
SELECT TOP 1000 [CASEID]
,[EVENTNO]
,[CYCLE]
,[EVENTDATE]
,[EVENTDUEDATE]
,[DATEREMIND]
,[DATEDUESAVED]
,[OCCURREDFLAG]
,[CREATEDBYACTION]
,[CREATEDBYCRITERIA]
,[ENTEREDDEADLINE]
,[PERIODTYPE]
,[DOCUMENTNO]
,[DOCSREQUIRED]
,[DOCSRECEIVED]
,[USEMESSAGE2FLAG]
,[GOVERNINGEVENTNO]
,[EVENTTEXT]
,[LONGFLAG]
,[EVENTLONGTEXT]
,[JOURNALNO]
,[IMPORTBATCHNO]
,[EVENTTEXT_TID]
,[rowguid]
,[EMPLOYEENO]
,[SENDMETHOD]
,[SENTDATE]
,[RECEIPTDATE]
,[RECEIPTREFERENCE]
,[DISPLAYORDER]
,[FROMCASEID]
,[DUEDATERESPNAMETYPE]
,[LOGUSERID]
,[LOGIDENTITYID]
,[LOGTRANSACTIONNO]
,[LOGDATETIMESTAMP]
,[LOGAPPLICATION]
,[LOGOFFICEID]
FROM [fbdlive].[dbo].[CASEEVENT]
I have the table above and I want to select all record for two different EVENTNO ( 1 and 100 for instance) but same CYCLE (3) for instance where the EVENTDUEDATE are different. Normally, on this record, if two or more different EVENTNO have the same CYCLE, their due date must be the same. How can I accomplish this?
Many thanks in advance
August 5, 2011 at 6:01 am
The task that I want to accomplish is summarised below:
FOR ANY CYCLE
FIND RECORDS
WHERE EVENTNO = -11 AND 1425
WHERE EVENTDUEDATE ARE NOT THE SAME
August 5, 2011 at 6:14 am
Can you please provide:
1) Create Table scripts
2) Some sample data
3) Expected results based on your sample data ?
If in doubt, check the article in my signature line "get your two-cent answer quickly".
-- Gianluca Sartori
August 5, 2011 at 8:09 am
Have you considered selecting one record via a CTE then selecting from the CTE and unioning to another select statement where eventID and EVentDate do not equal the value from the CTE?
August 5, 2011 at 8:54 am
This is the create DB statement
USE [SQL2K8R2DB]
GO
/****** Object: Table [dbo].[CASEEVENT] Script Date: 08/05/2011 15:48:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CASEEVENT](
[CASEID] [int] NOT NULL,
[EVENTNO] [int] NOT NULL,
[CYCLE] [smallint] NOT NULL,
[EVENTDATE] [datetime] NULL,
[EVENTDUEDATE] [datetime] NULL,
[DATEREMIND] [datetime] NULL,
[DATEDUESAVED] [decimal](1, 0) NULL,
[OCCURREDFLAG] [decimal](1, 0) NULL,
[CREATEDBYACTION] [nvarchar](2) NULL,
[CREATEDBYCRITERIA] [int] NULL,
[ENTEREDDEADLINE] [int] NULL,
[PERIODTYPE] [nchar](1) NULL,
[DOCUMENTNO] [smallint] NULL,
[DOCSREQUIRED] [smallint] NULL,
[DOCSRECEIVED] [smallint] NULL,
[USEMESSAGE2FLAG] [decimal](1, 0) NULL,
[GOVERNINGEVENTNO] [int] NULL,
[EVENTTEXT] [nvarchar](254) NULL,
[LONGFLAG] [decimal](1, 0) NULL,
[EVENTLONGTEXT] [ntext] NULL,
[JOURNALNO] [nvarchar](20) NULL,
[IMPORTBATCHNO] [int] NULL,
[EVENTTEXT_TID] [int] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[EMPLOYEENO] [int] NULL,
[SENDMETHOD] [int] NULL,
[SENTDATE] [datetime] NULL,
[RECEIPTDATE] [datetime] NULL,
[RECEIPTREFERENCE] [nvarchar](50) NULL,
[DISPLAYORDER] [smallint] NULL,
[FROMCASEID] [int] NULL,
[DUEDATERESPNAMETYPE] [nvarchar](3) NULL,
[LOGUSERID] [nvarchar](50) NULL,
[LOGIDENTITYID] [int] NULL,
[LOGTRANSACTIONNO] [int] NULL,
[LOGDATETIMESTAMP] [datetime] NULL,
[LOGAPPLICATION] [nvarchar](128) NULL,
[LOGOFFICEID] [int] NULL,
CONSTRAINT [XPKCASEEVENT] PRIMARY KEY CLUSTERED
(
[CASEID] ASC,
[EVENTNO] ASC,
[CYCLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_1276] FOREIGN KEY([EMPLOYEENO])
REFERENCES [dbo].[NAME] ([NAMENO])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_1276]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_1334] FOREIGN KEY([CREATEDBYCRITERIA])
REFERENCES [dbo].[CRITERIA] ([CRITERIANO])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_1334]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_20006] FOREIGN KEY([CREATEDBYACTION])
REFERENCES [dbo].[ACTIONS] ([ACTION])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_20006]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_20024] FOREIGN KEY([CASEID])
REFERENCES [dbo].[CASES] ([CASEID])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_20024]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_20090] FOREIGN KEY([EVENTNO])
REFERENCES [dbo].[EVENTS] ([EVENTNO])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_20090]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_888] FOREIGN KEY([DOCUMENTNO])
REFERENCES [dbo].[DOCUMENT] ([DOCUMENTNO])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_888]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_91433] FOREIGN KEY([DUEDATERESPNAMETYPE])
REFERENCES [dbo].[NAMETYPE] ([NAMETYPE])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_91433]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [R_91751] FOREIGN KEY([FROMCASEID])
REFERENCES [dbo].[CASES] ([CASEID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [R_91751]
GO
ALTER TABLE [dbo].[CASEEVENT] WITH NOCHECK ADD CONSTRAINT [RI_1277] FOREIGN KEY([SENDMETHOD])
REFERENCES [dbo].[TABLECODES] ([TABLECODE])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CASEEVENT] CHECK CONSTRAINT [RI_1277]
GO
ALTER TABLE [dbo].[CASEEVENT] ADD CONSTRAINT [DF__CASEEVENT__rowgu__05257EFE] DEFAULT (newsequentialid()) FOR [rowguid]
GO
August 5, 2011 at 8:58 am
Sample data:
CASEIDEVENTNOCYCLEEVENTDATEEVENTDUEDATEDATEREMINDDATEDUESAVEDOCCURREDFLAGCREATEDBYACTIONCREATEDBYCRITERIAENTEREDDEADLINEPERIODTYPEDOCUMENTNODOCSREQUIREDDOCSRECEIVEDUSEMESSAGE2FLAGGOVERNINGEVENTNOEVENTTEXTLONGFLAGEVENTLONGTEXTJOURNALNOIMPORTBATCHNOEVENTTEXT_TIDrowguidEMPLOYEENOSENDMETHODSENTDATERECEIPTDATERECEIPTREFERENCEDISPLAYORDERFROMCASEIDDUEDATERESPNAMETYPELOGUSERIDLOGIDENTITYIDLOGTRANSACTIONNOLOGDATETIMESTAMPLOGAPPLICATIONLOGOFFICEID
-999499000-2111990-06-26 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL041D622E-4748-4D45-9072-9A8DA6F69D20NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-2011990-06-26 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL061AAD9D-FDAE-40D4-AF0E-C52180CC2DB3NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-1312005-01-07 11:54:31.987NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLConversion DateNULLNULLNULLNULLNULL9629D178-42F3-41E0-820B-26DF10EAB37CNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-811992-08-07 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLE7D93781-3D30-493D-BC84-BE9B2E361988NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-411990-06-26 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLEED5836E-E0A7-4914-BABB-B8E185E7744ENULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000102111992-09-18 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLB5FA181D-1757-4187-A91F-5AC9F8C7D4EDNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000108912005-01-07 13:22:05.237NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLConversion DateNULLNULLNULLNULLNULL4C4B71A9-B630-49E6-B449-15681FDE7360NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000124012005-01-07 11:50:21.610NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLConversion DateNULLNULLNULLNULLNULLD9D9F40A-F50C-45DC-95B0-88850E20453DNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-99949900012681NULL1997-06-26 00:00:00.000NULL10NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL10B18489-FB6F-46F4-8DB2-7173E4D9950BNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-1178211997-10-11 00:00:00.0001997-10-11 00:00:00.000NULL01RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULL616EF280-A3BC-4930-A673-12B28EB3BB17NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-117822NULL2007-10-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULL4CA456B8-5481-40AC-82AB-958DD78CB5C6NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-117823NULL2017-10-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULL8D323D68-4347-419B-9332-A13E39DE6ADBNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-117824NULL2027-10-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULLE6B31261-1A35-469B-99D4-8959538AE42ANULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-69411990-04-11 00:00:00.0001990-04-11 00:00:00.000NULL01RS-1093NULLNULLNULLNULLNULL0-9NULLNULLNULLNULLNULLNULLE74B7D20-415C-4822-9966-99A346568FEENULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-6921NULL2036-04-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-130NULLNULLNULLNULLNULLNULL07CEBF77-6D25-4197-8086-872A3E92F583NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-26122005-01-07 00:00:00.0002005-01-07 00:00:00.000NULL01RS-1094NULLNULLNULLNULLNULL0-135NULLNULLNULLNULLNULLNULLC8780887-AEEF-402E-9525-62E19D670300NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-23411992-03-06 00:00:00.0001992-03-06 00:00:00.000NULL01AS10138NULLNULLNULLNULLNULL0-8NULLNULLNULLNULLNULLNULLB9EEBA3D-3FE6-409E-BDEA-C3ED93D48066NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-23311992-03-06 00:00:00.0001992-03-06 00:00:00.000NULL01AS10138NULLNULLNULLNULLNULL0-8NULLNULLNULLNULLNULLNULLEA8E7F62-7AAF-4FA9-9AA9-26C43297273CNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-19922007-04-12 00:00:00.0002007-04-12 00:00:00.000NULL01RN10273NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULLA27889F7-7CC6-4C1C-8067-E4CC5F0E4FCDNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-14122007-09-12 00:00:00.000NULLNULL01RN10273NULLNULLNULLNULLNULLNULLNULLNULL0NULLNULLNULLNULLD5F2DE87-B2A4-421A-BB86-105632B07AA4NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
August 5, 2011 at 8:59 am
Many thanks epridy. i am not used to writing cte. i used self join but i was only close to getting the result. can u mail me the cte statement that u talked about? tx.
August 5, 2011 at 9:33 am
Thanks for the table scripts.
Sample data is not in a readily consumable format (INSERT INTO OneOfYourTables).
We're still missing the expected output based on that sample data.
It's not that I want to be picky, it's just that I can't help without those info.
Would you help us help you?
-- Gianluca Sartori
August 5, 2011 at 10:01 am
CASEIDEVENTNOCYCLEEVENTDATEEVENTDUEDATEDATEREMINDDATEDUESAVEDOCCURREDFLAGCREATEDBYACTIONCREATEDBYCRITERIAENTEREDDEADLINEPERIODTYPEDOCUMENTNODOCSREQUIREDDOCSRECEIVEDUSEMESSAGE2FLAGGOVERNINGEVENTNOEVENTTEXTLONGFLAGEVENTLONGTEXTJOURNALNOIMPORTBATCHNOEVENTTEXT_TIDrowguidEMPLOYEENOSENDMETHODSENTDATERECEIPTDATERECEIPTREFERENCEDISPLAYORDERFROMCASEIDDUEDATERESPNAMETYPELOGUSERIDLOGIDENTITYIDLOGTRANSACTIONNOLOGDATETIMESTAMPLOGAPPLICATIONLOGOFFICEID
-999499000-2111990-06-26 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL041D622E-4748-4D45-9072-9A8DA6F69D20NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-2011990-06-26 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL061AAD9D-FDAE-40D4-AF0E-C52180CC2DB3NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-1312005-01-07 11:54:31.987NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLConversion DateNULLNULLNULLNULLNULL9629D178-42F3-41E0-820B-26DF10EAB37CNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-811992-08-07 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLE7D93781-3D30-493D-BC84-BE9B2E361988NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000-411990-06-26 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLEED5836E-E0A7-4914-BABB-B8E185E7744ENULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000102111992-09-18 00:00:00.000NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLB5FA181D-1757-4187-A91F-5AC9F8C7D4EDNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000108912005-01-07 13:22:05.237NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLConversion DateNULLNULLNULLNULLNULL4C4B71A9-B630-49E6-B449-15681FDE7360NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999499000124012005-01-07 11:50:21.610NULLNULL01NULLNULLNULLNULLNULLNULLNULLNULLNULLConversion DateNULLNULLNULLNULLNULLD9D9F40A-F50C-45DC-95B0-88850E20453DNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-99949900012681NULL1997-06-26 00:00:00.000NULL10NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL10B18489-FB6F-46F4-8DB2-7173E4D9950BNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-1178211997-10-11 00:00:00.0001997-10-11 00:00:00.000NULL01RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULL616EF280-A3BC-4930-A673-12B28EB3BB17NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-117822NULL2007-10-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULL4CA456B8-5481-40AC-82AB-958DD78CB5C6NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-117823NULL2017-10-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULL8D323D68-4347-419B-9332-A13E39DE6ADBNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-117824NULL2027-10-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULLE6B31261-1A35-469B-99D4-8959538AE42ANULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-69411990-04-11 00:00:00.0001990-04-11 00:00:00.000NULL01RS-1093NULLNULLNULLNULLNULL0-9NULLNULLNULLNULLNULLNULLE74B7D20-415C-4822-9966-99A346568FEENULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-6921NULL2036-04-11 00:00:00.000NULL00RS-1094NULLNULLNULLNULLNULL0-130NULLNULLNULLNULLNULLNULL07CEBF77-6D25-4197-8086-872A3E92F583NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-26122005-01-07 00:00:00.0002005-01-07 00:00:00.000NULL01RS-1094NULLNULLNULLNULLNULL0-135NULLNULLNULLNULLNULLNULLC8780887-AEEF-402E-9525-62E19D670300NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-23411992-03-06 00:00:00.0001992-03-06 00:00:00.000NULL01AS10138NULLNULLNULLNULLNULL0-8NULLNULLNULLNULLNULLNULLB9EEBA3D-3FE6-409E-BDEA-C3ED93D48066NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-23311992-03-06 00:00:00.0001992-03-06 00:00:00.000NULL01AS10138NULLNULLNULLNULLNULL0-8NULLNULLNULLNULLNULLNULLEA8E7F62-7AAF-4FA9-9AA9-26C43297273CNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-19922007-04-12 00:00:00.0002007-04-12 00:00:00.000NULL01RN10273NULLNULLNULLNULLNULL0-11NULLNULLNULLNULLNULLNULLA27889F7-7CC6-4C1C-8067-E4CC5F0E4FCDNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-999497000-14122007-09-12 00:00:00.000NULLNULL01RN10273NULLNULLNULLNULLNULLNULLNULLNULL0NULLNULLNULLNULLD5F2DE87-B2A4-421A-BB86-105632B07AA4NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
August 5, 2011 at 10:03 am
Expected result? I honestly don't know. I was told that it implies that there is a problem if we have a record with the same cycle number for event no = -11 and eventno = 1425 but different due date. That's all I was told and I was asked to write a code to pull those records out. Thanks
August 8, 2011 at 1:46 am
This was removed by the editor as SPAM
August 8, 2011 at 1:57 am
The task that I want to accomplish is summarised below:
FOR ANY CYCLE
FIND RECORDS
WHERE EVENTNO = -11 AND 1425
WHERE EVENTDUEDATE ARE NOT THE SAME
I am thinking this should work for you
Select distinct [CASEID]
,[EVENTNO]
,[CYCLE]
,[EVENTDATE]
,[EVENTDUEDATE]
,[DATEREMIND]
,[DATEDUESAVED]
,[OCCURREDFLAG]
,[CREATEDBYACTION]
,[CREATEDBYCRITERIA]
,[ENTEREDDEADLINE]
,[PERIODTYPE]
,[DOCUMENTNO]
,[DOCSREQUIRED]
,[DOCSRECEIVED]
,[USEMESSAGE2FLAG]
,[GOVERNINGEVENTNO]
,[EVENTTEXT]
,[LONGFLAG]
,[EVENTLONGTEXT]
,[JOURNALNO]
,[IMPORTBATCHNO]
,[EVENTTEXT_TID]
,[rowguid]
,[EMPLOYEENO]
,[SENDMETHOD]
,[SENTDATE]
,[RECEIPTDATE]
,[RECEIPTREFERENCE]
,[DISPLAYORDER]
,[FROMCASEID]
,[DUEDATERESPNAMETYPE]
,[LOGUSERID]
,[LOGIDENTITYID]
,[LOGTRANSACTIONNO]
,[LOGDATETIMESTAMP]
,[LOGAPPLICATION]
,[LOGOFFICEID]
FROM [fbdlive].[dbo].[CASEEVENT]
where eventno in ( 1, 100)
The eventno are part of thw where clause to filter the records.
The distinct makes sure you get only unique dates.
August 8, 2011 at 5:16 am
Thanks so much Stu and Jayanth. I will try both.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply