Ms SQL script to extract data from a resulting script and present new results

We have a Ms Sql script that groups transactions by dates, tools and how many techs used them.  When running the query, the result is as follows:
Date1      tools  ToolUse
---------- ------ -------
2017-01-03 Desk   2
2017-01-03 Hammer 3
2017-05-10 Hammer 1
2017-10-02 Desk   1
2017-10-02 Hammer 2

Open in new window


We are trying to extract from that result the the tools used the same days.

Based on the above the result we are looking for is:

Date     Tools      Date      Tools
-------- ---------- --------- ----------
1/3/2017 Desk (2)   10/2/2017 Desk (1)
1/3/2017 Hammer (3) 10/2/2017 Hammer (2)

Open in new window


Notice that the date 2017-05-10 is not present because only 1 tool was used that day.

We are looking into the "with" clause as to extract from the first result to get the second.

What would be the best way to go about this?
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

rayluvsAuthor Commented:
Again forgot to include the test script I am used:

	With TableName(Tools,Date1,Time1,Tech)
		AS (select 'Hammer','2017-01-03', '12:00:43', 'Joe' union all
		    select 'Hammer','2017-01-03', '12:15:00', 'Dereck' union all
		    select 'Hammer','2017-01-03', '12:17:00', 'Lillian' union all
		    select 'Desk','2017-01-03', '10:27:01', 'Sandy' union all
		    select 'Desk','2017-01-03', '11:00:00', 'Joe' union all
		    select 'Hammer','2017-05-10', '10:00:00', 'Joe' union all
		    select 'Desk','2017-10-02', '11:00:00', 'Joe' union all
		    select 'Hammer','2017-10-02', '14:27:00', 'Smith'  union all
		    select 'Hammer','2017-10-02', '16:00:00', 'Smith')


SELECT Date1,  tools, cast(COUNT(*) as varchar(3)) AS ToolUse
FROM TableName GROUP BY tools,Date1

Open in new window

Dustin SaundersDirector of OperationsCommented:
Probably the simplest way is to just filter on Date1 counts greater than 1.

	With TableName(Tools,Date1,Time1,Tech)
		AS (select 'Hammer','2017-01-03', '12:00:43', 'Joe' union all
		    select 'Hammer','2017-01-03', '12:15:00', 'Dereck' union all
		    select 'Hammer','2017-01-03', '12:17:00', 'Lillian' union all
		    select 'Desk','2017-01-03', '10:27:01', 'Sandy' union all
		    select 'Desk','2017-01-03', '11:00:00', 'Joe' union all
		    select 'Hammer','2017-05-10', '10:00:00', 'Joe' union all
		    select 'Desk','2017-10-02', '11:00:00', 'Joe' union all
		    select 'Hammer','2017-10-02', '14:27:00', 'Smith'  union all
		    select 'Hammer','2017-10-02', '16:00:00', 'Smith')


SELECT Date1,  tools + ' (' + cast(COUNT(*) as varchar(3)) + ')' AS Tools
FROM TableName 
WHERE Date1 IN (SELECT Date1 FROM TableName GROUP BY Date1 HAVING COUNT(Date1) > 1)
GROUP BY tools,Date1

Open in new window

Mark WillsTopic AdvisorCommented:
Well, I get different results in the counts, but did double check and think it is right...
With TableName(Tools,Date1,Time1,Tech) AS 
(  select 'Hammer','2017-01-03', '12:00:43', 'Joe' union all
   select 'Hammer','2017-01-03', '12:15:00', 'Dereck' union all
   select 'Hammer','2017-01-03', '12:17:00', 'Lillian' union all
   select 'Desk','2017-01-03', '10:27:01', 'Sandy' union all
   select 'Desk','2017-01-03', '11:00:00', 'Joe' union all
   select 'Hammer','2017-05-10', '10:00:00', 'Joe' union all
   select 'Desk','2017-10-02', '11:00:00', 'Joe' union all
   select 'Hammer','2017-10-02', '14:27:00', 'Smith'  union all
   select 'Hammer','2017-10-02', '16:00:00', 'Smith'
), grp as
(  SELECT Date1,  tools, COUNT(*) AS ToolUse
   FROM TableName 
   GROUP BY tools,Date1
) select min(date1) as Sdate, tools, sum(tooluse) as tcount, max(date1) as edate, count(date1) as dcount
  from grp 
  where tooluse > 1
  group by tools

Open in new window

Results :
Sdate	tools	tcount	edate      	dcount
2017-01-03	Desk	2	2017-01-03	1
2017-01-03	Hammer	5	2017-10-02	2

Open in new window

