SQL Query for timebookings on projects epics, sprints, stories and items for Zoho Analytics

SQL Query for timebookings on projects epics, sprints, stories and items for Zoho Analytics

At CEYOND, we use Zoho Sprints for our agile projects. To evaluate and charge the effort of the tasks we use Zoho Analytics.

With this SQL query you could access all relevant accounting data and assign it to projects, sprints, epics, stories and items. Hope this helps.

  2. SELECT
  3. date(substr(t."Log Date", 1, 10)) "Log Date",
  4. t."Description" "Description",
  5. t."Log Time in Minutes" "Log Time in Minutes",
  6. t."Log Time in Hours" "Log Time in Hours",
  7. t."Last Updated Time" "Last Updated Time",
  8. t."Billable Type" "Billable Type",
  9. ou."User Name" "Owner",
  10. au."User Name" "Approved By",
  11. p."Project Name" 'Project Name',
  12. s."Sprint Name" 'Sprint Name',
  13. s."Sprint Type" 'Sprint Type',
  14. i."Item No" 'Item No',
  15. i."Item Name" 'Item Name',
  16. i."Item Type Name" 'Item Type Name',
  17. i."Priority Name" 'Item Priority Name',
  18. e."Epic Name" 'Epic Name',
  19. e."Epic Type" 'Epic Type'
  20. FROM  "Timesheets (Zoho Sprints)" t
  21. LEFT JOIN "Users (Zoho Sprints)" au ON (au."ZSUser ID"  = t."Approved By") 
  22. LEFT JOIN "Users (Zoho Sprints)" ou ON (ou."ZSUser ID"  = t."Owner ID") 
  23. LEFT JOIN "Items (Zoho Sprints)" i ON (i."Item ID"  = t."Item ID") 
  24. LEFT JOIN "Sprints (Zoho Sprints)" s ON (s."Sprint ID"  = i."Sprint ID") 
  25. LEFT JOIN "Epics (Zoho Sprints)" e ON (e."Epic ID"  = i."Epic ID") 
  26. LEFT JOIN "Projects (Zoho Sprints)" p ON (p."Project ID"  = i."Project ID")  
  27. ORDER BY t."Log Date" 

    Access your files securely from anywhere

          Zoho Developer Community

              Bootcamp
              Community
              Pricing
              Webinars
              What's New
              Integrations
              Contact Support
              Contact Sales

                Zoho Survey Resources
                  Features
                  Survey Templates
                  Integrations
                  Community
                  iOS Mobile Surveys
                  Pricing
                  Android Mobile Surveys
                  Insurvey Blogs
                  Insight Blogs
                  Tips & Tricks





                                Latest Feature Updates


                                    Zoho Desk Resources

                                    • Desk Community Learning Series


                                    • Digest


                                    • Functions


                                    • Meetups


                                    • Kbase


                                    • Resources


                                    • Glossary


                                    • Desk Marketplace


                                    • MVP Corner


                                    • Word of the Day




                                        Zoho Marketing Automation
                                          Zoho SalesIQ Resources

                                                • Sticky Posts

                                                • Tip #28 - Plan less and deliver more using WIP limit in Zoho Sprints

                                                  Hello, It's been a while since we met with a quick, interesting tip. As the saying goes, "Too much of anything is good for nothing", today the focus is on delivering your outcomes with the right amount of planning. Your plan should be practical, calculative, and achievable for driving a qualitative success.    Laura's plan   Laura has a habit of planning her project deliverables before assigning work to her team-mates. Once the plan is finalized, she schedules a general meeting with her team and

                                                • Tip # 3- Working on the Scrum board

                                                  Continuing from our Tip #2 on leading to a sprint, let's see how to manage the work items on the Scrum board.  Once you start the sprint your work items are automatically displayed on the Scrum board where you will actually manage the work items. It is a snapshot of the backlog items identified for the current sprint.    The layout of the Scrum board Simply put, the scrum board is just like a physical board with sticky notes on which the work items of the active sprint are displayed.  The scrum board

                                                • Tip#2- Leading you to a Sprint

                                                  Product Backlog After the user stories are written and finalized, they are sorted to create the Product Backlog for the project during the Backlog Grooming meeting. This is a master list of all the work items that have been identified for the project and sorted by priority. Requirements are not constant during this period.The Product Backlog is dynamic and is an ongoing process. Every user story in the Product Backlog is customer centric. The Product Backlog includes: User centric stories based on

                                                • Tip #1- Why swimlanes?

                                                  Lanes define a clear path to reach your destination. In Agile, swimlane is one such concept that sets a simple and clear process of the work that you do.   The concept of swimlane can be related to the pool, where the swimmers gather in their respective lanes to start off. Similar to the pool, work items are grouped in different categories. Each category is referred as a lane and is displayed in horizontal format. Swimlanes are effective in categorizing the work items with respective to specific


                                                Manage your brands on social media
                                                Access Zoho Social

                                                  Zoho Pagesense Resources


                                                      Zoho TeamInbox Resources

                                                        Zoho DataPrep Resources



                                                          Zoho CRM Plus Resources

                                                            Zoho Books Resources


                                                              Zoho Subscriptions Resources

                                                                Zoho Projects Resources


                                                                  Zoho Sprints Resources


                                                                    Qntrl Resources


                                                                      Zoho Creator Resources



                                                                          Zoho Campaigns Resources


                                                                            Zoho CRM Resources

                                                                            • CRM Community Learning Series

                                                                              CRM Community Learning Series


                                                                            • Kaizen

                                                                              Kaizen

                                                                            • Functions

                                                                              Functions

                                                                            • Meetups

                                                                              Meetups

                                                                            • Kbase

                                                                              Kbase

                                                                            • Resources

                                                                              Resources

                                                                            • Digest

                                                                              Digest

                                                                            • CRM Marketplace

                                                                              CRM Marketplace

                                                                            • MVP Corner

                                                                              MVP Corner





                                                                                Design. Discuss. Deliver.

                                                                                Create visually engaging stories with Zoho Show.

                                                                                Get Started Now


                                                                                  Zoho Show Resources


                                                                                    Zoho Writer Writer

                                                                                    Get Started. Write Away!

                                                                                    Writer is a powerful online word processor, designed for collaborative work.

                                                                                      Zoho CRM コンテンツ






                                                                                        Nederlandse Hulpbronnen


                                                                                            ご検討中の方





                                                                                                  • Recent Topics

                                                                                                  • LinkedIn verification link and otp not receiving

                                                                                                    For the last 2 to 3 weeks I'm trying to verify my LinkedIn account to access my company's LinkedIn page, Linkedin is sending verification links and codes to this email address but I have not received any codes or links. Please help me here. Looking forward

                                                                                                  • 2024 Wrap: Rediscover these features and enhancements in Zoho CRM

                                                                                                    Hello everyone! I wish all of you a joyful and prosperous 2025! As we welcome 2025, I’m excited to share a recap of the year 2024 and highlight some of the coolest new features and enhancements we’ve added to the Zoho CRM platform. Last year, we announced

                                                                                                  • Allow "add new" option to picklists and multiselection fields from add or edit pages

                                                                                                    Hello zoho, please insert an add option (ie +)sign) to pick and multiselect fields so we can add new options while entering or editing records. For example. in my lead module, while adding a new record, I realized I had a new lead source. I went to my

                                                                                                  • A quicker way to provide accountants access to Zoho Books, similar to Xero and Quickbooks

                                                                                                    Hey guys, I'm finding the procedure to give access to an external accountant to Zoho Books less than ideal. Having to create an account by Zoho instead of myself, and then wait for it to be given a license to then pass to the accountant seems a bit time

                                                                                                  • This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details

                                                                                                    Hello, Just signed up to ZOHO on a friend's recommendation. Got the TXT part (verified my domain), but whenever I try to add ANY user, I get the error: This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details I have emailed as well and writing here as well because when I searched, I saw many people faced the same issue and instead of email, they got a faster response here. My domain is: raisingreaderspk . com Hope this can be resolved.  Thank you

                                                                                                  • This mobile number has been marked spam. Please contact support.

                                                                                                    Hi Support, Can you tell me why number was marked as spam. I have having difficult to add my number as you keep requesting i must use it. My number is +63....163 Or is Zoho company excluding Philippines from their services?

                                                                                                  • Update Candidate Status Through Workflow in Blueprint

                                                                                                    Hi Team,  We have a blueprint built out with custom functions that update particular fields based on candidate actions. When particular fields are updated we need to move the candidate forward in the blueprint. We tried to do this through a workflow,

                                                                                                  • Zoho Canned respond do have a huge lag issue.

                                                                                                    Previously the Zoho canned respond works perfectly ... on once server update and all the Canned respond enconter huge lag... in the end cause most of the canned respond just shown code with /xxx and not the sentence....

                                                                                                  • Pay Contractor Timesheets

                                                                                                    I have contractors that fill out a timesheet. Each hour must be assigned to a current client. I need the easiest way to get the contracts paid. They are paid on an hourly bases. How can this be done?

                                                                                                  • ShipStation and Zoho Inventory

                                                                                                    Hello, I am looking to sync zoho inventory with shipstation ZOHO INVENTORY           SHIP STATION Sales Order  ==>  create ORDERS INVOICE  <==    Shipments What exactly does BETA mean on the Shipstation connector?  This is required for me to sign-on in the next month. Thanks in advance for your efforts

                                                                                                  • Saving slide elements

                                                                                                    I have created grouped items including text and animation that I want to use in later slides. (Like an animated logo) Is there a way to save these grouped elements in my library?

                                                                                                  • Are downloadable product available in Zoho Commerce

                                                                                                    Hi all. We're considering switching to Zoho Commerce for our shop, but we sell software and remote services. Is there a features for downloadable products? I can't find any information about this. Thank you very much Alice

                                                                                                  • Function #10: Update item prices automatically based on the last transaction created

                                                                                                    In businesses, item prices are not always fixed and can fluctuate due to various factors. If you find yourself manually adjusting the item rates every time they change, we have the ideal time-saving solution for you. In today's post, we bring you custom

                                                                                                  • Move site from WIX to ZOHO Sites

                                                                                                    I have a simple website on WIX.  I am wondering if someone is available to help me move this website - https://www.videothreezero.com/ to ZOHO.  Michael  Boston

                                                                                                  • zoho calendar week view - "super compact by default"

                                                                                                    every time i go to my calendar i have to re-engage the "super-compact view" for the week view...is there a way to make "super-compact" a default view so I dont have to keep on setting it manually?

                                                                                                  • Change work hours per day for employees

                                                                                                    Hello, Is there a way to modify the work hours per day for employees in Zoho projects? This would be helpful for resource allocation to more accurately see when an employee who works 35 hours a week vs 40 hours has a full schedule. Thanks.

                                                                                                  • Zoho CRM Automation Help: Send Email When Fault is Marked as Done & Module Relationships

                                                                                                    Hi everyone, I have the following User-Created Modules in Zoho CRM: Clients Assets Faults Handymen Every client can have multiple assets. Every asset can have multiple faults. Every fault is assigned to one handyman. What I Want to Achieve: ✅ I want to

                                                                                                  • Adding New Domain to Zoho mail

                                                                                                    Hi, I have one Zoho account already called for example "Awesome Animals". Under this account I have one domain already setup with zoho mail, example: - awesomecats.com I have another website as well which I want to add under this "Awesome Animals" account,

                                                                                                  • I cannot receive emails.

                                                                                                    I need help, I've tried everything but I still can't receive emails from other people. I can send it but I can't receive emails, When I created the email it was all in order and suddenly I can't get emails from anyone anymore.

                                                                                                  • Incoming Gmail Email Not Coming Into Zoho

                                                                                                    My outbound email from Zoho is working, but when people respond to the email, it's not coming back into Zoho. I can see it when I'm in Gmail, but it's not in Zoho.

                                                                                                  • Não foi possível enviar a mensagem;Razão:554 5.1.8 Email Outgoing Blocked.

                                                                                                    Preciso de ajuda não consigo enviar emails,conta recen criada

                                                                                                  • ERROR_CODE :554, ERROR_CODE :rejected due to spam

                                                                                                    Please verify bounce message: This message was created automatically by mail delivery software.  A message that you sent could not be delivered to one or more of its recipients. This is a permanent error.  xxx@thalesesec.com Error, ERROR_CODE :554, ERROR_CODE :rejected due to spam content yyy@thalesesec.com Error, ERROR_CODE :554, ERROR_CODE :rejected due to spam content  Received:from RMRPC (190.102.146.226 [190.102.146.226]) by mx.zohomail.com with SMTPS id 1376415816667533.3706558194896; Tue,

                                                                                                  • Expand Zia's Language Support and AI Capabilities

                                                                                                    Dear Zoho Desk Support, I would like to submit a feature request to improve Zia, the AI-driven support assistant in Zoho Desk. Currently, Zia only supports the English language, while other AI agents such as Gemini, ChatGPT, and Claude can work with a

                                                                                                  • Average Costing / Weighted Average Costing

                                                                                                    Hello fellow maadirs. I understand Zoho Books uses FIFO method of dealing with inventory costing, but do you guys have any plans to introduce average costing? We indians need average costing. It's part of our culture. Please. I beg thee. Thanks.

                                                                                                  • Links in Instagram

                                                                                                    Hi there, I have been using Later for a while now but keen to come back to Zoho Social as Later doesn't offer tagging of pages on Facebook but they offer something Zoho doesn't. You can add a link to your bio which opens up your profile feed where images

                                                                                                  • Credit note

                                                                                                    By mistake I issued credit note in Jan 2025 for the invoice related to Dec 2024. Now I want to delete this credit note but anable to do so. Need help in this

                                                                                                  • How to query for Deals record based on Pipeline?

                                                                                                    I want to query for Deals records that matches a specified Pipeline using a Deluge function. When I call zoho.crm.searchRecords("Deals","(Pipeline:equals:" + myPipeline + ")"), I get this error: { code: 'INVALID_QUERY' , details: {...} , message: 'Invalid

                                                                                                  • CRM formula field help

                                                                                                    Hello! i was hoping to get some help with a formula i'm creating within a module. I'm looking to make a formula that changes based on a date field but based upon the present date. This is the formula i have so far: If(Now() < ${Instructors.Start Date},

                                                                                                  • Change to copy/paste functionality in Deluge code editor

                                                                                                    Recently there was a change to the Deluge code etidor where it now inserts backslashes into strings automatically when copying/pasting strings with double quotes, it's a nightmare to have to go delete all these. Is it possible to toggle this on or off?

                                                                                                  • ChatGPT only summarize in English

                                                                                                    Hello i' v enabled chatgpt in salesIQ, it works great inside conversation (revise, Rephrase etc) add tags works well with another language than English. But when I want to summarize it render only in English, despite sales IQ is set to another language.

                                                                                                  • Chart with Filtered Data vs Unfiltered Data

                                                                                                    I am looking to create a chart view that displays the full data set vs a subset of the data filtered by user filter. However I do not seem to find any method by which to exclude a plot from the applied filter or any other method by which to display the

                                                                                                  • BIN Locations

                                                                                                    Hi, I’m new to Zoho inventory and unless Im missing something, I cannot find BIN locations anywhere in ‘items’? please tell me it’s there somewhere?!? Thanks

                                                                                                  • Can't verify domain with AWS Route53

                                                                                                    I have a domain successfully transferred to AWS Route53 from NameCheap. When I try to CNAME or TXT Records as suggested, they are added in AWS console however zohomail does not verify them.  For the TXT record zohomail says the value is wrong, whereas for CNAME record zohomail can't find any CNAME record. I don't know what to do! 

                                                                                                  • Send Whatsapp with API including custom placeholders

                                                                                                    Is is possible to initiate a session on whatsapp IM channel with a template that includes params (placeholders) that are passed on the API call? This is very usefull to send a Utility message for a transactional notification including an order number

                                                                                                  • BANK FEED - MAYBANK , provider from YODLEE IS NOT WORKING

                                                                                                    As per topic, the provider YODLEE is not working for the BANK FEED. It have been reported since 2023 Q3, and second report on 2023 Q4. now almost end of 2024 Q1, and coming to 2024 Q2. Malaysia Bank Maybank is NOT working. can anyone check on this issue?

                                                                                                  • Create a custom button to modify custom fields in zoho Inventory

                                                                                                    I am needing a script for two buttons, 1. Button will add todays date to a custom field named cf_sent_to_sov 2. Button will mark a checkbox or unmark a checkbox field named cf_parts_ordered I have been trying to figure out deluge but have not got anywhere

                                                                                                  • Canadian payroll

                                                                                                    Hello ZOHO, is any updates on when payroll for Canada will be available?

                                                                                                  • Default reminders on Emails

                                                                                                    I have seen that it is possible to set, for each email, a reminder when the email remains unresponded for some time. This is very useful, so useful that I would like to set it for all my emails, by default. Because an unanswered email usually requires

                                                                                                  • in zoho assist I would like to temporarliy go into view only mode

                                                                                                    During a zoho assist remote support / remote control session, I would like an option to turn off control, and go into view only mode. Then later, turn back on control, all within the same session. (Many other remote login apps have this feature.) For

                                                                                                  • QR code image is not exported in PDFs

                                                                                                    The new QR code field works fine when I include it in a report template and I choose the print option: https://creatorapp.zoho.com/<username>/<app_link_name>/record-print/<report_link_name>/<record_ID>/ But when I try to save the document to a .pdf file

                                                                                                  • Next Page