i have problem whith google keep site
i have one note from google keep site, which work very very slowly, but google chrome no problem...
Замена товаров в операциях UPDATE Operations SET GoodID=292 WHERE GoodID=251
дубли товаров по алкокоду (каталог 2) SELECT * FROM Goods WHERE Catalog2 In (SELECT Catalog2 FROM Goods WHERE Catalog2 <> AND ID > 1 GROUP BY Catalog2 HAVING COUNT(Catalog2) > 1) ORDER BY Catalog2
дубли партнеров по ФСРАР id (карта) SELECT * FROM partners WHERE CardNumber In (SELECT CardNumber FROM Partners WHERE CardNumber <> AND ID > 1 GROUP BY CardNumber HAVING COUNT(CardNumber) > 1) ORDER BY CardNumber
дубли считанных марок (mssql) SELECT * FROM InternalLog WHERE Message In (SELECT Message FROM InternalLog WHERE Message like '%UTM_%' GROUP BY Message HAVING COUNT(Message ) > 1)
дубли считанных марок (access) SELECT * FROM InternalLog WHERE CStr(Message) LIKE '%UTM_%' AND CStr(Message) In (SELECT CStr(Message) FROM InternalLog WHERE CStr(Message) <> GROUP BY CStr(Message) HAVING COUNT(CStr(Message)) > 1)
Замена товаров в операциях UPDATE Operations SET GoodID='ИД товара который нужно оставить' WHERE GoodID='ИД товара который убираем'
выбор только пивной продукции Select * From goods where Ltrim(Rtrim(substring(goods.catalog3, 178, 15))) in (261,262,263,500,510,520)
select * from goodsgroups where code like 'aab%'
SELECT * FROM Goods WHERE Catalog2 In (SELECT Catalog2 FROM Goods WHERE Catalog2 <> AND ID > 1 GROUP BY Catalog2 HAVING COUNT(Catalog2) > 1) ORDER BY Catalog2
SELECT * FROM Goods WHERE IsNumeric(Barcode1)=0 AND Barcode1<> SELECT * FROM Goods WHERE IsNumeric(Code)=0 AND Code<>
update operations set userrealtime = userrealtime -1 WHERE...
update operations set userrealtime = date + convert(nvarchar(8),userrealtime,114) WHERE ...
А что может быть проще? Insert [ИМЯ НОВОЙ БАЗЫ].[dbo].[partners] ([Code],[Company],[Company2],[MOL],[MOL2],[City],[City2],[Address],[Address2],[Phone],[Phone2],[Fax],[eMail],[TaxNo],[Bulstat],[BankName],[BankCode],[BankAcct],[BankVATName],[BankVATCode],[BankVATAcct],[PriceGroup],[Discount],[Type],[IsVeryUsed],[UserID],[GroupID],[UserRealTime],[Deleted],[CardNumber],[Note1],[Note2],[PaymentDays]) (Select [Code],[Company],[Company2],[MOL],[MOL2],[City],[City2],[Address],[Address2],[Phone],[Phone2],[Fax],[eMail],[TaxNo],[Bulstat],[BankName],[BankCode],[BankAcct],[BankVATName],[BankVATCode],[BankVATAcct],[PriceGroup],[Discount],[Type],[IsVeryUsed],[UserID],[GroupID],[UserRealTime],[Deleted],[CardNumber],[Note1],[Note2],[PaymentDays] from [ИМЯ СТАРОЙ БАЗЫ].[dbo].[Partners])
Server1 - 1000-2499.99 => 4% UPDATE p SET Discount = 4 FROM partners AS p INNER JOIN
( SELECT PartnerID, SUM(Qtty*Sign) AS TurnoverSum FROM payments WHERE [Mode] = 1 GROUP BY PartnerID ) paym ON p.ID = paym.PartnerID
WHERE paym.TurnoverSum >= 1000 AND paym.TurnoverSum < 2500
Server2 - 2500+ => 5% UPDATE p SET Discount = 5 FROM partners AS p INNER JOIN
( SELECT PartnerID, SUM(Qtty*Sign) AS TurnoverSum FROM payments WHERE [Mode] = 1 GROUP BY PartnerID ) paym ON p.ID = paym.PartnerID
WHERE paym.TurnoverSum >= 2500
разные цены
Select * from operations o2 where goodid in (select distinct goodid from operations o left join goods g2 on g2.id = o.goodid where o.Priceout <> g2.priceout2) SELECT * FROM partners WHERE TaxNo = AND CardNumber not like '%050000%' AND CardNumber <>
Александр, вот триггер:
USE [vd3]
GO
/***** Object: Trigger [dbo].[PaymentTypesByUsers] Script Date: 08/14/2017 07:36:40 *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create Trigger [dbo].[PaymentTypesByUsers] ON [dbo].[Payments] AFTER INSERT
AS
IF (SELECT OperType from inserted) = 2 and (SELECT Type from inserted) = 6 and not (select pg.code from inserted left join partners p on inserted.PartnerID=p.id left join PartnersGroups pg on p.groupid=pg.id) like 'AAA%' begin raiserror 100001 'ЗапрещПродажа_Lesop' ROLLBACK TRANSACTION RETURN end IF (SELECT OperType from inserted) = 2 and (SELECT Type from inserted) in (1,2,3,4,5) and (select pg.code from inserted left join partners p on inserted.PartnerID=p.id left join PartnersGroups pg on p.groupid=pg.id) like 'AAA%' begin raiserror 100001 'ЗапрещПродажа_Lesop' ROLLBACK TRANSACTION RETURN end
триггер в Payments на запрет видов оплаты в зависимости от партнера
DELETE FROM ApplicationLog DELETE FROM CashBook DELETE FROM CurrenciesHistory DELETE FROM Documents DELETE FROM ECRReceipts DELETE FROM InternalLog DELETE FROM Lots where id > 1 DELETE FROM Network DELETE FROM NextAcct DELETE FROM Operations Where OperType NOT IN (20,21) DELETE FROM Payments DELETE FROM Transformations DELETE FROM Store
Эту выполнять после всех. DELETE FROM Goods WHERE ID NOT IN (SELECT DISTINCT GoodID FROM Operations)
UPDATE Partners SET Phone2=Phone WHERE NOT Phone=Phone2 AND ID>1 В шедулер
операции без товара: select goodid from operations where goodid > 0 and goodid not in (select distinct id from goods)
DECLARE @CashbookID as int DECLARE @ObjectID as int DECLARE @Acct as int DECLARE Acct_Cursor CURSOR local FOR SELECT ID FROM cashbook where SUBSTRING([Desc],13,10) IN (SELECT DISTINCT Acct FROM payments where opertype = 2) OPEN Acct_Cursor
FETCH NEXT FROM Acct_Cursor into @CashbookID WHILE @@FETCH_STATUS = 0 IF @@FETCH_STATUS = 0 BEGIN SET @Acct = (SELECT SUBSTRING([Desc],13,10) FROM cashbook WHERE ID = @CashbookID) SET @ObjectID = (SELECT TOP(1) ObjectID FROM payments WHERE acct = @Acct) UPDATE cashbook SET ObjectID = @ObjectID WHERE ID = @CashbookID FETCH NEXT FROM Acct_Cursor into @CashbookID; END ELSE CLOSE Acct_Cursor DEALLOCATE Acct_Cursor
Привет! Подскажите пожалуйста, как поправить таблицу CashBook, поле ObjectID? Нужно привести в соответствии с таблицей Operations или Payments. БД MSSQL 2012
дата из примечания
обновит все строки, где в примечании есть слово ЕГАИС
MSSQL:
update operations set date = cast(convert(datetime, SUBSTRING(Note, 7, 10), 104) as date) where note like 'ЕГАИС%' and opertype = 1
ACCESS:
________________
it isn't all
v.s. -->