CODEWITHSUNDEEP
sqllogginglogparser
Mike Campbell
Mike Campbell

Reputation: 7978

Log Parser ORDER BY CASE statement

Quite a trivial problem here that I can't seem to resolve. I am using LogParser to analyse some telephone system CSV log files. I am trying to produce a graph of all the incoming calls between two dates, grouped by day of the week. I have achieved this, but I am struggling to have the graph display the columns in the correct order, starting with Monday. My SQL query that I pass to LogParser is as follows:

SELECT to_string(to_timestamp(c1, 'yyyy/MM/dd HH:mm'), 'dddd') as Day, count(*) as Calls
INTO graph.gif
FROM C:\logs\*.txt
WHERE c5 = 'I'
AND to_timestamp(c1, 'yyyy/MM/dd HH:mm')
    BETWEEN 
        timestamp('10/05/2012', 'dd/MM/yyyy')
    AND timestamp('24/05/2012', 'dd/MM/yyyy')
GROUP BY Day

It actually outputs in the correct order without any ORDER BY statement at all, but I think that the first column is always the day that the date range starts on, whereas I would like it to always be Monday (for easy comparison of graphs). What happens currently is it converts the timestamp from my log file into a string representation of the day of the week (for the purpose of output) and as far as I am aware there is no built in logic that 'Monday' is less than 'Tuesday' and so on. My instinct was to use a CASE statement in an ORDER BY, but LogParser won't accept this.

This is my entire LogParser command just in case it's relevant,

C:\Program Files (x86)\Log Parser 2.2\Logparser.exe" -i:CSV "SELECT to_string(to_timestamp(c1, 'yyyy/MM/dd HH:mm'), 'dddd') as Day, count(*) as Calls INTO graph.gif FROM C:\logs\*.txt WHERE c5 = 'I' AND to_timestamp(c1, 'yyyy/MM/dd HH:mm') BETWEEN timestamp('10/05/2012', 'dd/MM/yyyy') AND timestamp('24/05/2012', 'dd/MM/yyyy') GROUP BY Day" -chartType:Column3D -chartTitle:"Incoming Calls" -values:ON -config:MyConfig.js -o:CHART -headerRow OFF -iHeaderFile C:\logs\header\header.txt

Simply all I would like is for my results order to always start on Monday, regardless of the date range. Any ideas?

EDIT - This is the query with the CASE statement included in the ORDER BY.

SELECT to_string(to_timestamp(c1, 'yyyy/MM/dd HH:mm'), 'dddd') as Day, count(*) as Calls
INTO graph.gif
FROM C:\logs\*.txt
WHERE c5 = 'I'
AND to_timestamp(c1, 'yyyy/MM/dd HH:mm')
    BETWEEN 
        timestamp('10/05/2012', 'dd/MM/yyyy')
    AND timestamp('24/05/2012', 'dd/MM/yyyy')
GROUP BY Day
ORDER BY (CASE
    WHEN Day = 'Monday' THEN 1
    WHEN Day = 'Tuesday' THEN 2
    WHEN Day = 'Wednesday' THEN 3
    WHEN Day = 'Thursday' THEN 4
    WHEN Day = 'Friday' THEN 5
    WHEN Day = 'Saturday' THEN 6
    WHEN Day = 'Sunday' THEN 7
END)

Upvotes: 1

Views: 2926

Answers (2)

Goran
Goran

Reputation: 71

.....

`   CASE Day WHEN 'Monday' THEN 1
else CASE Day WHEN 'Tuesday' THEN 2
else CASE Day WHEN 'Wednesday' THEN 3
else CASE Day WHEN 'Thursday' THEN 4
else CASE Day WHEN 'Friday' THEN 5
else CASE Day WHEN 'Saturday' THEN 6
else CASE Day WHEN 'Sunday' THEN 7
END end end end end end end AS DayNumber`

........

`Order by DayNumber`

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Your order by needs to look something like:

order by (case when day = 'Monady' then 1
               when day = 'Tuesday' then 2
               when day = 'Wednesday' then 3
               when day = 'Thursday' then 4
               when day = 'Friday' then 5
               when day = 'Saturday' then 6
               when day = 'Sunday' then 7
          end)

Alternatively, if your database has a day-of-week function, then you can do something like:

order by (datepart(day, <date>)+5)%7

This is the SQL Server syntax. The datepart gives Sunday a day of 1 and Monday of 2. The arithmetic is simply to shift the Monday to 0.

Upvotes: 0

Related Questions