Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CREATE VIEW [dbo].[vwCustomerUpload]
AS
SELECT
MAX([ocp].[OnlineCustomerPersonID]) as [OnlineCustomerPersonID],
MAX([ocp].[Email]) as [Email],
MAX([ocp].[Password]) as [Password],
MAX([ocp].[DemoID]) as [DemoID],
MAX([ocp].[FirstName]) as [FirstName],
MAX([ocp].[LastName]) as [LastName],
(CASE WHEN MAX([ocp].[ShippingAddressCountry]) = 'US' THEN 'en-us'
WHEN MAX([ocp].[ShippingAddressCountry]) = 'CA' AND MAX([ocp].[PrimaryLanguage]) = 'ENG' THEN 'en-ca'
WHEN MAX([ocp].[ShippingAddressCountry]) = 'CA' AND MAX([ocp].[PrimaryLanguage]) = 'FRE' THEN 'fr-ca'
END) as [Culture],
MAX([ocp].[HomePhoneNumber]) as [HomePhoneNumber],
MAX([ocp].[ShippingAddressAddress1]) as [ShippingAddressAddress1],
MAX([ocp].[ShippingAddressAddress2]) as [ShippingAddressAddress2],
MAX([ocp].[ShippingAddressAddress3]) as [ShippingAddressAddress3],
MAX([ocp].[ShippingAddressCity]) as [ShippingAddressCity],
MAX([ocp].[ShippingAddressRegion]) as [ShippingAddressRegion],
MAX([ocp].[ShippingAddressPostalCode]) as [ShippingAddressPostalCode],
MAX([ocp].[ShippingAddressCountry]) as [ShippingAddressCountry],
MAX([ocp].[ShippingAddressSuburb]) as [ShippingAddressSuburb],
MAX([ocp].[ShippingAddressCounty]) as [ShippingAddressCounty],
MAX([ocp].[ShippingAddressTaxAreaID]) as [ShippingAddressTaxAreaID],
MAX([ocp].[ShippingAddressTaxAreaIDDate]) as [ShippingAddressTaxAreaIDDate],
MAX(CONVERT(int,[ocp].[IsActive])) as [IsActive],
CAST (HASHBYTES('SHA1',
CONVERT(NVARCHAR(max), MAX([ocp].[OnlineCustomerPersonID]))
+ ISNULL(MAX([ocp].[Email]), '')
+ ISNULL(MAX([ocp].[Password]), '')
+ ISNULL(CONVERT(NVARCHAR(max), MAX([ocp].[DemoID])), '')
+ ISNULL(MAX([ocp].[FirstName]), '')
+ ISNULL(MAX([ocp].[LastName]), '')
+ ISNULL(MAX([ocp].[HomePhoneNumber]), '')
+ ISNULL(MAX([ocp].[ShippingAddressAddress1]), '')
+ ISNULL(MAX([ocp].[ShippingAddressAddress2]), '')
+ ISNULL(MAX([ocp].[ShippingAddressAddress3]), '')
+ ISNULL(MAX([ocp].[ShippingAddressCity]), '')
+ ISNULL(MAX([ocp].[ShippingAddressRegion]), '')
+ ISNULL(MAX([ocp].[ShippingAddressPostalCode]), '')
+ ISNULL(MAX([ocp].[ShippingAddressCountry]), '')
+ ISNULL(MAX([ocp].[ShippingAddressSuburb]), '')
+ ISNULL(MAX([ocp].[ShippingAddressCounty]), '')
+ ISNULL(CONVERT(NVARCHAR(max), MAX([ocp].[ShippingAddressTaxAreaID])), '')
+ ISNULL(CONVERT(NVARCHAR(max), MAX([ocp].[ShippingAddressTaxAreaIDDate])), '')
+ ISNULL(CONVERT(NVARCHAR(max), MAX(CONVERT(int,[ocp].[IsActive]))), '')
) AS VARBINARY(MAX)) AS [DataHash]
from OnlineCustomerPerson [ocp]
GROUP BY Email
--ORDER BY OnlineCustomerPersonID DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment