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


ADVERTISEMENT

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 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 View Related

T-SQL (SS2K8) :: How To Show Last Purchased Rate In All Months Of Output

Nov 12, 2014

I've the table like

create table test
(
product varchar(50),
QTY int,
rate float,
Bill_date datetime,
total_val float
)

insert into test values ('Milk','50','500','2014-04-20', '25000')
values ('Milk','20','560','2014-05-20','12000')
values ('Milk','30','450','2014-06-18','13500')
values ('Milk','30','700','2014-06-18','21000')

My expecting output:

Month product Qty Rate T_val
April Milk 50 700 25000
MAY MILK 20 700 12000

Like I've to show the price of latest price in all of months product billed. How to make Query?

View 9 Replies View Related

SQL Server 2012 :: Identify Sets That Have Same Items (where Set ID And Items In Same Table)

Feb 25, 2015

I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:

SetIdItemCode
1A
1B
24
28
26
310
312
410

[code]....

You can see that there are some sets that have the same members:

- 1 and 10
- 2 and 11
- 7, 8 & 9

What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.

View 8 Replies View Related

SQL 2012 :: Results To Show Up As Separate Columns

Aug 22, 2014

We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.

SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,

[code]....

View 3 Replies View Related

SQL Server 2012 :: Differences In Rounding / Converting Strings?

Dec 9, 2013

difference between

SELECT ROUND ('6.465',2) --- result 6.46
and
SELECT ROUND (6.465,2) --- result 6.47
with

It's because you're relying on an implicit conversion from a string to a decimal data type which SQL server will do to 2 decimal places by default...

Alright:

SELECT ROUND (CONVERT(DECIMAL(3,2),'6.465'),2) --- result 6.47 Now please explain this:
SELECT ROUND('0.285',2) -- 0.28
SELECT ROUND(0.285,2) -- 0.29
SELECT ROUND (CONVERT(DECIMAL(3,2),'0.285'),2) --- result 0.29 The string value does not seem to be converted to decimal with 2 decimal places.

MS is on the safe side with mentioning the last digit is always an estimate But because the result of the estimate is always the same, I would like to know:

* how is a string value exactly implicitly converted?

* how exactly does the estimation work, that in case of doubt rounds a value up or off?

View 2 Replies View Related

Differences In Results Of Nchar() And Unicode() Fu

Apr 3, 2008

Hi,

I am working on an application that uses accent characters.

SELECT nchar(256) returns A. But if I do

SELECT Unicode(A) , then it returns 65 i.e. the value for simple 'A' character. I am confused, because I have data in nvarchar fields,

but when I do string comparison then A and A are treated as equal.

Please let me know, what could be the issue.



Thanks

View 1 Replies View Related

How To Show Multiple Items In A Textbox?

Nov 27, 2007

In a multi-parameters report I want to show the parameter(s) user selected in a text box. How to set this up. For example user selected A, B, C in the parameter dropdown etc in the text box I want to show A, B, C.
Thanks

View 1 Replies View Related

SQL Server 2012 :: Generate Script With All Items In Given Schema?

Oct 24, 2014

I need to copy all objects (views, procedures, tables, functions, etc -- no data) that are under a specific Schema in a database, but the Generate Script option in SSMS doesn't give me the option to do this. How I can generate a script that's Schema specific?

View 2 Replies View Related

Reporting Services :: Columngrouping In Matrix Don't Show Existing Items

Oct 12, 2015

I'm trying to create a report in SSRS using a Matrix.

The data in my dataset looks like this one:

Part      Action   SortID  Count
---------------------------------------------------
Login      LA1      1         12
Login      LA2      2         25
Login      LA3      3         548
Register  RA1      1         12
Register  RA2      2          56

I have one rowgroup by the column Part and one columngroup by SortID and my Data should look like this:

Login          LA1       LA2        LA3
                  12         25         548
Register      RA1       RA2
                  12         56

But it looks like

Login          LA1       LA2        LA3
                  12         25         548
