Solved

Spiceworks SQL Lite query help

Posted on 2013-05-28
2
406 Views
Last Modified: 2013-06-17
In 6.2 they changed some of the tables and now I can't get this query to work.

  Select "1. Global" as "Site","1.6. Hours spent on maintenance this week" as "Category",(sum(ticket_work.time_spent)/3600) ||'h '|| ((sum(ticket_work.time_spent)%3600)/60) ||'m' as "Total"
  from tickets, ticket_work
  where (tickets.created_at>=date('now','-7 days')) and (category='Maintenance')

Open in new window


They moved time_spent into another table.
0
Comment
Question by:ats2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39203520
try this:
SELECT "1. Global" AS "Site"
	, "1.6. Hours spent on maintenance this week" AS "Category"
	, (sum(ticket_work.time_spent) / 3600) || 'h ' || ((sum(ticket_work.time_spent) % 3600) / 60) || 'm' AS "Total"
FROM tickets
INNER JOIN [ticket_work] ON [tickets].[id] = [ticket_work].[ticket_id]
WHERE (
		tickets.created_at >= DATE (
			'now'
			, '-7 days'
			)
		)
	AND (category = 'Maintenance')

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39253009
Hi - how did you go with this query?
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question