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
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
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
Hi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
Hi,
I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.
I have a form where they can add this and i add this experience using the following stored procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);
It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.
But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.
Is this possible ?
But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.
Returning "completed" when status = 1 and "not completed when status = 0
View 3 Replies View RelatedSQL7, sp3, NT4...
Unless I grant a developer "system admin" role, they are unable to see whether or not any job *not* owned by themselves is running?
EX: Developer A can't see whether Developer B's job is executing? Why? I've granted them both db_owner in msdb, and all permissions possible in msdb. Is there an extended sp I can look for?
Thanks,
Hi Gurus,
can any one help me to get the job status of a sql job.
My requirement is as under
ordinary user (not SA) will query pass job name as argument to stored procedure and sp has to give me the job status.
Thanks in advance
I have a table with columns c1, c2, c3, c4.if all nulls or blanks. Status = 0if c1 assigned but no c2, c3, and c4, then status = 1if c2 assigned but no c3 and c4, then status = 2if c3 .. then ..if c4 .. then ..I want to have one SQL to get the status like (ignored checking forblanks here for demo)SELECT Status = (if not c4 is null then 4else not c3 is null then 3else not c2 is null then 2else not c1 is null then 1else 0)FROM mytable.Thought of using CASE ... WHEN ... but it is only on one colum.Any better idea.ThanksJohn
View 1 Replies View RelatedIn SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.Thanks a lot!Penn
View 1 Replies View RelatedHi 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.
Hello,
Can anyone guide me to monitor whether Production Sql server is up and running and would e-mail or page me if by chance it has stopped.
Any help is appreciated.
Thanks.
Priti
is there any way to make the status of a package execution to be success without caring about failure of certain steps in the package?
View 1 Replies View RelatedI need to know the meaning of the status bit in sysdatabases because BOL only reports some of them.
For SQL 7.0 SP1 I have some database with a status bit equal to 0 (zero) and equal to 12.
For SQL 2000 SP2 I have some database with a status bit equal to 0 (zero),24,20 and 1073741840.
So I would really appreciate if someone can explain the meaning of these status bits.
Thank you very much.
Franco
Hi,
Is there any way to update the status bar in Query Analyzer.
Suppose I am running a script which updates data to a file. I want to know the status showing the percentage it completed.
Thanks
:confused:
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
Any recommendations?
I have a question I hope someone can help me with.
My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
Thanks for any help,
Andrew
Hi
My boss and I have recently started designing a new database, and we've got into a bit of a debate about creating status tables (e.g. order statuses).
It was my intial instinct to create a separate table for each kind of status (e.g. an OrderStatuses table, a JobStatuses table, etc), however my boss wanted to create one table containing all the statuses, with an extra field for defining what kind of status it is.
I was just wondering what others thought was the best approach.
Thanks
Tom
Hi Gurus,
While iam connecting to servers through management studio iam facing
iam seeing like few servers are connected with Green colour (like>)and few are connected with circle as white.Frm these how can i decide the server status.
Is there any comand to view server status ?
Thanks,
ServerTeam
How can i exclude those with more than one status? i tried using rows but it seems doesn't work.
ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close
the result should appear as:
ID status
2 new
2 close
4 new
4 close
I have a table of status_Master where the details are maintained by street name with status but I need the result of total count by each status against the street name.
Status_Master
Sl NoStreetNameStatus
1       Delhi        Pending
2       Delhi        Working
3       Delhi        Working
4       Mumbai        Pending
5       Mumbai        Pending
6       Delhi        Working
7       Delhi        Problem
8       Mumbai        Working
9       Mumbai        Problem
50Â Â Â Â Â Â Â . Â Â Â Â Â Â Â .
100       .       .
200       .       .
Result as below
StreetNameWorkingPendingProblem
Delhi              3           1           1
Mumbai          1           2           1
table1 has 3 fields:
bookNumber
123
234
345
status -- status defaul value is 0
partnerNumber
1
2
3
table 2 has 2 fields
bookNumber
234
345
567
789
partnerNumber
1
2
3
4
A stored procedure named remove_books, which remove books if the conditions are true.
If stored procedure is executed. That means, it removes the bookNumbers from table2 if the conditions are true. Lets say it removes 234 from table2
then
do update in table1 status = 1 for BookNumber 234.
I need to know how can i see the replication status. I have 4 servers with ms sql 200 standard server and hundreds of commnad to replicate. Sometime is important to know how mush of this commnads is replicated.
Thank You.
T-SQL for checking which jobs are currently running ?
And
what happen if index rebuild job overlap with shrink log file job
Is database wont allow to insert or update for other application
Thanks in Advance
Dina Satam
Hi All,
Using SQL Server 2005 w/ SP2 I have a simple select query with a parameterized WHERE clause that in Management Studio runs nearly instantly, everytime. Using the same login, when I run the same query using SQL RS the results (only 6 rows) takes upwards of 5 minutes. If I look at the Activity Monitor, it shows the process as suspended and indicates a CXPACKET. Everything I have read on the forums about this indicate it is an issue with not having SP2 , which I do. Or a 64 bit version, I have 32. Or for large bulk updates, I am doing a very simple select. Does anyone have any clue on this?
regards
Bill
Hi everyone,
while executing sp_who sub directory, I see a column which makes me totally confused. This column is status column.
I understood nothing about its usage. So would you please inform me ?
Thanks
This probably isn€™t a T-SQL question but couldn€™t find an appropriate place in the forum for it.
I€™m building a statistics page for our customers and i want to show how long the SQL server has been up since the last restart, I€™ve got the windows uptime from elsewhere but wondering if there€™s a way to get the SQL Server uptime.
Thanks
Chris
Dwebs Ltd
I need a way to determine if a query is still running on the database server. Is there a way to do this?
If the query is still running after 2 hours I need to send a message. How can I query the database for the status of a query?
Hi.
How can i check the job status using SQL Query?
I have a database that tracks events as they occur. The events are used to track the completion of a series of tasks. The events have a event type id and a result. The type id is used to track a specific task, and there is a result for it.
so the table looks something like:
DATETIME, EVENT_ID, RESULT_CODE
Since the same task can be performed many times a day, I am only interested in getting a snapshot of all event types at 11:59:59 PM. If an Task of type A had a sucess event then failed event on the same day, I would count it as failed.
Additionally, I if an Task succeeded on say 5/18, an the same task did not occur on 5/19, I would want to count it as a success because that was the last known state from the 18th.
I need to produce a report showing our progress over time (I would guess plotting a line between 0 and 100%), around tasks that have passed, failed, and have not been completed. (no completion record exists)
As far as I can tell, I need to get the last known result, per event type, per day.
I know how to do this in TSQL, but my team is making a big bet in Analysis Server and I'm at a loss as to how I should tackle this.
Any help would be appreciated.
Hi There
View 1 Replies View Related
Hi,
In command line how do i get status of Package Execution?
Thanks
Hello
The basic requirement is to import data from excel sheet and save it to a table. The excel filename and sheet name is passed in as a parameter to a stored procedure. I am using sp_executesql to execute the statement.
First I delete all the records from the table and then copy the data from excel into the table. I want to do this as a transaction so that if there is any error in the import process, I want to rollback the transaction otherwise commit it. sp_executesql returns 0 for success or 1 for failure.
In my case, even if there is any syntax error, I get the status of 0. Also the @@ROWCOUNT contains some positive number greater than 0 even if some error occurs.
BEGIN TRANSACTION T1
GO
DELETE FROM SORTGROUP
GO
DECLARE @Statement as NVARCHAR(1024)
DECLARE @TableName as VARCHAR(32)
DECLARE @ExcelFile AS VARCHAR(512)
DECLARE @ParamNameList as VARCHAR(1024)
DECLARE @SheetName as VARCHAR(64)
DECLARE @Status as INT
DECLARE @ErrorCode as INT
DECLARE @RowCount AS INT
SET @TableName = 'SORTGROUP'
SET @ExcelFile = 'E:PROJECTSRALEIGHTEST.XLS'
SET @ParamNameList = '*'
SET @SheetName = 'SORTGROUPDATA'
EXEC master..xp_sprintf @Statement OUTPUT, 'INSERT %s SELECT %s FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 5.0;DATABASE=%s'', ''SELECT %s FROM [%s$]'')', @TableName, @ParamNameList, @ExcelFile, @ParamNameList, @SheetName
SET @Status = -1
exec @Status=sp_executesql @Statement
SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT
IF @Status = -1
ROLLBACK TRANSACTION T1
ELSE COMMIT TRANSACTION T1
PRINT @ErrorCode
PRINT @RowCount
PRINT @Status