Some Query Help Please


Joined
Aug 14, 2010
Messages
3
Reaction score
0
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.
 
Last edited:
Ad

Advertisements

Joined
Aug 14, 2010
Messages
3
Reaction score
0
I've sort of figured it out...

Here is my query currently
Code:
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)
 
Ad

Advertisements

Joined
Aug 14, 2010
Messages
3
Reaction score
0
Figured it all out, was making it more complicated than I needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query help please 12
Some Help Please? 7
USA Need Some Help in Understanding Accounting Basics 1
UK please help with my query :-( 2
SQL Query Help, Please 12
UK Invoicing queries 1
amend a PO with a query? Please help! 3
some queries on CGT 2

Top