Some Query Help Please

[ikonis]

I've been racking my brain for the past three days, and I have yet to figure it out.

I am trying to figure out how to make a SELECT query to grab info from the database to not only break down my daily sales into registers, but then subsequently, into departments.

A few things I have figured out:
The DailySales.TypeID = 3 is the Department ID sales total
and TypeID = 4 is the Register ID sales total.

So, I'm thinking, DailySales is not where I should be looking, as it doesn't break it down any other way.

But considering the X and Z reports at the end of the day do break down the department sales (just for that register), I think it should be possible.

Thanks in advance,
- Scott

Edit: This is for a custom report I am trying to build. I am completely comfortable writing the code, just getting a headache looking at all the tables and coming up short.

 

[ikonis]

I've sort of figured it out...

Here is my query currently

SELECT
  ViewDetailedSalesReport.Department,
  SUM(CASE WHEN ViewItemMovementReport.QuantitySold = -1
           THEN ViewDetailedSalesReport.SoldPrice*-1
           ELSE ViewDetailedSalesReport.SoldPrice
           END) AS TotalSales,
  ViewDetailedSalesReport.RegisterDescription 
                      
FROM         [Transaction] INNER JOIN
                      ViewDetailedSalesReport ON [Transaction].ReceiptNumber = ViewDetailedSalesReport.ReceiptNumber INNER JOIN
                      ViewItemMovementReport ON [Transaction].ID = ViewItemMovementReport.TransactionID
WHERE     ([Transaction].Time > dbo.LocalToUtcTime(@DateFrom))
GROUP BY ViewDetailedSalesReport.Department, ViewDetailedSalesReport.RegisterDescription, ViewItemMovementReport.QuantitySold

I am trying to get the returns taken off. Right now, it just lists the department twice, one with a negative value, the other with the same positive value. (Only one transaction on that register)

 

[ikonis]

Figured it all out, was making it more complicated than I needed.