asked on

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?

jana

ASKER

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

SOLUTION

Dustin Saunders

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

Hi! Will check!

jana

ASKER

(I was gettging 1 column)

jana

ASKER

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?

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

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 Wills

Phwew... Easy enough to overlook, and very pleased you found the rogue ')'

Hope it is now working for you :)

jana

ASKER

It is! Thanx!

Will proceed to close the question.