Register      RA1       RA2       LA3
                  12         56

how I could solve my Problem, that in row number 2 the LA3 isn't shown?

View 4 Replies View Related

Is It Possible To Make A Filter Dropdown Only Show Items Satisfying Any Existing Filters?

May 22, 2008



Hi all,

In my cube there is a customer dimension and a product dimension.
If I filter on a single customer, and then click on the filter dropdown for product, all of the products for every customer are shown.
Is it possible to make it so it only show the members for the customer selected?

I suspect the answer is no, as that would mean you can't untick any previously ticked members, but our account management team have got the right hump about them showing so I need to at least attempt it.

Regards,
Phil

View 2 Replies View Related

Getting Results Of 2 Items In Querystring Using Two Different Sqldatasources

Mar 8, 2008

Hi, I'm using the following to send two items in the querystring over to the next page, where I can display tables based on them. Both querystrings appear fine in the URL. The first querystring works fine to single out the order specified by the orderid from the querystring, but I can't seem to get my other sqldatasource to show the userid (+contents of aspnet_users table) using the userid I passed in the querystring. <asp:HyperLinkField DataNavigateUrlFields="OrderID,UserId" DataNavigateUrlFormatString="Copy of Orders.aspx?OrderID={0}&UserId={1}" DataTextField="OrderID, UserId" />  This is what I'm using to try and pick up the userid     <asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource3" DataKeyNames="UserId">        <Columns>            <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" />            <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />        </Columns>    </asp:GridView>    <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"        SelectCommand="SELECT [UserId], [UserName] FROM [aspnet_Users] WHERE UserId = @UserId">        <SelectParameters>            <asp:QueryStringParameter DefaultValue="UserId" Name="UserId" QueryStringField="UserId"                Type="object" />        </SelectParameters>        </asp:SqlDataSource> 

View 1 Replies View Related

How To Find Out Number Of SQL Server Seats Purchased?

Jul 2, 2007

 How to find out number of SQL Server seats purchased?

View 2 Replies View Related

SQL 2012 :: Connection Properties Versus SSMS Server Properties

Mar 16, 2015

I have an ODBC connection string that is working fine with the following properties:

Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.

I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.

Under Registered server name I've tried:

YYYYYYXXXXX

When I browse the server for the database instance list, I receive "network path was not found".

I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.

What am I doing wrong ?

View 1 Replies View Related

SQL Server 2012 :: Show Empty Output With 0?

Oct 7, 2014

I have a sql query that gets the count of exams held in each month.

Below is the code that I have used.

select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid

The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.

I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.

I tried using case but it does not work.

View 6 Replies View Related

SQL Server 2012 :: How To Show Only First Friday Of Every Month

Jul 2, 2015

I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

View 9 Replies View Related

SQL Server 2012 :: Show Encryption For Connections

Nov 3, 2015

I have setup ssl encryption for SQL connections by using a self-signed certificate (not the best I know) and setting "Force Encryption" to Yes. Now I am trying to show that the connection is indeed encrypted and I like to see which the ssl-key is used for a connection; preferably the one that I provided. How can I do this? I am just not comfortable trusting the OS or SQL to do what I ask.The query "SELECT encrypt_option FROM sys.dm_exec_connections" Shows incrypted but no extra info.

View 0 Replies View Related

SQL 2012 :: What Are Differences Between AlwaysOn And Clustering

Apr 24, 2015

What are differences between always on and SQL Server Clustering

View 1 Replies View Related

SQL Server 2014 :: Find Names Of Customers Who Have Purchased Academic Books?

Nov 10, 2013

1. I need to find the names of the customers who have purchased academic books. (Coding required as Subquery NOT as Join)

2. Here, I need to show a list of authors who have written books and list the books they have written.

Sort the list by last name

(Coding required as Subquery)

View 4 Replies View Related

SQL 2012 :: How To Show All Files Uploaded To Server By A User

Oct 16, 2015

