Question

我有一个查询，试图返回一个客户号和他们连续年成为客户的次数。它通过构建活动年份和客户列表来实现这一点，然后将其与可能的年份列表进行比较，并返回没有活动的最低年份。问题是，可能的年份列表是一个巨大的交叉连接。我认为，如果我能在MIN中烘焙EXCEPT逻辑，并重复使用我列出的10年可能的时间，这会运行得更快。

查询：

SELECT SUBSTRING(D,3,9) AS Cust, MIN(SUBSTRING(D,1,1)) AS Years FROM 
(SELECT DISTINCT
  CAST (y.years AS VARCHAR) +  -  + CAST(pm.BillToCustomerId AS VARCHAR ) AS D
   FROM [DW_Mart].[dbo].[vProMaster] pm
   cross join 
   (VALUES ( 0 ),( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 9 )) AS y(years)
        EXCEPT
    SELECT DISTINCT CAST (DATEDIFF(YEAR,[ShipmentDate],CURRENT_TIMESTAMP) AS VARCHAR)
 +  -  + CAST ([BillToCustomerId] AS VARCHAR ) AS D
   FROM [DW_Mart].[dbo].[vProMaster] pm ) AS X GROUP BY SUBSTRING(D,3,9)

我的伪代码修改了查询：

SELECT SUBSTRING(D,3,9) AS Cust, MIN((VALUES ( 0 ),( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 9 )) EXCEPT SUBSTRING(D,1,1)) AS Years FROM 
(SELECT DISTINCT CAST (DATEDIFF(YEAR,[ShipmentDate],CURRENT_TIMESTAMP) AS VARCHAR)
 +  -  + CAST ([BillToCustomerId] AS VARCHAR ) AS D
   FROM [DW_Mart].[dbo].[vProMaster] pm ) AS X GROUP BY SUBSTRING(D,3,9)

Answer 1

比如。。。

select billtocustomerid, max( DATEDIFF(YEAR,[ShipmentDate],CURRENT_TIMESTAMP))
from dw_mart.dbo.vpromaster
group by billtocustomerid

Answer 2

Turns out that the cross join crushed the server.
I’m not sure how proud I am of this puppy, but it works and it’s quick:

SELECT SUBSTRING(D,10,9) AS Cust,
CHARINDEX( 1 ,RIGHT( 00000000 +(CAST (11111111-SUM(CAST(SUBSTRING(D,1,8) AS INT)) AS VARCHAR)),8)) AS Years
  FROM 
(SELECT DISTINCT RIGHT( 00000000 +(CAST (POWER(10,(FLOOR(8-DATEDIFF(MONTH,[ShipmentDate],CURRENT_TIMESTAMP)/12))) AS VARCHAR)),8)
+  -  + CAST ([BillToCustomerId] AS VARCHAR ) AS D
FROM [DW_Mart].[dbo].[vProMaster] pm
where ShipmentDate < CURRENT_TIMESTAMP and ShipmentDate > DATEADD(YEAR,-8,CURRENT_TIMESTAMP)) AS X GROUP BY SUBSTRING(D,10,9)

友情链接