SQL examples for SQL Server

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders Where Id=''zzzzzzzzzz''';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders WITH(StartDate=''2020-01-01'', EndDate=''2021-12-31'' ) --use function like today, yesterday, yearstart, monthstart, yearstart-1y';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders 
--WHERE Currency=''USD'' and TotalInPayoutCurrency>=1599
WITH(StartDate=''today-30day'', EndDate=''today'') --try today, yesterday, yearstart, yearend, monthstart, monthend, yearstart-1y so on';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_product_ids';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Products';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Products Where Id=''some-product-id''';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
 Product,
 Country,
 Currency,
 Price,
 PriceDisplay,
 
 Discount2DiscountPercent,
 Discount2DiscountValue,
 Discount2DiscountValueDisplay,
 Discount2UnitPrice,
 Discount2UnitPriceDisplay,
 
 Discount3DiscountPercent,
 Discount3DiscountValue,
 Discount3DiscountValueDisplay,
 Discount3UnitPrice,
 Discount3UnitPriceDisplay,
 
 Discount4DiscountPercent,
 Discount4DiscountValue,
 Discount4DiscountValueDisplay,
 Discount4UnitPrice,
 Discount4UnitPriceDisplay,
 
 Discount5DiscountPercent,
 Discount5DiscountValue,
 Discount5DiscountValueDisplay,
 Discount5UnitPrice,
 Discount5UnitPriceDisplay,
 
 DiscountReasonEn,
 DiscountPeriodCount,

 AvailableStart,
 AvailableEnd,
 SetupFeePrice,
 SetupFeePriceDisplay,
 SetupFeeReasonEn
 
FROM ProductPrices 
--WITH(Country=''US'') --//get prices for a specific country
--WITH(Currency=''USD'') --//get proces for a specific currency';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Products
SET 
  Display=''Product ABCD''
, Sku=''PRD-ABCD-01''
, Summary=''Product Abcd **STD**''
, Description=''This product can be used to call **API** supports markdown syntax''
, Action=''**Action** supports markdown''
, TaxCode=''DC010500''
, Image=''https://zappysys.com/api/images/ZappySys-icon.png''
, FulfillmentInstructions=''Thank you for shopping. **Activate** license by using our License App''
, Format=''digital''
, PriceUSD=''101.5''
, PriceEUR=''102.5''
, PriceGBP=''103.5''
, PriceCAD=''104.5''
, PriceNZD=''105.5''
, PriceCHF=''106.5''
, PriceSEK=''107.5''
, PriceCZK=''108.5''
, PriceDKK=''109.5''
, PricePLN=''101.5''
, PriceSGD=''102.5''
, PriceJPY=''103.5''
, PriceCNY=''104.5''
, PriceHKD=''105.5''
, PriceINR=''106.5''
, PriceBRL=''107.5''
, PriceAUD=''108.5''

, Badge=''Badge-1''
, Rank=1
, Trial=14 --how many trial days
, TrialPriceUSD=10.5 

--, Renew=1 --not allowed for update : Only for create (enable subscription)
, Interval=''year'' --allowed values: adhoc, day, week, year, only needed if you are creating a subscription
, IntervalLength=1
, IntervalCount=0

