Solved

Spiceworks SQL Lite query help

Posted on 2013-05-28
2
403 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 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