Havent put the counts in brackets  above yet - but just a matter of formatting accordingly
With TableName(Tools,Date1,Time1,Tech) AS 
(  select 'Hammer','2017-01-03', '12:00:43', 'Joe' union all
   select 'Hammer','2017-01-03', '12:15:00', 'Dereck' union all
   select 'Hammer','2017-01-03', '12:17:00', 'Lillian' union all
   select 'Desk','2017-01-03', '10:27:01', 'Sandy' union all
   select 'Desk','2017-01-03', '11:00:00', 'Joe' union all
   select 'Hammer','2017-05-10', '10:00:00', 'Joe' union all
   select 'Desk','2017-10-02', '11:00:00', 'Joe' union all
   select 'Hammer','2017-10-02', '14:27:00', 'Smith'  union all
   select 'Hammer','2017-10-02', '16:00:00', 'Smith'
), grp as
(  SELECT Date1,  tools, COUNT(*) AS ToolUse
   FROM TableName 
   GROUP BY tools,Date1
) select min(date1) as Sdate, tools + ' (' + cast(sum(tooluse) as varchar(3))+')' [tools used], max(date1) as Edate, tools + ' ('+cast(count(date1) as varchar(3))+')' as [Dates Used]
  from grp 
  where tooluse > 1
  group by tools

Open in new window

And that final select could be written as (in SQL2012)
  select min(date1) as Sdate, tools + format(sum(tooluse),' \(#\)') [tools used], max(date1) as Edate, tools + format(count(date1),' \(#\)') [Dates Used]

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rayluvsAuthor Commented:
Hi! Will check!
rayluvsAuthor Commented:
(I was gettging 1 column)
rayluvsAuthor Commented:
Thank you very much, worked!  But when running against the actual msSql table, gives an error.

with grp as
(SELECT Date1, tools, COUNT(*) AS ToolUse FROM TableName where Tech='Jon'
GROUP BY tools,Date1)
 select min(date1) as Sdate, tools + ' (' + cast(sum(tooluse) as varchar(3))+')' [tools used], 
 max(date1) as Edate, tools + ' ('+cast(count(date1) as varchar(3))+')' as [Dates Used]
 from grp 
 where tooluse > 1
 group by tools

Open in new window


Noticed that if I take out the ")" from "GROUP BY tools,Date1)", it runs but displays but wrong values:
with grp as
(SELECT Date1, tools, COUNT(*) AS ToolUse FROM TableName where Tech='Jon'
GROUP BY tools,Date1
 select min(date1) as Sdate, tools + ' (' + cast(sum(tooluse) as varchar(3))+')' [tools used], 
 max(date1) as Edate, tools + ' ('+cast(count(date1) as varchar(3))+')' as [Dates Used]
 from grp 
 where tooluse > 1
 group by tools

Open in new window



Am I missing something in the script when running against a SQL table rather than a virtual table?
Mark WillsTopic AdvisorCommented:
Hmmm... What was the error ?

"with" is an sql construct for Common Table Expression (aka CTE)

It basically says WITH my CTE name AS (select stuff) I can then use select * from that CTE name. Bit like a subquery, except we establish the query first, give it a name, and then use the results of that query. Little bit confusing to begin with.

In my post above we went a step further to embed another CTE layer (named as grp)

In your code above, the CTE name given for
(SELECT Date1, tools, COUNT(*) AS ToolUse 
  FROM TableName where Tech='Jon'
 GROUP BY tools,Date1)

Open in new window

is "grp" and needs both those brackets. If by removing the closing bracket, it runs, then I think there is a different problem.

If I run
with grp as
(  SELECT Date1,  tools, COUNT(*) AS ToolUse
   FROM TableName 
   --where Tech = 'Jon'             -- no Jon in sample data
   GROUP BY tools,Date1
)
  select min(date1) as Sdate, tools + ' (' + cast(sum(tooluse) as varchar(3))+')' [tools used], max(date1) as Edate, tools + ' ('+cast(count(date1) as varchar(3))+')' as [Dates Used]
  from grp 
  where tooluse > 1
  group by tools

Open in new window

I certainly get results. Now the WITH construct must be the first statement in a batch, so we often see a semicolon in front ie ";with grp as ..... "
What was the error message ?

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
rayluvsAuthor Commented:
Hi! My mistake!  Since your script worked I reviewed my again ans it was that SQL studio design windows made it dificult for me to see that specific ")" that was in the row below.

Listen, thank you very much!
Mark WillsTopic AdvisorCommented:
Phwew... Easy enough to overlook, and very pleased you found the rogue ')'

Hope it is now working for you :)
rayluvsAuthor Commented:
It is! Thanx!

Will proceed to close the question.
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

From novice to tech pro — start learning today.