Sql：将位数按顺序滚动 [英] Sql: count bits as rolling sequentially
问题描述
我正在尝试使用我的位列day_business_day_flag作为将滚动工作日计为另一个值rolling_business_day_sequence的方法。 day_business_day_flag设置为查找日期是假日，工作日/周末，如果非假日和非周末则返回1，如果日期是假日或周末则返回0。我正在尝试将rolling_business_day_sequence设置为将其前一行值添加为一种滞后函数或另一种计算特定日期过去的工作日数的方式。
[rolling_day_sequence] = datediff（day，@ StartDate，day_date）+1，
我用它来计算正常滚动天数序列。这从一开始，每天增加另一个数字。我想用我的day_business_day_flag栏做同样的事情
day_business_day_flag是一个比特值，如果day_date是非周末/非假日，则返回1
rolling_business_day_sequence是我正在尝试填充的列。
@startdate是01/01/2000
@enddate是12/31/2050
day_date是行的日期，可以是在2000-01-01和2050-12-31之间的任何地方。
如果需要更多信息才能让这个工作。请告诉我。
我得到列名或数字提供的值与表定义不匹配。大部分时间。
我尝试过：
我尝试在@startdate和day_date之间选择rolling_business_day_sequence = COUNT（day_business_day_flag）--WHERE day_date> = @StartDate和day_date< = @EndDate AS rolling_business_day_sequence
我也试过
SELECT day_date，SUM（CASE WHEN day_business_day_flag = 1然后1 else 0 END）AS rolling_business_day_sequence FROM CALENDAR_DIM group by day_date;
另一个尝试
SELECT day_date，day_business_day_flag，count（day_business_day_flag）FROM CALENDAR_DIM as rolling_business_day_sequence其中day_business_day_flag = 1 group by day_date，day_business_day_flag
INSERT INTO CALENDAR_DIM
--SELECT SUM（CASE（day_business_day_flag）当1那么1 0结束时）作为rolling_business_day_sequence来自CALENDAR_DIM
INSERT INTO CALENDAR_DIM
SELECT COUNT（NULLIF（day_business_） day_flag，0））AS rolling_business_day_sequence FROM CALENDAR_DIM
新的尝试截至2019年4月3日
UPDATE [EDW_MDM]。[dbo]。[FCFCU_CALENDAR_DIM] SET
business_day_flag_int =（从FCFCU_CALENDAR_DIM中选择CAST（day_business_day_flag AS INT）） ，
lag_rolling_business_day_sequence =（SELECT LAG（day_business_day_flag，1）OVER（ORDER BY fcfcu_calendar_dim_id）FROM FCFCU_CALENDAR_DIM）
UPDATE [EDW_MDM]。[dbo]。[FCFCU_CALENDAR_DIM] SET
rolling_business_day_sequence = business_day_flag_int + lag_rolling_business_day_sequence
我收到错误Subquery返回的值超过1。当子查询跟随=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。当它自己运行时，我会看到01/01的day_date / 2000在2000年2月1日之前重复了大约200次。任何建议都会非常感激，因为我觉得我接近我正在寻找的东西。
< blockquote>如果我已经理解你要做什么，这样的事情应该有效：
UPDATE
T
SET
rolling_business_day_sequence = 1 +
（
SELECT 计数（ 1 ）
FROM [EDW_MDM]。[dbo]。[FCFCU_CALENDAR_DIM] 作为 L
WHERE L.day_business_day_flag = 1
和 L.day_date< T.day_date
）
FROM
[EDW_MDM]。[dbo]。[FCFCU_CALENDAR_DIM] 作为 T
WHERE
T.day_business_day_flag = 1
;
看看这篇文章：构建没有预先存在的表的日历 [ ^ ]
如果你不想使用只需这样做：
UPPER（DATENAME（WEEKDAY， yourDateField ）） NOT IN （' SUNDAY'，' MONDAY'）
来评估它是否是一个周末的日子（如果那是你的弯曲，你也可以使用日期数字。）
I am trying to use my bit column "day_business_day_flag" as a way to count rolling business days as another value "rolling_business_day_sequence". day_business_day_flag is set to find if the day is a holiday, weekday/weekend and returns a 1 if it's non holiday and non weekend and 0 is if the day is a holiday or weekend. I am trying to set rolling_business_day_sequence to either add the previous row value for it as a sort of lag function or another way to calculate how many business days have past to the specific date.
[rolling_day_sequence] = datediff(day,@StartDate,day_date)+1,
I use this to count the normal rolling days sequence. This starts at one and everyday adds another number. I want to do the same with my day_business_day_flag column
day_business_day_flag is a bit value that returns a 1 if day_date is a non-weekend/non-holiday
rolling_business_day_sequence is the column I'm trying to populate.
@startdate is 01/01/2000
@enddate is 12/31/2050
day_date is the date of the row which can be anywhere between 2000-01-01 and 2050-12-31.
Let me know if more info is needed to get this working.
I get "Column name or number of supplied values does not match table definition." Most of the time.
What I have tried:
I tried doing
SELECT rolling_business_day_sequence = COUNT(day_business_day_flag) between @startdate and day_date --WHERE day_date >= @StartDate and day_date <= @EndDate AS rolling_business_day_sequence
I also tried
SELECT day_date, SUM(CASE WHEN day_business_day_flag = 1 then 1 else 0 END) AS rolling_business_day_sequence FROM CALENDAR_DIM group by day_date;
Another Try
SELECT day_date, day_business_day_flag, count(day_business_day_flag) FROM CALENDAR_DIM as rolling_business_day_sequence where day_business_day_flag = 1 group by day_date, day_business_day_flag
INSERT INTO CALENDAR_DIM --SELECT SUM(CASE(day_business_day_flag) WHEN 1 THEN 1 ELSE 0 END) AS rolling_business_day_sequence FROM CALENDAR_DIM
INSERT INTO CALENDAR_DIM SELECT COUNT(NULLIF(day_business_day_flag,0)) AS rolling_business_day_sequence FROM CALENDAR_DIM
New Try as of April 3rd, 2019
UPDATE [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] SET business_day_flag_int = (SELECT CAST(day_business_day_flag AS INT) FROM FCFCU_CALENDAR_DIM), lag_rolling_business_day_sequence = (SELECT LAG(day_business_day_flag, 1) OVER (ORDER BY fcfcu_calendar_dim_id) FROM FCFCU_CALENDAR_DIM) UPDATE [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] SET rolling_business_day_sequence = business_day_flag_int +lag_rolling_business_day_sequence
I'm getting the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." When ran as it's own I see the day_date for 01/01/2000 repeated roughly 200 times before it goes to 01/02/2000. Any suggestions will be greatly appreciated because I feel I am close to what I am looking for.
If I've understood what you're trying to do, something like this should work:
UPDATE T SET rolling_business_day_sequence = 1 + ( SELECT Count(1) FROM [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] As L WHERE L.day_business_day_flag = 1 And L.day_date < T.day_date ) FROM [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] As T WHERE T.day_business_day_flag = 1 ;
Look at this article: Build a Calendar Without Pre-Existing Tables[^]
If you don't feel like using that, simply do this:
UPPER(DATENAME(WEEKDAY, yourDateField )) NOT IN ('SUNDAY','MONDAY')
to evaluate if it's a weekend day (you could also use day number if that's your bent).
这篇关于Sql：将位数按顺序滚动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
