March 14, 2016 at 4:28 pm
Removing the DECLARE and WHERE part I added seems to have worked. I got confused by the original post saying I needed to use them in conjunction with the other code.
I will check this all in the morning as I've been at work for 12 hours (not just this!) and confirm this is resolved then.
Many thanks to you all and i'll speak to you tomorrow.
JT.
March 15, 2016 at 3:47 am
Yay, this is working well, thanks guys. I've managed to complete my report now. I had wanted to do a subtraction of the result of this code from the result of this code using different fields but it didn't like it, so I will leave it as is 🙂
Thanks again.
JT
March 15, 2016 at 8:02 am
I'm using 2 variables, because I don't have sample data provided by you.
I'm using @StartDT which for you is either QueueDate or StartDate if QueueDate is null.
This is translated as ISNULL(QueueDate, StartDate), so you just need to replace the variable for the expression.
Then, there's @EndDT which is EndDate. Just replace the variable for the column.
Once you replaced the variables, you can use the formula in your query.
The formula might look something like this:
SELECT Duration = STUFF(CONVERT(VARCHAR(15),DATEADD( ss, DATEDIFF( ss, ISNULL(QueueDate, StartDate), EndDate), 0),114),1,2,DATEDIFF(hh,ISNULL(QueueDate, StartDate), EndDate))
,DurationLess24 = CONVERT(CHAR(13),DATEADD( ss, DATEDIFF( ss, ISNULL(QueueDate, StartDate), EndDate), 0),114)
March 16, 2016 at 1:07 pm
Having gotten what I thought I wanted, sadly the output into Excel can't be summed no matter what format I make the columns.
E.g. The output for what we did to get the hh:mm:ss format gives this:
00:04:53
00:13:33
00:07:04
00:04:05
00:08:14
If I say "=sum(a2:a5) it returns 00:00:00". If I say "=A2+A3", it does work. Very confusing and the latter is no use to my report.
I had managed to do all the calcs using formulas previously but the file size goes to 15mb and takes 5 minutes to run. The SQL version is 800kb and runs instantly. Any ideas? I read on Excel forums that if I copy this output into another column, it does recognise it, but surely there's a way for it to do it without copying it all into other cells?
I was told to change the column manually each time on another forum by using "text to columns" as the data is being recognised as text, not numbers but then when I rerun the report it goes back to default.
:/
March 16, 2016 at 1:47 pm
Well what format are you writing the data out from the program your using? If it's a csv then yes excel will treat those as text and not sum them correctly. If you open it in excel you should be able to change the format of that column to HH:MM:SS then you could sum it.
Another option would be to do the sum in SQL and add it onto the end of the result set with a union, so you add something like this onto the end of the query.
UNION ALL
SELECT RIGHT(CONVERT(varchar(30), DATEADD(second, SUM(DATEDIFF(second, CASE WHEN QueueDate IS NULL THEN StartDate ELSE QueueDate END, EndDate)), 0), 120), 8) FROM <your table>
March 16, 2016 at 2:03 pm
I have a macro-enabled spreadsheet. I'm doing the SQL via a VB script.
I've read around some of the Excel forums, apparently this is a common problem with imported data. Although Excel says it is in either time or HH:MM:SS format, it isn't. For some people using [h]:mm:ss fomat fixes this, but everyone else has to select each column individually and then:
Data>Text To Columns>Delimited>Unselect all tick boxes then 'Next;>General.
This works for me manually, but, when recording it as a macro and trying to rerun, it doesn't work. Plus, I have 12 columns similar to what we did in this topic, so it is taking a long time.
As for doing it as one sum. I have a second worksheet that has some (fairly) complex formulas such as
SumIfs: Column A = 01/03/2016, Column B = Yes, Column C = 1, Column D=Between 08:00:00 and 08:15:00 etc. etc. so to write that all in code would take a long time.
March 16, 2016 at 2:16 pm
Is there a reason you can't do it in SQL using the method I posted?
March 16, 2016 at 2:24 pm
I'm not quite sure what to do with your code tbh. Would it help if I posted my full SQL code (with anything identifiable removed)? (EDIT: full, working code below..anything ending in Dt is DateTime2)
The part that you guys have kindly helped me with is purely part of a data dump to which I've got 3 other worksheets with fairly complex formulas querying the data the SQL pulls through.
SELECT cast(StartDt AS DATE),CONVERT(VARCHAR(8), StartDt, 108), SeqNum, User_Id,
CONVERT(VARCHAR(8), QueueStartDt, 108), CONVERT(VARCHAR(8), QStartDt, 108), CONVERT(VARCHAR(8), QueueEndDt, 108), CONVERT(VARCHAR(8), ConnClearDt, 108), CONVERT(VARCHAR(8), WrapEndDt, 108), CONVERT(VARCHAR(8), InsertDt, 108), AgentDispId,
CONVERT(VARCHAR(8), EndDt, 108), CONVERT(VARCHAR(8), ReservedStartDt, 108), CONVERT(VARCHAR(8), ReservedEndDt, 108), CASE WHEN QStartDt IS NULL THEN CONVERT(VARCHAR(8), StartDt, 108) ELSE CONVERT(VARCHAR(8), QueueEndDt, 108) END,
CASE WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '08:00:00' AND '14:00:00' THEN 1 WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '14:00:01' AND '20:00:00' THEN 2 ELSE 0 END, CASE WHEN AgentDispID IS NULL THEN DATEDIFF(ss,QueueStartDt,QueueEndDt) ELSE 0 END, DATEDIFF(ss,StartDt,EndDt), convert(char(8),dateadd(s,datediff(s,StartDt,EndDt),'1900-1-1'),8), Datepart(dw,startdt), Case when Datepart(dw,startdt) =1 THEN 0 ELSE 1 END,
CASE WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '08:00:00' AND '08:30:00' THEN 1
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '08:30:00' AND '09:00:00' THEN 2
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '09:00:00' AND '09:30:00' THEN 3
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '09:30:00' AND '10:00:00' THEN 4
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '10:00:00' AND '10:30:00' THEN 5
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '10:30:00' AND '11:00:00' THEN 6
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '11:00:00' AND '11:30:00' THEN 7
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '11:30:00' AND '12:00:00' THEN 8
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '12:00:00' AND '12:30:00' THEN 9
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '12:30:00' AND '13:00:00' THEN 10
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '13:00:00' AND '13:30:00' THEN 11
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '13:30:00' AND '14:00:00' THEN 12
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '14:00:00' AND '14:30:00' THEN 13
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '14:30:00' AND '15:00:00' THEN 14
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '15:00:00' AND '15:30:00' THEN 15
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '15:30:00' AND '16:00:00' THEN 16
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '16:00:00' AND '16:30:00' THEN 17
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '16:30:00' AND '17:00:00' THEN 18
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '17:00:00' AND '17:30:00' THEN 19
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '17:30:00' AND '18:00:00' THEN 20
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '18:00:00' AND '18:30:00' THEN 21
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '18:30:00' AND '19:00:00' THEN 22
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '19:00:00' AND '19:30:00' THEN 23
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '19:30:00' AND '20:00:00' THEN 24
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '20:00:00' AND '20:30:00' THEN 25
WHEN CONVERT(VARCHAR(8), startdt, 108) BETWEEN '20:30:00' AND '21:00:00' THEN 26 ELSE 0 END,
CASE WHEN QStartDt Is Null THEN DATEDIFF(s,StartDt,EndDt) ELSE DATEDIFF(s,QStartDt,EndDt) End, RIGHT(CONVERT(varchar(30), DATEADD(second, DATEDIFF(second, ISNULL(QStartDt, StartDt), EndDt), 0), 120), 8),
CASE WHEN WrapEndDt IS NULL THEN 0 ELSE DATEDIFF(s, ConnClearDt, WrapEndDt) END, CASE WHEN QStartDt IS NULL THEN 0 ELSE DATEDIFF(s, StartDt, QStartDt) END, RIGHT(CONVERT(varchar(30), DATEADD(second, DATEDIFF(second, ISNULL(QStartDt, StartDt), WrapEndDt), 0), 120), 8)
FROM DBO.Detailed WHERE (My_Id=82) AND StartDt BETWEEN '2016/03/01 00:00:00' AND '2016/04/01 00:00:00' ORDER BY SeqNum, StartDt
March 16, 2016 at 3:07 pm
I've managed to get my text to columns macro to work. It takes the report about 20 secs to populate but I can now use the data. Unless you can find a quick fix with the above code, please don't waste any of your time on this. Thank you all!
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply