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?
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
I want to list all Salesman in the Salesman file along with there sales. If there is no record in the sales file I still want to see the salesman listed.
I've tried this but it doesn't seem to work. The code below is only listing salesman if they have sales in period 10.
select s.humres_id , sum(q.Sales) as Sales, sum(q.Cost)as cost from arslmfil_SQL s left outer join QIVSalesMTDYTDCustSalesPerson Q on s.humres_id = q.slspsn_no where Year=2014 and Period = 10 group by s.humres_id
I desire output by each hour of sales by different shop total with sales_datetime format is 20140831 22:30:xxx.
for example now is 23:05 then desire result is 23:00-23:05 of sale. ----------------------------------------------------------------- select shopid,qty,amount from sales where sales_datetime>='hh:mm' and sales_datetime<='hh:mm' group by shop
I know this is an easy one, but for some reason i keep gettin the wrong results. This displays something like this : Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams
But i want it to show all the SR_Name's ..like this: Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams NULL null Tom Jones NULL null Kim Jones NULL null Jon Travis
Any help will be grately appreciated! thanks!
Code Block ALTER PROCEDURE [dbo].[PROC_RPT_SR_DAILY_ACTIVITIES] (@Region_Key int=null, @Daily_activity_statistics_datetime datetime ) AS BEGIN
I have a subquery that grabs all the sales reps with less then 6 visits. Only problem is, when i have a date when there are any number of sales reps that dont make visits, so the column would be null, they dont show up. I want to display these, because this report is supposed to show the visits made, so if they made none, i want it to show zero, instead of not showing the whole date column, since zero visits were made on that date that were below 6 and more then zero. Here's my stored procedure: (the subquery is highlighted)
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Visits_6] (@Region_Key int=null) AS BEGIN SELECT dbo.Qry_Visits.Status, dbo.Qry_Visits.Customer_code, Qry_Sales_Group.Name, dbo.Qry_Sales_Group.SR_Name, dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week, dbo.Qry_Date_Dim.Date_Dimension_Date, dbo.Qry_Date_Dim.Day_Of_Month, dbo.Qry_Sales_Group.Region, dbo.Qry_Visits.period_code, dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits, dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name, dbo.Qry_Date_Dim.Date_Dimension_Year, dbo.Qry_Date_Dim.Date_Dimension_Period, CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date, dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code], B.VisitsTotal FROM dbo.Qry_Visits INNER JOIN dbo.Qry_Sales_Group ON dbo.Qry_Visits.[SR Code] COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS INNER JOIN dbo.Qry_Date_Dim ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110) INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status FROM dbo.Qry_Visits WHERE Qry_Visits.Status=2 GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status HAVING SUM(Visits) < 6)B ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate
WHERE REGION_KEY=@Region_Key and Qry_Visits.Status=2 ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)
(SQL Server 2008) I want to display a total for each user that is returned from my query result. I was trying the union all route as that is what I am most comfortable with, however, the result set returns the total row at the bottom instead of under each user. This is the desired result set...Here is my query, what do I need to alter to have the output display like so?
Code: Create Table #Test ( id varchar(50), name varchar(500), out1 int, out2 int, total4day int,
I have a categories table and a products table, the products are associated with a category, and my current select statement looks like this..
SELECT COUNT(dbo.tbl_Categories.CatName) AS TotQty, dbo.tbl_Categories.CatName FROM dbo.tbl_Products INNER JOIN dbo.tbl_Categories ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID GROUP BY dbo.tbl_Categories.CatName
this give me ..
TotQty CatName 1 Books 2 DVD
I wonder how I can change the select statement so I retrieve a result like this..
TotQty CatName Description 1 Books Oliver Twist 2 DVD Dire Straits 2 DVD Elvis
I tried my syntax below, but it said that the field were not in the group by. This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:
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?
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
I have 2 columns 1) Total Premium and 2) New-Renew Indicator in my Powerpivot.
The requirement is to show the a) New Premium as a Percent of Total Premium and b) Renew Premium as a percentage of Total Premium. Here is what i did:
a) Created a calculated measure called Percentage:= Divide(Total Premium, Total Premium, 0) . The percentage shows 100% as expected.
b) Now when i try to bring in the Column 2)New-Renew alongside this Percentage in the pivot table, both New and Renew shows 100%. I only have about 20 percent rows with New, and 80% of Renew.
When i bring in the original column = 1) Total Premium, the new-renew split shows correctly, just the percentage is not splitting up correctly. How to achieve it?
I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).
select shop ,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span ,sum(case when datediff(dd,yourdatetimefield,getdate())=0
I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this
Internet Mark .... statistics here Phone Mark - applied for credit .... statistics here Phone Mark - customer referral ..... statistics here Marks Sales Totals .... statistics here Next salesman data would go here
but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'..This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed
If user want to see the grand total for a measure with include all members, even though the user has limited access for that member, so how we can do using DAX?For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but also want to see the Grand Total for the revenue as $15,000How we can achieve above scenerio in tabular model (DAX).
select col1,count(*) from client1..table1 group by col1 union select col1,count(*) from client2..table1 group by col1 union select col1,count(*) from client3..table1 group by col1
The results yields
33915 3405 3412
I am trying to get the following result but can't figure out how to get the total in the end.
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?
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.
I have a field Char(10) named bank, and another one Char(15) named namebank.
I need to create a TSQL query, but i need to preserve the white space on my first field named bank and concatenate with the other namebank, this last i dont need white space.
Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select fs_locn, fs_accno, amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1 end ) from accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014' groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone_optimized @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
I need to calculate total discount on item in case when user has several discounts, and they each apply on discounted amount. I thought to have something like:
DECLARE @Disc float SET @Disc = 0 SELECT @Disc = @Disc + (100 - @Disc) * Disc / 100 FROM UserDiscounts WHERE UserID = 123
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to