addition of date in sql function

问题:

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.

回答1:

The easiest way to solve your question is using ROLLUP in group by query (http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx)

Here is SQL Fiddle (http://sqlfiddle.com/#!3/3ce09/19)

Date definition:

create table test (
    Id  INT IDENTITY(1,1),
    date  DateTime,
    balls  int );
insert into test(date, balls) values('11/21/2013',  2);
insert into test(date, balls) values('11/22/2013',  3);
insert into test(date, balls) values('11/23/2013',  4);
insert into test(date, balls) values('11/24/2013',  5);
insert into test(date, balls) values('11/25/2013',  2);
insert into test(date, balls) values('11/26/2013',  5);
insert into test(date, balls) values('11/27/2013',  4);
insert into test(date, balls) values('11/28/2013',  3);
insert into test(date, balls) values('11/29/2013',  9);
insert into test(date, balls) values('11/30/2013',  8);
insert into test(date, balls) values('12/01/2013',  7);
insert into test(date, balls) values('12/02/2013',  4);
insert into test(date, balls) values('12/03/2013',  5);
insert into test(date, balls) values('12/04/2013',  6);
insert into test(date, balls) values('12/05/2013',  2);
insert into test(date, balls) values('12/06/2013',  0);
insert into test(date, balls) values('12/07/2013',  1);

Actual query:

select 
  label, 
  balls 
  from ( 
     SELECT  
       (DATEDIFF(d , '11/21/2013' , date) / 7) as week, 
       ((DATEDIFF(d , '11/21/2013' , date) / 7) + 1) as week1, 
       LEFT(CONVERT(VARCHAR, (DateAdd(d, (DATEDIFF(d , '11/21/2013' , date) / 7)*7, '11/21/2013')), 120), 10) +    ' - ' +  
       LEFT(CONVERT(VARCHAR, (DateAdd(d, ((DATEDIFF(d , '11/21/2013' , date) / 7) + 1)*7 - 1, '11/21/2013')), 120), 10) as label, 
       SUM(Balls) as balls 
      FROM test 
      GROUP BY rollup((DATEDIFF(d , '11/21/2013' , date) / 7))   
  ) as t 
  where t.label is not null

Result:

LABEL                       BALLS
2013-11-21 - 2013-11-27     25
2013-11-28 - 2013-12-04     42
2013-12-05 - 2013-12-11     3

回答2:

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?