Create A View That Will Give Most Current Status (by Statusdatetime) Of Each Order Number
Jan 20, 2015
We are having trouble figuring out how to create a view for this scenario:
We have a status log table that holds an order number, statusdatetime, and statuscode. This table will have multiple status' for the same order number. I want to create a view that will give me the most current status (by statusdatetime) of each order number. This view would show: order number, statusdatetime, and statuscode.
Here is a sample of the data:
Order numberStatusDateTimeStatusCode
1234512/15/2014 15:00CREATE
1234512/15/2014 16:30CONFIRMED
4567812/16/2014 08:00CREATE
9876412/18/2014 12:00CREATE
9876412/19/2014 08:00CONFIRMED
4567812/17/2014 09:30CONFIRMED
4567812/19/2014 15:30IN-TRANSIT
So my view should result in :
Order numberStatusDateTimeStatusCode
1234512/15/2014 16:30CONFIRMED
9876412/19/2014 08:00CONFIRMED
4567812/19/2014 15:30IN-TRANSIT
View 4 Replies
ADVERTISEMENT
May 14, 2012
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
View 7 Replies
View Related
Apr 24, 2007
I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.
For example:
Table Structure:
============
Application: ApplicationID, Name, Address, City, State, Zip, etc..
ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt
View should display:
==============
ApplicantID, ApplicantActionID, Status, CreateDt
Example:
==========
ApplicantID=4, Name=Bob Smith, etc....
ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00
ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00
ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00
.... etc....
View should return:
Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00
etc....
View 4 Replies
View Related
May 22, 2006
Hello,
I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue.
CREATE VIEW [dbo].[vw_SalesRep_Chaining]
AS
SELECT Top 100 percent Rep_id AS RepId,
Rep_cd AS RepCode,
Region as Region,
CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico'
WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro'
WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other'
ELSE '' END AS Sub_Region,
RSM AS RSM,
UPPER(Regional_Manager) AS Regional_Manager,
Market,
Rep_Manager_id AS SalesManager,
UPPER(MarketManager) AS MarketManager,
New_Position AS NewPosition,
Rep_Status AS RepStatus,
UPPER(Rep_Payroll_name) AS PayrollName
FROM (SELECT SalesRep_GUID, Rep_id, Rep_cd, Rep_Payroll_name, Rep_SSN, Rep_Status, Hire_dt, Termination_dt, Commission_start_dt,
Rep_Manager_id, Region, Market, New_Position, Validity_start_dt, Validity_end_dt, Load_interval_id, Create_process_id, current_ind
FROM dbo.SalesRep
WHERE (current_ind = 1) AND (New_Position IN ('SC1', 'SC2', 'SC3', 'MSO')) AND (Region NOT IN ('Emerging Market')) OR
(current_ind = 1) AND (New_Position IS NULL) AND (Region IN ('TeleSales')) AND (Region NOT IN ('Emerging Market'))) AS A
INNER JOIN (SELECT Rep_id RepId, Rep_Payroll_name as MarketManager, Rep_Manager_id as RSM
FROM dbo.SalesRep AS SalesRep_2
WHERE (current_ind = 1)) AS B
ON A.Rep_Manager_id = B.Repid
INNER JOIN (SELECT Rep_id RepId1, Rep_Payroll_name as Regional_Manager FROM dbo.SalesRep AS SalesRep_1
WHERE (current_ind = 1)) AS C
ON B.RSM = C.Repid1
WHERE (A.Region IS NOT NULL)
Order by Rep_Id, Rep_Cd
This is the script I have used to create the view. But when is simply query the view it does not appear in the order of Rep_Id, Rep_Cd
View 1 Replies
View Related
Apr 9, 2008
I have a View created from 2 tables. How do I add an autoindex (0,1,2,3,..) to a new column?
View 8 Replies
View Related
Jun 5, 2008
Hello All,
Can someone help me with this query?
I have the following table and data structure
Table1: Orders
Field1: OrderID
Table2: OrderStatus
Field1: OrderStatusID
FIeld2: OrderID
Field3: StatusID
Field4: InsertDate
Sample Data:
Table1:
11
12
Table2:
1,11,1,6/1/2008
2,12,1,6/1/2008
3,11,2,6/2/2008
4,12,2,6/2/2008
5,11,3,6/3/2008
6,11,4,6/4/2008
Wanted Results:
11,4,6/4/2008
12,2,6/2/2008
View 3 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Sep 24, 2012
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
View 4 Replies
View Related
Sep 1, 2007
Hi all - am having trouble working out what would be the best way to do this (most efficient)I have written an order, despatch and accounts system. To get a list of orders ready for despatch, I have written a query which calculates the amount of items ordered against the amount of items despatched from that order. If there are any outstanding items, then this order is in the list of orders which are outstanding for despatch.
I am having trouble working out how to go about creating a list of orders ready do be invoiced... Orders that are ready to be invoiced can be completely despatched orders or part despatched orders. That is to say that if an order is only half despatched, my client still wants to be able to raise an invoice against that part of the order. I envisage creating a query which checks my orderline table to see if the item has been invoiced and creating the list from there. But as the application is used more and more, this query will take longer to run? ANy ideas?
Thanks.
Steve.
View 1 Replies
View Related
Oct 4, 2007
Hello, without going into the politics of why I'm asking, does anyone have a compiled list of functionality that only sysadmin's could do in 2000 that VIEW SERVER STATE permission opens up in 2005?
From the documentation, so far I've found fn_get_sql (and it's dm view) but I'd really like a complete list.
View 5 Replies
View Related
Jun 18, 2008
We are an accountanting firm who wants to upload tax returns in the .pdf form for our clients. We want them to be able to login, receive that status of their return, and if completed download the .pdf. But what we cannot have is everybody having access to everybody's returns, it has to be exclusive to that client and their username and password. It also has to be a simplified process where once a .pdf is made, one of the employees will have administration access to upload the file to the website corrolating with a clients information. I'm assuming that would be done by a web application?? I have already set up a a login control, on the login.aspx page, along with the ASPNETDB.MDF through the ASP.NET Configuration tool. Also, I'm assuming the file naming process of the tax returns would fall into play somewhere?? I some how need to take the login information from the ASPNETDB.MDF and relate it to their PDF's. What I have explained is the major purpose of the site and if I could find the right help I would greatly appreciate it because I've been stuck on this road block for awhile.
Thanks,
Chris120590
View 7 Replies
View Related
May 18, 2006
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
Thanks,
View 16 Replies
View Related
Nov 27, 2014
I have a requirement to present computed sales order status value
Parent table : consumer
Child table of consumer : sales order
Child table of sales order: items
Items table has status column with awaiting, accepted , rejected.
Status of the sales order should be set as following
If all items are rejected then sales order status=reject
Or if at least one item is accepted then sales order status = accept
Or if items status is awaiting then sales order status = pend
Query to find all sales order per consumer with computed order status for each sales order.
View 2 Replies
View Related
Apr 7, 2008
I am looking for assistance coming up with a function (or maybe not a function if there is a better way) to make the Status column in my order table dynamic. The default value for new records will always be "1" to designate a quote. At this point the field is not dynamic. Once the customer confirms the order, the user needs to execute a command to change the status to "3" to designate a Confirmed order. At this point the field needs to be dynamic based on the shipping records. There are two order details tables. One for sales items and one for rental items. Each of these details tables has their own shipping record. the CheckInOut Tables are for rental while the Ship tables are for sales. So, if some (but not all) of the items in either of these order details tables has a shipping record associated with it, then the status should be changed to "5". If everything has been shipping, the status is changed to "4". If everything has been shipping but some items have been returned, the status is "6" if everything has been shipping and all of the RentalDetail items have been returned then the status is "7" and if there is any other combination of a variety of ships and returns, the status is "8". Also, at any time, the user needs to be able to execute a command to change the value to "2". once the value is changed to "2" the field stops being dynamic again.
Below are my tables creation commands.
CREATE TABLE OrderHeader
(
OrderID int identity primary key,
Status int,
StartDate datetime,
EndDate datetime
)--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed"
CREATE TABLE OrderRentalDetail
(
OrderRentalDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE CheckInOutHeader
(
CheckInOutID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost"
CREATE TABLE CheckInOutDetail
(
CheckInOutDetailID int identity primary key,
CheckInOutID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderRentalDetailID int,
Qty int
)
CREATE TABLE OrderSalesDetail
(
OrderSalesDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE ShipHeader
(
ShippingID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return"
CREATE TABLE ShipDetail
(
ShipDetailID int identity primary key,
ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderSalesDetailID int,
Qty int
)
View 3 Replies
View Related
May 20, 2014
I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
View 7 Replies
View Related
Aug 28, 2015
Here is my requirement
Table 1 Order
order ID, Sales order ID order date, order type
Table 2 Order details
Order Details ID, Order ID, Order Stage
Table 3 Related Order details
Order ID(FK to Order ID), Related Order Details ID(FK to Order Details ID), Related Order ID( FK to Order ID)
Here is example
Table 1 Order
1, 1234, 2015-01-01, Refill
2, 1234, 2015-02-02, Extension
Table 2 Order Details
1, 1, Approved
2, 1, Approved
3, 2, Rejected
Table 3 Related Order Details
2, 1, 1
2, 2, 1
I have to Select Order, Order Details and Order Status
Order Status is determined from Order Stage as follows:
If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.
For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)
How to combined order stage from table 2 and table 3 and then compute order status.
View 3 Replies
View Related
Feb 25, 2008
Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?
View 1 Replies
View Related
Jul 17, 2015
I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.
View 3 Replies
View Related
Jul 8, 2015
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies
View Related
Nov 25, 2013
I am trying to get the current row number using this:
SELECT ROW_NUMBER() OVER (ORDER BY QUANTITY) FROM RDR1 WHERE Row = CurrentRow
However i got error message says row is an invalid column name.
View 11 Replies
View Related
Jul 9, 2015
Created Prod order status report, in status, we have different status
created =0
start =4
released =3
reported as finished =5
ended =7
I have the report, in report don't want to show the Prod order for ended status, how can I add the filter for this so it can show for all the other status not ended status. when I did on filter <7 , it did not work
View 4 Replies
View Related
Aug 12, 2015
Need to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies
View Related
May 12, 2015
Is there a way i can grant a windows authenticated user say abcadam privilege:
-> to create, execute and alter the stored procedures that will be created by him only:
Point is we do not want the user to execute alter or modify other stored procedures out there for that DB
View 13 Replies
View Related
Jun 26, 2007
How do you find out the current number of connections to a SQL Server 2005 instance in SQL Server Managment Studio? I need to see if my number of connections is exceeding my Maximum Worker Threads.
Thanks,
Joe
View 3 Replies
View Related
Aug 5, 2015
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
View 4 Replies
View Related
Nov 20, 2002
Trying to view current activity in EM however get error 1222 Lock request time out period exceeded, this also occurs when trying to view tempdb database.
Is this a EM Gui error or locking issue and hwo do you resolve?
Thanks
GaryW
View 5 Replies
View Related
Jan 17, 2007
hi
How to find current row number in MS SQL 2000 server DB ?
like in oracle rowid is there to get unique no. of each row.
I want to track the last row in the resultset ..
is any fun for this ??
View 7 Replies
View Related
Oct 11, 2000
I can set the compatibility level by using :sp_dbcmptlevel
But before doing this - How can i view my current DB's compatibility level?
Any help appreciated
View 1 Replies
View Related
Jul 10, 2007
Hi, I'm dabbling in trying to retrieve some info from an SQL database and publish it in Sharepoint data view. I'm trying to create a View in the SQL database that retrieves all records for the current month. So that in July, it automatically shows me July records, and when August ticks over, it automatically displays August's records. I can hard code the dates in to the query no probs (e.g. a statement like ENTRYDATE >= '1/07/2007') but was hoping there is a way to specify current month. So maybe the statement looks like ENTRYDATE >= "CURRENTMONTH".
Thanks in advance!
View 9 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
May 16, 2006
I have >200 tables and I want to create a table that lists the name ofeach table, the number of records, and the number of locations withinthe table.I've created a cursor to do this but it doesn't like it. I get thefollowing error.Invalid column name '<tablename>'.Here's my scriptDECLARE @tbl varchar(100)DECLARE @sql varchar(1000)-- Insert statements for procedure heredeclare c_table cursor forselect table_name from INFORMATION_SCHEMA.TABLES where table_type ='base table' order by table_nameopen c_tablefetch next from c_table into @tblwhile (@@fetch_status = 0)beginset @SQL = 'INSERT INTO [zzTable_Status]SELECT ('+ @tbl +') as tblname, count(distinct station__no),count(station__no)FROM [bronze_views].'+@tbl+''exec (@SQL)Print @tbl + ' Updated'fetch next from c_table into @tblendclose c_tabledeallocate c_tableAny help is appreciated...
View 13 Replies
View Related
Mar 21, 2008
Do anybody know how can I find or display to the current SQL server 2005 record number (eg. 10 of 1600) on a VB 2005 form label. The BindingNavigator on the form has been deleted. Thanks.
View 6 Replies
View Related
Jan 26, 2012
RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23
The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:
RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34
The code below is almost there but falls over on Recordno 8:
select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno
[code]...
View 4 Replies
View Related
