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

SQL Server 2012 :: Results To Show Differences In Items Purchased Versus Sold?

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

SQL Server 2012 :: Daily Sum Aggregation

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

SQL Server 2012 :: Maximum Tenancy Reference

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

SQL Server 2012 :: Search For Maximum Value Of A Specified Index On Dataset

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

SQL Server 2012 :: Select Maximum - Using Fields From 2 Tables?

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

SQL Server 2012 :: Maximum Number Of Global Temporary Tables?

Dec 9, 2014

What is the maximum no.of global temporary tables can create in sql server

View 4 Replies View Related

SQL Server 2012 :: Getting Custom Week Data From Daily Data?

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

SQL 2012 :: Copy A Database To Another Instance Daily

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

SQL 2012 :: Argument Daily Transaction Log Shrink?

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

SQL 2012 :: How To Schedule Daily Backup At 6:00am

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

SQL 2012 :: Scheduled Query To Export To Daily XLS File

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

Check Sum Of Qty Sold Against Available Qty

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

SQL 2012 :: Maximum Cluster Nodes In AlwaysOn

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

SQL 2012 :: Selecting Maximum Value From Denormalized Table

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

Top 100 Items Sold In The Last 60 Days Rank Tracking

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

SQL 2012 :: Get Maximum Row Number For Duplicate Valued Rows

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

Transact SQL :: Error - Maximum Row Size Exceeds Allowed Maximum Of 8060 Bytes

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

Error - Maximum Row Size Exceed Allowed Maximum Of 8060 Bytes

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

Daily Auto Email Using SQL Server

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

Generate A Daily KPI Report From Server

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

...Contains More Than The Maximum Number Of Prefixes. The Maximum Is 3.

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

Setup Backup Daily In SQL Server 2005

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

SQL Server 2008 :: Get A Report To Run Daily Between Certain Hours?

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

Storing Daily Cached Query Results In SQL Server

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

SQL Server 2014 :: How To Auto Update Multiple Tables Daily

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

DTS Package In SQL Server 2000 To Transfer Data Across Tables Daily

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

SQL Server Admin 2014 :: SSMS Restarts Daily On Remote Desktop?

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

SQL Server Admin 2014 :: Automatically Daily Load Data From Oracle?

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

SQL Server 2014 :: Change Daily Info To Weekly Periods In Pivot Report

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

Maximum # Of Databases Per Server

Aug 9, 2000

In SQL 7, what is the Maximum # of databases per Server?
thanks in advance.

View 1 Replies View Related

Maximum Database A Single SQL Server Can Contain?

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

Maximum Allowed Stored Procedure In SQL Server

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







Copyrights 2005-15 www.BigResource.com, All rights reserved