Table Name Changes - How To Grab Last 24 Hours
May 30, 2014
My DB saves it's data into a table at the end of each day like:
'e4_event_20140530' where the last bit changes according to the date. So 30th May 2014 in this case.
What I am trying to do is query the last 24 hours. I know i can grab from 2 tables and do a 'between' with times but it means having to change table name and times in the query every time i run it. I'd just like to run it and for it to just fetch the last 24 hours at any point in time.
My DB outputs time like '2014-05-30 08:54:23'
View 2 Replies
ADVERTISEMENT
Aug 21, 2001
I want to include the name of the user whom so ever has changed the record.
I want to insert the name of the user in the column.
How to grab the name of the user from the action...?
Thanks,
Harish
View 1 Replies
View Related
Oct 11, 2004
i have 2 tables right now, po and notes...
the notes table contain the note_id and notetext, so example of a row would be:
1 this is the note for the first row
2 this is the note for computer parts
etc..
the po table has item info, description, po_id, and note_id, heres an example:
po_id item desc note_id
1 215-33 computer parts 2
the problem is, is there a way where say i insert the note row into the notes table first and then get the note_id of that, so that i can then insert the row into the po table which includes the note_id from the notes table?
would i have to use two separate inserts or can i use one? i was going to just insert the notes row first, and then use a query using the LIKE() function on the note text to get the note_id, but some notes may be the same as others, so the note_id wouldn't be the correct one :( and i think just getting the last row of the notes table would be bad too incase someone happens to insert a row into that table at the same time
any ideas please?
thanks!
View 1 Replies
View Related
Nov 16, 2006
I have run into an issue that seems very simple but I am new to SSIS and DBs in general and therefore cannot solve it. I would like to take data from a SQL Server table, lookup a key from a dimension table and update the same SQL Server table with this data. Is there anyway to do an update using SSIS?
Thanks,
Curtis
View 1 Replies
View Related
Apr 22, 2015
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
View 6 Replies
View Related
Sep 21, 2006
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies
View Related
May 13, 2008
I have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
View 10 Replies
View Related
Nov 7, 2007
I'm currently running a reorganize on a large table of ~60 gig. I started the reorganize when I noticed the fragmentation was 97.95%!!!
Well the reorganize has been running for 25 hours now and apparently bulk inserts cannot happen during this time since my SSIS package just bombed trying to prepare for bulk insert.
Anyway, my question is can I cancel this reorg? I didn't start the reorg through the query analyzer. I saw this cute little reorganize button when I right clicked my indexes, properties, fragmentation in the SQL 2005 management studio. I clicked it and then clicked ok.
I know I should have done an alter rebuild but I wasn't comfortable with the process and did the one click solution that is now killing me.
What happens if I go into task manager and shut down the process? Am I risking a serious side effect of corruption or will SQL just stop so I can rebuild the index properly?
View 4 Replies
View Related
Sep 2, 2013
how can i only grab the last 4 ID:
data
19Jul hello ID UKAU
9Jul hello ID UJKO
7Jul hello ID POJP
View 2 Replies
View Related
Mar 25, 2014
I have a list of data sample as below:
MYDATA
2/26/2014 19:35:0 2/26/2014 19:35:0 2/26/2014 22:25:0 UK AUS start end
3/1/2014 9:20:0 3/1/2014 9:20:0 3/1/2014 10:5:0 AUS UK start end
How can I only grab as below:
newColumn
UK AUS
AUS UK
View 8 Replies
View Related
Jan 28, 2004
Hello-
I have a chart I am producing in .net and I need to values. The problem is the values (sums) i need are on 2 different servers. Is there any where to combine the query using two databases? Does anyone have any suggestions?
thanks
View 5 Replies
View Related
Aug 4, 2014
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="ABC"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="KJHKH"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="POJ"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="AKJFISM"/>
<Hello currency 988 v=""/><hello::Value v="0"/><Arrival Code v="PM"/>
how can i only grab the arrival code?
newColumnArrivalCode
ABC
KJHKH
POJ
AKJFISM
'
'
'
'
View 4 Replies
View Related
Mar 6, 2008
Hi, I am very new to the .net framework, but I have a lot of experince in php/mysql applications. So this is this is proboly going to seem like a nwebie question.
I am trying to pull a single item out of the database programaticlly, take the value of that item and set it to a varible so I can work with it in other areas.
Here is basically what I have this is on the vb page in a sub I have the varible MonthlyPrice Defined earlier in the pageDim connString As String = _ConfigurationManager.ConnectionStrings("ConnectioString").ConnectionString'Create a SqlConnection instanceUsing myConnection As New SqlConnection(connString)'Specify the SQL queryConst sql As String = "SELECT Price FROM Plans Where PlainID='" & PlanId & "'"'Create a SqlCommand instanceDim myCommand As New SqlCommand(sql, myConnection)'Get back a DataSetDim myDataSet As New DataSet'Create a SqlDataAdapter instanceDim ReadData As New SqlDataReader(myCommand) 'Bind the DataSet to the GridViewMonthlyPrice = ReadData("Price")'Close the connectionmyConnection.Close()End Using
I just want to get the price of the plan where planID is equal to the value of my varible planID. I want to set that equal to MonthlyPrice which is a decimal so I can add it into some calculations and return a value. I don't really want to bind the data to anything but everytime I search google I can't find anything excpet for binding data to grideview and things like that.
I have tried several diffrent approaches that I have found out there but have gotten errors or and things I am not understanding.
I originally was trying to access a datasource I already have defined that is on the aspx page.<asp:SqlDataSource ID="SQLSelectPlan" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Plans] WHERE ([PlanID] = @PlanID2)">
<SelectParameters>
<asp:FormParameter FormField="grSelectPlan" Name="PlanID2" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Is there a way for me to grab this datasource which works and is tied to a detailed view, take the value in the price collom and stick it in the MonthlyPrice varible on my vb page? That is the approach that seems the most logical to me.
If someone has better way I would really like to know.
Thanks
View 12 Replies
View Related
Dec 9, 2004
Is there a handy-dandy way for me to be able, from a stored procedure, to capture the notification email address for a user associated with a job?
I need to send an email out from a stored procedure, and I want it to be set up to send to the same user that is set up in the job that calls the stored procedure.
the same notification stored proc will be used in multiple jobs, but the jobs are already set up with a notification email to be sent to the defined users "on completion" (error or not). I want a separate email to be sent, but I want to use xp_Sendmail because I have to perform a select who's output I want to appear in the body of the email (and unless I am missing something, I have no control over the body of the standard "job completion" email).
I would prefer to NOT have to "hard code" the user email address in the stored proc that calls xp_sendmail, OR to put it in some user table in the database, when the user I want to send it to will always be the same one defined in at the JOB level in Enterprise Manager.
Any thoughts? Thanks in advance for your instantaneous and informative answer-packed responses! ;)
View 2 Replies
View Related
May 5, 2008
hello,
I'd like to know how I can grab the output of the following code and use it as variable:
declare @sql nvarchar(25)
set @sql = 'SELECT CURRENT_TIMESTAMP'
EXEC(@sql)
Thank you.
View 2 Replies
View Related
Apr 28, 2014
ColumnA
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 8769 datas, 9.20USD cash"/>
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 1000 datas, 14.40USD cash"/>
I have a list of data as above.How can i create another new column just to grab the data?
NewColumn
8769
1000
View 5 Replies
View Related
Jul 23, 2005
I have MSDE running on my system and I want to get a list of databasesfor that server programmatically. I'm using vb.net and I want to view,add, delete and modify databases on a server from within a class.I'm aware I can use the "Server Explorer" feature in Visual Studio, butthat isn't what I'm looking for. Any references or suggestions on whereto start are appreciated.TIARalf
View 7 Replies
View Related
Jan 6, 2014
I have the following table with two columns:
SEQ_NUM STATUS_CODE
101 D12
102 DWI - REGULAR DWW - EXPIRED
103 WRW - REG WRT - FUTURE
I would like to output the following:
SEQ_NUM STATUS_CODE
101 D12
102 DWI
102 DWW
103 WRW
103 WRT
The Status code column in the source table will have a single 3 DIGIT alphanumeric code, and sometimes a longer string. I need a code that will automatically grab the 3 digit code before the dash.
View 8 Replies
View Related
Jul 21, 2014
I want to grab some key value pairs from the text in sql column
e.g.
some text[Key1=Val1]some text[Key2=Val2][Key3=Val3]some text
I want a function which takes Keyname as input & returns the value related with it if found.
GetValueFmKey('Key1') should return Val1 and like on.
View 1 Replies
View Related
Sep 21, 2007
I have a problem....SOMEONE PLEASE HELP!
Here is the setup.
Text Box: User enters in customer transaction number
Button: User clicks button to display information about the customer
Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far.
Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click
Try
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.DataSet11 = New links.DataSet1
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=<14852>;packet size=4096;user id=<userID>;password=<Strong Password>;data source=ZRTPD0WB;p" & _
"ersist security info=False;initial catalog=DTR"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT DocumentId, PartnerId, PartnerName, Direction, TranSet FROM DTR_Document_S" & _
"ummary "
'WHERE (DTR_Document_Summary.PartnerId = 'txtPartnerId.text.toString')"
'THE STATEMENT ABOVE DOESNT WORK
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
'Open the connection
SqlConnection1.Open()
TextBox1.Text = "Connection Open"
'Populate DataSet11
SqlDataAdapter1.Fill(DataSet11)
TextBox1.Text = "DataSet11 has been filled!"
'Display Data
DataGrid1.DataBind()
TextBox1.Text = "Here is your requested information"
Catch ex As Exception
TextBox1.Text = ex.Message
End Try
'Close the connection
SqlConnection1.Close()
End Sub
View 6 Replies
View Related
Jul 20, 2005
I need just the names of tables, views and sprocs within a SQL Serverdatabase. What's the easiest way to do this?
View 3 Replies
View Related
Jun 28, 2001
We have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...
Is my assumption correct or do I need to look at something else. Thanks!
Billy
View 1 Replies
View Related
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Oct 31, 2005
SELECT sysjobs.name, sysjobservers.last_run_outcome, CONVERT(varchar(8), sysjobservers.last_run_date, 112) AS LastRunDate, CONVERT(varchar(8),
sysjobservers.last_run_time, 8) AS LastRunTime, GETDATE() AS CurrentDateTime
FROM sysjobs LEFT OUTER JOIN
sysjobservers ON sysjobs.job_id = sysjobservers.job_id
WHERE (sysjobservers.last_run_outcome = 0) AND (CONVERT(varchar(8), sysjobservers.last_run_date, 112) >= DATEADD(day, - 1, GETDATE()))
I am wanting a similar solution, I need to convert an "int" to a time format, when I use this where ">= DATEADD(minute, - 720, GETDATE())" for the last_run_time I get this error:
Syntax error converting datetime from character string.
View 3 Replies
View Related
Jul 1, 2004
I ran the following:
ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0
On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.
Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID
Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?
View 13 Replies
View Related
May 15, 2008
My database is current 135GB. I have been running the following:
DBCC SHRINKFILE([database_data],2)
DBCC SHRINKDATABASE ( [Database],20,TRUNCATEONLY)
It's been running for 28 hours. Should it be taking this long? If I stop the shrink, will it need to rollback?
Thanks!
View 2 Replies
View Related
Feb 29, 2012
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
View 6 Replies
View Related
Mar 17, 2004
I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
View 14 Replies
View Related
Apr 21, 2015
I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
HoursBill_rate_1 Hours_2Bill_rate_2
34.2610.26 0.000.00
5.7410.26 5.689.67
I want to report to the business:
40hrs 10.26
5.68 9.67
The query I have written is below:
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3
From Valid_Timesheets
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and
((Bill_Rate_1 = £10.26 or
Bill_Rate_1 = £9.67 or
Bill_Rate_1 = £8.27 or
[Code] ....
View 4 Replies
View Related
Jul 23, 2005
Hi,I have the following query:SELECT p.employee_code, p.employee_name,CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,CONVERT(VARCHAR(12),t.post_date,101) AS PostDate, SUM(tobill_hrs) ASTotalHoursFROM tat_time t, hbm_persnl pWHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '2005-03-01'AND '2005-03-31' AND p.employee_code IN ('0340')GROUP BY p.employee_code, p.employee_name, t.tran_date, t.post_dateORDER BY p.employee_codeI would like to sum the tobill_hrs by week (Monday-Sunday) instead ofby day. For instance the user will put in a date range (a from dateand to date).Is this possible to do?Thanks!
View 9 Replies
View Related
Apr 18, 2006
I am creating a report that has call duration and if i run it over amulti-day span the duration is limited to 24 hours and starts over atzero. is there a way to not limit hours?I am usign the following in my select statement:convert(varchar(12), dateadd(s,SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS[Outgoing Call Duration]
View 3 Replies
View Related
Dec 14, 2007
Is there a function in SQL Server 2005 that can add GMT deviation hours to the existing date. I have a data field with the GMT_Deviation_Hours listed in numeric format e.g. -1, 12 etc. I have another date field to whom i wish to deviate the datetime according to the GMT_Deviation hours.
Thanks
View 2 Replies
View Related
Sep 28, 2007
Hi, i have this problem :
I have to compare 1 hour between two days , example:
I have this datetime value (BitacoraProcesoUsuario.FechaInicioProceso) in 108 format : 23:35:22
and I make this query:
Select TOP 100 * from BitacoraProcesoUsuario
INNER JOIN Turno
ON convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) > = '24:00:00'
AND convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) <= '06:29:00'
and returns nothing........ this because 23:35:22 is gtreater than 06:29:00' value, my question is how can I compare this hours in this format.
Regards
View 14 Replies
View Related
