Question

2005年在微软Kh服务器工作,需要分析哪些参与者在星期二获得50+锦标赛的资格——在2点之前一周在Mon或Tes获得5+点,这些要点不是累积的(在2点之前必须获得5点在Mon或Tues)。我使用的守则如下,但迄今为止只包含一个具体星期一和星期二的日期范围。我需要所有星期一和星期二在2个月时间内发言。我是新鲜事(我以身殉职为额外义务,因此我必须了解这一飞行。

到目前为止,正如我所说的那样,我能够自己成功地掌握了我所需要的全部信息,但只有一周时间。我迄今在网上发现的所有东西都比我能够解释的更令人困惑,没有人提到我提供的守则,而是给我以我无法使用的全新法典。

SELECT     
     dbo.CombinedPts.Account, dbo.CombinedPts.FirstName, 
     dbo.CombinedPts.LastName, 
     ISNULL(dbo.CombinedPts.EGSPts, 0) AS EGS, 
     ISNULL(dbo.CombinedPts.IRPts, 0) AS IR, 
     DATENAME(dw, dbo.CombinedPts.Date) AS WkDay, 
     DATEDIFF(YY, dbo.PlayerMaster.Birthdate, {fn current_date()}) - CASE WHEN   (MONTH(dbo.PlayerMaster.Birthdate)=MONTH({fn current_date()}) AND DAY(dbo.PlayerMaster.Birthdate) > DAY({fn current_date()}) OR MONTH (dbo.PlayerMaster.Birthdate) > MONTH ({fn current_date()}))  THEN 1   ELSE 0   END   AS Age
FROM       
     dbo.CombinedPts, dbo.PlayerMaster 
WHERE      
     (DATEDIFF(YY, dbo.PlayerMaster.Birthdate, {fn current_date()}) >= 50) 
     AND (dbo.CombinedPts.EGSPts >= 5 OR dbo.CombinedPts.IRPts >= 5) 
     AND (dbo.CombinedPts.Account = dbo.PlayerMaster.Account) 
     AND (Date BETWEEN  10/11/2010 00:00:00  AND  10/11/2010 13:59:59  OR Date BETWEEN  10/12/2010 00:00:00  AND  10/12/2010 13:59:59 ) 
     AND (DATEPART(dw, dbo.CombinedPts.Date) = 2 OR DATEPART(dw, dbo.CombinedPts.Date) = 3)
GROUP BY   
     dbo.CombinedPts.Account, 
     dbo.CombinedPts.FirstName, 
     dbo.CombinedPts.LastName, 
     DATENAME(dw, dbo.CombinedPts.Date), 
     dbo.CombinedPts.EGSPts, 
     dbo.CombinedPts.IRPts, 
     dbo.PlayerMaster.Birthdate
ORDER BY
     dbo.CombinedPts.Account

作为副笔记,在2005年我们的服务器中,我没有许可设立分局(我已成为所有电离层用户,迄今为止在电离层中证明是无用的),我也没有许可制作表格,因此,我必须做的是一问......。迄今为止,所有已感知的帮助都一直在使用表格和子类。

Answer 1

它的结构差别不大,但大多相同。我使用了别处,而不是全处提及。它还未经测试,但教区是干净的。

<代码>AND DATEDIFF(m,cp.Date,GETDATE() < 2给你2个月窗口。

EDIT - 为国际日及时间添加条款。 AND (DATEPART(dw, cp.Date) in (2,3) and DATEPART(h,cp.Date) > 14)在2,00pm (1 400小时)之后仅星期一和星期二印发。

SELECT cp.Account
, cp.FirstName
, cp.LastName
, ISNULL(cp.EGSPts,0) AS EGS
, ISNULL(cp.IRPts,0) AS IR
, DATENAME(dw, cp.Date) AS WkDay
, DATEDIFF(YY, pm.Birthdate, GETDATE()) 
        - CASE WHEN (MONTH(pm.Birthdate) = MONTH(GETDATE()) 
        AND DAY(pm.Birthdate) > DAY(GETDATE()) 
        OR MONTH (pm.Birthdate) > MONTH (GETDATE()) )
        THEN 1 
        ELSE 0 
        END AS Age

FROM dbo.CombinedPts cp
join dbo.PlayerMaster pm on cp.Account = pm.Account

WHERE DATEDIFF(YY, pm.Birthdate, GETDATE()) >= 50
 AND (cp.EGSPts >= 5 OR cp.IRPts >= 5)
 AND (DATEPART(dw, cp.Date) in (2,3) AND DATEPART(hh,cp.Date) > 14)
 AND DATEDIFF(m,cp.Date,GETDATE()) < 2

GROUP BY cp.Account
, cp.FirstName
, cp.LastName
, DATENAME(dw, cp.Date)
, cp.EGSPts
, cp.IRPts
, pm.Birthdate

ORDER BY cp.Account

Answer 2

我不明白你想要达到什么目标。

我认为,你希望这样做:

select *
  from CombinedPts
 where CombinedPts.Date > start_of_contest
  and CombinedPts.Date < end_of_contest
  and ( datepart( dw, CombinedPts.Date ) = 2 or datepart( h, CombinedPts.Date ) < 14 )
  and ( datepart( dw, CombinedPts.Date ) = 3 or datepart( h, CombinedPts.Date ) < 14 )
 having sum( CombinedPts.EGSPts ) >= 5

日期在此用于确保时间准确。

友情链接