select distinct ( u.userID ), u.lastLogin, totalUsers = COUNT( u.userID ) OVER()
From ZipCodes z
inner join tblUsers u on z.zipCode = u.zipCode
inner join tblUserOffers uo on u.userID = uo.userID
Where <a big long where clause>
order by u.lastLogin OFFSET 0
ROWS FETCH NEXT 500 ROWS ONLY
SELECT
u.userID
, u.lastLogin
, z.totalUsers
from tblUsers u
INNER JOIN (
SELECT
zipCode
, COUNT(*) AS totalUsers
FROM tblUsers
GROUP BY zipCode
) z ON z.zipCode = u.zipCode
INNER JOIN tblUserOffers uo ON u.userID = uo.userID
WHERE <a big long where clause>
ORDER BY u.lastLogin OFFSET 0
ROWS FETCH NEXT 500 ROWS ONLY
select distinct u.userID, u.lastLogin, totalUsers = COUNT( DISTINCT u.userID ) OVER()
From ZipCodes z
inner join tblUsers u on z.zipCode = u.zipCode
inner join tblUserOffers uo on u.userID = uo.userID
Where <a big long where clause>
order by u.lastLogin OFFSET 0
ROWS FETCH NEXT 500 ROWS ONLY
DECLARE @tblOffers TABLE
([offerID] int, [offer] varchar(28), [description] varchar(26), [categoryID] int, [createDate] varchar(7), [active] varchar(4), [isXTOffer] varchar(4))
;
INSERT INTO @tblOffers
([offerID], [offer], [description], [categoryID], [createDate], [active], [isXTOffer])
VALUES
(101, 'Diet & Lifestyle Coaching147', '08:50.0', 1, '1', NULL, NULL),
(102, 'Disability Consulting', 'Disability Consulting desc', 113, '09:22.2', '1', '1'),
(103, 'Web Design', 'web design desc', 107, '10:42.6', '1', '1')
;
DECLARE @tblUserOffers TABLE
([userOfferID] int, [offerID] int, [userID] int, [visible] int)
;
INSERT INTO @tblUserOffers
([userOfferID], [offerID], [userID])
VALUES
(1194, 101, 100),
(573, 102, 5593),
(605, 103, 6679)
;
DECLARE @tblUsers TABLE
([userID] int, [firstName] varchar(10), [lastName] varchar(10), [displayName] varchar(12), [city] varchar(5), [stateProvince] varchar(14)
, [profilePicture] varchar(14), [confirmed] int, [lastLogin] datetime)
;
INSERT INTO @tblUsers
([userID], [firstName], [lastName], [displayName], [city], [stateProvince], [profilePicture], [confirmed])
VALUES
(100, 'afirstName', 'alastName', 'adisplayName', 'acity', 'astateProvince', 'profilePicture', 1),
(5593, 'bfirstName', 'b lastName', 'bdisplayName', 'bcity', 'bstateProvince', 'profilePicture', 1),
(6679, 'cfirstName', 'clastName', 'cdisplayName', 'ccity', 'cstateProvince', 'profilePicture', 1)
;
SELECT
u.userID
, fullName = u.firstName + ' ' + u.lastName
, u.displayName
, u.city
, u.stateProvince
--, ROUND(dbo.CalculateDistance(-71.43520, 42.32320, z.Longitude, z.Latitude), 2) AS Distance
, profilePicture = CASE
WHEN ISNULL(u.profilePicture, '') = '' THEN '/images/person.png'
ELSE u.profilePicture END
, confirmed = CASE u.confirmed
WHEN 1 THEN '<html string>'
ELSE '' END
, (
SELECT
offer + ', '
FROM @tblOffers o
INNER JOIN @tblUserOffers uo ON o.offerID = uo.offerID
WHERE u.userID = uo.userID
AND uo.visible = 1
FOR xml PATH ('')
)
AS offers
, (
SELECT
COUNT(o.offerID)
FROM @tblOffers o
INNER JOIN @tblUserOffers uo ON uo.offerID = o.offerID
WHERE u.userID = uo.userID
AND uo.visible = 1
FOR xml PATH ('')
)
AS offerCount
, u.lastLogin
, totalUsers = COUNT(*) OVER ()
FROM @tblUsers as U
That query, using that sample data, produces the following result:
+---+--------+-----------------------+--------------+-------+----------------+----------------+---------------+--------+------------+-----------+------------+
| | userID | fullName | displayName | city | stateProvince | profilePicture | confirmed | offers | offerCount | lastLogin | totalUsers |
+---+--------+-----------------------+--------------+-------+----------------+----------------+---------------+--------+------------+-----------+------------+
| 1 | 100 | afirstName alastName | adisplayName | acity | astateProvince | profilePicture | <html string> | NULL | 0 | NULL | 3 |
| 2 | 5593 | bfirstName b lastName | bdisplayName | bcity | bstateProvince | profilePicture | <html string> | NULL | 0 | NULL | 3 |
| 3 | 6679 | cfirstName clastName | cdisplayName | ccity | cstateProvince | profilePicture | <html string> | NULL | 0 | NULL | 3 |
+---+--------+-----------------------+--------------+-------+----------------+----------------+---------------+--------+------------+-----------+------------+
Have a look at this url: http://rextester.com/KAP76290
SELECT
u.userID
, fullName = u.firstName + ' ' + u.lastName
, u.displayName
, u.city
, u.stateProvince
--, ROUND(dbo.CalculateDistance(-71.43520, 42.32320, z.Longitude, z.Latitude), 2) AS Distance
, profilePicture = CASE
WHEN ISNULL(u.profilePicture, '') = '' THEN '/images/person.png'
ELSE u.profilePicture END
, confirmed = CASE u.confirmed
WHEN 1 THEN '<html string>'
ELSE '' END
, oa1.offers
, oa2.offerCount
, u.lastLogin
, totalUsers = COUNT(*) OVER ()
FROM @tblUsers as U
OUTER APPLY (
SELECT
STUFF((
SELECT
', ' + offer
FROM @tblOffers o
INNER JOIN @tblUserOffers uo ON o.offerID = uo.offerID
WHERE u.userID = uo.userID
--AND uo.visible = 1
FOR XML PATH ('')
)
, 1, 1, '')
) AS OA1 (offers)
OUTER APPLY (
SELECT
COUNT(o.offerID)
FROM @tblOffers o
INNER JOIN @tblUserOffers uo ON o.offerID = uo.offerID
WHERE u.userID = uo.userID
--AND uo.visible = 1
) AS OA2 (offerCount)
From ZipCodes z
inner join tblUsers u on z.zipCode = u.zipCode
inner join tblUserOffers uo on u.userID = uo.userID
Where u.userID <> 100 and
Longitude Between -72.41289 And -70.45751 And
Latitude Between 41.59901 And 43.04739 And
dbo.CalculateDistance(-71.43520, 42.32320, Longitude, Latitude) <= 50 and
status = 1
DECLARE @tblOffers TABLE
([offerID] int, [offer] varchar(28), [description] varchar(26), [categoryID] int, [createDate] varchar(7), [active] varchar(4), [isXTOffer] varchar(4))
;
INSERT INTO @tblOffers
([offerID], [offer], [description], [categoryID], [createDate], [active], [isXTOffer])
VALUES
(1193, 'Diet & Lifestyle Coaching', '08:50.0', 1, '1', NULL, NULL),
(658, 'Disability Consulting', 'Disability Consulting desc', 113, '09:22.2', '1', '1'),
(713, 'Web Design', 'web design desc', 107, '10:42.6', '1', '1'),
(643, 'Web Design2', 'web design desc2', 107, '10:42.6', '1', '1'),
(220, 'Web Design3', 'web design desc3', 107, '10:42.6', '1', '1')
;
DECLARE @tblUserOffers TABLE
([userOfferID] int, [offerID] int, [userID] int, [visible] int)
;
INSERT INTO @tblUserOffers
([userOfferID], [offerID], [userID])
VALUES
(1, 1193, 135),
(2, 658, 135),
(3, 713, 135),
(4, 643, 136),
(5, 220, 136)
;
DECLARE @tblUsers TABLE
([userID] int, [firstName] varchar(10), [lastName] varchar(10), [displayName] varchar(12), [city] varchar(5), [stateProvince] varchar(14)
, [profilePicture] varchar(14), [confirmed] int, [lastLogin] datetime, [ZipCode] [char](5))
;
INSERT INTO @tblUsers
([userID], [firstName], [lastName], [displayName], [city], [stateProvince], [profilePicture], [confirmed], zipCode)
VALUES
(112, 'afirstName', 'alastName', 'adisplayName', 'acity', 'astateProvince', 'profilePicture', 1, '11111'),
(122, 'bfirstName', 'blastName', 'bdisplayName', 'bcity', 'bstateProvince', 'profilePicture', 1, '22222'),
(135, 'cfirstName', 'clastName', 'cdisplayName', 'ccity', 'cstateProvince', 'profilePicture', 1, '33333'),
(136, 'dfirstName', 'dlastName', 'ddisplayName', 'dcity', 'dstateProvince', 'profilePicture', 1, '44444')
;
declare @ZIPCodes TABLE ( [ZipCode] [char](5), [Latitude] [decimal](12, 4), [Longitude] [decimal](12, 4) );
insert into @ZipCodes
( zipCode, latitude, Longitude )
values
( '11111', 42.59901, -71.41289 ),
( '22222', 43.12901, -71.46389 ),
( '33333', 44.59901, -74.12345 )
SELECT
u.userID
, fullName = u.firstName + ' ' + u.lastName
, u.displayName
, u.city
, u.stateProvince
, profilePicture = CASE
WHEN ISNULL(u.profilePicture, '') = '' THEN '/images/person.png'
ELSE u.profilePicture END
, confirmed = CASE u.confirmed
WHEN 1 THEN '<html string>'
ELSE '' END
, oa1.offers
, oa2.offerCount
, u.lastLogin
, totalUsers = COUNT(*) OVER ()
From @ZipCodes z
inner join @tblUsers u on z.zipCode = u.zipCode
inner join @tblUserOffers uo on u.userID = uo.userID
OUTER APPLY (
SELECT
STUFF((
SELECT
', ' + offer
FROM @tblOffers o
INNER JOIN @tblUserOffers uo ON o.offerID = uo.offerID
WHERE u.userID = uo.userID
--AND uo.visible = 1
FOR XML PATH ('')
)
, 1, 1, '')
) AS OA1 (offers)
OUTER APPLY (
SELECT
COUNT(o.offerID)
FROM @tblOffers o
INNER JOIN @tblUserOffers uo ON o.offerID = uo.offerID
WHERE u.userID = uo.userID
--AND uo.visible = 1
) AS OA2 (offerCount)
--Where Longitude Between -72.41289 And -70.45751
--And Latitude Between 41.59901 And 43.04739
what are you trying to do?
give a sample from input and the result you are looking for...