Post reply

T-SQL SELECT STATEMENT

  • August 5, 2011 at 5:23 am

    #241194

    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

    #1364254

    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

    #1364272

    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

    #1364368

    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

    #1364408

    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

    #1364411

    Sample data:

    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

    -999499000 -21 1 1990-06-26 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 041D622E-4748-4D45-9072-9A8DA6F69D20 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -20 1 1990-06-26 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 061AAD9D-FDAE-40D4-AF0E-C52180CC2DB3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -13 1 2005-01-07 11:54:31.987 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL Conversion Date NULL NULL NULL NULL NULL 9629D178-42F3-41E0-820B-26DF10EAB37C NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -8 1 1992-08-07 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL E7D93781-3D30-493D-BC84-BE9B2E361988 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -4 1 1990-06-26 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL EED5836E-E0A7-4914-BABB-B8E185E7744E NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1021 1 1992-09-18 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL B5FA181D-1757-4187-A91F-5AC9F8C7D4ED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1089 1 2005-01-07 13:22:05.237 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL Conversion Date NULL NULL NULL NULL NULL 4C4B71A9-B630-49E6-B449-15681FDE7360 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1240 1 2005-01-07 11:50:21.610 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL Conversion Date NULL NULL NULL NULL NULL D9D9F40A-F50C-45DC-95B0-88850E20453D NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1268 1 NULL 1997-06-26 00:00:00.000 NULL 1 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 10B18489-FB6F-46F4-8DB2-7173E4D9950B NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 1 1997-10-11 00:00:00.000 1997-10-11 00:00:00.000 NULL 0 1 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL 616EF280-A3BC-4930-A673-12B28EB3BB17 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 2 NULL 2007-10-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL 4CA456B8-5481-40AC-82AB-958DD78CB5C6 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 3 NULL 2017-10-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL 8D323D68-4347-419B-9332-A13E39DE6ADB NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 4 NULL 2027-10-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL E6B31261-1A35-469B-99D4-8959538AE42A NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -694 1 1990-04-11 00:00:00.000 1990-04-11 00:00:00.000 NULL 0 1 RS -1093 NULL NULL NULL NULL NULL 0 -9 NULL NULL NULL NULL NULL NULL E74B7D20-415C-4822-9966-99A346568FEE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -692 1 NULL 2036-04-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -130 NULL NULL NULL NULL NULL NULL 07CEBF77-6D25-4197-8086-872A3E92F583 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -261 2 2005-01-07 00:00:00.000 2005-01-07 00:00:00.000 NULL 0 1 RS -1094 NULL NULL NULL NULL NULL 0 -135 NULL NULL NULL NULL NULL NULL C8780887-AEEF-402E-9525-62E19D670300 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -234 1 1992-03-06 00:00:00.000 1992-03-06 00:00:00.000 NULL 0 1 AS 10138 NULL NULL NULL NULL NULL 0 -8 NULL NULL NULL NULL NULL NULL B9EEBA3D-3FE6-409E-BDEA-C3ED93D48066 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -233 1 1992-03-06 00:00:00.000 1992-03-06 00:00:00.000 NULL 0 1 AS 10138 NULL NULL NULL NULL NULL 0 -8 NULL NULL NULL NULL NULL NULL EA8E7F62-7AAF-4FA9-9AA9-26C43297273C NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -199 2 2007-04-12 00:00:00.000 2007-04-12 00:00:00.000 NULL 0 1 RN 10273 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL A27889F7-7CC6-4C1C-8067-E4CC5F0E4FCD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -141 2 2007-09-12 00:00:00.000 NULL NULL 0 1 RN 10273 NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL D5F2DE87-B2A4-421A-BB86-105632B07AA4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

  • August 5, 2011 at 8:59 am

    #1364413

    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

    #1364447

    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

    #1364480

    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

    -999499000 -21 1 1990-06-26 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 041D622E-4748-4D45-9072-9A8DA6F69D20 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -20 1 1990-06-26 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 061AAD9D-FDAE-40D4-AF0E-C52180CC2DB3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -13 1 2005-01-07 11:54:31.987 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL Conversion Date NULL NULL NULL NULL NULL 9629D178-42F3-41E0-820B-26DF10EAB37C NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -8 1 1992-08-07 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL E7D93781-3D30-493D-BC84-BE9B2E361988 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 -4 1 1990-06-26 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL EED5836E-E0A7-4914-BABB-B8E185E7744E NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1021 1 1992-09-18 00:00:00.000 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL B5FA181D-1757-4187-A91F-5AC9F8C7D4ED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1089 1 2005-01-07 13:22:05.237 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL Conversion Date NULL NULL NULL NULL NULL 4C4B71A9-B630-49E6-B449-15681FDE7360 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1240 1 2005-01-07 11:50:21.610 NULL NULL 0 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL Conversion Date NULL NULL NULL NULL NULL D9D9F40A-F50C-45DC-95B0-88850E20453D NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999499000 1268 1 NULL 1997-06-26 00:00:00.000 NULL 1 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 10B18489-FB6F-46F4-8DB2-7173E4D9950B NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 1 1997-10-11 00:00:00.000 1997-10-11 00:00:00.000 NULL 0 1 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL 616EF280-A3BC-4930-A673-12B28EB3BB17 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 2 NULL 2007-10-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL 4CA456B8-5481-40AC-82AB-958DD78CB5C6 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 3 NULL 2017-10-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL 8D323D68-4347-419B-9332-A13E39DE6ADB NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -11782 4 NULL 2027-10-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL E6B31261-1A35-469B-99D4-8959538AE42A NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -694 1 1990-04-11 00:00:00.000 1990-04-11 00:00:00.000 NULL 0 1 RS -1093 NULL NULL NULL NULL NULL 0 -9 NULL NULL NULL NULL NULL NULL E74B7D20-415C-4822-9966-99A346568FEE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -692 1 NULL 2036-04-11 00:00:00.000 NULL 0 0 RS -1094 NULL NULL NULL NULL NULL 0 -130 NULL NULL NULL NULL NULL NULL 07CEBF77-6D25-4197-8086-872A3E92F583 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -261 2 2005-01-07 00:00:00.000 2005-01-07 00:00:00.000 NULL 0 1 RS -1094 NULL NULL NULL NULL NULL 0 -135 NULL NULL NULL NULL NULL NULL C8780887-AEEF-402E-9525-62E19D670300 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -234 1 1992-03-06 00:00:00.000 1992-03-06 00:00:00.000 NULL 0 1 AS 10138 NULL NULL NULL NULL NULL 0 -8 NULL NULL NULL NULL NULL NULL B9EEBA3D-3FE6-409E-BDEA-C3ED93D48066 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -233 1 1992-03-06 00:00:00.000 1992-03-06 00:00:00.000 NULL 0 1 AS 10138 NULL NULL NULL NULL NULL 0 -8 NULL NULL NULL NULL NULL NULL EA8E7F62-7AAF-4FA9-9AA9-26C43297273C NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -199 2 2007-04-12 00:00:00.000 2007-04-12 00:00:00.000 NULL 0 1 RN 10273 NULL NULL NULL NULL NULL 0 -11 NULL NULL NULL NULL NULL NULL A27889F7-7CC6-4C1C-8067-E4CC5F0E4FCD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

    -999497000 -141 2 2007-09-12 00:00:00.000 NULL NULL 0 1 RN 10273 NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL D5F2DE87-B2A4-421A-BB86-105632B07AA4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

  • August 5, 2011 at 10:03 am

    #1364482

    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

    #1365051

    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.

    Jayanth Kurup[/url]

  • August 8, 2011 at 5:16 am

    #1365096

    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