The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION:
What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available date is grouped/summed together.
The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION: What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available date is grouped/summed together.
I have 2 DB on the same machine DB 1 is called Test1 and DB 2 is called Test2
I want to be able to write one query to compare 2 tables from each of the Databases.
I wrote the first query ie, use Test SELECT Unit_Identifier,Piece_Id,Piece_Count FROM Piece_Unit where Version_Date = '01/01/2005' order BY Piece_Id
and I get the result from the Piece_unit Table
I wrote another query ie use Test1 SELECT Piece_Id,PieceName,Inserts from PalletDetails WHERE VersionDate = '01/01/2005' ORDER BY Piece_Id
and I get the result from the PalletDetails table
The piece_id of both Tables are same value
I want to combine both queries to a single query and ONLY list the difference from both Tables in reference to the piece_id ( Only list piece_id) that exist on either table only
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123 Children:Â Sale ID # 456, Sale ID #789, Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
Let say I have this table in MS SQL server table transaction(date,sales)
how to query to get result like this (date,sales,sum(sales last 7 day)) I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.
note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day
Looking for sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun. Thus start day is last sun.
now the time is 22:00 & group by shop plus underline +24 hour format
Sun Mon Tue Wed Thur Fri Sat Sun Total Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx ------------------------------------------------------------------- Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx Total $30 $30 $10 $4 $20 $0 $no sales $xxx
I'm developing a 2 year comparison rolling 12 month Sales report but am having problems with the query I'm using. Because I need to include every month (whether no sales or not) I have a calendar table named metaDates and each table gets outer joined as a result. (Forgive the long query) Please see below.
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost, ( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) )AS GM, 'Current 12 Months' AS yearNum FROM MetaDates LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) ) LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) ) LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId) WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
UNION SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost, ( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) ) AS GM, 'Previous 12 Months' AS yearNum FROM MetaDates LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) ) LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) ) LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId) WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid ORDER BY MetaDates.[Year], MetaDates.[Month]
That said the second outer join (Customer.country IN (@country)) never gets executed. It's really a simple concept. "Give me last 2 year sales by month (every previous and last month even if no sales) but only for the country the user is in"
Can someone help me out with this sql statement. Any help would be much appreciated.
I am trying to calculate the maximum sales per store where multiple stores exist in the same region (i.e there must be doubles of that row) in order to determine the most profitable store per that region.
E.g. Store Sales A 500 B 200 B 100 C 400 C 800 D 100 D 200 D 700
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
I am creating a sales report that needs to display Sales statistics for a selected month grouped by Material. Also, it needs to display the same stats for the selected month of the previous year. Finally, it needs to display Year To Date Statistics for the current year and previous year up to and including the selected month.
Currently, I am using 3 queries to do this. The first one gets the statistics the current month grouped by material. The others sprocs get their corresponding information by me passing in the material number and the month and doing the appropriate sum. So, essentially, for a single line of the report, 3 queries are being done. The problem arises in that sometimes there may be as many as 300 materils displayed in a given month, which amounts to alot of db activity and a long delay loading the report.
Is there anyway to get this information in one swoop using some fancy aggregation?
I'm trying to develop a query that provides sales data by Customer.GroupCode in monthly columns as depicted below:
GrpCd JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOT Film 5,000 15,000 20,000 Aero Elct 3,000 950 3,950 Desg Edu 150 150
Here€™s a simplified version of the DDL: CREATE TABLE invchead ( invoicenum int NULL , invoicedate datetime NULL , invoiceamt decimal(16, 2) NULL , custnum int NULL )
The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.
SELECT distinct c.Name, c.GroupCode, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB, ...... (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum) WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'
Grateful for any advice that will get me closer to accomplishing this.
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code: $describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p join (select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales group by ProductCode, [Year], [Month])dt on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
My requirements are to return the sales for each customer for each store, for the past 6 weeks.
The catch is that I only want those customers which have had sales over 10,000 within the last week.
This is my query:
WITH SET [CustomerList] AS FILTER( ([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]), [Measures].[Sales] >= 10000 AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is callingPartyNumber. I am trying to SELECT a few fields including callingPartyNumber, and SUM(duration). Is it possible to group the results according to the callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)? The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned. SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect, dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect, CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration, SUM(duration)
FROM Calls
GROUP BY callingPartyNumber
I cannot normalize the tables (implies that I have to work on one and only one table).Anyone...
I have the following sql statement below;DECLARE @val intSET @val = 1WHILE (@val <= 7 )BEGIN SELECT TOP(1) id, queueFROM itn_articles WHERE asection = @valORDER BY queue DESCSET @val = @val + 1END Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable
Hi Guys,I have the following select sql (sql server) select statement SELECT dbo.AlternativesAssessment.AlternativeNumber, dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.MembershipValue, dbo.AlternativesAssessment.CriteriaID, dbo.CriteriaDefinition.CriteriaName, dbo.CriteriaDefinition.DecisionSessionID
FROM dbo.AlternativesAssessment INNER JOIN dbo.CriteriaDefinition ON dbo.AlternativesAssessment.CriteriaID = dbo.CriteriaDefinition.CriteriaID
WHERE (dbo.CriteriaDefinition.SessionID = @SessionID)
ORDER BY dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.AlternativeNumber and it produces a result like this:In this case there are 4 alternatives all evaluated through 3 criteria "c1_Cost", "c2_Durability" and "c3_Reputation".My objective is to sum up all the MembershipValue's for each alternative.it shoud look like this:AlternativeNumber|CriteriaName|MembershipValue| 1 Cost 2314 1 Durability 214 1 Reputation 981 2 Cost 814 2 Durability 914 2 Reputation 381.etcDo you guys have an idea how I could achieve that?I played around with SUM's and GROUP By's for hours now but it does not seem to work.Many thanks,polynaux
I have a vendor table with vendor codes of six characters. The same vendor could have two codes (I'm new to this accounting system so I don't know why this is the case). If a vendor has two codes, the first character for one vendor code would be an "H", then the first character for the other code would be a "P", with the other 5 characters being the same (i.e. HCAMER and PCAMER).
We want to print only one 1099 for the vendor. So, if the vendor has two codes, I need to get a total for that vendor. Can this be done in a query?
I have a table in which column are id, date, lname, fname, add In this table there are duplicate records but have different date for each duplicate record. I am trying to get just max(date) for each record.
when i try like these it works. select id, max(date) from table group by id
but when i try something like these it does not work select id, fname, lname, max(date) from table group by id, fname, lname
I get all the record. I am writing these query from perl script. any help.
Please help in writing a SQL query. I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.
Now I want get sum of both for a particular manager on a daily basis. Like
SaleDate sum(Directsales) sum(TeammemSales)
if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.
I have a query that looks like this: ---------------------------------------------------------------------------- SELECT TOP 8 TABLEDATA.VW_DATA.COMPLETIONDATE AS COMPLETIONDATE, TABLEDATA.VW_DATA.TYPE, TABLEDATA.VW_DATA.TEAM_ID, AVG(TABLEDATA.VW_DATA.DURATION) AS AVGDURATION, TABLEDATA.VW_DATA.SITE, TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION, TABLEDATA.TEAM_TYPE.TYPE AS TEAMTYPE FROM TABLEDATA.VW_DATA INNER JOIN TABLEDATA.MODELS ON TABLEDATA.VW_DATA.MODEL_ID = TABLEDATA.MODELS.MODEL_ID INNER JOIN TABLEDATA.TEAMS ON TABLEDATA.MODELS.MODEL_ID = TABLEDATA.TEAMS.MODEL_ID AND TABLEDATA.VW_DATA.TEAM_ID = TABLEDATA.TEAMS.TEAM_ID INNER JOIN TABLEDATA.TEAM_TYPE ON TABLEDATA.TEAMS.TYPE_ID = TABLEDATA.TEAM_TYPE.TYPE_ID WHERE (TABLEDATA.VW_DATA.DURATION > (SELECT MIN(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27)) AND (TABLEDATA.VW_DATA.DURATION < (SELECT MAX(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27)) GROUP BY TABLEDATA.VW_DATA.COMPLETIONDATE, TABLEDATA.VW_DATA.TYPE, TABLEDATA.VW_DATA.TEAM_ID, TABLEDATA.VW_DATA.SITE, TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION, TABLEDATA.TEAM_TYPE.TYPE HAVING (TABLEDATA.VW_DATA.TYPE = 'Complete') AND (TABLEDATA.VW_DATA.TEAM_ID = 27) AND (TABLEDATA.MODELS.MODEL <> 'Model1') ORDER BY TABLEDATA.VW_DATA.COMPLETIONDATE DESC ----------------------------------------------------------------------------
What I need is one row per site, and I can get that when I exclude the completiondate field, but I need to sort on that field. How can I use the GROUP BY to return only one row with the team_id, avgduration, site, model, duration, and teamtype. I do not need to display the completiondate, I just need it for sorting. The avgduration column is supposed to be an average of the duration of each of the records.
This is a fairly compliacted query and I just can't seem to figure it out. I will try to psuedocode it to see if anyone can figure out how to do it with sql.
Table Descriptions: I have to join two tables. Houses and Inpsections. A house can have many inspections. A single inspection can only be done on 1 house. Houses: HouseID, HouseDescription Inspections: InspectionID, Inspection_date, House_Id, pass_inspection ("yes"/"no"),
What I need: I need to have only 1 result per House. For each house, I need the results for the LATEST INSPECTION. That is pass_inspection, houseID, inspection_date for the last time the house was inspected ONLY (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet").
A query would be greatly appreciated.
Thanks,
DB
If you want to go further (which I will need to do) I need actually do this for each pair of House + problem. Houses and problems have a many to many relationship.
Can you group by a value returned by a UDF? Example:SELECT Col1, SUM(Col2), MyFunc(Col3, Col4) AS 'MyResult'GROUP BY Col1, MyResultI've had no luck. Can this be done?
I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.
Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?
This is what the DMX query looks like...
SELECT t.[AgeGroupName], t.[ChildrenStatusName], t.[EducationName], Sum(t.[Profit]) as Profit From [Revenue Estimate DT] PREDICTION JOIN OPENQUERY([DM Reports DM], 'SELECT [AgeGroupName], [ChildrenStatusName], [EducationName], [Profit], [IncomeName], [HomeOwnerName], [SexName], [Country], [ProductTypeCode], [ProductName], [MailCount], [OrderAmount], [SalesAmount], [MailCost] FROM (SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode, CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount) AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey FROM [DataMining.RevenueEstimate.Predict] GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode, CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID HAVING (ClientID = 1)) as [Prediction] ') AS t ON [Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND [Revenue Estimate DT].[Education Name] = t.[EducationName] AND [Revenue Estimate DT].[Income Name] = t.[IncomeName] AND [Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND [Revenue Estimate DT].[Sex Name] = t.[SexName] AND [Revenue Estimate DT].[Country] = t.[Country] AND [Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND [Revenue Estimate DT].[Product Name] = t.[ProductName] AND [Revenue Estimate DT].[Mail Count] = t.[MailCount] AND [Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND [Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND [Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND [Revenue Estimate DT].[Profit] = t.[Profit] AND [Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName] GROUP BY t.[AgeGroupName], t.[ChildrenStatusName], t.[EducationName]
