Dynamic pivot and calculating total and ordering the total
-
Create table #temp
(
UserName Varchar(100),
Report_Date Date,
Time_MS BIGINT,
TIME_COLUMN TIME
)
INSERT INTO #temp (UserName,Report_Date,Time_MS,TIME_COLUMN) Values
('DK1','2017-08-16',3494,'00:00:03.494'),
('DK1','2017-08-16',5241,'00:00:05.241'),
('DK2','2017-08-16',1747,'00:00:01.747'),
('DK2','2017-08-16',19217,'00:00:19.217'),
('DK2','2017-08-16',13976,'00:00:13.976'),
('DK3','2017-08-17',13976,'00:00:13.976'),
('DK3','2017-08-17',3494,'00:00:03.494'),
('DK5','2017-08-17',41928,'00:00:41.928'),
('DK6','2017-08-17',10482,'00:00:10.482'),
('DK7','2017-08-17',13976,'00:00:13.976'),
('DK8','2017-08-17',34940,'00:00:34.940'),
('DK9','2017-08-18',5241,'00:00:05.241'),
('DK1','2017-08-18',5241,'00:00:05.241'),
('DK2','2017-08-18',8735,'00:00:08.735'),
('DK3','2017-08-18',1747,'00:00:01.747'),
('DK4','2017-08-18',15723,'00:00:15.723'),
('DK5','2017-08-18',6988,'00:00:06.988'),
('DK6','2017-08-18',19217,'00:00:19.217'),
('DK7','2017-08-18',24458,'00:00:24.458'),
('DK8','2017-08-18',8735,'00:00:08.735'),
('DK9','2017-08-18',24458,'00:00:24.458')
I have provided input and required out putand right side total should be order by desc, Please let me know if any questions, Thank You guys for help 🙂
-
So do you already have a query to do that? If so, please post it.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
FROM #TEMP c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT UserName,ID, ' + @cols + ' from
(
select Username
, HH
,ID
,Report_Date
from #TEMP
) x
pivot
(
max(TIME_COLUMN)
for Report_Date in (' + @cols + ')
) p '
EXEC(@query)
I have added '31/12/2999' to get total, but I feel its not working as expected ,Thanks
-
Do you have to do this in SQL, or could you use a different tool? SSRS and a Matrix would be much easier, or even Excel and a Pivot Table.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
That's what I was gonna say... If I had the option, I'd do this in SSRS.. .Could do this in about 20 seconds.
-
I'm thinking that this is actually a pretty easy thing to do using a CROSS TAB with ROLLUP and a bit of SQL prestidigitation with pre-aggregation, the summing of times, and doing the standard thing of keeping the data layer code separate from the presentation code even if it is in the same query. I'll give it a try after work tonight.
And thank you very much for posting readily consumable test data. It does help a whole lot.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Thom A /pietlinden
I'm good with SSRS, As far I know we have to use Matrix here, but how sum will work , for example user dk1 can have multiple entries for same day( multiple time entries) and how we can sum all dates sum for one user and grand total
- koti.raavi - Wednesday, August 23, 2017 8:39 AM
SSRS can do groupings. Just like sql, you use SUM:
SUM(Fields!Time_MS.Value)Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, August 23, 2017 8:47 AM
Yes, I know that, I need sum on Time_column not on Time_Ms 🙁 , thanks for response
- koti.raavi - Wednesday, August 23, 2017 8:52 AM
Time_MS is a millisecond version of Time_column though, right? So Summing that field is still fine. you just need to do a little logic to turn it into a "time":
=Dateadd("d",0, "01/01/2000").AddMilliseconds(Sum(Fields!Time_MS.Value))Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, August 23, 2017 9:06 AM
Perfect, little logic working fine, thank you so much Thom:)
-
Heh... bit of a long day for me. Just getting back from work. Glad you folks sussed it. Nice job, Thom!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply