Query Grouping on multiple fields

Question

Post reply

Query Grouping on multiple fields

HakunaMatata

SSCrazy

Points: 2610
More actions
October 13, 2010 at 11:36 am

#239672

Hi
I want to write a query which will allow me to see the age of particular item in particular dept. It can come back in the department it has visited so time spent should be addition of total time in that dept.
for e.g.
Invoice NoRecord/Transfer TimeDepartment
12010-01-01 11:01:00A
22010-01-01 11:04:00B
12010-01-01 11:02:00C
12010-01-01 11:03:00D
22010-01-01 12:00:00A
12010-01-01 11:07:00B
32010-01-01 11:08:00A
12010-01-01 11:09:00A
Now what I want to find is, Invoice no 1 took how much time in each dept.
e.g.
Invoice Dept Time Spent
1 A00:01:00
1 C00:01:00
1 D00:01:00
1 B00:04:00
2 B00:00:00
2 B00:56:00
3 A00:00:00
I am not sure if this is possible via a single query.. anybody knows any superior way to find the expected result?
:rolleyes:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HakunaMatata SSCrazy Points: 2610 More actions · Answer 1

Well table you can consider as -

Invoice Table -

[Invoice No] int, [Record/Transfer Time] datetime, [Departname] varchar(50)

I do agree that we will need to do self join as there is no way db designer has used start and end timestamp. Only End Timestamp is used.

:rolleyes:

WayneS SSC Guru Points: 95410 More actions · Answer 2

Sacheen (10/13/2010)
I am not sure if this is possible via a single query.. anybody knows any superior way to find the expected result?

But of course... superior ways to achieve expected results is all we do around here! :w00t:

Does this do what you want it to?

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

declare @test-2 table (InvoiceNo int, [Record/Transfer Time] datetime, Department char(1));

INSERT INTO @test-2

SELECT 1,'2010-01-01 11:01:00', 'A' UNION ALL

SELECT 2,'2010-01-01 11:04:00', 'B' UNION ALL

SELECT 1,'2010-01-01 11:02:00', 'C' UNION ALL

SELECT 1,'2010-01-01 11:03:00', 'D' UNION ALL

SELECT 2,'2010-01-01 12:00:00', 'A' UNION ALL

SELECT 1,'2010-01-01 11:07:00', 'B' UNION ALL

SELECT 3,'2010-01-01 11:08:00', 'A' UNION ALL

SELECT 1,'2010-01-01 11:09:00', 'A';

WITH CTE AS

(

SELECT *,

RN = ROW_NUMBER() OVER(PARTITION BY InvoiceNo ORDER BY [Record/Transfer Time])

FROM @test-2

), CTE2 AS

(

SELECT t1.InvoiceNo,

t1.Department,

t1.[Record/Transfer Time],

ElapsedTime = t2.[Record/Transfer Time] - t1.[Record/Transfer Time]

FROM CTE t1

LEFT JOIN CTE t2

ON t1.InvoiceNo = t2.InvoiceNo

AND t1.RN = t2.RN -1

)

SELECT InvoiceNo,

Department,

ElapsedTime = IsNull(CONVERT(char(8), ElapsedTime, 108), '00:00:00')

FROM CTE2

ORDER BY InvoiceNo, [Record/Transfer Time];

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Sacheen (10/13/2010)
Well table you can consider as -
Invoice Table -
[Invoice No] int, [Record/Transfer Time] datetime, [Departname] varchar(50)
I do agree that we will need to do self join as there is no way db designer has used start and end timestamp. Only End Timestamp is used.

Hi

Find this this might be complicated i tried in my way

Declare @Temp table (inv int,dates datetime,dep varchar(5))

Declare @Temp1 table (Rid int ,inv int,dates datetime,dep varchar(5))

insert into @Temp

Select 1,'2010-01-01 11:01:00','A'

union Select 2,'2010-01-01 11:04:00','B'

union Select 1,'2010-01-01 11:02:00','C'

union Select 1,'2010-01-01 11:03:00','D'

union Select 2,'2010-01-01 12:00:00','A'

union Select 1,'2010-01-01 11:07:00','B'

union Select 3,'2010-01-01 11:08:00','A'

union Select 1,'2010-01-01 11:09:00','A'

Insert into @Temp1 Select ROW_NUMBER() over(order by inv) as Rid,* from @Temp

Select a.inv,a.dep,

case when Right('00'+Convert(varchar,datediff(mi,(Select convert(time,b.dates) from @Temp1 as b where a.Rid=b.Rid+1 ),convert(time,a.dates))),2)< 0

then '00' else Right('00'+Convert(varchar,datediff(mi,(Select convert(time,b.dates) from @Temp1 as b where a.Rid=b.Rid+1 ),convert(time,a.dates))),2) end

from @Temp1 as a

where datediff(mi,(Select convert(time,b.dates) from @Temp1 as b where a.Rid=b.Rid+1 ),convert(time,a.dates)) is not null

It is in Mins you have to convert it in hr:mi:ss

Thanks

Parthi

Thanks
Parthi

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

WayneS (10/13/2010)
Sacheen (10/13/2010)
I am not sure if this is possible via a single query.. anybody knows any superior way to find the expected result?
But of course... superior ways to achieve expected results is all we do around here! :w00t:
Does this do what you want it to?
(Having trouble posting code again... please see the attached file.)

ho..Thats was Wayne who has already given the solution.i have opened the page long time back , if i would have seen wayne solution before i post , i would not posted my post :-D:-D:-D