SQL Server手动添加记录记录到视图

问题描述：

我有一个视图，其中包含下图中显示的数据。该视图显示当前财政年度每个学校/银行假期中每个月有多少个工作日可用。SQL Server手动添加记录记录到视图

由于八月份的月份有零天，因此本月已从视图中排除。

由于8月份的可用天数总是零，因此硬编码SQL在8月始终为0，而且4月至8月的记录与4月相同-七月。

什么是添加这两个记录的最佳方式，并在有关的代码应该把它放在看到示例代码布局：

见链接（回答问题）对代码的布局：

SQL populate total working days per month minus bank holidays for current financial year

enter image description here

答

我的回答，我会假设你有带有与您的屏幕截图匹配的列的视图vDays：period，availabledays,year。

要附加任何零天时间到结果的任何一个月可能为零（这将迎合月和其他月份，恰好拥有零天），你可以扩展你的看法是这样的：

WITH Mths (Mth) AS (
    SELECT 'January' 
    UNION SELECT 'February' 
    UNION SELECT 'March' 
    UNION SELECT 'April' 
    UNION SELECT 'May' 
    UNION SELECT 'June' 
    UNION SELECT 'July' 
    UNION SELECT 'August' 
    UNION SELECT 'September' 
    UNION SELECT 'October' 
    UNION SELECT 'November' 
    UNION SELECT 'December' 
    UNION SELECT 'April - January' 
    UNION SELECT 'April - February' 
    UNION SELECT 'April - March' 
    UNION SELECT 'April - May' 
    UNION SELECT 'April - June' 
    UNION SELECT 'April - July' 
    UNION SELECT 'April - August' 
    UNION SELECT 'April - September' 
    UNION SELECT 'April - October' 
    UNION SELECT 'April - November' 
    UNION SELECT 'April - December' 

), Years (Year) AS (
    SELECT DISTINCT year 
    FROM vDays 

), ZeroPeriods (Mth, Years) AS (
    SELECT Mth, Year 
    FROM Mths, Years 

), JoinedData (Mth, AvailableDays, Year) AS (
    SELECT Mth, 0, Years 
    FROM ZeroPeriods 
    UNION ALL 
    SELECT period, availabledays, year 
    FROM vDays 

), GroupedData (Mth, AvailableDays, Year) AS (
    SELECT Mth, SUM(AvailableDays), Year 
    FROM JoinedData 
    GROUP BY Mth, Year 

) 
SELECT * 
FROM GroupedData 
ORDER BY Year, CASE UPPER(LEFT(Mth, 3)) 
       WHEN 'JAN' THEN 1 WHEN 'FEB' THEN 2 WHEN 'MAR' THEN 3 
       WHEN 'APR' THEN 4 WHEN 'MAY' THEN 5 WHEN 'JUN' THEN 6 
       WHEN 'JUL' THEN 7 WHEN 'AUG' THEN 8 WHEN 'SEP' THEN 9 
       WHEN 'OCT' THEN 10 WHEN 'NOV' THEN 11 ELSE 12 END;

我已经把它分解成许多单独的查询，虽然有些可以合并到子查询中，但是像这样做会让它理解得更清晰。

答

这会给你你想要的结果集吗？

SELECT Period, DaysAvailable, Year FROM YOURVIEW 
UNION ALL 
SELECT DISTINCT 'April-August', DaysAvailable, Year FROM YOURVIEW where Period = 'April-July' 
UNION ALL 
SELECT DISTINCT 'August', 0, YEAR FROM YOURVIEW

SQL Server手动添加记录记录到视图

相关推荐