API tools faq
paste
jc4hokies

Lovely use of CASE

jc4hokies
Jun 8th, 2017
196
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 34.81 KB | None | 0 0
raw download clone embed print report
  1. CASE WHEN LoadDateKey < 0 OR DateOfServiceDateKey < 0 THEN NULL
  2.     ELSE DATEDIFF(DAY,CONVERT(DATE,CONVERT(CHAR(8),DateOfServiceDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),LoadDateKey,112))) --
  3.         - 2 * DATEDIFF(WEEK,CONVERT(DATE,CONVERT(CHAR(8),DateOfServiceDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),LoadDateKey,112)))
  4.         - CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),DateOfServiceDateKey,112))) = 1 THEN 1 ELSE 0 END
  5.         + CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),LoadDateKey,112))) = 1 THEN 1 ELSE 0 END
  6.         - CASE WHEN 20110117 >= DateOfServiceDateKey AND 20110117 < LoadDateKey THEN 1 ELSE 0 END
  7.         - CASE WHEN 20110221 >= DateOfServiceDateKey AND 20110221 < LoadDateKey THEN 1 ELSE 0 END
  8.         - CASE WHEN 20110530 >= DateOfServiceDateKey AND 20110530 < LoadDateKey THEN 1 ELSE 0 END
  9.         - CASE WHEN 20110704 >= DateOfServiceDateKey AND 20110704 < LoadDateKey THEN 1 ELSE 0 END
  10.         - CASE WHEN 20110905 >= DateOfServiceDateKey AND 20110905 < LoadDateKey THEN 1 ELSE 0 END
  11.         - CASE WHEN 20111010 >= DateOfServiceDateKey AND 20111010 < LoadDateKey THEN 1 ELSE 0 END
  12.         - CASE WHEN 20111111 >= DateOfServiceDateKey AND 20111111 < LoadDateKey THEN 1 ELSE 0 END
  13.         - CASE WHEN 20111124 >= DateOfServiceDateKey AND 20111124 < LoadDateKey THEN 1 ELSE 0 END
  14.         - CASE WHEN 20111226 >= DateOfServiceDateKey AND 20111226 < LoadDateKey THEN 1 ELSE 0 END
  15.         - CASE WHEN 20120102 >= DateOfServiceDateKey AND 20120102 < LoadDateKey THEN 1 ELSE 0 END
  16.         - CASE WHEN 20120116 >= DateOfServiceDateKey AND 20120116 < LoadDateKey THEN 1 ELSE 0 END
  17.         - CASE WHEN 20120220 >= DateOfServiceDateKey AND 20120220 < LoadDateKey THEN 1 ELSE 0 END
  18.         - CASE WHEN 20120528 >= DateOfServiceDateKey AND 20120528 < LoadDateKey THEN 1 ELSE 0 END
  19.         - CASE WHEN 20120704 >= DateOfServiceDateKey AND 20120704 < LoadDateKey THEN 1 ELSE 0 END
  20.         - CASE WHEN 20120903 >= DateOfServiceDateKey AND 20120903 < LoadDateKey THEN 1 ELSE 0 END
  21.         - CASE WHEN 20121018 >= DateOfServiceDateKey AND 20121018 < LoadDateKey THEN 1 ELSE 0 END
  22.         - CASE WHEN 20121112 >= DateOfServiceDateKey AND 20121112 < LoadDateKey THEN 1 ELSE 0 END
  23.         - CASE WHEN 20121122 >= DateOfServiceDateKey AND 20121122 < LoadDateKey THEN 1 ELSE 0 END
  24.         - CASE WHEN 20121225 >= DateOfServiceDateKey AND 20121225 < LoadDateKey THEN 1 ELSE 0 END
  25.         - CASE WHEN 20130101 >= DateOfServiceDateKey AND 20130101 < LoadDateKey THEN 1 ELSE 0 END
  26.         - CASE WHEN 20130121 >= DateOfServiceDateKey AND 20130121 < LoadDateKey THEN 1 ELSE 0 END
  27.         - CASE WHEN 20130218 >= DateOfServiceDateKey AND 20130218 < LoadDateKey THEN 1 ELSE 0 END
  28.         - CASE WHEN 20130527 >= DateOfServiceDateKey AND 20130527 < LoadDateKey THEN 1 ELSE 0 END
  29.         - CASE WHEN 20130704 >= DateOfServiceDateKey AND 20130704 < LoadDateKey THEN 1 ELSE 0 END
  30.         - CASE WHEN 20130902 >= DateOfServiceDateKey AND 20130902 < LoadDateKey THEN 1 ELSE 0 END
  31.         - CASE WHEN 20131014 >= DateOfServiceDateKey AND 20131014 < LoadDateKey THEN 1 ELSE 0 END
  32.         - CASE WHEN 20131111 >= DateOfServiceDateKey AND 20131111 < LoadDateKey THEN 1 ELSE 0 END
  33.         - CASE WHEN 20131128 >= DateOfServiceDateKey AND 20131128 < LoadDateKey THEN 1 ELSE 0 END
  34.         - CASE WHEN 20131225 >= DateOfServiceDateKey AND 20131225 < LoadDateKey THEN 1 ELSE 0 END
  35.         - CASE WHEN 20140101 >= DateOfServiceDateKey AND 20140101 < LoadDateKey THEN 1 ELSE 0 END
  36.         - CASE WHEN 20140120 >= DateOfServiceDateKey AND 20140120 < LoadDateKey THEN 1 ELSE 0 END
  37.         - CASE WHEN 20140217 >= DateOfServiceDateKey AND 20140217 < LoadDateKey THEN 1 ELSE 0 END
  38.         - CASE WHEN 20140526 >= DateOfServiceDateKey AND 20140526 < LoadDateKey THEN 1 ELSE 0 END
  39.         - CASE WHEN 20140704 >= DateOfServiceDateKey AND 20140704 < LoadDateKey THEN 1 ELSE 0 END
  40.         - CASE WHEN 20140901 >= DateOfServiceDateKey AND 20140901 < LoadDateKey THEN 1 ELSE 0 END
  41.         - CASE WHEN 20141013 >= DateOfServiceDateKey AND 20141013 < LoadDateKey THEN 1 ELSE 0 END
  42.         - CASE WHEN 20141111 >= DateOfServiceDateKey AND 20141111 < LoadDateKey THEN 1 ELSE 0 END
  43.         - CASE WHEN 20141127 >= DateOfServiceDateKey AND 20141127 < LoadDateKey THEN 1 ELSE 0 END
  44.         - CASE WHEN 20141225 >= DateOfServiceDateKey AND 20141225 < LoadDateKey THEN 1 ELSE 0 END
  45.         - CASE WHEN 20150101 >= DateOfServiceDateKey AND 20150101 < LoadDateKey THEN 1 ELSE 0 END
  46.         - CASE WHEN 20150119 >= DateOfServiceDateKey AND 20150119 < LoadDateKey THEN 1 ELSE 0 END
  47.         - CASE WHEN 20150216 >= DateOfServiceDateKey AND 20150216 < LoadDateKey THEN 1 ELSE 0 END
  48.         - CASE WHEN 20150525 >= DateOfServiceDateKey AND 20150525 < LoadDateKey THEN 1 ELSE 0 END
  49.         - CASE WHEN 20150703 >= DateOfServiceDateKey AND 20150703 < LoadDateKey THEN 1 ELSE 0 END
  50.         - CASE WHEN 20150907 >= DateOfServiceDateKey AND 20150907 < LoadDateKey THEN 1 ELSE 0 END
  51.         - CASE WHEN 20151012 >= DateOfServiceDateKey AND 20151012 < LoadDateKey THEN 1 ELSE 0 END
  52.         - CASE WHEN 20151111 >= DateOfServiceDateKey AND 20151111 < LoadDateKey THEN 1 ELSE 0 END
  53.         - CASE WHEN 20151126 >= DateOfServiceDateKey AND 20151126 < LoadDateKey THEN 1 ELSE 0 END
  54.         - CASE WHEN 20151225 >= DateOfServiceDateKey AND 20151225 < LoadDateKey THEN 1 ELSE 0 END
  55.         - CASE WHEN 20160101 >= DateOfServiceDateKey AND 20160101 < LoadDateKey THEN 1 ELSE 0 END
  56.         - CASE WHEN 20160118 >= DateOfServiceDateKey AND 20160118 < LoadDateKey THEN 1 ELSE 0 END
  57.         - CASE WHEN 20160215 >= DateOfServiceDateKey AND 20160215 < LoadDateKey THEN 1 ELSE 0 END
  58.         - CASE WHEN 20160530 >= DateOfServiceDateKey AND 20160530 < LoadDateKey THEN 1 ELSE 0 END
  59.         - CASE WHEN 20160704 >= DateOfServiceDateKey AND 20160704 < LoadDateKey THEN 1 ELSE 0 END
  60.         - CASE WHEN 20160905 >= DateOfServiceDateKey AND 20160905 < LoadDateKey THEN 1 ELSE 0 END
  61.         - CASE WHEN 20161010 >= DateOfServiceDateKey AND 20161010 < LoadDateKey THEN 1 ELSE 0 END
  62.         - CASE WHEN 20161111 >= DateOfServiceDateKey AND 20161111 < LoadDateKey THEN 1 ELSE 0 END
  63.         - CASE WHEN 20161124 >= DateOfServiceDateKey AND 20161124 < LoadDateKey THEN 1 ELSE 0 END
  64.         - CASE WHEN 20161226 >= DateOfServiceDateKey AND 20161226 < LoadDateKey THEN 1 ELSE 0 END
  65.         - CASE WHEN 20170102 >= DateOfServiceDateKey AND 20170102 < LoadDateKey THEN 1 ELSE 0 END
  66.         - CASE WHEN 20170116 >= DateOfServiceDateKey AND 20170116 < LoadDateKey THEN 1 ELSE 0 END
  67.         - CASE WHEN 20170220 >= DateOfServiceDateKey AND 20170220 < LoadDateKey THEN 1 ELSE 0 END
  68.         - CASE WHEN 20170529 >= DateOfServiceDateKey AND 20170529 < LoadDateKey THEN 1 ELSE 0 END
  69.         - CASE WHEN 20170704 >= DateOfServiceDateKey AND 20170704 < LoadDateKey THEN 1 ELSE 0 END
  70.         - CASE WHEN 20170904 >= DateOfServiceDateKey AND 20170904 < LoadDateKey THEN 1 ELSE 0 END
  71.         - CASE WHEN 20171009 >= DateOfServiceDateKey AND 20171009 < LoadDateKey THEN 1 ELSE 0 END
  72.         - CASE WHEN 20171110 >= DateOfServiceDateKey AND 20171110 < LoadDateKey THEN 1 ELSE 0 END
  73.         - CASE WHEN 20171123 >= DateOfServiceDateKey AND 20171123 < LoadDateKey THEN 1 ELSE 0 END
  74.         - CASE WHEN 20171225 >= DateOfServiceDateKey AND 20171225 < LoadDateKey THEN 1 ELSE 0 END
  75.         - CASE WHEN 20180101 >= DateOfServiceDateKey AND 20180101 < LoadDateKey THEN 1 ELSE 0 END
  76.         - CASE WHEN 20180115 >= DateOfServiceDateKey AND 20180115 < LoadDateKey THEN 1 ELSE 0 END
  77.         - CASE WHEN 20180219 >= DateOfServiceDateKey AND 20180219 < LoadDateKey THEN 1 ELSE 0 END
  78.         - CASE WHEN 20180528 >= DateOfServiceDateKey AND 20180528 < LoadDateKey THEN 1 ELSE 0 END
  79.         - CASE WHEN 20180704 >= DateOfServiceDateKey AND 20180704 < LoadDateKey THEN 1 ELSE 0 END
  80.         - CASE WHEN 20180903 >= DateOfServiceDateKey AND 20180903 < LoadDateKey THEN 1 ELSE 0 END
  81.         - CASE WHEN 20181008 >= DateOfServiceDateKey AND 20181008 < LoadDateKey THEN 1 ELSE 0 END
  82.         - CASE WHEN 20181112 >= DateOfServiceDateKey AND 20181112 < LoadDateKey THEN 1 ELSE 0 END
  83.         - CASE WHEN 20181122 >= DateOfServiceDateKey AND 20181122 < LoadDateKey THEN 1 ELSE 0 END
  84.         - CASE WHEN 20181225 >= DateOfServiceDateKey AND 20181225 < LoadDateKey THEN 1 ELSE 0 END
  85.         - CASE WHEN 20190101 >= DateOfServiceDateKey AND 20190101 < LoadDateKey THEN 1 ELSE 0 END
  86.         - CASE WHEN 20190121 >= DateOfServiceDateKey AND 20190121 < LoadDateKey THEN 1 ELSE 0 END
  87.         - CASE WHEN 20190218 >= DateOfServiceDateKey AND 20190218 < LoadDateKey THEN 1 ELSE 0 END
  88.         - CASE WHEN 20190527 >= DateOfServiceDateKey AND 20190527 < LoadDateKey THEN 1 ELSE 0 END
  89.         - CASE WHEN 20190704 >= DateOfServiceDateKey AND 20190704 < LoadDateKey THEN 1 ELSE 0 END
  90.         - CASE WHEN 20190902 >= DateOfServiceDateKey AND 20190902 < LoadDateKey THEN 1 ELSE 0 END
  91.         - CASE WHEN 20191014 >= DateOfServiceDateKey AND 20191014 < LoadDateKey THEN 1 ELSE 0 END
  92.         - CASE WHEN 20191111 >= DateOfServiceDateKey AND 20191111 < LoadDateKey THEN 1 ELSE 0 END
  93.         - CASE WHEN 20191128 >= DateOfServiceDateKey AND 20191128 < LoadDateKey THEN 1 ELSE 0 END
  94.         - CASE WHEN 20191225 >= DateOfServiceDateKey AND 20191225 < LoadDateKey THEN 1 ELSE 0 END
  95. END AS DateOfServiceToLoadLag,
  96. CASE WHEN LoadDateKey < 0 OR CreationDateKey < 0 THEN NULL
  97.     ELSE DATEDIFF(DAY,CONVERT(DATE,CONVERT(CHAR(8),LoadDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),CreationDateKey,112))) --
  98.         - 2 * DATEDIFF(WEEK,CONVERT(DATE,CONVERT(CHAR(8),LoadDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),CreationDateKey,112)))
  99.         - CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),LoadDateKey,112))) = 1 THEN 1 ELSE 0 END
  100.         + CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),CreationDateKey,112))) = 1 THEN 1 ELSE 0 END
  101.         - CASE WHEN 20110117 >= LoadDateKey AND 20110117 < CreationDateKey THEN 1 ELSE 0 END
  102.         - CASE WHEN 20110221 >= LoadDateKey AND 20110221 < CreationDateKey THEN 1 ELSE 0 END
  103.         - CASE WHEN 20110530 >= LoadDateKey AND 20110530 < CreationDateKey THEN 1 ELSE 0 END
  104.         - CASE WHEN 20110704 >= LoadDateKey AND 20110704 < CreationDateKey THEN 1 ELSE 0 END
  105.         - CASE WHEN 20110905 >= LoadDateKey AND 20110905 < CreationDateKey THEN 1 ELSE 0 END
  106.         - CASE WHEN 20111010 >= LoadDateKey AND 20111010 < CreationDateKey THEN 1 ELSE 0 END
  107.         - CASE WHEN 20111111 >= LoadDateKey AND 20111111 < CreationDateKey THEN 1 ELSE 0 END
  108.         - CASE WHEN 20111124 >= LoadDateKey AND 20111124 < CreationDateKey THEN 1 ELSE 0 END
  109.         - CASE WHEN 20111226 >= LoadDateKey AND 20111226 < CreationDateKey THEN 1 ELSE 0 END
  110.         - CASE WHEN 20120102 >= LoadDateKey AND 20120102 < CreationDateKey THEN 1 ELSE 0 END
  111.         - CASE WHEN 20120116 >= LoadDateKey AND 20120116 < CreationDateKey THEN 1 ELSE 0 END
  112.         - CASE WHEN 20120220 >= LoadDateKey AND 20120220 < CreationDateKey THEN 1 ELSE 0 END
  113.         - CASE WHEN 20120528 >= LoadDateKey AND 20120528 < CreationDateKey THEN 1 ELSE 0 END
  114.         - CASE WHEN 20120704 >= LoadDateKey AND 20120704 < CreationDateKey THEN 1 ELSE 0 END
  115.         - CASE WHEN 20120903 >= LoadDateKey AND 20120903 < CreationDateKey THEN 1 ELSE 0 END
  116.         - CASE WHEN 20121018 >= LoadDateKey AND 20121018 < CreationDateKey THEN 1 ELSE 0 END
  117.         - CASE WHEN 20121112 >= LoadDateKey AND 20121112 < CreationDateKey THEN 1 ELSE 0 END
  118.         - CASE WHEN 20121122 >= LoadDateKey AND 20121122 < CreationDateKey THEN 1 ELSE 0 END
  119.         - CASE WHEN 20121225 >= LoadDateKey AND 20121225 < CreationDateKey THEN 1 ELSE 0 END
  120.         - CASE WHEN 20130101 >= LoadDateKey AND 20130101 < CreationDateKey THEN 1 ELSE 0 END
  121.         - CASE WHEN 20130121 >= LoadDateKey AND 20130121 < CreationDateKey THEN 1 ELSE 0 END
  122.         - CASE WHEN 20130218 >= LoadDateKey AND 20130218 < CreationDateKey THEN 1 ELSE 0 END
  123.         - CASE WHEN 20130527 >= LoadDateKey AND 20130527 < CreationDateKey THEN 1 ELSE 0 END
  124.         - CASE WHEN 20130704 >= LoadDateKey AND 20130704 < CreationDateKey THEN 1 ELSE 0 END
  125.         - CASE WHEN 20130902 >= LoadDateKey AND 20130902 < CreationDateKey THEN 1 ELSE 0 END
  126.         - CASE WHEN 20131014 >= LoadDateKey AND 20131014 < CreationDateKey THEN 1 ELSE 0 END
  127.         - CASE WHEN 20131111 >= LoadDateKey AND 20131111 < CreationDateKey THEN 1 ELSE 0 END
  128.         - CASE WHEN 20131128 >= LoadDateKey AND 20131128 < CreationDateKey THEN 1 ELSE 0 END
  129.         - CASE WHEN 20131225 >= LoadDateKey AND 20131225 < CreationDateKey THEN 1 ELSE 0 END
  130.         - CASE WHEN 20140101 >= LoadDateKey AND 20140101 < CreationDateKey THEN 1 ELSE 0 END
  131.         - CASE WHEN 20140120 >= LoadDateKey AND 20140120 < CreationDateKey THEN 1 ELSE 0 END
  132.         - CASE WHEN 20140217 >= LoadDateKey AND 20140217 < CreationDateKey THEN 1 ELSE 0 END
  133.         - CASE WHEN 20140526 >= LoadDateKey AND 20140526 < CreationDateKey THEN 1 ELSE 0 END
  134.         - CASE WHEN 20140704 >= LoadDateKey AND 20140704 < CreationDateKey THEN 1 ELSE 0 END
  135.         - CASE WHEN 20140901 >= LoadDateKey AND 20140901 < CreationDateKey THEN 1 ELSE 0 END
  136.         - CASE WHEN 20141013 >= LoadDateKey AND 20141013 < CreationDateKey THEN 1 ELSE 0 END
  137.         - CASE WHEN 20141111 >= LoadDateKey AND 20141111 < CreationDateKey THEN 1 ELSE 0 END
  138.         - CASE WHEN 20141127 >= LoadDateKey AND 20141127 < CreationDateKey THEN 1 ELSE 0 END
  139.         - CASE WHEN 20141225 >= LoadDateKey AND 20141225 < CreationDateKey THEN 1 ELSE 0 END
  140.         - CASE WHEN 20150101 >= LoadDateKey AND 20150101 < CreationDateKey THEN 1 ELSE 0 END
  141.         - CASE WHEN 20150119 >= LoadDateKey AND 20150119 < CreationDateKey THEN 1 ELSE 0 END
  142.         - CASE WHEN 20150216 >= LoadDateKey AND 20150216 < CreationDateKey THEN 1 ELSE 0 END
  143.         - CASE WHEN 20150525 >= LoadDateKey AND 20150525 < CreationDateKey THEN 1 ELSE 0 END
  144.         - CASE WHEN 20150703 >= LoadDateKey AND 20150703 < CreationDateKey THEN 1 ELSE 0 END
  145.         - CASE WHEN 20150907 >= LoadDateKey AND 20150907 < CreationDateKey THEN 1 ELSE 0 END
  146.         - CASE WHEN 20151012 >= LoadDateKey AND 20151012 < CreationDateKey THEN 1 ELSE 0 END
  147.         - CASE WHEN 20151111 >= LoadDateKey AND 20151111 < CreationDateKey THEN 1 ELSE 0 END
  148.         - CASE WHEN 20151126 >= LoadDateKey AND 20151126 < CreationDateKey THEN 1 ELSE 0 END
  149.         - CASE WHEN 20151225 >= LoadDateKey AND 20151225 < CreationDateKey THEN 1 ELSE 0 END
  150.         - CASE WHEN 20160101 >= LoadDateKey AND 20160101 < CreationDateKey THEN 1 ELSE 0 END
  151.         - CASE WHEN 20160118 >= LoadDateKey AND 20160118 < CreationDateKey THEN 1 ELSE 0 END
  152.         - CASE WHEN 20160215 >= LoadDateKey AND 20160215 < CreationDateKey THEN 1 ELSE 0 END
  153.         - CASE WHEN 20160530 >= LoadDateKey AND 20160530 < CreationDateKey THEN 1 ELSE 0 END
  154.         - CASE WHEN 20160704 >= LoadDateKey AND 20160704 < CreationDateKey THEN 1 ELSE 0 END
  155.         - CASE WHEN 20160905 >= LoadDateKey AND 20160905 < CreationDateKey THEN 1 ELSE 0 END
  156.         - CASE WHEN 20161010 >= LoadDateKey AND 20161010 < CreationDateKey THEN 1 ELSE 0 END
  157.         - CASE WHEN 20161111 >= LoadDateKey AND 20161111 < CreationDateKey THEN 1 ELSE 0 END
  158.         - CASE WHEN 20161124 >= LoadDateKey AND 20161124 < CreationDateKey THEN 1 ELSE 0 END
  159.         - CASE WHEN 20161226 >= LoadDateKey AND 20161226 < CreationDateKey THEN 1 ELSE 0 END
  160.         - CASE WHEN 20170102 >= LoadDateKey AND 20170102 < CreationDateKey THEN 1 ELSE 0 END
  161.         - CASE WHEN 20170116 >= LoadDateKey AND 20170116 < CreationDateKey THEN 1 ELSE 0 END
  162.         - CASE WHEN 20170220 >= LoadDateKey AND 20170220 < CreationDateKey THEN 1 ELSE 0 END
  163.         - CASE WHEN 20170529 >= LoadDateKey AND 20170529 < CreationDateKey THEN 1 ELSE 0 END
  164.         - CASE WHEN 20170704 >= LoadDateKey AND 20170704 < CreationDateKey THEN 1 ELSE 0 END
  165.         - CASE WHEN 20170904 >= LoadDateKey AND 20170904 < CreationDateKey THEN 1 ELSE 0 END
  166.         - CASE WHEN 20171009 >= LoadDateKey AND 20171009 < CreationDateKey THEN 1 ELSE 0 END
  167.         - CASE WHEN 20171110 >= LoadDateKey AND 20171110 < CreationDateKey THEN 1 ELSE 0 END
  168.         - CASE WHEN 20171123 >= LoadDateKey AND 20171123 < CreationDateKey THEN 1 ELSE 0 END
  169.         - CASE WHEN 20171225 >= LoadDateKey AND 20171225 < CreationDateKey THEN 1 ELSE 0 END
  170.         - CASE WHEN 20180101 >= LoadDateKey AND 20180101 < CreationDateKey THEN 1 ELSE 0 END
  171.         - CASE WHEN 20180115 >= LoadDateKey AND 20180115 < CreationDateKey THEN 1 ELSE 0 END
  172.         - CASE WHEN 20180219 >= LoadDateKey AND 20180219 < CreationDateKey THEN 1 ELSE 0 END
  173.         - CASE WHEN 20180528 >= LoadDateKey AND 20180528 < CreationDateKey THEN 1 ELSE 0 END
  174.         - CASE WHEN 20180704 >= LoadDateKey AND 20180704 < CreationDateKey THEN 1 ELSE 0 END
  175.         - CASE WHEN 20180903 >= LoadDateKey AND 20180903 < CreationDateKey THEN 1 ELSE 0 END
  176.         - CASE WHEN 20181008 >= LoadDateKey AND 20181008 < CreationDateKey THEN 1 ELSE 0 END
  177.         - CASE WHEN 20181112 >= LoadDateKey AND 20181112 < CreationDateKey THEN 1 ELSE 0 END
  178.         - CASE WHEN 20181122 >= LoadDateKey AND 20181122 < CreationDateKey THEN 1 ELSE 0 END
  179.         - CASE WHEN 20181225 >= LoadDateKey AND 20181225 < CreationDateKey THEN 1 ELSE 0 END
  180.         - CASE WHEN 20190101 >= LoadDateKey AND 20190101 < CreationDateKey THEN 1 ELSE 0 END
  181.         - CASE WHEN 20190121 >= LoadDateKey AND 20190121 < CreationDateKey THEN 1 ELSE 0 END
  182.         - CASE WHEN 20190218 >= LoadDateKey AND 20190218 < CreationDateKey THEN 1 ELSE 0 END
  183.         - CASE WHEN 20190527 >= LoadDateKey AND 20190527 < CreationDateKey THEN 1 ELSE 0 END
  184.         - CASE WHEN 20190704 >= LoadDateKey AND 20190704 < CreationDateKey THEN 1 ELSE 0 END
  185.         - CASE WHEN 20190902 >= LoadDateKey AND 20190902 < CreationDateKey THEN 1 ELSE 0 END
  186.         - CASE WHEN 20191014 >= LoadDateKey AND 20191014 < CreationDateKey THEN 1 ELSE 0 END
  187.         - CASE WHEN 20191111 >= LoadDateKey AND 20191111 < CreationDateKey THEN 1 ELSE 0 END
  188.         - CASE WHEN 20191128 >= LoadDateKey AND 20191128 < CreationDateKey THEN 1 ELSE 0 END
  189.         - CASE WHEN 20191225 >= LoadDateKey AND 20191225 < CreationDateKey THEN 1 ELSE 0 END
  190. END AS LoadToCreationLag,
  191. CASE WHEN CreationDateKey < 0 OR PrimaryFileDateKey < 0 THEN NULL
  192.     ELSE DATEDIFF(DAY,CONVERT(DATE,CONVERT(CHAR(8),CreationDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),PrimaryFileDateKey,112))) --
  193.         - 2 * DATEDIFF(WEEK,CONVERT(DATE,CONVERT(CHAR(8),CreationDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),PrimaryFileDateKey,112)))
  194.         - CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),CreationDateKey,112))) = 1 THEN 1 ELSE 0 END
  195.         + CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),PrimaryFileDateKey,112))) = 1 THEN 1 ELSE 0 END
  196.         - CASE WHEN 20110117 >= CreationDateKey AND 20110117 < PrimaryFileDateKey THEN 1 ELSE 0 END
  197.         - CASE WHEN 20110221 >= CreationDateKey AND 20110221 < PrimaryFileDateKey THEN 1 ELSE 0 END
  198.         - CASE WHEN 20110530 >= CreationDateKey AND 20110530 < PrimaryFileDateKey THEN 1 ELSE 0 END
  199.         - CASE WHEN 20110704 >= CreationDateKey AND 20110704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  200.         - CASE WHEN 20110905 >= CreationDateKey AND 20110905 < PrimaryFileDateKey THEN 1 ELSE 0 END
  201.         - CASE WHEN 20111010 >= CreationDateKey AND 20111010 < PrimaryFileDateKey THEN 1 ELSE 0 END
  202.         - CASE WHEN 20111111 >= CreationDateKey AND 20111111 < PrimaryFileDateKey THEN 1 ELSE 0 END
  203.         - CASE WHEN 20111124 >= CreationDateKey AND 20111124 < PrimaryFileDateKey THEN 1 ELSE 0 END
  204.         - CASE WHEN 20111226 >= CreationDateKey AND 20111226 < PrimaryFileDateKey THEN 1 ELSE 0 END
  205.         - CASE WHEN 20120102 >= CreationDateKey AND 20120102 < PrimaryFileDateKey THEN 1 ELSE 0 END
  206.         - CASE WHEN 20120116 >= CreationDateKey AND 20120116 < PrimaryFileDateKey THEN 1 ELSE 0 END
  207.         - CASE WHEN 20120220 >= CreationDateKey AND 20120220 < PrimaryFileDateKey THEN 1 ELSE 0 END
  208.         - CASE WHEN 20120528 >= CreationDateKey AND 20120528 < PrimaryFileDateKey THEN 1 ELSE 0 END
  209.         - CASE WHEN 20120704 >= CreationDateKey AND 20120704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  210.         - CASE WHEN 20120903 >= CreationDateKey AND 20120903 < PrimaryFileDateKey THEN 1 ELSE 0 END
  211.         - CASE WHEN 20121018 >= CreationDateKey AND 20121018 < PrimaryFileDateKey THEN 1 ELSE 0 END
  212.         - CASE WHEN 20121112 >= CreationDateKey AND 20121112 < PrimaryFileDateKey THEN 1 ELSE 0 END
  213.         - CASE WHEN 20121122 >= CreationDateKey AND 20121122 < PrimaryFileDateKey THEN 1 ELSE 0 END
  214.         - CASE WHEN 20121225 >= CreationDateKey AND 20121225 < PrimaryFileDateKey THEN 1 ELSE 0 END
  215.         - CASE WHEN 20130101 >= CreationDateKey AND 20130101 < PrimaryFileDateKey THEN 1 ELSE 0 END
  216.         - CASE WHEN 20130121 >= CreationDateKey AND 20130121 < PrimaryFileDateKey THEN 1 ELSE 0 END
  217.         - CASE WHEN 20130218 >= CreationDateKey AND 20130218 < PrimaryFileDateKey THEN 1 ELSE 0 END
  218.         - CASE WHEN 20130527 >= CreationDateKey AND 20130527 < PrimaryFileDateKey THEN 1 ELSE 0 END
  219.         - CASE WHEN 20130704 >= CreationDateKey AND 20130704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  220.         - CASE WHEN 20130902 >= CreationDateKey AND 20130902 < PrimaryFileDateKey THEN 1 ELSE 0 END
  221.         - CASE WHEN 20131014 >= CreationDateKey AND 20131014 < PrimaryFileDateKey THEN 1 ELSE 0 END
  222.         - CASE WHEN 20131111 >= CreationDateKey AND 20131111 < PrimaryFileDateKey THEN 1 ELSE 0 END
  223.         - CASE WHEN 20131128 >= CreationDateKey AND 20131128 < PrimaryFileDateKey THEN 1 ELSE 0 END
  224.         - CASE WHEN 20131225 >= CreationDateKey AND 20131225 < PrimaryFileDateKey THEN 1 ELSE 0 END
  225.         - CASE WHEN 20140101 >= CreationDateKey AND 20140101 < PrimaryFileDateKey THEN 1 ELSE 0 END
  226.         - CASE WHEN 20140120 >= CreationDateKey AND 20140120 < PrimaryFileDateKey THEN 1 ELSE 0 END
  227.         - CASE WHEN 20140217 >= CreationDateKey AND 20140217 < PrimaryFileDateKey THEN 1 ELSE 0 END
  228.         - CASE WHEN 20140526 >= CreationDateKey AND 20140526 < PrimaryFileDateKey THEN 1 ELSE 0 END
  229.         - CASE WHEN 20140704 >= CreationDateKey AND 20140704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  230.         - CASE WHEN 20140901 >= CreationDateKey AND 20140901 < PrimaryFileDateKey THEN 1 ELSE 0 END
  231.         - CASE WHEN 20141013 >= CreationDateKey AND 20141013 < PrimaryFileDateKey THEN 1 ELSE 0 END
  232.         - CASE WHEN 20141111 >= CreationDateKey AND 20141111 < PrimaryFileDateKey THEN 1 ELSE 0 END
  233.         - CASE WHEN 20141127 >= CreationDateKey AND 20141127 < PrimaryFileDateKey THEN 1 ELSE 0 END
  234.         - CASE WHEN 20141225 >= CreationDateKey AND 20141225 < PrimaryFileDateKey THEN 1 ELSE 0 END
  235.         - CASE WHEN 20150101 >= CreationDateKey AND 20150101 < PrimaryFileDateKey THEN 1 ELSE 0 END
  236.         - CASE WHEN 20150119 >= CreationDateKey AND 20150119 < PrimaryFileDateKey THEN 1 ELSE 0 END
  237.         - CASE WHEN 20150216 >= CreationDateKey AND 20150216 < PrimaryFileDateKey THEN 1 ELSE 0 END
  238.         - CASE WHEN 20150525 >= CreationDateKey AND 20150525 < PrimaryFileDateKey THEN 1 ELSE 0 END
  239.         - CASE WHEN 20150703 >= CreationDateKey AND 20150703 < PrimaryFileDateKey THEN 1 ELSE 0 END
  240.         - CASE WHEN 20150907 >= CreationDateKey AND 20150907 < PrimaryFileDateKey THEN 1 ELSE 0 END
  241.         - CASE WHEN 20151012 >= CreationDateKey AND 20151012 < PrimaryFileDateKey THEN 1 ELSE 0 END
  242.         - CASE WHEN 20151111 >= CreationDateKey AND 20151111 < PrimaryFileDateKey THEN 1 ELSE 0 END
  243.         - CASE WHEN 20151126 >= CreationDateKey AND 20151126 < PrimaryFileDateKey THEN 1 ELSE 0 END
  244.         - CASE WHEN 20151225 >= CreationDateKey AND 20151225 < PrimaryFileDateKey THEN 1 ELSE 0 END
  245.         - CASE WHEN 20160101 >= CreationDateKey AND 20160101 < PrimaryFileDateKey THEN 1 ELSE 0 END
  246.         - CASE WHEN 20160118 >= CreationDateKey AND 20160118 < PrimaryFileDateKey THEN 1 ELSE 0 END
  247.         - CASE WHEN 20160215 >= CreationDateKey AND 20160215 < PrimaryFileDateKey THEN 1 ELSE 0 END
  248.         - CASE WHEN 20160530 >= CreationDateKey AND 20160530 < PrimaryFileDateKey THEN 1 ELSE 0 END
  249.         - CASE WHEN 20160704 >= CreationDateKey AND 20160704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  250.         - CASE WHEN 20160905 >= CreationDateKey AND 20160905 < PrimaryFileDateKey THEN 1 ELSE 0 END
  251.         - CASE WHEN 20161010 >= CreationDateKey AND 20161010 < PrimaryFileDateKey THEN 1 ELSE 0 END
  252.         - CASE WHEN 20161111 >= CreationDateKey AND 20161111 < PrimaryFileDateKey THEN 1 ELSE 0 END
  253.         - CASE WHEN 20161124 >= CreationDateKey AND 20161124 < PrimaryFileDateKey THEN 1 ELSE 0 END
  254.         - CASE WHEN 20161226 >= CreationDateKey AND 20161226 < PrimaryFileDateKey THEN 1 ELSE 0 END
  255.         - CASE WHEN 20170102 >= CreationDateKey AND 20170102 < PrimaryFileDateKey THEN 1 ELSE 0 END
  256.         - CASE WHEN 20170116 >= CreationDateKey AND 20170116 < PrimaryFileDateKey THEN 1 ELSE 0 END
  257.         - CASE WHEN 20170220 >= CreationDateKey AND 20170220 < PrimaryFileDateKey THEN 1 ELSE 0 END
  258.         - CASE WHEN 20170529 >= CreationDateKey AND 20170529 < PrimaryFileDateKey THEN 1 ELSE 0 END
  259.         - CASE WHEN 20170704 >= CreationDateKey AND 20170704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  260.         - CASE WHEN 20170904 >= CreationDateKey AND 20170904 < PrimaryFileDateKey THEN 1 ELSE 0 END
  261.         - CASE WHEN 20171009 >= CreationDateKey AND 20171009 < PrimaryFileDateKey THEN 1 ELSE 0 END
  262.         - CASE WHEN 20171110 >= CreationDateKey AND 20171110 < PrimaryFileDateKey THEN 1 ELSE 0 END
  263.         - CASE WHEN 20171123 >= CreationDateKey AND 20171123 < PrimaryFileDateKey THEN 1 ELSE 0 END
  264.         - CASE WHEN 20171225 >= CreationDateKey AND 20171225 < PrimaryFileDateKey THEN 1 ELSE 0 END
  265.         - CASE WHEN 20180101 >= CreationDateKey AND 20180101 < PrimaryFileDateKey THEN 1 ELSE 0 END
  266.         - CASE WHEN 20180115 >= CreationDateKey AND 20180115 < PrimaryFileDateKey THEN 1 ELSE 0 END
  267.         - CASE WHEN 20180219 >= CreationDateKey AND 20180219 < PrimaryFileDateKey THEN 1 ELSE 0 END
  268.         - CASE WHEN 20180528 >= CreationDateKey AND 20180528 < PrimaryFileDateKey THEN 1 ELSE 0 END
  269.         - CASE WHEN 20180704 >= CreationDateKey AND 20180704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  270.         - CASE WHEN 20180903 >= CreationDateKey AND 20180903 < PrimaryFileDateKey THEN 1 ELSE 0 END
  271.         - CASE WHEN 20181008 >= CreationDateKey AND 20181008 < PrimaryFileDateKey THEN 1 ELSE 0 END
  272.         - CASE WHEN 20181112 >= CreationDateKey AND 20181112 < PrimaryFileDateKey THEN 1 ELSE 0 END
  273.         - CASE WHEN 20181122 >= CreationDateKey AND 20181122 < PrimaryFileDateKey THEN 1 ELSE 0 END
  274.         - CASE WHEN 20181225 >= CreationDateKey AND 20181225 < PrimaryFileDateKey THEN 1 ELSE 0 END
  275.         - CASE WHEN 20190101 >= CreationDateKey AND 20190101 < PrimaryFileDateKey THEN 1 ELSE 0 END
  276.         - CASE WHEN 20190121 >= CreationDateKey AND 20190121 < PrimaryFileDateKey THEN 1 ELSE 0 END
  277.         - CASE WHEN 20190218 >= CreationDateKey AND 20190218 < PrimaryFileDateKey THEN 1 ELSE 0 END
  278.         - CASE WHEN 20190527 >= CreationDateKey AND 20190527 < PrimaryFileDateKey THEN 1 ELSE 0 END
  279.         - CASE WHEN 20190704 >= CreationDateKey AND 20190704 < PrimaryFileDateKey THEN 1 ELSE 0 END
  280.         - CASE WHEN 20190902 >= CreationDateKey AND 20190902 < PrimaryFileDateKey THEN 1 ELSE 0 END
  281.         - CASE WHEN 20191014 >= CreationDateKey AND 20191014 < PrimaryFileDateKey THEN 1 ELSE 0 END
  282.         - CASE WHEN 20191111 >= CreationDateKey AND 20191111 < PrimaryFileDateKey THEN 1 ELSE 0 END
  283.         - CASE WHEN 20191128 >= CreationDateKey AND 20191128 < PrimaryFileDateKey THEN 1 ELSE 0 END
  284.         - CASE WHEN 20191225 >= CreationDateKey AND 20191225 < PrimaryFileDateKey THEN 1 ELSE 0 END
  285. END AS CreationToPrimaryFileLag,
  286. CASE WHEN PrimaryFileDateKey < 0 OR FirstPaymentDateKey < 0 THEN NULL
  287.     ELSE DATEDIFF(DAY,CONVERT(DATE,CONVERT(CHAR(8),PrimaryFileDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),FirstPaymentDateKey,112))) --
  288.         - 2 * DATEDIFF(WEEK,CONVERT(DATE,CONVERT(CHAR(8),PrimaryFileDateKey,112)),CONVERT(DATE,CONVERT(CHAR(8),FirstPaymentDateKey,112)))
  289.         - CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),PrimaryFileDateKey,112))) = 1 THEN 1 ELSE 0 END
  290.         + CASE WHEN DATEPART(WEEKDAY,CONVERT(DATE,CONVERT(CHAR(8),FirstPaymentDateKey,112))) = 1 THEN 1 ELSE 0 END
  291.         - CASE WHEN 20110117 >= PrimaryFileDateKey AND 20110117 < FirstPaymentDateKey THEN 1 ELSE 0 END
  292.         - CASE WHEN 20110221 >= PrimaryFileDateKey AND 20110221 < FirstPaymentDateKey THEN 1 ELSE 0 END
  293.         - CASE WHEN 20110530 >= PrimaryFileDateKey AND 20110530 < FirstPaymentDateKey THEN 1 ELSE 0 END
  294.         - CASE WHEN 20110704 >= PrimaryFileDateKey AND 20110704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  295.         - CASE WHEN 20110905 >= PrimaryFileDateKey AND 20110905 < FirstPaymentDateKey THEN 1 ELSE 0 END
  296.         - CASE WHEN 20111010 >= PrimaryFileDateKey AND 20111010 < FirstPaymentDateKey THEN 1 ELSE 0 END
  297.         - CASE WHEN 20111111 >= PrimaryFileDateKey AND 20111111 < FirstPaymentDateKey THEN 1 ELSE 0 END
  298.         - CASE WHEN 20111124 >= PrimaryFileDateKey AND 20111124 < FirstPaymentDateKey THEN 1 ELSE 0 END
  299.         - CASE WHEN 20111226 >= PrimaryFileDateKey AND 20111226 < FirstPaymentDateKey THEN 1 ELSE 0 END
  300.         - CASE WHEN 20120102 >= PrimaryFileDateKey AND 20120102 < FirstPaymentDateKey THEN 1 ELSE 0 END
  301.         - CASE WHEN 20120116 >= PrimaryFileDateKey AND 20120116 < FirstPaymentDateKey THEN 1 ELSE 0 END
  302.         - CASE WHEN 20120220 >= PrimaryFileDateKey AND 20120220 < FirstPaymentDateKey THEN 1 ELSE 0 END
  303.         - CASE WHEN 20120528 >= PrimaryFileDateKey AND 20120528 < FirstPaymentDateKey THEN 1 ELSE 0 END
  304.         - CASE WHEN 20120704 >= PrimaryFileDateKey AND 20120704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  305.         - CASE WHEN 20120903 >= PrimaryFileDateKey AND 20120903 < FirstPaymentDateKey THEN 1 ELSE 0 END
  306.         - CASE WHEN 20121018 >= PrimaryFileDateKey AND 20121018 < FirstPaymentDateKey THEN 1 ELSE 0 END
  307.         - CASE WHEN 20121112 >= PrimaryFileDateKey AND 20121112 < FirstPaymentDateKey THEN 1 ELSE 0 END
  308.         - CASE WHEN 20121122 >= PrimaryFileDateKey AND 20121122 < FirstPaymentDateKey THEN 1 ELSE 0 END
  309.         - CASE WHEN 20121225 >= PrimaryFileDateKey AND 20121225 < FirstPaymentDateKey THEN 1 ELSE 0 END
  310.         - CASE WHEN 20130101 >= PrimaryFileDateKey AND 20130101 < FirstPaymentDateKey THEN 1 ELSE 0 END
  311.         - CASE WHEN 20130121 >= PrimaryFileDateKey AND 20130121 < FirstPaymentDateKey THEN 1 ELSE 0 END
  312.         - CASE WHEN 20130218 >= PrimaryFileDateKey AND 20130218 < FirstPaymentDateKey THEN 1 ELSE 0 END
  313.         - CASE WHEN 20130527 >= PrimaryFileDateKey AND 20130527 < FirstPaymentDateKey THEN 1 ELSE 0 END
  314.         - CASE WHEN 20130704 >= PrimaryFileDateKey AND 20130704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  315.         - CASE WHEN 20130902 >= PrimaryFileDateKey AND 20130902 < FirstPaymentDateKey THEN 1 ELSE 0 END
  316.         - CASE WHEN 20131014 >= PrimaryFileDateKey AND 20131014 < FirstPaymentDateKey THEN 1 ELSE 0 END
  317.         - CASE WHEN 20131111 >= PrimaryFileDateKey AND 20131111 < FirstPaymentDateKey THEN 1 ELSE 0 END
  318.         - CASE WHEN 20131128 >= PrimaryFileDateKey AND 20131128 < FirstPaymentDateKey THEN 1 ELSE 0 END
  319.         - CASE WHEN 20131225 >= PrimaryFileDateKey AND 20131225 < FirstPaymentDateKey THEN 1 ELSE 0 END
  320.         - CASE WHEN 20140101 >= PrimaryFileDateKey AND 20140101 < FirstPaymentDateKey THEN 1 ELSE 0 END
  321.         - CASE WHEN 20140120 >= PrimaryFileDateKey AND 20140120 < FirstPaymentDateKey THEN 1 ELSE 0 END
  322.         - CASE WHEN 20140217 >= PrimaryFileDateKey AND 20140217 < FirstPaymentDateKey THEN 1 ELSE 0 END
  323.         - CASE WHEN 20140526 >= PrimaryFileDateKey AND 20140526 < FirstPaymentDateKey THEN 1 ELSE 0 END
  324.         - CASE WHEN 20140704 >= PrimaryFileDateKey AND 20140704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  325.         - CASE WHEN 20140901 >= PrimaryFileDateKey AND 20140901 < FirstPaymentDateKey THEN 1 ELSE 0 END
  326.         - CASE WHEN 20141013 >= PrimaryFileDateKey AND 20141013 < FirstPaymentDateKey THEN 1 ELSE 0 END
  327.         - CASE WHEN 20141111 >= PrimaryFileDateKey AND 20141111 < FirstPaymentDateKey THEN 1 ELSE 0 END
  328.         - CASE WHEN 20141127 >= PrimaryFileDateKey AND 20141127 < FirstPaymentDateKey THEN 1 ELSE 0 END
  329.         - CASE WHEN 20141225 >= PrimaryFileDateKey AND 20141225 < FirstPaymentDateKey THEN 1 ELSE 0 END
  330.         - CASE WHEN 20150101 >= PrimaryFileDateKey AND 20150101 < FirstPaymentDateKey THEN 1 ELSE 0 END
  331.         - CASE WHEN 20150119 >= PrimaryFileDateKey AND 20150119 < FirstPaymentDateKey THEN 1 ELSE 0 END
  332.         - CASE WHEN 20150216 >= PrimaryFileDateKey AND 20150216 < FirstPaymentDateKey THEN 1 ELSE 0 END
  333.         - CASE WHEN 20150525 >= PrimaryFileDateKey AND 20150525 < FirstPaymentDateKey THEN 1 ELSE 0 END
  334.         - CASE WHEN 20150703 >= PrimaryFileDateKey AND 20150703 < FirstPaymentDateKey THEN 1 ELSE 0 END
  335.         - CASE WHEN 20150907 >= PrimaryFileDateKey AND 20150907 < FirstPaymentDateKey THEN 1 ELSE 0 END
  336.         - CASE WHEN 20151012 >= PrimaryFileDateKey AND 20151012 < FirstPaymentDateKey THEN 1 ELSE 0 END
  337.         - CASE WHEN 20151111 >= PrimaryFileDateKey AND 20151111 < FirstPaymentDateKey THEN 1 ELSE 0 END
  338.         - CASE WHEN 20151126 >= PrimaryFileDateKey AND 20151126 < FirstPaymentDateKey THEN 1 ELSE 0 END
  339.         - CASE WHEN 20151225 >= PrimaryFileDateKey AND 20151225 < FirstPaymentDateKey THEN 1 ELSE 0 END
  340.         - CASE WHEN 20160101 >= PrimaryFileDateKey AND 20160101 < FirstPaymentDateKey THEN 1 ELSE 0 END
  341.         - CASE WHEN 20160118 >= PrimaryFileDateKey AND 20160118 < FirstPaymentDateKey THEN 1 ELSE 0 END
  342.         - CASE WHEN 20160215 >= PrimaryFileDateKey AND 20160215 < FirstPaymentDateKey THEN 1 ELSE 0 END
  343.         - CASE WHEN 20160530 >= PrimaryFileDateKey AND 20160530 < FirstPaymentDateKey THEN 1 ELSE 0 END
  344.         - CASE WHEN 20160704 >= PrimaryFileDateKey AND 20160704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  345.         - CASE WHEN 20160905 >= PrimaryFileDateKey AND 20160905 < FirstPaymentDateKey THEN 1 ELSE 0 END
  346.         - CASE WHEN 20161010 >= PrimaryFileDateKey AND 20161010 < FirstPaymentDateKey THEN 1 ELSE 0 END
  347.         - CASE WHEN 20161111 >= PrimaryFileDateKey AND 20161111 < FirstPaymentDateKey THEN 1 ELSE 0 END
  348.         - CASE WHEN 20161124 >= PrimaryFileDateKey AND 20161124 < FirstPaymentDateKey THEN 1 ELSE 0 END
  349.         - CASE WHEN 20161226 >= PrimaryFileDateKey AND 20161226 < FirstPaymentDateKey THEN 1 ELSE 0 END
  350.         - CASE WHEN 20170102 >= PrimaryFileDateKey AND 20170102 < FirstPaymentDateKey THEN 1 ELSE 0 END
  351.         - CASE WHEN 20170116 >= PrimaryFileDateKey AND 20170116 < FirstPaymentDateKey THEN 1 ELSE 0 END
  352.         - CASE WHEN 20170220 >= PrimaryFileDateKey AND 20170220 < FirstPaymentDateKey THEN 1 ELSE 0 END
  353.         - CASE WHEN 20170529 >= PrimaryFileDateKey AND 20170529 < FirstPaymentDateKey THEN 1 ELSE 0 END
  354.         - CASE WHEN 20170704 >= PrimaryFileDateKey AND 20170704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  355.         - CASE WHEN 20170904 >= PrimaryFileDateKey AND 20170904 < FirstPaymentDateKey THEN 1 ELSE 0 END
  356.         - CASE WHEN 20171009 >= PrimaryFileDateKey AND 20171009 < FirstPaymentDateKey THEN 1 ELSE 0 END
  357.         - CASE WHEN 20171110 >= PrimaryFileDateKey AND 20171110 < FirstPaymentDateKey THEN 1 ELSE 0 END
  358.         - CASE WHEN 20171123 >= PrimaryFileDateKey AND 20171123 < FirstPaymentDateKey THEN 1 ELSE 0 END
  359.         - CASE WHEN 20171225 >= PrimaryFileDateKey AND 20171225 < FirstPaymentDateKey THEN 1 ELSE 0 END
  360.         - CASE WHEN 20180101 >= PrimaryFileDateKey AND 20180101 < FirstPaymentDateKey THEN 1 ELSE 0 END
  361.         - CASE WHEN 20180115 >= PrimaryFileDateKey AND 20180115 < FirstPaymentDateKey THEN 1 ELSE 0 END
  362.         - CASE WHEN 20180219 >= PrimaryFileDateKey AND 20180219 < FirstPaymentDateKey THEN 1 ELSE 0 END
  363.         - CASE WHEN 20180528 >= PrimaryFileDateKey AND 20180528 < FirstPaymentDateKey THEN 1 ELSE 0 END
  364.         - CASE WHEN 20180704 >= PrimaryFileDateKey AND 20180704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  365.         - CASE WHEN 20180903 >= PrimaryFileDateKey AND 20180903 < FirstPaymentDateKey THEN 1 ELSE 0 END
  366.         - CASE WHEN 20181008 >= PrimaryFileDateKey AND 20181008 < FirstPaymentDateKey THEN 1 ELSE 0 END
  367.         - CASE WHEN 20181112 >= PrimaryFileDateKey AND 20181112 < FirstPaymentDateKey THEN 1 ELSE 0 END
  368.         - CASE WHEN 20181122 >= PrimaryFileDateKey AND 20181122 < FirstPaymentDateKey THEN 1 ELSE 0 END
  369.         - CASE WHEN 20181225 >= PrimaryFileDateKey AND 20181225 < FirstPaymentDateKey THEN 1 ELSE 0 END
  370.         - CASE WHEN 20190101 >= PrimaryFileDateKey AND 20190101 < FirstPaymentDateKey THEN 1 ELSE 0 END
  371.         - CASE WHEN 20190121 >= PrimaryFileDateKey AND 20190121 < FirstPaymentDateKey THEN 1 ELSE 0 END
  372.         - CASE WHEN 20190218 >= PrimaryFileDateKey AND 20190218 < FirstPaymentDateKey THEN 1 ELSE 0 END
  373.         - CASE WHEN 20190527 >= PrimaryFileDateKey AND 20190527 < FirstPaymentDateKey THEN 1 ELSE 0 END
  374.         - CASE WHEN 20190704 >= PrimaryFileDateKey AND 20190704 < FirstPaymentDateKey THEN 1 ELSE 0 END
  375.         - CASE WHEN 20190902 >= PrimaryFileDateKey AND 20190902 < FirstPaymentDateKey THEN 1 ELSE 0 END
  376.         - CASE WHEN 20191014 >= PrimaryFileDateKey AND 20191014 < FirstPaymentDateKey THEN 1 ELSE 0 END
  377.         - CASE WHEN 20191111 >= PrimaryFileDateKey AND 20191111 < FirstPaymentDateKey THEN 1 ELSE 0 END
  378.         - CASE WHEN 20191128 >= PrimaryFileDateKey AND 20191128 < FirstPaymentDateKey THEN 1 ELSE 0 END
  379.         - CASE WHEN 20191225 >= PrimaryFileDateKey AND 20191225 < FirstPaymentDateKey THEN 1 ELSE 0 END
  380. END AS PrimaryFileToFirstPaymentLag
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!