On Monday, October 30, 2017 at 8:34:27 AM UTC-7, Shad wrote: > > > > On Monday, October 30, 2017 at 12:55:02 PM UTC+1, Shad wrote: >> >> >> <https://lh3.googleusercontent.com/-ldK266UtSBs/WfcRZzvZPNI/AAAAAAAAAnQ/O5rxyp0n_TYXWHg2zBmdZeQseqIa5JU2QCLcBGAs/s1600/Capture.PNG> >> Hi, >> >> I wanted to create a report statistics based on version, thus I used this >> code to do the following: >> >> WITH >> cutoff_time(n, usec) AS >> (SELECT 0,1e6 * (strftime('%s', 'now')) >> UNION ALL >> SELECT n + 1, usec - (1e6 * 86400 * 7) FROM cutoff_time LIMIT 520), >> >> custom_time(id, usec, version, priority) AS >> (SELECT id, time, version, priority FROM ticket >> WHERE version IS "1.0" AND priority IN ("major", "Critical") AND >> status NOT IN ("closed", "invalid", "fixed", "duplicate", "wontfix") >> GROUP BY id), >> >> open_time(id, usec,version) AS >> (SELECT id, time, version FROM ticket >> WHERE version IS "1.0"), >> >> close_time(id, usec, version) AS >> (SELECT id, MAX(time), version >> FROM ticket >> WHERE status IN ('closed', 'fixed', 'invalid', 'duplicate', >> 'wontfix', 'closed(wontfix)', 'closed(invalid)') AND version IS '1.0' >> GROUP BY id), >> >> num_closed(n, cnt) AS >> (SELECT n, COUNT(id) >> FROM cutoff_time >> JOIN close_time ON cutoff_time.usec > close_time.usec >> GROUP BY n), >> >> num_opened(n, cnt) AS >> (SELECT n, COUNT(id) >> FROM cutoff_time >> JOIN open_time ON cutoff_time.usec > open_time.usec >> GROUP BY n), >> >> num_custom(n, cnt) AS >> (SELECT n, COUNT(id) >> FROM cutoff_time >> JOIN custom_time ON cutoff_time.usec > custom_time.usec >> GROUP BY n) >> >> SELECT >> date(cutoff_time.usec / 1e6, 'unixepoch') AS 'Date', >> num_custom.cnt AS 'Critical_&_Major_Tickets', >> num_opened.cnt - IFNULL(num_closed.cnt, 0) AS 'Active', >> num_opened.cnt AS 'ALL_Tickets', >> IFNULL(num_closed.cnt, 0) AS 'Closed' >> FROM cutoff_time >> JOIN num_custom USING (n) >> LEFT OUTER JOIN num_opened USING (n) >> LEFT OUTER JOIN num_closed USING (n) >> ORDER BY date >> >> But using this I cannot group the tickets properly. I mean the tickets >> are merging irrespective of time. For example if I create a ticket on week >> 1 and close it in week 2 , it is been counted for week 1 and week 2 i.e, >> the active tickets number for week 1 which should be 1 is changing to zero >> even if it is closed during week 2. >> >> In the attached image one can see that the 2 tickets were opened on >> 10/07/2017 and was closed on 30/10/2017 but the closed tickets are also >> being counted for other days from 10/07/2017 to 30/10/2017 >> >> Please guide me in this regard, I am sure that there is a problem of >> grouping. >> >> Thanking you & regards, >> Shad >> > > I have edited the query in the following way: > > close_time(id, usec) AS > (SELECT ticket, MAX(time) AS modified > FROM ticket_change > INNER JOIN ticket tc ON id = ticket > WHERE field = 'status' AND newvalue IN ('closed', 'fixed', 'invalid', > 'duplicate', 'wontfix', 'closed(wontfix)', 'closed(invalid)') AND version IS > '2.0.0' AND type IS 'Bug' > GROUP BY ticket), > > But I have an ambiguity error for 'time' and its normal since the component > 'time' exists in both the tables. But how I do overcome it? Any help is > appreciated. > > P.S: I used the following for the table datasets > https://trac.edgewall.org/wiki/TracDev/DatabaseSchema/TicketSystem#Tableticket > > > Regards, > Shad > >
Use as alias for ticket: "ticket t" or "ticket AS t", and reference the column as "t.time". Use a different alias for the table with the conflicting time column. https://en.wikipedia.org/wiki/Alias_(SQL) - Ryan -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to trac-users+unsubscr...@googlegroups.com. To post to this group, send email to trac-users@googlegroups.com. Visit this group at https://groups.google.com/group/trac-users. For more options, visit https://groups.google.com/d/optout.
