Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Created tables with your structure. Very minor column name changes are there. Please change accordingly. ActualCost is ActualPrice in my table.</p> <p>Following will be the query</p> <pre><code>SELECT ISNULL(SalesJournalUserId,TotalSalesUserId) AS UserId, ISNULL(SalesJournalUserName,TotalSalesUserName) AS UserName, [Orders Yesterday], [Lines Yesterday], [Sales Yesterday], [Cost Yesterday], [Sales MTD], [Cost MTD] FROM ( ( SELECT SalesUser.Id as SalesJournalUserId, SalesUser.Name as SalesJournalUserName, [Orders Yesterday], [Lines Yesterday], [Sales Yesterday], [Cost Yesterday] FROM [SalesUser] LEFT JOIN ( select [User].Id, [User].Name, Count(distinct(SalesOrderJournalDetail.ID)) as 'Orders Yesterday', Count((SalesOrderJournalDetail.ID)) as 'Lines Yesterday', convert(float,round(sum(SalesOrderJournalDetail.Price*SalesOrderJournalDetail.Shipped),2)) as 'Sales Yesterday', convert(float,round(sum(SalesOrderJournalDetail.ActualPrice*SalesOrderJournalDetail.Shipped),2)) as 'Cost Yesterday' from [SalesUser] as [User] LEFT JOIN SalesOrderJournalDetail on [User].Id = SalesOrderJournalDetail.SalesPersonUserId where --[User].Id =1 and convert(varchar(10),SalesOrderJournalDetail.InvoiceDate,111) = '2012/04/19' group by [User].Id,[user].Name ) SOJD ON SalesUser.Id = SOJD.ID ) SOJDALLUSERS INNER JOIN ( SELECT SalesUser.Id as TotalSalesUserId, SalesUser.Name as TotalSalesUserName, [Sales MTD], [Cost MTD] FROM [SalesUser] LEFT JOIN ( select [User].Id, [User].Name, convert(float,sum(UserTotal.SalesMTD),2) as 'Sales MTD', round(convert(float,sum(UserTotal.CostMTD),2),2) as 'Cost MTD' from [SalesUser] as [User] LEFT JOIN UserTotal on [User].Id = UserTotal.UserId --where --[User].Id =1 group by [User].Id,[User].name ) AS SOUT ON SalesUser.Id = SOUT.ID ) SOUTALLUSERS ON SOJDALLUsers.SalesJournalUserId = SOUTALLUSERS.TotalSalesUserId ) </code></pre> <p>How it works Subquery SOJDALLUSERS: Joins SalesUser table with your SalesOrderJournalTable. Fetches data for every user</p> <p>Subquery SOUT : Joins SalesUser table with UserTotal table. fetches data for every user. Your SalesMTD and CostMTD should be a "SUM" for every user if i am not wrong. You have missed it.</p> <p>The above two results are inner joined to get the details for every user. Since the above subquery brings data for every user in sales table, an inner join for the above two subqueries will guarantee data for all users with appropriate null values whenever data is missing.</p> <p>I have created the tables and checked following scenarios 1. data in both journal and salestotal table for a user 2. data in journal table but not in salestotal table for a user 3. data in salestotal table but not in journal table for a user.</p> <p>IT WORKS.. ENJOY</p> <p>Table schema for your reference<img src="https://i.stack.imgur.com/XiJMM.jpg" alt="Table schema for your reference"></p> <p>Mark as answer if it gives you some direction into your problem. It was a nice challenge doing this query..</p>
    singulars
    1. This table or related slice is empty.
    1. POOne Query, Three Tables, Show all rows in left table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USajay swamy
    1. USajay swamy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOne Query, Three Tables, Show all rows in left table?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTHANK YOU VERY MUCH, much thorough answer than I expected :) However it doesn't seem to work when I stick it into SQL Management Studio to test it. I get a "Invalid object name 'SalesUser'." error. I changed "ActualPrice" back to "ActualCost" to match my database but am unsure if there are others I need to change as well? Sorry, I'm very new to SQL. Thank you!
      singulars
      1. PO
      1. USBlueCaret
    2. COMy table name is [SalesUser] and your table name is [User].. you need to make that change and if any if required to match your table schema... It should work since i have created the tables, inserted data and ran the above query.. just make sure the object names i.e. table and column names match your table..
      singulars
      1. PO
      1. USajay swamy
    3. COGot it! It works! THANK you SO much! I've been trying to figure this out for a week and based on what the query is now, I was no where close :)
      singulars
      1. PO
      1. USBlueCaret
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload