Sql Server如果在日期范围内的另一个表中没有收集日期，则从一个表中选择行

I have two table one is client team and another is CHeader, When i enter a record in client team i am supposed to collect data in CHeader (with kind column data as 'Ini Test') within 7 days. If the 7 days are passed and the Cheader data is not collected for that client then i should be able to get those results in query output. Note : only Kind with 'Ini Test' is valid data for this case. following is the query .

我有两个表一个是客户团队,另一个是CHeader,当我在客户团队中输入记录时,我应该在7天内收集CHeader中的数据(种类列数据为'Ini Test')。如果7天过去并且没有为该客户收集Cheader数据,那么我应该能够在查询输出中获得这些结果。注意:只有具有'Ini Test'的Kind才是此案例的有效数据。以下是查询。

            CREATE TABLE [dbo].[ClientTeam](
        [ClientTeamID] [int] IDENTITY(1,1) NOT NULL,
        [TeamID] [int] NOT NULL,
        [ClientID] [int] NOT NULL,
        [Startdate] [smalldatetime] NOT NULL,
        CONSTRAINT [PK15] PRIMARY KEY NONCLUSTERED 
        (
        [ClientTeamID] 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]

        GO

        insert into [ClientTeam] ([TeamID],[ClientID],[Startdate]) values(123,1,   GETDATE())
        insert into [ClientTeam] ([TeamID],[ClientID],[Startdate]) values(12,23, DATEdiff(day,2,GETDATE()))
        insert into [ClientTeam] ([TeamID],[ClientID],[Startdate]) values(12,25, DATEdiff(day,3,GETDATE()))


        CREATE TABLE [dbo].[CHeader](
        [CHID] [int] IDENTITY(1,1) NOT NULL,
        [ClientID] [int] NOT NULL,
        [TeamID] [int] NOT NULL,
        [CreateDate] [datetime] NOT NULL,
        [Kind] [varchar](80) NOT NULL,
        CONSTRAINT [PK22] PRIMARY KEY NONCLUSTERED 
        (
        [CHID] 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]

        GO



        insert into [CHeader] ([ClientID],[TeamID],[CreateDate],[Kind])   values(1,123,DATEdiff(HOUR,4,GETDATE()),'Ini Test')
        insert into [CHeader] ([ClientID],[TeamID],[CreateDate],[Kind]) values(5,123,DATEdiff(HOUR,4,GETDATE()),'Gen Test')
        insert into [CHeader] ([ClientID],[TeamID],[CreateDate],[Kind]) values(6,126,DATEdiff(HOUR,4,GETDATE()),'XYZ')
        insert into [CHeader] ([ClientID],[TeamID],[CreateDate],[Kind]) values(23,126,DATEdiff(HOUR,5,GETDATE()),'XYZ')

1 个解决方案

#1

From what I can understand, you want to take data from ClientTeam and only join it to CHeader based on the criteria mentioned.

根据我的理解,您希望从ClientTeam获取数据,并仅根据提到的标准将其加入CHeader。

I would say select from ClientTeam and Left Join onto CHeader. You can Join ON C.ClientID = CH.ClientID AND C.StartDate > DATEADD(DAY,-7,CH.CreateDate) AND CH.Kind = 'Ini Test'

我会说从ClientTeam中选择并选择左连接到CHeader。你可以加入C.ClientID = CH.ClientID AND C.StartDate> DATEADD(DAY,-7,CH.CreateDate)和CH.Kind ='Ini Test'

#1