Transact SQL :: How To Sum Sales Value Between Groups Of Values
Jul 3, 2015
I have a table
Acct No Sales
1 90
2 129
3 1200
4 270
The table in real life has much more data and more columns. I want to be able to select Acct No and sum of sales for each band
Bands: <250 // 250-500 // 500-1000 // 1000+
I want to be able to get a table as below:
Account No // <250 // 250-500 // 500-1000 // 1000+
1 // 90 // 0 // 0 // 0
2 // 129 // 0 / / 0 // 0
2 // 0 // 0 / / 0 // 1200
2 // 0 // 270 / / 0 // 0
Is there any easy way of doing this?
View 4 Replies
ADVERTISEMENT
May 19, 2015
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?
View 4 Replies
View Related
May 30, 2007
I need to provide a way to handle groups that buy tickets in bulk lots, say 20 at a time. Users can later register with a "ticket code". I need to tie these two together and provide a way for groups to buy more tickets at a later time.
There should be a code that can be generated for each ticket that a group buys. This ticket is then to be given to a user to register with. A rogue user should not find it easy to guess the code. So, I want to generate a code for each group as they register, that is a random 6 or 7 digit, alphanumeric string and then something similar for a ticket. See table definitions below.
So for a ticket code, we could have something like this:
A23486D_TR34, where A23486D would be the group code
comments/suggestions/ideas welcome
At present Ive built two tables, [groups] and [group_tickets]
[group]
id int(10) unsigned
name varchar(45)
phone varchar(10)
email varchar(45)
contact_namevarchar(100)
signup_datedatetime
last_updatedatetime
group_codevarchar(6)
addr1 varchar(45)
addr2 varchar(45)
city varchar(45)
state char(2)
zip varchar(10)
[group_ticket]
id int(10) unsigned
group_idint(10) unsigned
purchase_datedatetime
fill_datedatetime
ticket_notestext
ticket_code varchar(12)
View 1 Replies
View Related
Dec 3, 2007
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
SELECT Customer.Name as Store_Name,
Tbl_Daily_Activity_Statistics.SR_Code,
Tbl_Daily_Activity_Statistics.Territory_Code,
Tbl_Daily_Activity_Statistics.Customer_code,
Tbl_Daily_Activity_Statistics.in_progress_time,
Tbl_Daily_Activity_Statistics.completed_time,
Tbl_Daily_Activity_Statistics.Visit_Activity_Id,
Tbl_Daily_Activity_Statistics.dial_in_datetime,
Tbl_Daily_Activity_Statistics.question_code,
tbl_Questions_to_RC_Question.RC_Question_description,
Qry_Sales_Group.SR_Name, dbo.Qry_Sales_Group.Region,
Qry_Sales_Group.Region_Key,
Tbl_Daily_Activity_Statistics.Daily_activity_statistics_datetime
FROM Tbl_Daily_Activity_Statistics
INNER JOIN tbl_Questions_to_RC_Question
ON Tbl_Daily_Activity_Statistics.question_code = tbl_Questions_to_RC_Question.question_code
INNER JOIN Qry_Sales_Group
ON Tbl_Daily_Activity_Statistics.SR_Code = Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
LEFT OUTER JOIN customer
ON dbo.Tbl_Daily_Activity_Statistics.Customer_code = dbo.customer.customer_code
WHERE Region_key=@Region_key AND Daily_activity_statistics_datetime = @Daily_activity_statistics_datetime
END
View 3 Replies
View Related
Aug 19, 2015
I have 2 tables as follows:
table A-NewParts
PartNumber
Description
LaunchDate
a123
product a
4/1/2009
[code].....
I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.
PartNumber
Description
LaunchDate
Yr1 Sales
Total Yr1 Sales
Yr2 Sales
Total Yr2 Sales
a123
[code].....
View 3 Replies
View Related
Dec 3, 2007
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
SELECT Customer.Name as Store_Name,
Tbl_Daily_Activity_Statistics.SR_Code,
Tbl_Daily_Activity_Statistics.Territory_Code,
Tbl_Daily_Activity_Statistics.Customer_code,
Tbl_Daily_Activity_Statistics.in_progress_time,
Tbl_Daily_Activity_Statistics.completed_time,
Tbl_Daily_Activity_Statistics.Visit_Activity_Id,
Tbl_Daily_Activity_Statistics.dial_in_datetime,
Tbl_Daily_Activity_Statistics.question_code,
tbl_Questions_to_RC_Question.RC_Question_description,
Qry_Sales_Group.SR_Name, dbo.Qry_Sales_Group.Region,
Qry_Sales_Group.Region_Key,
Tbl_Daily_Activity_Statistics.Daily_activity_statistics_datetime
FROM Tbl_Daily_Activity_Statistics
INNER JOIN tbl_Questions_to_RC_Question
ON Tbl_Daily_Activity_Statistics.question_code = tbl_Questions_to_RC_Question.question_code
INNER JOIN Qry_Sales_Group
ON Tbl_Daily_Activity_Statistics.SR_Code = Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
LEFT OUTER JOIN customer
ON dbo.Tbl_Daily_Activity_Statistics.Customer_code = dbo.customer.customer_code
WHERE Region_key=@Region_key AND Daily_activity_statistics_datetime = @Daily_activity_statistics_datetime
View 1 Replies
View Related
Nov 28, 2012
I have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
S_DATE
S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
View 12 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Nov 10, 2015
I have a Cost table with the following field:
Year, Quarter, Cost1, Cost2
I would like to write a SQL so that it will give me something like that, so that I will have 2 years across the top with the same same columns.
2016
2015
Quarter
Cost1
Cost2
Cost1
Cost2
1
100
200
[code]....
View 3 Replies
View Related
May 27, 2009
I would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?
View 21 Replies
View Related
May 25, 2015
I have a table which has 2 columns and the data is like below
API_Number Group_Name
1234 Group A
3241 Group A
1234 Group B
4567 Group C
7896 Group D
3241 Group E
I wanted to find the API numbers which are repeating in different groups. In the output I want
API_Number Group_Name
1234 Group A,Group B
3241 Group A,Group E
View 5 Replies
View Related
Aug 20, 2015
I would have to handover my DB with only Primary tables to client as part of SLA.
I am planning to keep these primary tables on a secondary file-group but how-ever, I will still have my procedures on primary file-group.
How can I accomplish this with client having no exposure to my stored procedures.
View 2 Replies
View Related
May 21, 2015
I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.
declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,
[Code] ....
-- If Referral start date is between our requested dates
ref.Referral_Start_Date between @startdate and @enddate
OR
-- Include referrals which started before our requested date, but are still active during our date range.
(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,
[code]....
View 5 Replies
View Related
Aug 11, 2015
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
View 2 Replies
View Related
May 25, 2015
I have a requirement to display the total of a Group after subtracting a specific value from the same Group.
Example: Say the below data is grouped on a particular column
Group Values Month
Jan-15 Feb-15 Mar-15
A 10 20 30
B 5 10 25
C 1 2 3
D 5 10 15
Total 11 22 33
Formula is : Sum(A+C+D)- Sum(B)
What is the best way to Group the above scenario from SSRS level and display the result as shown above. I am able to display all the values except the last total row where am displaying the complete total i.e. 21 42 73.
How do I dynamically subtract the values for row B which is one of the group values.
View 4 Replies
View Related
Apr 24, 2015
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?
View 6 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Sep 30, 2015
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.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 Replies
View Related
Aug 26, 2015
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
[code]...
View 13 Replies
View Related
Oct 14, 2015
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
View 2 Replies
View Related
Jul 9, 2015
I have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
View 14 Replies
View Related
Jan 25, 2008
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1
ID2
DisplayData
1
1
A
1
2
B
1
3
C
2
1
A
2
2
B
2
3
C
Parent group is on ID1, child group is on ID2, report would show:
1
1
A
2
A
3
A
2
1
A
2
A
3
A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
View 2 Replies
View Related
Aug 19, 2015
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
View 17 Replies
View Related
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
Nov 26, 2007
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?
Thanks.
View 3 Replies
View Related
Nov 16, 2015
I can not modify DDL of the table, so creating a trigger etc is not an option. This is my syntax that I am using, and each #Testing.abcd has different possible options.
For example, when #Testing.abcd = Test1 then #Data.field2
okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
Declare abcd varchar(100), @sql varchar(1000)
Create Table #Testing (abcd varchar(100), efgh varchar(500)
Insert Into #Testing Values
('Test1', 'zzz'),
('Test2', 'xxx'),
[Code] ....
View 5 Replies
View Related
Oct 2, 2015
I have a query I am currently attempting to optimise. The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. For example:
SELECT Column1
,Column2
,DateColumn
FROM Table1 T
INNER JOIN
(
SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt
FROM Table1
GROUP BY Column1 + Column2
) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
View 19 Replies
View Related
Jul 19, 2015
Select a.uid
, a.name
, mewp.data As Association
from Asset a Join
wshhistory wsh
on a.uid = wsh.assetid
Join worksheet w
on wsh.wshid = w.uid left join
[code]...
When I return the data how do I eliminate the name's that are in bold but keep the association record as this is a relationship between joining tables..
View 7 Replies
View Related
Aug 20, 2006
I am using Visual Web Developer Express 2005 and SQL Server Express 2005.
I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine.
I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed.
How can I check for null values and skip a block of code within my Transact Sql trigger.
Thanks.....
View 2 Replies
View Related
Aug 10, 2015
I have [TableAccount.AccountType] field which can store these values:
Value
C
S
E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
View 4 Replies
View Related
Jun 28, 2015
The following stored procedure works well as far as it goes but I need to amend it.
@AE decimal (18,2) outputAS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare@odds decimal (18,2)
(SELECT @odds= sum
(1/ (RacingMaster.RM_SP) )
FROM RacingMaster
where RM_Draw='1')
[Code] .....
The column RM_Draw holds integer values from 1 through to 20. provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.
View 3 Replies
View Related
Jun 23, 2015
i want to display the max Date in place of NULL when Indicator is "1"
INPUT
Emp Num
Date
Indicator
1
Null
1
[code]....
View 3 Replies
View Related
Nov 16, 2015
I have table with column having values 1,2,3,5,6,10.
I want to get the missing values in that column between 1 and 10 i.e., min and max... using sql query.
I want to get the values 4,7,8,9.
View 8 Replies
View Related
