如何从表中显示存储过程中的所有日期。 [英] How to display all dates in stored procedure from table.?

问题描述

你好朋友，有人可以指导我如何编写商店程序查询



我的sql表：



日期|联系

20/10/15 | 3

21/10/15 | 1

22/10/15 | 5

24/10/15 | 10

27/10/15 | 3



这是我在插入时会得到的表格



这里我要写存储的查询程序



如果你看到那个日期里缺少第23和25个日期我想要0值



i想要获得存储过程的结果如

hello friends , Can somebody guide me how to write the store procedure query

my sql table :

dates | contacted
20/10/15| 3
21/10/15| 1
22/10/15| 5
24/10/15| 10
27/10/15| 3

this is table which i will get while inserting

here i want write the query for stored procedure

if u see table 23rd and 25th dates are missing in that dates i want 0 value

i want to get the stored procedure result like

 dates | contacted
20/10/15| 3
21/10/15| 1
23/10/15| 0
22/10/15| 5
24/10/15| 10
25/10/15| 0
26/10/15| 0
27/10/15| 3

有人可以指导我如何编写查询

can somebody guide me how to write the query

推荐答案

你可以试试这个

You can try this

DECLARE @MinDate DATE
DECLARE @MaxDate DATE
DECLARE @MissingDates TABLE ( [dates] DATE,[contacted] int )

SELECT @MinDate = min(dates) FROM [get_date]
SELECT @MaxDate = max(dates) FROM [get_date]

WHILE @MinDate <= @MaxDate
BEGIN
    IF NOT EXISTS (SELECT dates FROM [get_date]
                   WHERE dates = @MinDate)
        INSERT INTO @MissingDates ( [dates], [contacted])
        VALUES ( @MinDate,0 )

    SET @MinDate = DATEADD(day,1,@MinDate)
END

SELECT [dates], [contacted]  FROM [get_date]
UNION
SELECT [dates], [contacted]  FROM @MissingDates

1.在上面查询中，首先要确定最小日期和最长日期。

2.创建一个表变量，它将保存缺少的日期，并将其联系为0

3.如果不在原始表中，则将所有日期从最小值延伸到最大值将其插入表变量。

4.现在你有两个表第一个是你的原始表有日期，第二个是生成表变量，只有缺少日期与联系0

5.现在你必须从两个表中获取所有日期的数据。

1. In Above Query first you have to identify min date and max date .
2. Create a table variable which will hold the missing dates with contacted as 0
3. Loop through all the dates from min to max if it is not in your original table insert it in table variable.
4. Now you have two table First one is your original table having dates and contacted second one is generated table variable which have only missing dates with contacted 0
5. Now you have to take tha data from both tables which will have all the dates.

尝试使用 CTE [ ^ 然后将递归查询返回的数据与您的数据连接起来：

Try to use CTE[^] then join data returned by recursive query with your data:

;WITH AllDates AS
(
    --initial date
    SELECT CONVERT(DATE, '2015-10-20') AS Dates
    --recursive part
    UNION ALL
    SELECT DATEADD(DD, 1, Dates) AS Dates
    FROM AllDates
    WHERE DATEADD(DD, 1, Dates)< '2015-10-28'
)
SELECT t1.Dates, COALESCE(t2.Contacted, 0) AS Contacted
FROM AllDates As t1 LEFT JOIN YourTableName AS t2 ON t1.Dates = t2.Dates

如需了解更多信息，请参阅：

WITH common_table_expression（Transact-SQL） [ ^ ]

Recursive Queri使用公用表表达式 [ ^ ]

DATEADD [ ^ ]

视觉表现SQL联接 [ ^ ]



完整示例：

For further information, please see:
WITH common_table_expression (Transact-SQL)[^]
Recursive Queries Using Common Table Expressions[^]
DATEADD[^]
Visual Representation of SQL Joins[^]

Complete sample:

SET DATEFORMAT ymd;

DECLARE @YourTableName TABLE(dates DATE, contacted INT)

INSERT INTO @YourTableName (dates, contacted)
VALUES('2015-10-20',  3),
('2015-10-21',  1),
('2015-10-22',  5),
('2015-10-24',  10),
('2015-10-27',  3)


DECLARE @startDate DATE = '2015-10-20'
DECLARE @endDate DATE = '2015-10-28'

;WITH AllDates AS
(
    --initial date
    SELECT  @startDate AS Dates
    --recursive part
    UNION ALL
    SELECT DATEADD(DD, 1, Dates) AS Dates
    FROM AllDates
    WHERE DATEADD(DD, 1, Dates)<= @endDate
)
SELECT t1.Dates, COALESCE(t2.Contacted, 0) AS Contacted
FROM AllDates As t1 LEFT JOIN @YourTableName AS t2 ON t1.Dates = t2.Dates

退货：

Returns:

Dates	Contacted
2015-10-20	3
2015-10-21	1
2015-10-22	5
2015-10-23	0
2015-10-24	10
2015-10-25	0
2015-10-26	0
2015-10-27	3
2015-10-28	0

这篇关于如何从表中显示存储过程中的所有日期。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！