/*Skrypt zwraca firmy, ktore maja zdublowane wpisy w tabeli [firm].[firm], tj. istnieje wiecej niz jedna pozycja z tym samym firmName i streetName.*/

SELECT
	COUNT(*),
	CAST(firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI,
	CAST(streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI
FROM [firm].[firm]
WHERE status > -1
GROUP BY	CAST(firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI,
			CAST(streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI
HAVING COUNT(*) > 1

/*Ponizszy SELECT zwraca ID zdublowanych firm.*/

SELECT
	'''' + CAST(f.firmid AS VARCHAR(50)) + ''',',
	f.firmName,
	f.firmid
FROM firm.firm f
JOIN (SELECT
	COUNT(*) ile,
	CAST(firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI fn,
	CAST(streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI sn
FROM [CRM0102_HELPDESK].[firm].[firm]
WHERE status > -1
GROUP BY	CAST(firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI,
			CAST(streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI
HAVING COUNT(*) > 1) a
	ON CAST(f.firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI = a.fn
	AND CAST(f.streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI = a.sn
ORDER BY 2

/*Ponizszy SELECT wyswietla wszystkie informacje o zdublowanych firmach.*/

SELECT
	*
FROM firm.firm f
JOIN (SELECT
	COUNT(*) ile,
	CAST(firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI fn,
	CAST(streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI sn
FROM [CRM0102_HELPDESK].[firm].[firm]
WHERE status > -1
GROUP BY	CAST(firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI,
			CAST(streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI
HAVING COUNT(*) > 1) a
	ON CAST(f.firmname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI = a.fn
	AND CAST(f.streetname AS VARCHAR(300)) COLLATE SQL_Latin1_General_CP1253_CI_AI = a.sn
ORDER BY f.firmName

/* Ponizszy SELECT wybiera wszystkie firmy, ktore maja zdublowana nazwe, ale nie uwzglednia adresu.

SELECT count(*) ,cast(firmname as varchar(300)) collate SQL_Latin1_General_CP1253_CI_AI
  FROM [CRM0102_HELPDESK].[firm].[firm]
  where status >-1
  group by cast(firmname as varchar(300)) collate SQL_Latin1_General_CP1253_CI_AI
  having count(*) > 1*/



