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#

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.

3 Years
Discussion Span
Last Post by Shark_1

More easy process if yo use C# codifications without creating Function in SQL.
Make a Do...Loop and do your job.
It should be likely

Do Until Date > ToDate

'''open Connection
'''SQL Statement with Where Clause Date beteen FromDate and ToDate

'''Pick Your Data from Database
'''Make calculation
'''Close Connection

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.