Writing a SQL Query to bring back only a single record if more than one is found.

I'm working on a report in SSRS and I'm getting too many results from my query.  For instance, if I have User A enter who enters a service of PT on 8/30/15 for Sally and User B also enters a service of PT on 8/30/15 for Sally, I only want to count it once.  Basically, for certain services, if the database sees more than one of the same service entered for the same day, I only want the query to return one record.  Also, is it possible to have this same concept work but instead of the same service for the same day, I would like the query to return only one record if it sees more than one of the same service for a certain month.  I hope this makes sense.
hackman122Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Adding DISTINCT  to your SELECTs or COUNTs is many times the easiest and fastest option.
https://msdn.microsoft.com/en-us/library/ms187731.aspx
0
PortletPaulfreelancerCommented:
If your table/columns looked like this:

ThatTable
EnteredBy, Service, EntryDt, ForWhom
A, PT, 2015-08-30, Sally
B, PT, 2015-08-30, Sally

What result are you expecting? (don't describe it, display what you want)
0
PortletPaulfreelancerCommented:
With this (modified!) data
CREATE TABLE ThatTable
    ([EnteredBy] varchar(2), [Service] varchar(3), [EntryDt] date, [ForWhom] varchar(5))
;
    
INSERT INTO ThatTable
    ([EnteredBy], [Service], [EntryDt], [ForWhom])
VALUES
    ('A,', 'PT', '2015-08-30 00:00:00', 'Sally'),
    ('B,', 'PT', '2015-08-30 00:00:00', 'Sally'),
    ('C,', 'PT', '2015-08-30 00:00:00', 'Fred')
;

Open in new window

Using this query:
SELECT
      Service
    , EntryDt
    , COUNT(DISTINCT ForWhom) AS CountOf
FROM ThatTable
GROUP BY
      Service
    , EntryDt

Open in new window

The result is:
| Service |    EntryDt | CountOf |
|---------|------------|---------|
|      PT | 2015-08-30 |       2 |

Open in new window

also see: http://sqlfiddle.com/#!6/54871/1
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

hackman122Author Commented:
I'm wanting the query to look at three fields in a table and if the values in the fields are the same, regardless of the other fields, only return a count of 1.  Should the date field change from 01-01-2014 to 01-02-2014 and I have three of those, only return a count of 1.  I've attached a screen shot.
SQL-GH-query.JPG
0
PortletPaulfreelancerCommented:
>>" Should the date field change from 01-01-2014 to 01-02-2014 and I have three of those,"

Q1. Do you mean if the dates are within one day of each other consider them to be equal?

Note, i don't know if you are using dd/mm/yyyy or mm/dd/yyyy so those 2 dates shown in the quote above could be one month apart (1 jan to 1 feb).

Q2. Is the number 3 significant?
i.e. what happens if there are 2 rows? or 4? or 16?

{+edit} note this comment changed (added q2)
0
PortletPaulfreelancerCommented:
The best way to help us resolve this question is to supply "sample data" and the "expected result"

the "sample data" should cover all your test cases,
and the expected result must relate to only the sample data

ps. don't use images of data please, provide the data in a reusable format.
0
PortletPaulfreelancerCommented:
Using these 3 rows of data
CREATE TABLE ThatTable
    ([EnteredBy] varchar(2), [Service] varchar(3), [EntryDt] date, [ForWhom] varchar(5))
;
    
INSERT INTO ThatTable
    ([EnteredBy], [Service], [EntryDt], [ForWhom])
VALUES
    ('A,', 'PT', '2015-08-30 00:00:00', 'Sally'),
    ('B,', 'PT', '2015-08-30 00:00:00', 'Sally'),
    ('C,', 'PT', '2015-08-30 00:00:00', 'Fred')
;

Open in new window

and this query
SELECT
      Service
    , CONVERT(VARCHAR(8),[EntryDt],112) AS EntryDt
    , COUNT(DISTINCT CONVERT(VARCHAR(8),[EntryDt],112) ) AS CountOf
FROM ThatTable
GROUP BY
      Service
    , CONVERT(VARCHAR(8),[EntryDt],112)
;

Open in new window

I get this result
| Service |  EntryDt | CountOf |
|---------|----------|---------|
|      PT | 20150830 |       1 |

Open in new window

or for a whole month I could use this query (note the date length is now 6 characters)
SELECT
      Service
    , CONVERT(VARCHAR(6),[EntryDt],112) AS EntryDt
    , COUNT(DISTINCT CONVERT(VARCHAR(6),[EntryDt],112) ) AS CountOf
FROM ThatTable
GROUP BY
      Service
    , CONVERT(VARCHAR(6),[EntryDt],112)
;

Open in new window

and I get this result:
| Service | EntryDt | CountOf |
|---------|---------|---------|
|      PT |  201508 |       1 |

Open in new window

NOTE, I have included the date column in the result only to help show you how it works, you can remove it from the select list if not needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Why a grade of B?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.