In our new app, a user is given the option of uploading one or files to the server and saving the filename and path to the database.

We would like to display all files associated with a particular user.

Obviously, the code below is only displaying one file.

How to display all files associated with a particular employee?

select ROW_NUMBER()OVER (ORDER BY empID) as empnum , fName, filePath from Employee where EmpID='KF14170';

I might add the format of the file is empID plus the filename:

Example: KF14170Evidence1, KF14170Evidence2.

View 1 Replies View Related

Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition

Oct 1, 2015

I have a query that returns the data about test cases.  Each test case can have multiple bugs associated to it.  I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data

TestCaseID TestCaseDescription  BugID BugStatus
1                TestCase1                       1      Closed
2                TestCase1                       2      Open
3                TestCase2                      11     Closed
4                TestCase2                      12     Closed
5                TestCase2                      13     Closed

How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.

View 3 Replies View Related

SQL Server 2012 :: Finding Duplicates And Show Original / Duplicate Record

Nov 3, 2014

There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.

How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.

SELECT
[groom_first_name]
,[groom_last_name]
,[bride_first_name]
,[bride_last_name]

[code]....

View 5 Replies View Related

SQL 2012 :: Attach / Detach Versus Backup And Restore

Mar 20, 2014

We have 5 databases which will sum up to 8+TB, housed in our EDW server. We are planning to migrate to another storage system for better performance.

I am planning Full backup and Differential backup and detach the DB and restore the DB in the new storage system.

Or should I detach,copy the data and log file and attach the files, will this approach have any advantages ?

View 5 Replies View Related

Need Show 2 Results From Same Column In Same Row

Oct 29, 2007

I am trying to build a query for a report and I need both results for a column to show on the same row. The results are multiple names tied to the same account.
This is what I am trying.

SELECT
number AS 'Customer'
(case when seq = '0' then name end) as 'name1',
(case when seq = '1' then name end) as 'name2'
FROM customer
WHERE number between '600080' and '600230'

View 5 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

Create DB View To Show Results

Sep 10, 2007

I have 2 Table
Authors
ID Name
1 Clint
2 Voke

Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2

Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.

How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.

thanks,

View 15 Replies View Related

SQL 2000 View VS. Sproc Show Different Results

May 9, 2005

I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT      *FROM          VMRawUsageWHERE      (ACCOUNT = @VMBox) AND  (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND  (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results.  The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?

View 1 Replies View Related

Query Results To Only Show ONE Result Per Locale

Feb 5, 2015

My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?

Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]

[Code] ....

View 1 Replies View Related

Queries With Different Statements - Show Results In Datagridview

Oct 19, 2015

I have made a couple of queries I want to use into a Visual studio project, Now is my problem:

All my queries have different statements. I believe the best is to show the results in a Datagridview

How to view them there? I know how to do it... but I have got about 30 queries

Here is my code so far:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"

[Code] ....

View 2 Replies View Related

3 Results From One Field - Show Levels In Right Order

Nov 19, 2007


Hi there

We have a web application (database) that uses one field called Application and another called TicketType.

When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3

Eg TicketType, TicketType2, TicketType3

The extra two levels not being compulsory.

I am using sql server 2005 // Reporting Services

My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber



End result looks like this:





Application

App 2

App 3

TicketType

Type 2

Type 3


Software

Internal Apps

proACT





SW Other






Office Issues





General




Application

Click Track server



Alert (App)

Service




Network

Other





Network Fault


Software

Internal Apps

Other



User Account

New




Hardware

Network





HW Fault




Application

Click Track server



Alert (App)

Disk space






Office Issues





General






proACT



Configuration

Deployment


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.

Can someone help please.


Please ask if I haven't explained myself.

thanks
Dianne

View 9 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 Server 2012 :: Getting Results From All Tables In Database

Mar 13, 2014

below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01

SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,
SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013,
SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012,
SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010,
SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009
FROM

[code]...

I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.

View 9 Replies View Related







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