добавление даты в функцию sql

У меня есть запрос в sql, который я написал на С#. Что делает запрос, вычисляя что-то вроде того, есть ли у меня ввод даты двух столбцов и нет. шаров как

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

вывод должен выглядеть так

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

то есть дата находится в интервале 7 дней, например, с 21/11/2013 по 27/11/2013, затем следующие 7 28/11/2013-04/12/2013 и т. д., но она должна рассматривать дату окончания только как дату окончания базы данных (как показано в 3-м ряду). я хочу создать функцию в sql, используя функцию create functionname (например, имя функции в качестве интервала), которая должна возвращать столбец даты вывода, чтобы я мог использовать эту функцию (dbo.interval) в моем запросе sql, написанном на С# как

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: запас представляет собой таблицу из двух столбцов дата и №. шаров и + Fromdate + и + Todate + отображаются в раскрывающемся списке в сетке. Это в основном верхний и нижний пределы дат, предоставленные пользователем, чтобы узнать информацию между этими двумя датами (в этом случае даты для верхнего и нижнего пределов 21.11.2013 и 07.12.2013 соответственно). Поэтому выходной файл будет выглядеть так:

новая дата нет. шаров 21.11.2013 -27.11.2013 25 28.11.2013-04.12.2013 42 05.12.2013-07.12.2013 3

я пытался использовать RecursiveCTE в sql, но это не очень помогает, так как дает только временный результат.


sql date sql-server-2008 datetime dateadd
person Community    schedule 23.10.2014    source источник
comment
Вы используете MS SQL 2008 или выше? Вы можете использовать предложение OVER ( msdn.microsoft.com/en-us/library/ ms189461.aspx) и попробуйте разбить DATEPART(wk, Date)   -  person Max Markov    schedule 23.10.2014
comment
Я использую SQL Server 2008. Но как написать функцию SQL для даты   -  person    schedule 23.10.2014
comment
Начинается ли интервал 7 дней с наименьшей даты в таблице Stock или с дня недели? Пример: Mon-Sun.   -  person Shiva    schedule 23.10.2014
comment
любая дата, выбранная пользователем из раскрывающегося списка, будет начальной датой. Расчет 7 дней начинается с этой даты. Выпадающий список содержит все даты из таблицы запасов.   -  person    schedule 23.10.2014
comment
давайте представим, что у вас есть дата начала, хранящаяся в переменной @ Fromdate. разница в днях между Fromdate и Date в вашей таблице равна DATEDIFF(d, @ Fromdate, Date) Теперь вы можете разделить на 7 - (DATEDIFF(d, @ Fromdate, Date)/7 AS INTEGER)   -  person Max Markov    schedule 23.10.2014
comment
Теперь вы можете SELECT SUM(Balls) OVER (PARTITION BY (DATEDIFF(d, @ Fromdate, Date) / 7 AS INTEGER)) FROM Table Я не уверен, что это сработает, но общее направление выглядит многообещающим (не могу попробовать, как не не запускайте MS SQL сейчас)   -  person Max Markov    schedule 23.10.2014
comment
так что это означает, что я получил верхний и нижний предел дат. Что, если в datediff @fromdate 12.05.2013 и дата 12.07.2013, она должна остановиться на 12.07.2013... как написать это в sql, пожалуйста, помогите, так как я новичок в этом -   -  person    schedule 23.10.2014
comment
Давайте снова свяжем эти два вопроса вместе: в функции"> stackoverflow.com/questions/26505657/   -  person Richard Hansell    schedule 24.10.2014

Ответы (2)


arrow_upward
0
arrow_downward

Самый простой способ решить ваш вопрос — использовать ROLLUP в группе по запросу (http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx)

Вот SQL Fiddle (http://sqlfiddle.com/#!3/3ce09/19)

Определение даты:

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);

Фактический запрос:

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

Результат:

LABEL                       BALLS
2013-11-21 - 2013-11-27     25
2013-11-28 - 2013-12-04     42
2013-12-05 - 2013-12-11     3
person Max Markov    schedule 23.10.2014

arrow_upward
0
arrow_downward

Я не думаю, что вам вообще нужно использовать функцию. Я взял тестовые данные Максима, немного подправил их и написал запрос, который возвращает нужные вам данные без обращения к 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;

Это дает следующие результаты:

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 значениями, предоставленными пользователем, заменив @Test на [Stock] и исправив соответственно имена столбцов?

person Richard Hansell    schedule 24.10.2014
comment
спасибо, все работает отлично, за исключением одной вещи, у меня есть функция в C# с именем TRY as public DataTable TRY (string FromDate, string ToDate) {string query = DECLARE StartDate DATE = '20110101'; DECLARE EndDate DATE = '20110119';.... остальная часть запроса, предложенная вами.} Мне нужно знать, как я могу объявить StartDate Date = Fromdate (исходит из функции TRY), аналогично EndDate DATE = ToDate (исходит из функция TRY) означает, что они получают значение из функции, а не присваивают - person ; 24.10.2014
comment
Есть два способа сделать это, неправильный способ - включить даты в строку запроса, например. DECLARE StartDate = + myStartDate.ToString(YYYYMMDD) + , EndDate = ... Правильный способ - рассматривать это как хранимую процедуру и добавлять даты в качестве параметров. Это может быть довольно сложно, но предотвращает внедрение SQL. - person Richard Hansell; 24.10.2014
comment
по этой причине я спросил о функции sql, чтобы в запросе, написанном на С#, я мог написать ..string query = @ select dbo.interval([Date]) newdate, IsNull(ROUND(sum(No. of balls),2), 0) [нет. шаров] со склада Где дата между Convert(Datetime,' + Fromdate + ',103) и Convert(Datetime,' + Todate + ',103) Group By dbo.interval([date]);.. в запросе dbo .interval([Date]) newdate - это функция, написанная с использованием интервала создания функции..... но, как вы предложили, я написал хранимую процедуру для даты и добавил дату в качестве параметра. Теперь, как упомянуть их в моем запросе sql написан на С# - person ; 24.10.2014