Vyhľadajte odpoveď

Vyhnite sa podvodom s podporou. Nikdy vás nebudeme žiadať, aby ste zavolali alebo poslali SMS na telefónne číslo alebo zdieľali osobné informácie. Nahláste prosím podozrivú aktivitu použitím voľby “Nahlásiť zneužitie”.

Learn More

Táto téma bola uzamknutá a archivovaná. Ak potrebujete pomôcť, založte prosím novú otázku.

i have problem whith google keep site

  • Žiadne odpovede
  • 1 má tento problém
  • 3 zobrazenia
more options

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. -->

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. -->