, QuantityBehavior=''allow'' --allowed values: allow, lock, hide
, QuantityDefault=1
, Attributes = ''{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}''
--, Fulfillments=''[ { fullfillment }, { fullfillment } ... ]'' --JSON fragment 	
Where Id=''abcd''';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Products
SOURCE(''ODBC'',

--File input example (use as to match Column name allowed in Products Table to write)
--''Driver={ZappySys CSV Driver};DataPath=c:\data\products.csv'',
--''select P_ID as Id,P_TAXCODE as TaxCode From $''

--Direct input example
''Driver={ZappySys CSV Driver}'',
''select ''''abcd'''' as Id, ''''DC010500'''' as TaxCode UNION
 select ''''xyz'''' as Id, ''''DC010500'''' as TaxCode UNION
 select ''''zzz'''' as Id, ''''DC010500'''' as TaxCode 
''
)';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = '--Insert or Update
--UPSERT INTO Products 

--Insert or Update
UPSERT INTO Products
(     [Id]	, [Display]	, [Summary]
	, [Description]	, [Action]	, [Format]
	, [Sku]	, [TaxCode]	, [Image]
	, [Renew]	, [Interval]	, [IntervalLength]
	, [IntervalCount]	, [QuantityBehavior]	, [QuantityDefault]
	, [PriceUSD]	, [PriceEUR]	, [PriceGBP]
	, [PriceCAD]	, [PriceNZD]	, [PriceCHF]
	, [PriceSEK]	, [PriceCZK]	, [PriceDKK]
	, [PricePLN]	, [PriceSGD]	, [PriceJPY]
	, [PriceCNY]	, [PriceHKD]	, [PriceINR]	, [PriceBRL]	, [PriceAUD]
	, [Trial]	
	, [TrialPriceUSD]	, [TrialPriceEUR]	, [TrialPriceGBP]
	, [PaymentCollected]	, [PaidTrial]
	, [FulfillmentInstructions]
	, [DateLimitsEnabled]	, [CancellationInterval]	, [CancellationIntervalLength]	
	, [SetupFeeTitle]	
	, [SetupFeePriceUSD]	, [SetupFeePriceEUR]	, [SetupFeePriceGBP]	
	, [Badge]	, [Rank]	
	, [Attributes]		
	)
VALUES(''test-std-id''
	, ''3Year **standard** subscription edition''
	, ''summary **standard edition** _some italic_ '' -- markdown syntax allowed
	
	, ''long description **test STD** and many more lines'' --markdown syntax allowed
	, ''action text with **markdown** ''
	, ''digital'' -- e.g. digital OR physical OR digital-and-physical
	
	, ''TEST-STD-001''
	, ''DC010500''
	, ''https://zappysys.com/api/images/ZappySys-icon.png''
	
	, true	, ''year''	, 1
	, 1 	, ''allow'' -- e.g. allow Or lock Or hide
	, 3	--stop renewal after 3 periods
	, 10.12	, 11.12	, 12.12	
	, 13.12	, 14.12	, 15.12	
	, 16.12	, 17.12	, 18.12	
	, 19.12	, 20.12	, 21.12	
	, 22.12	, 23.12	, 24.12	, 25.12	, 26.12	
    , 14 --how many days trial allowed
    , 10.12	, 11.12	, 12.12	
    , ''true'', true
	, ''Thank you for shopping, **here are** license instructions'' --markdown supported
	, false, ''day'', 5
	, ''Setup title''	
	--setup fees in common currencies
	, 10.50	, 11.50	, 12.50	
	, ''badge-1''	, 1
	, ''{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}'' --JSON fragment 
	--, ''[... fullfillments.. ]'' --JSON fragment 	
)';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Quotes 
WITH(
	  Statuses=''OPEN~COMPLETED'',
	  --SearchFor=''Test'' --quote contains word "Test" in Name or blling information
	  -- query more status like below
	  --Statuses=''OPEN~COMPLETED~CANCELED~EXPIRED'',
	  
)';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from get_products_visibility';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders 
WHERE SubscriptionId_1=''iBPfMFS6TZSxrLzSOrq8PQ'' 
	OR SubscriptionId_2=''iBPfMFS6TZSxrLzSOrq8PQ''
	OR SubscriptionId_3=''iBPfMFS6TZSxrLzSOrq8PQ'' 
ORDER BY OrderDate';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_subscription_report
Order by transaction_date
WITH(
	  Country=''AL~UA~US''
	, ProductPaths=''odbc-pp-ent-sub~odbc-pp-pro-sub~odbc-pp-std~ssis-pp-pro-sub~ssis-pp-std-sub~ssis-pp-ent-sub''
	, StartDate=''monthstart-1m'' --or  2022-01-01
	, EndDateDate=''monthend+2d'' --or  2022-01-01
	, ReportColumns=''transaction_date~transaction_month~transaction_year~activations~average_mrr~cancellations~mrr~new_subscribers~subscriptions''
	, GroupByColumns=''transaction_date~transaction_month~transaction_year''
)';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_revenue_report
Order by transaction_date
WITH(
	  Country=''AL~UA~US''
	, ProductPaths=''odbc-pp-ent-sub~odbc-pp-pro-sub~odbc-pp-std~ssis-pp-pro-sub~ssis-pp-std-sub~ssis-pp-ent-sub''
	, StartDate=''monthstart-1m'' --or  2022-01-01
	, EndDateDate=''monthend+2d'' --or  2022-01-01
	, ReportColumns=''Transaction_Year~Transaction_Month~Transaction_Date~Company_Name~Product_Path~Product_Units~Tax_in_USD~Transaction_Amount_in_USD~Grand_Total_In_USD''
	, GroupByColumns=''transaction_date~transaction_month~transaction_year''
)';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM Accounts	

SELECT "Id"
	, "ContactFirst"
	, "ContactLast"
	, "ContactEmail"
	, "ContactCompany"
	, "ContactPhone"
	, "ContactSubscribed"
	, "AddressLine1"
	, "AddressLine2"
	, "City"
	, "Region"
	, "RegionCustom"
	, "PostalCode"
	, "AddressCompany"
	, "Language"
	, "Country"
	, "LookupGlobal"
	, "Url"
	, "PaymentMethods"
	, "PaymentActive"
	, "Orders"
	, "Subscriptions"
	, "Charges"
	, "Subscribed"
	, "TaxExemptionData"
FROM Accounts

--Use WITH clause --OR-- Key column(s) in WHERE clause
--WHERE [Id] = ''abcd''

--search by one or more parameters below
/*
WITH(
	  Email=''X''
	, CustomKey=''X''
	, GlobalKey=''X''
	, OrderID=''X''
	, OrderReference=''X''
	, SubscriptionId=''X''
	, Products=''PROD-1,PROD-2,PROD-3''
	, Refunds=''true''
	, SubscriptionStatus=''active''
)
*/';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts WITH(Products=''ssis-pp-pro,ssis-pp-pro-sub,ssis-pp-ent,ssis-pp-ent-sub'')';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from AccountCharges 

--search by one or more parameters below
/*
WITH(
	  Email=''X''
	, CustomKey=''X''
	, GlobalKey=''X''
	, OrderID=''X''
	, OrderReference=''X''
	, SubscriptionId=''X''
	, Products=''PROD-1,PROD-2,PROD-3''
	, Refunds=''true''
	, SubscriptionStatus=''active''
)
*/';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from AccountOrders
WITH(
	  Email=''bob@abc.com''
)
	  
--search by one or more parameters below
/*
WITH(
	  Email=''X''
	, CustomKey=''X''
	, GlobalKey=''X''
	, OrderID=''X''
	, OrderReference=''X''
	, SubscriptionId=''X''
	, Products=''PROD-1,PROD-2,PROD-3''
	, Refunds=''true''
	, SubscriptionStatus=''active''
)
*/';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from AccountOrders WHERE Reference=''BIN240202-9666-28133'' WITH(OrderReference=''BIN240202-9666-28133'')	
--or for handful rows use below way
--SELECT * from Orders WHERE Reference=''BIN240202-9666-28133''';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];