Currently I have a query that captures all sales for specific manufacturing codes within a date range.
I would like to capture only those customers that have sales greater than x dollars, for the same mfg codes and date range. Would I have to create a maketable of the first data, aggregate by customer to obtain my solution or is there a one step method in the same query. MS Access SQL statment is below.
SELECT dbo_vw_Dimension_CalendarDate.MonthsAgo, dbo_vw_Dimension_CalendarDate.YearSeq AS [Year], Left([CalendarMonth],(InStr([calendarmonth]," ")-1)) AS [Month], Left([Calendarquarter],(InStr([calendarquarter]," ")-1)) AS Quarter, dbo_vw_Dimension_CalendarDate.CalendarDate AS InvoiceDate, dbo_vw_Dimension_Customer.CustomerCodeINT, dbo_vw_Dimension_Customer.CustomerShortDescription, dbo_vw_Fact_Invoice_Detail.InvoiceCode, dbo_vw_Fact_Invoice_Detail.InvoiceLineSeq, dbo_vw_Dimension_Order.OrderCode, dbo_vw_Fact_Invoice_Detail.ReferenceCode, dbo_vw_Fact_Invoice_Detail.SalesQuantity, dbo_vw_Fact_Invoice_Detail.SalesDollarAmount, dbo_vw_Dimension_Product.ItemCode, dbo_vw_Dimension_Product.ItemDescription, dbo_vw_Dimension_Product.ItemTypeCode, dbo_vw_Dimension_Product.ItemTypeDescription, dbo_vw_Dimension_Product.ItemClassCode, dbo_vw_Dimension_Product.ItemClassDescription, dbo_vw_Dimension_Product.BrandDescription, dbo_vw_Dimension_Product.ManufacturerCode, dbo_vw_Dimension_Product.ManufacturerDescription, dbo_vw_Dimension_Product.ItemManufacturerPartNumber, dbo_vw_Dimension_Coworker_AccountManager.AccountManagerCode, dbo_vw_Dimension_Coworker_AccountManager.CoworkerCode, dbo_vw_Dimension_Coworker_AccountManager.CoworkerName, dbo_vw_Dimension_Coworker_AccountManager.ManagerCoworkerCode, dbo_vw_Dimension_Coworker_AccountManager.ManagerCoworkerName, dbo_vw_Dimension_Coworker_Manager.EMailAddress AS MgrEmail, dbo_vw_Dimension_Coworker_AccountManager.TerminationFlag, dbo_vw_Dimension_Coworker_AccountManager.TerminationDescription, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel4Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel5Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel6Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel7Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel8Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel9Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel10Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel11Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel12Alias INTO Aletha75Kinvoice
FROM dbo_vw_Dimension_Order INNER JOIN (dbo_vw_Dimension_Coworker AS dbo_vw_Dimension_Coworker_Manager INNER JOIN (dbo_vw_Dimension_Commissionable INNER JOIN (dbo_vw_Dimension_Coworker_AccountManager INNER JOIN (dbo_vw_Dimension_Company INNER JOIN (dbo_vw_Dimension_Customer INNER JOIN (dbo_vw_Dimension_CalendarDate INNER JOIN (dbo_vw_Dimension_Product INNER JOIN dbo_vw_Fact_Invoice_Detail ON dbo_vw_Dimension_Product.ItemSeq = dbo_vw_Fact_Invoice_Detail.ItemSeq) ON dbo_vw_Dimension_CalendarDate.DateSeq = dbo_vw_Fact_Invoice_Detail.InvoiceDateSeq) ON dbo_vw_Dimension_Customer.CustomerSeq = dbo_vw_Fact_Invoice_Detail.OriginalCustomerSeq) ON dbo_vw_Dimension_Company.CompanySeq = dbo_vw_Fact_Invoice_Detail.CompanySeq) ON dbo_vw_Dimension_Coworker_AccountManager.AccountManagerSeq = dbo_vw_Dimension_Customer.PrimaryAccountManagerSeq) ON dbo_vw_Dimension_Commissionable.CommissionableSeq = dbo_vw_Fact_Invoice_Detail.CommissionableSeq) ON dbo_vw_Dimension_Coworker_Manager.CoworkerSeq = dbo_vw_Dimension_Coworker_AccountManager.ManagerCoworkerSeq) ON dbo_vw_Dimension_Order.OrderSeq = dbo_vw_Fact_Invoice_Detail.OrderSeq
WHERE (((dbo_vw_Dimension_CalendarDate.MonthsAgo)=0 Or (dbo_vw_Dimension_CalendarDate.MonthsAgo)=1 Or (dbo_vw_Dimension_CalendarDate.MonthsAgo)=2) AND ((dbo_vw_Dimension_Product.BrandDescription)="microsoft") AND ((dbo_vw_Dimension_Product.ManufacturerCode) In ("MSA ","MA6 ","MB6 ","MC6 ","MD6 ","MLG ","MG6 ","MLE ","MLA ","MLB ","MLC ","MLD ","S+D ","M+M ","M+G ","M+A ","M+B ","M+C ","M+D ","M+S ","MSU")) AND ((dbo_vw_Fact_Invoice_Detail.CommissionableSeq)=1) AND ((dbo_vw_Dimension_Company.CompanyTypeDescription)="sales") AND ((dbo_vw_Dimension_Company.CountryCode)="us"));
I would like to capture only those customers that have sales greater than x dollars, for the same mfg codes and date range. Would I have to create a maketable of the first data, aggregate by customer to obtain my solution or is there a one step method in the same query. MS Access SQL statment is below.
SELECT dbo_vw_Dimension_CalendarDate.MonthsAgo, dbo_vw_Dimension_CalendarDate.YearSeq AS [Year], Left([CalendarMonth],(InStr([calendarmonth]," ")-1)) AS [Month], Left([Calendarquarter],(InStr([calendarquarter]," ")-1)) AS Quarter, dbo_vw_Dimension_CalendarDate.CalendarDate AS InvoiceDate, dbo_vw_Dimension_Customer.CustomerCodeINT, dbo_vw_Dimension_Customer.CustomerShortDescription, dbo_vw_Fact_Invoice_Detail.InvoiceCode, dbo_vw_Fact_Invoice_Detail.InvoiceLineSeq, dbo_vw_Dimension_Order.OrderCode, dbo_vw_Fact_Invoice_Detail.ReferenceCode, dbo_vw_Fact_Invoice_Detail.SalesQuantity, dbo_vw_Fact_Invoice_Detail.SalesDollarAmount, dbo_vw_Dimension_Product.ItemCode, dbo_vw_Dimension_Product.ItemDescription, dbo_vw_Dimension_Product.ItemTypeCode, dbo_vw_Dimension_Product.ItemTypeDescription, dbo_vw_Dimension_Product.ItemClassCode, dbo_vw_Dimension_Product.ItemClassDescription, dbo_vw_Dimension_Product.BrandDescription, dbo_vw_Dimension_Product.ManufacturerCode, dbo_vw_Dimension_Product.ManufacturerDescription, dbo_vw_Dimension_Product.ItemManufacturerPartNumber, dbo_vw_Dimension_Coworker_AccountManager.AccountManagerCode, dbo_vw_Dimension_Coworker_AccountManager.CoworkerCode, dbo_vw_Dimension_Coworker_AccountManager.CoworkerName, dbo_vw_Dimension_Coworker_AccountManager.ManagerCoworkerCode, dbo_vw_Dimension_Coworker_AccountManager.ManagerCoworkerName, dbo_vw_Dimension_Coworker_Manager.EMailAddress AS MgrEmail, dbo_vw_Dimension_Coworker_AccountManager.TerminationFlag, dbo_vw_Dimension_Coworker_AccountManager.TerminationDescription, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel4Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel5Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel6Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel7Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel8Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel9Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel10Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel11Alias, dbo_vw_Dimension_Coworker_AccountManager.CoworkerGLLevel12Alias INTO Aletha75Kinvoice
FROM dbo_vw_Dimension_Order INNER JOIN (dbo_vw_Dimension_Coworker AS dbo_vw_Dimension_Coworker_Manager INNER JOIN (dbo_vw_Dimension_Commissionable INNER JOIN (dbo_vw_Dimension_Coworker_AccountManager INNER JOIN (dbo_vw_Dimension_Company INNER JOIN (dbo_vw_Dimension_Customer INNER JOIN (dbo_vw_Dimension_CalendarDate INNER JOIN (dbo_vw_Dimension_Product INNER JOIN dbo_vw_Fact_Invoice_Detail ON dbo_vw_Dimension_Product.ItemSeq = dbo_vw_Fact_Invoice_Detail.ItemSeq) ON dbo_vw_Dimension_CalendarDate.DateSeq = dbo_vw_Fact_Invoice_Detail.InvoiceDateSeq) ON dbo_vw_Dimension_Customer.CustomerSeq = dbo_vw_Fact_Invoice_Detail.OriginalCustomerSeq) ON dbo_vw_Dimension_Company.CompanySeq = dbo_vw_Fact_Invoice_Detail.CompanySeq) ON dbo_vw_Dimension_Coworker_AccountManager.AccountManagerSeq = dbo_vw_Dimension_Customer.PrimaryAccountManagerSeq) ON dbo_vw_Dimension_Commissionable.CommissionableSeq = dbo_vw_Fact_Invoice_Detail.CommissionableSeq) ON dbo_vw_Dimension_Coworker_Manager.CoworkerSeq = dbo_vw_Dimension_Coworker_AccountManager.ManagerCoworkerSeq) ON dbo_vw_Dimension_Order.OrderSeq = dbo_vw_Fact_Invoice_Detail.OrderSeq
WHERE (((dbo_vw_Dimension_CalendarDate.MonthsAgo)=0 Or (dbo_vw_Dimension_CalendarDate.MonthsAgo)=1 Or (dbo_vw_Dimension_CalendarDate.MonthsAgo)=2) AND ((dbo_vw_Dimension_Product.BrandDescription)="microsoft") AND ((dbo_vw_Dimension_Product.ManufacturerCode) In ("MSA ","MA6 ","MB6 ","MC6 ","MD6 ","MLG ","MG6 ","MLE ","MLA ","MLB ","MLC ","MLD ","S+D ","M+M ","M+G ","M+A ","M+B ","M+C ","M+D ","M+S ","MSU")) AND ((dbo_vw_Fact_Invoice_Detail.CommissionableSeq)=1) AND ((dbo_vw_Dimension_Company.CompanyTypeDescription)="sales") AND ((dbo_vw_Dimension_Company.CountryCode)="us"));
