vwCustomerUpload.sql

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