I am storing Circle details in one view and for each circle GeoId's in another view... Now I have to get results from both views from another table by Circle and GeoId's.
with
vwArea
AS
(
select CircleName,CircleCode,SalesGeoTag,SalesGeoID
from SALESGEO d where SalesGeoType='C'
),
vwSalesGeo
AS
(
SELECT vwArea.CircleName,
(SELECT CONVERT(VARCHAR,SalesGeoID) + ',' FROM SALESGEO WHERE SalesGeoTag LIKE vwArea.SalesGeoTag + '.%' FOR XML PATH(''))SalesGeoId
FROM vwArea
)
I am getting results as
Kolkata 23,24,25,26,766,767,768,769,770,771,
West Bengal 27,28,29,30,1486,1487,1488,1489,1490,1491,1492,
Gujarat 32,33,34,35,128,129,130,131,132,133,134,135,136,137,
SELECT vwArea.CircleName,
(SELECT COUNT(POSID) FROM vwData WHERE SalesGeoID IN (vwSalesGeo.SalesGeoID))POSID
FROM vwSalesGeo LEFT JOIN vwArea ON vwArea.CircleName = vwSalesGeo.CircleName
WHERE vwArea.CircleName IS NOT NULL
I am getting error
'conversion failed when converting the nvarchar value to int'. I want to know that it is problem with comma seperated Id.
How can I do this.