use db2
go
ALTER TABLE shop ADD [LinkShort1] [nvarchar](max) NULL;
go
ALTER FUNCTION [dbo].[GetOpinionsForPlan]
(
    -- Add the parameters for the function here
    @platformId varchar(50),
    @planDate date
)
RETURNS TABLE
AS
RETURN
(
    SELECT Shop.Id as ShopId, Shop.Link, Shop.LinkShort, Shop.InternetName, Shop.MarketName, Shop.Help, Shop.Term, Shop.YandexCoords1, Shop.YandexCoords2, Region.Id as RegionId, Region.Name as RegionName,
    OpinionsCount.Count, OpinionDate.Id as DateId, OpinionDate.Date as Date, OpinionsCount.Id as OpinionsCountId, OpinionMethod.Id as OpinionMethodId, OpinionMethod.Name as MethodName, PublishFrequency.Frequency, PublishFrequency.Id as PublishFrequencyId,
    Platform.id as PlatformId
    FROM Shop
    INNER JOIN Region ON Region.Id = Shop.Id_Region
    LEFT OUTER JOIN OpinionsCount ON OpinionsCount.Id_Shop = Shop.Id
    LEFT OUTER JOIN OpinionDate ON OpinionDate.Id_Shop = Shop.Id AND OpinionDate.Date >= @planDate AND OpinionDate.Date < dateadd(day,7, @planDate)
    LEFT OUTER JOIN OpinionMethod ON OpinionsCount.Id_OpinionMethod = OpinionMethod.Id OR OpinionDate.Id_Method = OpinionMethod.Id
    INNER JOIN PublishFrequency ON Shop.Id_PublishFrequency = PublishFrequency.Id
    INNER JOIN Customer ON Customer.Id = Shop.Id_Customer
    INNER JOIN Platform ON Platform.id = Customer.Id_Platform
    WHERE
            Platform.id = @platformId AND
            (Count != 0 OR CompletedDate = @planDate OR OpinionDate.Date IS NOT NULL) AND
            (OpinionsCount.Id_Shop IS NOT NULL OR OpinionDate.Id_Shop IS NOT NULL)
)
Go