gistfile1.sql

SELECT 34 AS [product_id],
SUBSTRING(CAST(NEWID() AS nvarchar(50)), 1, 5) + '-' + SUBSTRING(CAST(NEWID() AS nvarchar(50)), 1, 5) + '-' + SUBSTRING(CAST(NEWID() AS nvarchar(50)), 1, 5) + '-' + SUBSTRING(CAST(NEWID() AS nvarchar(50)), 1, 5) + '-' + SUBSTRING(CAST(NEWID() AS nvarchar(50)), 1, 5) AS [license_code], -- can we gen this from SQL or join into a table of available codes?
0 AS [refunded],
UniversityStartDate AS [purchase_ts],
null AS [order_id],
CustomerID AS [customer_id],
CustomerID AS [user_id],
UniversityStartDate AS [first_activation_ts],
UniversityEndDate AS [valid_until_ts],
NULL AS [grandfather_price],
'XU SUB' AS [sold_as_sku],
NULL AS [original_product],
0 AS [grandfather_status]
FROM Nop_Customer
WHERE UniversityStartDate IS NOT NULL
AND UniversityEndDate IS NOT NULL
AND NOT EXISTS(SELECT * FROM xam_Vendor_Licenses WHERE sold_as_sku = 'XU SUB' AND customer_id = Nop_Customer.CustomerID)