在sql函数中添加日期 [英] addition of date in sql function
问题描述
日期球数
21/11/2013 2
22/11 / 2013 3
23/11/2013 4
24/11/2013 5
25/11/2013 2
26/11/2013 5
27/11 / 2013 4
28/11/2013 3
29/11/2013 9
30/11/2013 8
01/12/2013 7
02/12 / 2013 4
03/12/2013 5
04/12/2013 6
05/12/2013 2
06/12/2013 0
07/12 / 2013 1
输出应该看起来像
newdate no。的球
21/11/2013 -27/11/2013 25
28/11 / 2013-04 / 12/2013 42
05/12 / 2013-07 / 12/2013 3
ie日期为7天的时间间隔，如21/11/2013至27/11/2013，接下来的7 28/11 / 2013-04 / 12/2013等等，但应该将enddate考虑为仅数据库日期的结尾（如第3列所示）。
i想要使用create function functioname（例如函数名称作为interval）在sql中创建一个函数，该函数应该返回输出的date列，以便我可以在我的sql查询中使用该函数（dbo.interval）写入c＃
as
string query = @select dbo.interval（[Date]）newdate，
IsNull ROUND（总和（球数），2），0）[球数]
从股票
其中日期转换（日期时间，'+ Fromdate +'，103）和转换（Datetime，'+ Todate +'，103）
Group By dbo.interval（[date]）;
PS：股票是两列的日期和否。的球和+ Fromdate +和+ Todate +都是下拉式显示在grid.it中基本上是由用户提供的知道这两个日期之间的信息的日期的上限和下限（在这种情况下，上限和下限的日期是21/11/2013和07/12/2013分别）。因此输出文件将看起来像
newdate no。的球
21/11/2013 -27/11/2013 25
28/11 / 2013-04 / 12/2013 42
05/12 / 2013-07 / 12/2013 3
我已经尝试在sql中使用RecursiveCTE，但没有太多的帮助，因为它只提供一个临时输出。
我根本不认为你真的需要使用一个函数。我采取了Maksym的测试数据，整理了一下，并写了一个查询，带回您需要的数据，无需调用UDF：
code> DECLARE @Test TABLE（
Id INT IDENTITY（1,1），
[Date] DATE，
Balls INT）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131121'，2）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131122'，3）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131123'，4）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131124'，5）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131125'，2）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131126'，5）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131127'，4）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131128'，3）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131129'，9）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131130'，8）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131201'，7）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131202'，4）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131203'，5）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131204'，6）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131205'，2）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131206'，0）;
INSERT INTO @Test（[Date]，Balls）VALUES（'20131207'，1）;
DECLARE @StartDate DATE ='20131121';
DECLARE @EndDate DATE ='20131207';
WITH StartDates AS（
SELECT
CASE WHEN DATEDIFF（DAY，@StartDate，[Date]）％7 = 0 THEN [Date] END As StartDate
FROM
@Test），
EndDates AS（
SELECT
StartDate，
CASE WHEN DATEADD（DAY，6，StartDate）> @EndDate THEN @EndDate ELSE DATEADD DAY，6，StartDate）END AS EndDate
FROM
StartDates）
SELECT
ed.StartDate，
ed.EndDate，
CONVERT（VARCHAR（12 ），ed.StartDate，103）+' - '+ CONVERT（VARCHAR（12），ed.EndDate，103）AS NewDate，
ISNULL（ROUND（SUM（Balls），2），0）[Balls]
FROM
@Test t
INNER JOIN EndDates ed ON DATEDIFF（DAY，ed.StartDate，t。[Date]）BETWEEN 0 AND 7 AND DATEDIFF（DAY，t。[Date] ed.EndDate）BETWEEN 0 AND 7
WHERE
[日期] BETWEEN @StartDate和@EndDate
GROUP BY
ed.StartDate，
ed.EndDate，
CONVERT（VARCHAR（12），ed.StartDate，103）+' - '+ CONVERT（VARCHAR（12），ed .EndDate，103）
ORDER BY
ed.StartDate;
这将给出以下结果：
StartDate EndDate NewDate Balls
2013-11-21 2013-11-27 21/11/2013 - 27/11/2013 25
2013-11-28 2013- 12-04 28/11/2013 - 04/12/2013 42
2013-12-05 2013-12-07 05/12/2013 - 07/12/2013 3
如果将我的查询转换为与[Stock]表相对应的工作，那么您应该可以将其嵌入到.NET解决方案中，替换@StartDate和@EndDate与用户提供的值，用[Stock]替换@Test并相应地修改列名称？
I have a query in sql which i wrote in C#.what the query do is, calculating something like if i have an input of two columns date and no. of balls as
Date No. of balls
21/11/2013 2
22/11/2013 3
23/11/2013 4
24/11/2013 5
25/11/2013 2
26/11/2013 5
27/11/2013 4
28/11/2013 3
29/11/2013 9
30/11/2013 8
01/12/2013 7
02/12/2013 4
03/12/2013 5
04/12/2013 6
05/12/2013 2
06/12/2013 0
07/12/2013 1
the output should be look like
newdate no. of balls
21/11/2013 -27/11/2013 25
28/11/2013-04/12/2013 42
05/12/2013-07/12/2013 3
i.e. the date is in the interval of 7 days like 21/11/2013 to 27/11/2013 then next 7 28/11/2013-04/12/2013 and so on but it should consider enddate as end of database date only(as shows in 3rd row). i want to create a function in sql using create function functioname (eg function name as interval) that should return the date column of output so that i can use that function(dbo.interval) in my sql query written in c# as
string query = @" select dbo.interval([Date]) newdate,
IsNull(ROUND(sum(No. of balls),2),0) [no. of balls]
from stock
Where date between Convert(Datetime,'" + Fromdate + "',103) and Convert(Datetime,'" + Todate + "',103)
Group By dbo.interval([date])";
PS: stock is a table of two columns date and no. of balls and + Fromdate + and + Todate + are drop-down displayed in grid.it is basically upper and lower limit of dates supplied by user to know the information between those two dates(in this case the dates for upper and lower limits are 21/11/2013 and 07/12/2013 respectively).therefore output file will be look like
newdate no. of balls 21/11/2013 -27/11/2013 25 28/11/2013-04/12/2013 42 05/12/2013-07/12/2013 3
i had tried using RecursiveCTE in sql but it is not of much help as it gives only a temporary output.
I don't think you actually need to use a function at all. I took Maksym's test data, tidied it up a bit and wrote a query that brings back the data you need without needing to call out to a UDF:
DECLARE @Test TABLE (
Id INT IDENTITY(1,1),
[Date] DATE,
Balls INT);
INSERT INTO @Test([Date], Balls) VALUES('20131121', 2);
INSERT INTO @Test([Date], Balls) VALUES('20131122', 3);
INSERT INTO @Test([Date], Balls) VALUES('20131123', 4);
INSERT INTO @Test([Date], Balls) VALUES('20131124', 5);
INSERT INTO @Test([Date], Balls) VALUES('20131125', 2);
INSERT INTO @Test([Date], Balls) VALUES('20131126', 5);
INSERT INTO @Test([Date], Balls) VALUES('20131127', 4);
INSERT INTO @Test([Date], Balls) VALUES('20131128', 3);
INSERT INTO @Test([Date], Balls) VALUES('20131129', 9);
INSERT INTO @Test([Date], Balls) VALUES('20131130', 8);
INSERT INTO @Test([Date], Balls) VALUES('20131201', 7);
INSERT INTO @Test([Date], Balls) VALUES('20131202', 4);
INSERT INTO @Test([Date], Balls) VALUES('20131203', 5);
INSERT INTO @Test([Date], Balls) VALUES('20131204', 6);
INSERT INTO @Test([Date], Balls) VALUES('20131205', 2);
INSERT INTO @Test([Date], Balls) VALUES('20131206', 0);
INSERT INTO @Test([Date], Balls) VALUES('20131207', 1);
DECLARE @StartDate DATE = '20131121';
DECLARE @EndDate DATE = '20131207';
WITH StartDates AS (
SELECT
CASE WHEN DATEDIFF(DAY, @StartDate, [Date]) % 7 = 0 THEN [Date] END AS StartDate
FROM
@Test),
EndDates AS (
SELECT
StartDate,
CASE WHEN DATEADD(DAY, 6, StartDate) > @EndDate THEN @EndDate ELSE DATEADD(DAY, 6, StartDate) END AS EndDate
FROM
StartDates)
SELECT
ed.StartDate,
ed.EndDate,
CONVERT(VARCHAR(12), ed.StartDate, 103) + ' - ' + CONVERT(VARCHAR(12), ed.EndDate, 103) AS NewDate,
ISNULL(ROUND(SUM(Balls), 2), 0) [Balls]
FROM
@Test t
INNER JOIN EndDates ed ON DATEDIFF(DAY, ed.StartDate, t.[Date]) BETWEEN 0 AND 7 AND DATEDIFF(DAY, t.[Date], ed.EndDate) BETWEEN 0 AND 7
WHERE
[Date] BETWEEN @StartDate AND @EndDate
GROUP BY
ed.StartDate,
ed.EndDate,
CONVERT(VARCHAR(12), ed.StartDate, 103) + ' - ' + CONVERT(VARCHAR(12), ed.EndDate, 103)
ORDER BY
ed.StartDate;
This gives the following results:
StartDate EndDate NewDate Balls
2013-11-21 2013-11-27 21/11/2013 - 27/11/2013 25
2013-11-28 2013-12-04 28/11/2013 - 04/12/2013 42
2013-12-05 2013-12-07 05/12/2013 - 07/12/2013 3
If you convert my query to work against your [Stock] table then you should be able to embed this into your .NET solution, replacing @StartDate and @EndDate with the values supplied by the user, replacing @Test with [Stock] and fixing up the column names accordingly?
这篇关于在sql函数中添加日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
