SQL Server 2012 :: Daily Maximum Sold - Per Day
Jul 14, 2015
I have a code I wrote and I'm required to get Product Name and Quantity with the maximum quantity sold per day. My issue is with the "per day" part, here's my code below.
SELECT
t.SalesOrder
,t.OrderQuantity
,t.OrderTotal
,t.OrderDate
,p.ProductName
FROM Transactions t
INNER JOIN SalesPerson sp
ON t.SalesPersonID = sp.SalesPersonID
INNER JOIN Product p
ON t.ProductID = p.ProductID
WHERE sp.SalesPersonName LIKE 'John%'
Now from this code I need to get only Product Name and Quantity with the maximum quantity sold per day, here's what I tried;
SELECT
SUM(t.OrderQuantity) AS Order_Quantity
,p.ProductName
FROM Transactions t
INNER JOIN Product p
ON t.ProductID = p.ProductID
WHERE sp.SalesPersonName LIKE 'John%'
GROUP BY p.ProductName, t.OrderQuantity
HAVING MAX(t.OrderQuantity)
View 2 Replies
ADVERTISEMENT
Jun 3, 2014
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc
View 5 Replies
View Related
Jul 14, 2015
I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it.
SELECT
Receipt
,"Date"
,Item
,Reason
[Code] ....
View 3 Replies
View Related
Jul 9, 2014
I have the following script -
SELECTDISTINCTLOC.[place-ref] AS 'Place Ref'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,PLA.address4 AS 'Address4'
,PLA.address5 AS 'Address5'
,PLA.[post-code] AS 'Postcode'
,LOC.[location-sts] AS 'Location Status'
[Code] ....
Above is a small example.
As you can see 13 Alamein Avenue is listed twice. This is because it pulls back the former and the current tenancy. I don't want to exclude former tenancies - so I want to pull back the most recent Tenancy Reference for each Place Reference.
View 5 Replies
View Related
Oct 16, 2014
I have a dataset that I need to search for a maximum value of a specified index.
Example of dataset:
Heat Index Heat # Frame Window
1 584 110 110
1 584 102 109
1 584 95 106
1 584 190 112
2 586 100 100
2 586 150 120
2 586 170 130
2 586 112 126
I need to find the maximum value of Frame for each heat and the maximum value of Window for each heat. As you can see each heat has several data points.
Is there a way I can send the dataset to a function? I could then use a loop to test for the maximum of Frame and the maximum of Window for each heat.
Could I use an array?
View 3 Replies
View Related
Jun 11, 2015
I have Database Library, which has a lot of tables and we need 3 tables for quary:
Table Librarians: field ID, field Surname;
Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use
Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use
Query: Select the librarian's surname, which gave the most count of books.
I know, how to resolve, when i took datas only from one table, e. g. TeacherCard
SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM
(SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T
WHERE T.Id_Lib IN (SELECT L.Id)
GROUP BY L.LastName) AS Librarians
GROUP BY Librarians.LastName
ORDER BY MAX(Librarians.CountOfBooks) DESC
GO
I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.
View 9 Replies
View Related
Dec 9, 2014
What is the maximum no.of global temporary tables can create in sql server
View 4 Replies
View Related
Oct 21, 2014
I want to select weekly data from daily data.lets say Today's date-10/23/2014(Thursday) My data is in date time but i want to see only date
output should be from last week Thursday to this week Wednesday. similar for previous dates
Weekly sum(profit)
10/16 - 10/21 - $1000
10/9 - 10/15 - $4100
10/2 - 10/8 - $ 8038
--
--
--
View 2 Replies
View Related
Jun 26, 2014
Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target. We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do. Permissions are different on each instance, so would prefer not to overlay users on destination database. Options we are looking at right now are...
Log Shipping
Snapshot Replication
Transfer SQL Objects Task (SSIS)
Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.
View 5 Replies
View Related
Jul 7, 2015
For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.
She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:
- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.
- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.
- It looks unprofessional.
These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.
Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go
Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO
My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?
View 2 Replies
View Related
Jul 29, 2015
I need to schedule daily backup at 6:00am. how to do this. I'm on Sql Server 2008r2
View 1 Replies
View Related
Jun 17, 2015
I'm using SSMS 2012. I have multiple queries that are run daily(I also have a few that are run every Monday) that I want to schedule to run at midnight and export to a new xls file on the network drive to be used the next morning. Is there something that I can add to each query to accomplish this or would I have to set something else up to call the queries?
View 3 Replies
View Related
Sep 13, 2013
I need to run a query on all of the items in the product table where the amount that I've sold over the past 2 days is greater than the amount I have available. Basically, am I likely to sell out of stock?
I have all of the information I need across 3 tables:
- bvc_product
- bvc_order
- bvc_orderitem
The database is available here: [URL] .... It's about 400 KB zipped & 3 MB unzipped.
I've written the following query & I can see all of the data that I need, but (even conceptually) I don't know how to make the comparison.
SELECT
o.[ID]
,o.[TimeOfOrder]
,oi.[Qty]
,p.[ID]
,p.[AggregateQty]
FROM [tmpDB].[dbo].[bvc_Order] o
INNER JOIN tmpDB.dbo.bvc_OrderItem oi
ON o.ID = oi.OrderID
INNER JOIN tmpDB.dbo.bvc_Product p
ON p.ID = oi.ProductID
I use Microsoft SQL 2008
View 16 Replies
View Related
Mar 17, 2014
How many nodes can you have in a cluster with SQL 2012 alwaysOn.
I understand that availability groups are limited to 5 nodes but if you had a 10 node cluster and decided to create multiple availability groups using various nodes within the 10 nodes but never exceeding 5, is that possible?
Or is there a counter or some validation from SQL AlwaysOn that actually hard limits to a grand total of 5 nodes in a cluster?
View 6 Replies
View Related
Mar 6, 2015
I inherited a table with this structure:
Value a Value b
x date a
x date b
x date c
y date d
z date e
z date e
z date f
Value a fields are one to many. The objective is to obtain the maximum date value for each unique a value.
View 2 Replies
View Related
Apr 14, 2006
I want to be able to track my top 100 items sold in the last 60 days. Each night I want to calculate it then compare it with the numbers from the previous night. Then show both ranks. My problem is assigning them ranks. Bascily I have a view that shows the top 200 sorted correctly. I am thinking creating a DTS to make a new table with adding transformations. But I am not sure how to code the Visial Basic. Below is what I thought it would be, but it doesn't work. Get a generic error. Any ideas?dim ranknumranknum = 0Function Main() ranknum += 1 DTSDestination("Rank") = ranknum DTSDestination("ItemNumber") = DTSSource("ItemNumber") DTSDestination("QtySold") = DTSSource("QtySold") Main = DTSTransformStat_OKEnd Function
View 2 Replies
View Related
May 20, 2015
I have a table which dont any Identity column. The data in the table has duplicate values, some rows are totally identical. I just want to select the row which has maximum Row Id/Row number if I am getting multiple rows in my select statement.
View 2 Replies
View Related
Sep 12, 2015
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
View 5 Replies
View Related
Apr 20, 2012
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
View 14 Replies
View Related
Mar 11, 2001
I want to send auto email to my subscribers(no. of subscribers always vary)
This auto email has to be send daily.
Could it be done by generating certain Stored Procedure ?
if yes then please help me by providing Stored Procedure.
Thank you
View 1 Replies
View Related
Aug 21, 2014
Trying to generate a daily KPI report from our SQL server. I do not have access to write any functions. In the "start date" and "end date" section what numerical value would I enter to give me the equivalent of CURRENT_DATE ()?
View 1 Replies
View Related
Oct 10, 2005
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
View 2 Replies
View Related
Dec 2, 2006
Hi All,
Is anyone can show me how to setup sql server to do backup daily automatically?
TIA
View 2 Replies
View Related
Jun 4, 2012
Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something?
If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?
View 9 Replies
View Related
Mar 7, 2005
I'm working on a reporting tool that could bring back hundreds of thousands of results back at once. I need some way to run the actual query only once a day, and then the reporting tool would just pull back this cached results. To be short, I need to figure out how to do this using a minimum amount of resources. Would a DataView work with something like this? How would I have it update only once a day? I appreciate any advice!
View 2 Replies
View Related
Apr 22, 2015
I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.
I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.
I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.
View 4 Replies
View Related
Dec 11, 2007
Hi,
I have a Users table in Oracle database and same table (Users) in SQL Server 2000 database. I want to create a DTS Package through which I can copy the data from Oracle database to SQL Server 2000 database. This package should run automatically at mid-night daily so that if there are some entries done in Oracle database then it get copied in SQL Server 2000 database. Also is there any way to copy only those entries from Oracle database which are not present in SQL Server 2000 database. Please help me in this regard as I am new to DTS.
Thanks
Rohit
View 1 Replies
View Related
Dec 11, 2014
I'm using ssms 2014, but got the same problem with 2012. I use ssms almost entirely on remote desktop sessions ( Windows 7, Server 2008R2, Server 2012 ). It may be related to having filtered job activity monitor windows open for hours, but about once per day ssms fails, and has to restart. Upon resuming usually only one of several queries is restored.
View 8 Replies
View Related
Oct 7, 2015
how to load the data from oracle to sql server
oracle source is having 7 tables
sql server target is having 7 tables
i have used VISUAL STUDIO and created the one data for individual but how to run in sql server that ssis package
View 9 Replies
View Related
Sep 25, 2015
I create a report base on categories and sales of goods. Now I have Daily Info about all Products.
But I Need to present this report base on weekly periods. in pivotal format.
I family with pivotal format but change between daily report and weekly report is ambiguous for me.
View 3 Replies
View Related
Aug 9, 2000
In SQL 7, what is the Maximum # of databases per Server?
thanks in advance.
View 1 Replies
View Related
Aug 13, 1999
How many database a single SQL server can manage. Assume that I have enough hard disk space. (eg. 25 MB DB can I have 500 databases). What should be accurate setting required for SQL server configuration like open database, objects, locks, connections etc.
Help me out with suggestions and proper source of Information for the above.
Thanks
Rajasekar
View 3 Replies
View Related
Nov 4, 2003
Here is something that perhaps a lot of you out there wonder about..
My company is on a large Enterprise Project.
The database plans for this to be fully supported predict 1000+ stored procedures.
Question:
Will there be any limitation for an SQL Server 2000 to handle so many stored procs?
and if there is a limitation what will we have to do in order to work around it?
View 4 Replies
View Related