I haven't done any SQL in a while. Having trouble getting me head around this one.
I have a table Sales:
(Transaction_ID int, Customer_ID, Sales_DateTime Time, Sales_Amt Dollar)
I need a query that returns:
For each Customer_ID that has made a purchase
Show the highest dollar Sales_Amt they have ever made
In C++ I would probably do a recursive solution, is this doable? advisable? in T-SQL?
Hi,
I'm using SQL Server 7. I have two tables as follows:
Table Invoices:
CustNo
DocDate
Amount
Table Payments:
CustNo
DocDate
Amount
Can anyone think of a process to find the invoice with no payment for each customer? I need to do some kind of process of elimination; I suppose.
Any Suggestions?
Thanks,
Denise
Hello,
I want to process a row from a source table by dividing the sales amount in that row over the period of the sale by month.
For instance if an item is sold for 500$ and it's duration is 5 months from 1/15/2004 till 6/15/2004, I want to divide the sale amount by month as follows:
Month 1: 50$
Month 2: 100$
Month 3: 100$
Month 4: 100$
Month 5: 100$
Month 6: 50$
I know I can create a script component and do the calculation for each month and insert 6 records in the fact table for each row in the source table, where each record holds the amount for the corresponding month. However I was wondering if there is another technique that utilizes the components of SSIS to do it more efficiently.
Thanks,
Grace
I'm developing and sql 2008 view that shows me the last month of sales of every customer and every item sold, the problem is that I need group by customer with the higher Sale Price of every item of the customer, example:
1.If We sold the same item to one customer then it must show just the higher sale price of every item
2.If the same item was sold to the same customer at the same price then it must show just the last sold date record of that item
This is I get :
OrderDate |DebNr|DebName|ItemCode|Descript|Qty|CostPrice|SalePrice|
2015-03-05|0001 |Deb0001|Item-001|Item 001| 3 | 223.10 | 289.00 |
2015-02-25|0001 |Deb0001|Item-001|Item 001| 2 | 220.00 | 286.00 |
2015-03-05|0001 |Deb0001|Item-002|Item 002| 1 | 75.00 | 110.00 |
2015-03-02|0002 |Deb0002|Item-001|Item 001| 1 | 218.00 | 265.00 |
2015-02-27|0002 |Deb0002|Item-001|Item 001| 1 | 218.00 | 265.00 |
This is I need :
OrderDate |DebNr|DebName|ItemCode|Descript|Qty|CostPrice|SalePrice|
2015-03-05|0001 |Deb0001|Item-001|Item 001| 3 | 223.10 | 289.00 |
2015-03-05|0001 |Deb0001|Item-002|Item 002| 1 | 75.00 | 110.00 |
2015-03-02|0002 |Deb0002|Item-001|Item 001| 1 | 218.00 | 265.00 |
This is my code :
SELECT TOP (100) PERCENT OrderDate, DebtorNr, DebtorName, ItemCode, Description, Qty, CostPrice, SalePrice
FROM dbo.VK_SALE_ORDERS
WHERE (OrderDate >= DATEADD(MM, - 1, GETDATE()))
ORDER BY DebtorNr, ItemCode
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies View Relatedi 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?
Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!
View 3 Replies View RelatedHi all,Given:f1 f2-- --1 a2 b3 b4 b5 c6 c7 dHow to get the highest count for field f2, output as:f2 f2count-- -------b 3
View 1 Replies View Related I have four columns that include numbers like: A B C D
I want to update D with highest value of A, B or C.
For example A = 1, B = 2, C= 3 then D should be 3.
Can you help me?
Can someone tell me what the highest value (number) is for an int data type in sql server 2005?
View 2 Replies View RelatedHai frendz,
I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-
"select top 1 EID, Salary from Employee ORDER BY Salary DESC"
Now I need to write a query which selects the second highest salary.
So how to achieve this?..
thanx
hello all,
i'm not new to SQL but i cant seem to get this right:
hope some one will:
how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)
i.e :
id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9
requested result:
if input param(name)=name1
result:
1 | name1 | 2.7
if input param(name)=name2
result:
8 | name2 | 2.9
hope some one can write the sql cmd for this
i'll be grateful !
thank you!
Got a table with salestatistics and I want to know what ItemNr sold most a certain day. Can anyone help me with the SQL?
View 1 Replies View RelatedHello,
I have the following problem.
I have three Columns
name / version / package
I want to create a select query, that shows me all names and their actuall package. The actual Package is the record with the highest version number.
For Example my Database has the following entries :
name / version / package
name1 / 1 / package1
name2 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2
The output should look like this :
name1 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2
Has anyone a idea and can help me ?
Thanks in Advance
Mirco
Hi all,
Hopefully you can assist this pleb (me) with some (hopefully) basic scripting.
I have a table which has bucket loads of rows in it (funnily enough)
Field1 & Field2
Field1 is repeated numerous times (but also has differing values) through the table
Field2 is a numeric value which is assigned to the Field1
What I need to do is pull back all the unique Field1's but only those with the highest value in Field2
Example:
Field1 Field2
blah 100
blah 400
blah1 12
blah1 9
blah1 2
blah 350
I need the return to be basically:
Field1 Field2
blah 400
blah1 12
I am sure that this has to be straight forward - however my brain just isn't connected the right way at the moment....
Thanks in advance for any assistance on this.
Troy
I am attempting to create a report that will display all the yearly, and monthly gas production for a company but the issue I am encountering is finding amended records for a lease. Below is what the query produces:
Company Number Year Month Gas Production Lease Number
3125 20091 296 9105701
3125 20091 345 165207
3125 20091 565 2250593 <--
3125 2009 1 1161 2250593
I would like to omit the lower production gas production, and keep the higher production from the same lease.
I want to obtain the top 1 students' name in each grade,but this code
dosen't work:
select top 1 name
from student
group by grade
order by GPA desc
Can anyone tells me what to do?
I have a table stock, on the table I have company_name, stock_value, sector.
Now I want to get top five rows from this table depending on the five highest value of stock_value and for each sector. The query has to run on both oracle DB and Microsoft SQL Server with top priority MSSQL.
I will appreciate if you help me on this
jideofor
Hi,
I need help on this one. Let's say I have a table like this:
Table_ID Value SomeOtherTable_ID
1 2 1
2 4 1
3 1 2
4 5 3
5 3 2
6 0 1
How can I get only two rows of each SomeOtherTable_ID? The result that I want is this:
SomeOtherTable_ID Value
1 4
1 2
2 3
2 1
3 5
Thanks in advance
Hi there!
I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.
The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.
Here's my base query:
SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC
...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:
SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID
How can I write combine the two in a nested subquery to display only the top score per user?
I want to calculate average of grades of each student and get the highest one with SQL command.
I have 2 tables:
Students:
*StudentId
*StudentName
___________
Grades:
*StudentId
*Grade
___________
I need to calculate average of each student and then get the highest.
My try:
Code:
SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId
GROUP BY Students.StudentId, Students.StudentName
ORDER BY avg_grade
LIMIT 1 FROM Students;
I encounter problem with this code, maybe it's Completely wrong...
I've got the following query
SELECT MAX(CountCategories.countOfCategory) AS maxCategory, CountCategories.phoneNumber, CountCategories.category
FROM
(SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory
FROM Categories
GROUP BY Categories.phoneNumber, Categories.category) AS CountCategories
GROUP BY CountCategories.phoneNumber, CountCategories.category;
As you can see I have a sub query that is counting categories for the same phoneNumber and category. I then create a Select query about this as I wanted to only return the highest result. However the above query is returning the same as the subquery.It isn't showing me the unique result for the highest count.
I have a need to create a line graph report in SSRS 2008. The report should show the top 10 servers from a group of servers with the highest CPU utilization for the last day. The report is for Microsoft System Center Operations Manager 2012. I have a SQL query that will return the average CPU for all of the servers in the group, with the average for each hour (24 records per server).
How can I get the top 10 servers with the highest average CPU? I think I need to create an average of the averages, then select the top 10. Here is the SQL query I have so far:
Use OperationsManagerDW
GO
SELECT
vPerf.DateTime,
vPerf.SampleCount,
cast(vPerf.AverageValue as numeric(10,2)) as AverageCPU,
vPerformanceRuleInstance.InstanceName,
[code]....
I have a query that I need to be able to pull the highest value for each row. Here is the query:
SELECT
V.VEND_ID
, V.AP_CHK_VEND_ID
, V.VEND_NOTES
, V.VEND_NAME AS VEND_NAME
, V.VEND_LONG_NAME
[Code] ....
So if someone makes a change to a vendor address, it creates a new row with the new address and a new "RowVersion" number is created. Right now this query will pull both addresses. I need it to pull the row with the highest rowversion number. The datatype for the RowVersion column is "int", and VEND_ID I believe is the unique identifier for the rowversion.
I have this query that returns the largest value in a row, but i need to know the column name that this value is in as well. any help in advance is appreciated
select clientID,
(select max(incomeValue)
from
(select earnings as incomeValue
union all
select unemployment
union all
select pensionRetirement
union all
select alimony
union all
select childSupport
union all
select dividendInterest
union all
select SS
union all
select SSI
union all
select SSDI
union all
select veteranBenefits
union all
select FIP
union all
select workStudy
union all
select other
union all
select otherHHWS) as income) as MaxIncomeValue
from tbl_income
I need to display the difference between the highest and lowest salaries. Label the column DIFFERENCE. like below:
From what I came up with is should look something like so:
<b>SQL> select * from 'salary' order by salary desc limit
select * from 'salary' order by salary desc limit 1,1 </b>
DIFFERENCE
------------------
4200
I have inherited a half-finished sql-server based project from a recently departed coworker. The critial point of this project is one app thread that reads barcodes, queries a single table in the database for the one record with that code as its primary key, and makes desisions based on that record. The faster that I can make that go, the better the process will run, up to a max rate as high as 20 queries per second if that were possible. I have a limited
general knowledge of sql, but very little of sql-server express.
My question is what is the best way with sql-server to maximize my single-table request rate?? On some other databases I could create an in-memory temp copy of the table with trigger events on the main table to keep the copy in sync, or I could do an initial select on the entire table to hopefully get the table into cache memory, or I could use some kind of ado-like table on the app side (but do I really gain much of anything doing this??)
With SQL server, what is my best approach to maximize my throughput under these conditions??
FYI..The c++ app uses direct odbc calls to a localhost database. Table theoretically could have 75000 ever-changing records in it. There are 5 or 6 other processes also hitting on this table, but at a far more lakadaisical (say once every 10 seconds level) rate.
I have a table like
TradeID ActionID
58096 3663
58096 3664
78901 2235
78901 2236
I want to select the only the TradeID with the highest ActionID
I tried using
select distinct tradeid,actionid
From
cct
Where ActionID = (SELECT MAX(ActionID)
FROM cct1
WHERE cct1.TradeID = cct.TradeID)
group by tradeid,actionid
but the result is not correct
please help
In god we trust,everything else we test.
I'd like to know the current value of my uniqueID column before Icreate a new record.Is there a way to find out this value?It is numeric in my case, but I can't just look for the MAX value,since some records may have been deleted, and the value for theuniqueID still stays at the higher value.Is there a way to read this internally kept value?
View 5 Replies View RelatedIs there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???
View 2 Replies View RelatedHello all,
Here is an SQL Server 2005 table that lists player scores:
Code Snippet
+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 7 | 01/04/08 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Liz | 25 | 25/12/07 |
| Liz | 11 | 10/04/08 |
| Mark | 11 | 22/03/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
I'd like to get, for each player, his/her best performance, including the date. Concretely, my SELECT query should return:
Code Snippet
+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 25 | 25/12/07 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
does someone have any idea ?
Thx
I have a table like
Tradeid ActionID
3664 58096
3665 58096
3666 58097
3667 58097
3668 58098
3669 58098
I want to select the only the rows representing TradeID with the highest ActionID
like
3665 58096
3667 58097
3668 58098
I tried using
select distinct tradeid,actionid
From
cct
Where ActionID = (SELECT MAX(ActionID)
FROM cct1
WHERE cct1.TradeID = cct.TradeID)
group by tradeid,actionid
but the result is not correct
please help
Hi all,
I have two problems.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Thanks,
Mahesh Manthena