locked
Dynamics RMS question - Pull sales by department and group by hour or half hour RRS feed

  • Question

  • Can anyone help me here? I'm trying to run a TSQL view I made for Dynamics RMS to allow me to see all sales made in the past 24 hours, grouping them by 1/2 hour, and only show by department (my example, the department id is 1602).

    What is happening: I'm getting a number back which is great....but it doesn't match the reports I pull in the MSFT report manager. More info: I can get it to match if I do a sum(total) on the transaction table...but that too doesn't match the MSFT report...IS there another field/table/calculation I should do to see "all sales" (not voids) over the past 24 hours? Thank you very much.

    Here is the code I'm using:

    SELECT        TOP (100) PERCENT DATEPART(hh, Time) AS Half_Hour, COUNT(*) AS Num_of_Sales, SUM(Total) AS Total_Sales
    FROM            dbo.[Transaction]
    WHERE        (TransactionNumber IN
                                 (SELECT DISTINCT dbo.TransactionEntry.TransactionNumber
                                   FROM            dbo.TransactionEntry WITH (NOLOCK) INNER JOIN
                                                             dbo.[Transaction] AS Transaction_1 WITH (NOLOCK) ON dbo.TransactionEntry.TransactionNumber = Transaction_1.TransactionNumber AND 
                                                             dbo.TransactionEntry.ItemType <> 9 LEFT OUTER JOIN
                                                             dbo.Item WITH (NOLOCK) ON dbo.TransactionEntry.ItemID = dbo.Item.ID LEFT OUTER JOIN
                                                             dbo.Department WITH (NOLOCK) ON dbo.Item.DepartmentID = dbo.Department.ID LEFT OUTER JOIN
                                                             dbo.Category WITH (NOLOCK) ON dbo.Item.CategoryID = dbo.Category.ID
                                   WHERE        (dbo.Item.DepartmentID = 1602) AND (Transaction_1.Time > '2013-12-10 00:00:00') AND (Transaction_1.Time < '2013-12-10 23:59:59')))
    GROUP BY DATEPART(YEAR, Time), DATEPART(MONTH, Time), DATEPART(DAY, Time), DATEPART(HOUR, Time), DATEPART(MINUTE, Time) / 30

    Thank you.

    Friday, December 13, 2013 4:53 PM

All replies

  • This is a Dynamics CRM Forum. For help from RMS SMEs pls post you question in http://www.microsoftdynamicsforums.com/forums/forum_topics.asp?FID=8

    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    • Proposed as answer by Mamatha Swamy Friday, December 13, 2013 5:54 PM
    • Marked as answer by commandlinekid Friday, December 13, 2013 8:31 PM
    • Unmarked as answer by commandlinekid Sunday, December 15, 2013 8:17 PM
    Friday, December 13, 2013 5:53 PM
  • ok. But tell them new accounts are not setting up. I'm getting this error:


    Server Error in Forum Application
    An error has occurred while sending an email.
    Please check that JMail 4 or above email component is installed on the server.
    Please contact the Forum Administrator.

    Support Error Code:- err_SQLServer_create_JMail_4_object
    File Name:- functions_send_mail.asp
    Forum Version:- 10.03

    Friday, December 13, 2013 8:06 PM
  • Oh! sorry to hear that. Forum issues are addressed here http://social.microsoft.com/Forums/en-US/home?forum=reportabug&filter=alltypes&sort=lastpostdesc

    I'm just a participant and don't think I can do that for you.


    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'



    Friday, December 13, 2013 8:28 PM
  • NOTE: It looks like THIS is a real Microsoft Dynamics forum I think...

    https://community.dynamics.com/rms/f/106.aspx

    Sunday, December 15, 2013 8:19 PM