SQL-запрос с функцией

У меня есть таблица данных, которую я использую оператор подсчета, чтобы получить количество записей для примера даты представления

AuditId Date    Crew    Shift   Cast    ObservedBy  2ndObserver AuditType   Product
16  2017-06-27  3   Day         B1974, B1975    Glen Mason  NULL    Identification  Billet    
20  2017-06-29  1   Day         9879    Corey Lundy NULL    Identification  Billet
21  2017-06-29  4   Day         T9627, T9625    Joshua Dwyer    NULL    ShippingPad Tee       
22  2017-06-29  4   Day         NULL    Joshua Dwyer    NULL    Identification  Billet    
23  2017-06-29  4   Day         S9874   Joshua Dwyer    NULL    ShippingPad Slab      
24  2017-06-29  4   Day         Bay 40  Joshua Dwyer    NULL    Identification  Billet

В основном я использую следующий код, чтобы получить свои результаты

SELECT YEAR([Date]) as YEAR, CAST([Date] as nvarchar(25)) AS [Date], COUNT(*) as "Audit Count"
        FROM    AuditResults
        where AuditType = 'Identification' AND Product = 'Billet' 
        group by Date

это возвращает пример

YEAR  Date  Audit Count
2017    2017-06-27  1
2017    2017-06-29  3

Теперь я хочу получить все даты, даже если они не указаны, поэтому я хочу, чтобы возврат был

YEAR  Date  Audit Count
2017  2017-06-27    1
2017  2017-06-28    0
2017  2017-06-29    3

У меня есть следующая функция, которую я пытаюсь использовать:

ALTER FUNCTION [dbo].[fnGetDatesInRange]
(   
    @FromDate datetime,
    @ToDate datetime
)
RETURNS @DateList TABLE (Dt date) 
AS
BEGIN
    DECLARE @TotalDays int, @DaysCount int


      SET @TotalDays =  DATEDIFF(dd,@FromDate,@ToDate)
      SET @DaysCount = 0


        WHILE @TotalDays >= @DaysCount
        BEGIN
                INSERT INTO @DateList
                SELECT (@ToDate - @DaysCount) AS DAT


                SET @DaysCount = @DaysCount + 1
        END
        RETURN
END

Как мне использовать мой оператор выбора с этой функцией? или есть лучший способ? ура

Источник

2 ответа

Решение

Попробуй это;

ALTER FUNCTION [dbo].[fnGetDatesInRange]
(   
    @FromDate datetime,
    @ToDate datetime
)
RETURNS @YourData TABLE ([Year] int, DateText nvarchar(25),[Audit Count] int) 
AS
begin
insert into @YourData
SELECT 
    YEAR(allDates.[Date]) as YEAR, 
    CAST(allDates.[Date] as nvarchar(25)) AS [Date], 
    COUNT(r.Product) as "Audit Count"
from
    (
    SELECT 
        [date]=convert(datetime, CONVERT(float,d.Seq))
    FROM 
        (
        select top 100000 row_number() over(partition by 1 order by A.name) as Seq 
        from syscolumns A, syscolumns B  
        )d 
    )allDates
left join
    AuditResults r on r.[Date]=allDates.[date] and  r.AuditType = 'Identification' AND r.Product = 'Billet' 
where
    allDates.[Date]>=@FromDate and allDates.[Date]<=@ToDate
group by    
    allDates.[Date]

return
end

Ключом является раздел "allDates";

SELECT 
    [date]=convert(datetime, CONVERT(float,d.Seq))
FROM 
    (
    select top 100000 row_number() over(partition by 1 order by A.name) as Seq 
    from syscolumns A, syscolumns B  
    )d

Это вернет все даты между 1900 и 2173 (в этом примере). Ограничьте это по мере необходимости, но хороший вариант. Тонна разных способов подойти к этому ясно

Источник

Вы должны создать другую таблицу calendar as (Mysql)- идея одинакова на всех RDBMS-

CREATE TABLE `calendar` (
    `dt` DATE NOT NULL,
    UNIQUE INDEX `calendar_dt_unique` (`dt`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

и заполните данные даты.

больше деталей

Источник
Другие вопросы по тегам