Math Is Driving Me Nuts
Jul 20, 2005
The business rule is, the sales manager is commissioned on the avg. number
of appointments set up per salesrep per day during the month.
I have 2 tables: The UserLog table records only 1 entry per day per user
(salesrep). This will log how many salesreps worked a particular day. The
second table logs any appointments set up.
UserLog: ID, UserName, EnteredTime
Appointment: ApptID, EnteredTime, ApptDate
I figured that, for a given date ranged, I could
1. sum the number of appointments
2. sum the number of days worked
2. sum the salesreps / number of days = avg number of salesreps per day
3. number of appointments / avg number of salesreps per day = avg number
of appointments per sales rep
But this logic is flawed. If I average out every day and then take an
average of this daily average, I get a different result. Any ideas on how
to best solve this problem?
Thanks.
View 7 Replies
ADVERTISEMENT
Apr 2, 2004
I have two different SQL server installations (7 and 2000) that I have configured for using the SP_processmail procedure. Both servers can properly execute xp_startmail, xp_stopmail, xp_readmail, and xp_findnextmsg. HOWEVER, both have different problems with SP_processmail.
Using this command (and a host of many other variations):
sp_processmail @filetype = 'CSV', @separator = ',', @dbuse = 'ryantest', @set_user = 'guest'
SQL 7.0 says: Server: Msg 18007, Level 16, State 1, Line 0
Supplied datatype for @set_user is not allowed, expecting 'varchar'
I have tried every variation possible, including not specifying the yser which should result in it defaulting to the "guest" account. The message is always the same.
SQL 2000 says: Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80004005
Queries processed: 0.
I have checked all my configurations and read every KB article - I can't find anything wrong.
Can someone PLEASE respond if you have any experience with SP_PROCESSMAIL on your servers.
Thanks in advance.
Ryan Hunt
View 1 Replies
View Related
Jul 20, 2005
Hi -I hope some one can help me with this.I am using sql server 2000[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 200316:08:15 Copyright (c) 1988-2003 Microsoft Corporation EnterpriseEdition on Windows NT 5.0 (Build 2195: Service Pack 4)]I have a stored procedure that is using cursors and a few joins, andwrites to a few tables. (I can post the code if that will help) Thestored procedure takes approximately 27 seconds to complete whenexecuted inside query analyser. However, if I run the stored proceduresource directly inside query analyser (like a long sql script), ittakes only 3 seconds!! These results are consistent and reproducible.I would think a stored procedure stores the plan, and I would expectbetter optimization. Why am I witnessing the opposite behaviour? Anyone has any experience?The server is manned by DBAs (I work at a large corporation), so Ibelieve it is well configured. We have noticed similar behaviour ondata restores on a different physical server.Thanks in advance,-praty77
View 6 Replies
View Related
Oct 17, 2007
I have an IIF statement in a text box. Its very basic
What I want is to check if the value of something is greater than 1. If it is, then make it 1. If it isn't then just use what the value is.
(loose example)
=IIF((Field!Var1.Value * Field!Var2.Value)>1, 1,Nothing)
Basically I can't have a pecentage greater than 100. If it is then make it 100. If its not then just use the result of the calculation.
View 7 Replies
View Related
Oct 16, 2007
Hello,
I'm currently evaluating Visual Studio Team System using SQL Server 2005 (9.00.3042.00, SP2, Enterprise Evaluation Edition) and from time to time, I get this error:
Error: 18456, Severity: 14, State: 16.
If I try to connect to the database using SQL Server Management Studio, I get the error:
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)".
If I restart the SQL Server services, everything works fine! This is driving me nuts!
Additional information:
- The user accounts and security are OK - they have been double checked, and once the services are restarted everything works fine.
- SQL Server is configured to accept remote connections - again, once the services are restarted eveything works fine.
- I have disabled meanwhile the following protocols: VIA, Named Pipes, Shared Memory both at the Server and Client - and the problem still remains.
Any help?
Thanks!
João
View 2 Replies
View Related
Jan 12, 2004
Im going completly nuts trying to do this. In ASP it would have taken me seconds.
Ok I think this is right but correct me if im wrong
I have a class that for example has a function that gets all the states from the DB:
File called Class.vb
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Configuration
Public Class Getalldata
Public Function GetPicdata() As SqlDataReader
Dim cnn As New SqlConnection(ConfigurationSettings.AppSettings("CONNECTION_STRING_USER"))
Dim cmd As New SqlCommand("Get_states", cnn)
cnn.Open()
Dim result As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
cnn.Close()
End Function
End Class
Ok How in the hell do I call this just to LOOP and print them out????
Sort of like this
Call class/object/whatever. I dont know how
Then
While Whaever
Loops thru the states response.writting them
Loop
Please hel;p before I completly go nuts
View 1 Replies
View Related
Aug 30, 2004
Alright- I plugged this code in to tell my form to write to SQLServer when I click the Submit button. However I error out when I load the form:
dim Conn as new OleDbConnection("DSN=MyDB")
Sub On_Click(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String
strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'")
ExecuteStatement(strSQL)
end sub
function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, Conn)
try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as Exception
end try
objCmd.Connection.Close()
end function
I get this error:
C:InetpubwwwrootSOSCommCommWeb4.aspx(14) : error BC30471: Expression is not an array or a method, and cannot have an argument list.
strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:InetpubwwwrootSOSCommCommWeb4.aspx(14) : error BC30205: End of statement expected.
strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~
C:InetpubwwwrootSOSCommCommWeb4.aspx(15) : error BC30035: Syntax error.
"'" & params(0) & "'," & _
Any ideas??
View 5 Replies
View Related
Aug 1, 2006
I been all over this forum trying to find out how to get the aspnetdb.mdf running on my remote site and its driving me nuts.
i been using VWD 2005 express Edition and SQL Express
Error Server Error in '/' Application.
An attempt to attach an auto-named database for file I:DataWebqsh.eumarker_93618920-db3b-4a04-a274-3fb613afc5dfwwwApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
over the last week it has been a nightmare trying to get anything working on the site but i have got all the other databases working but i can get the aspnetdb.mdf working. i am very new to ASP.net so please use the he kiss method "Keep It Simple, Stupid!" as im a newbe to this and it will just go over my head.
Thanks
View 3 Replies
View Related
Apr 17, 2006
Hi all, quick, hopefully simple question that has been driving me nuts for 3 days. Using Visual Studio 2005, doing a web app, connecting to an SQL server with already existing login names and passwords...
I have a login name and password text box and a login button. Upon clicking, sqldatasource1 checks to see if the items in the text box match a login and password in the DB. If so, it sets Profile.login and Profile.password to the values in that text box so I can use it to key off the DB for application IDs associated with that login/pass later.
It displays just the login name in a details view upon a match. If nothing is there that matches, it shows a hidden label saying "login failed".
My problem is, that I really don't want the detailsview to display if there is a match. I want it to automatically go to page2.aspx...but I can't figure this out. Response.Redirect("page2.aspx"); doesn't work. It's like the check is happening before detailsview1 shows any data and therefore it doesn't load page2....and I can't figure out how to get it to check detailsview1 for data AFTER it has been displayed.
I guess that I don't know how to get this thing to check if there is something in the detailsview, to go to page 2. It's just not working. Any help would be great! (BTW, not using the builtin login control because I can't figure out how to get that to connect to our sql database's login table.)
Thanks all!
View 7 Replies
View Related
May 27, 2006
I'm struggling with the different methodologies for using DataSets in 2.0. I can knock this out in no time flat in 1.0, but am bogged down in 2.0. Can anyone guide me on the right track? My code is below. I'm forming a DataSet from a SQL Server Express database from the user's choice from a drop down. My connection string "ASOCTCOConnectionString" is declared in my web.config and used successfully throughout in the WYSIWYG configurations of my databound controls. But I want to control this in code. This si getting frustrating and right now I'm longing for the clean simplicity I had with Web Matrix :(
In any case, any suggestions? It crashes with this error when opening the connection with MyConn.Open(). I just want to be able to configure a DataSet and apply it to my controls and results.
<code>
Dim connectionString As String = System.Configuration.ConfigurationManager.AppSettings("ASOCTCOConnectionString")
Dim MyConn As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim MyQuery, SearchString As String
Dim myAdapter As New System.Data.SqlClient.SqlDataAdapter
Dim myDataSet As New System.Data.DataSet
Dim mySelectCommand As New System.Data.SqlClient.SqlCommand
mySelectCommand.CommandText = "SELECT * from Device_Type where Category LIKE " & DDLDevType.SelectedItem.Text
mySelectCommand.Connection = MyConn
MyConn.Open()
Grid1.DataSource = myDataSet
Grid1.Databind()
MyConn.Close()
</code>
View 11 Replies
View Related
Jan 11, 2002
Has anyone else noticed that if you create a non-existing file as an attachment using xp_sendmail in SQL Server 2000, it does not create a copy of that file on the Hard Drive, nor does it format the attached file sensibly if you attach it as a .csv file ?
I am using the procedure below :-
CREATE PROCEDURE mailtest AS
declare @sql varchar (255)
SELECT PERSONID, FORENAME, SURNAME INTO ##TEMP FROM PERSON
SELECT @sql='SELECT * FROM ##TEMP'
IF @@ROWCOUNT > 0
begin
exec master.dbo.xp_sendmail
@recipients = 'itsmarkdavies@hotmail.com',
@Message = 'Test file',
@Query = @sql,
@attachments = 'C:MARKTEST.CSV',
@Attach_Results = 'True',
@Message = '',
@Subject = 'test',
@No_Header = 'True',
@Width = 500,
@Separator = ','
end
DROP TABLE ##TEMP
GO
In the example above, the file MARKTEST.CSV does not currently exist, but the procedure should create it, put it in the root of C: and e:mail it, as it did when it ran under SQL Server 6.5. However, under 2000 it now doesn`t put a copy on the Hard Drive and it formats the .csv file in a very odd manner.
View 1 Replies
View Related
Jan 13, 2007
We have a tree structure containing section names. Each node is a
section name and each section can have subsections. I have to copy the
tree structure but need to maintain the parent-child relationship
established within the id / parent_id fields. How do i acheive this?For example i have the treeSection 1 |-Section 1.1Section 2 |-Section 2.1The
"Section" table contains 3 fields: id, parent_id, and caption. ID is
the identity of the section record and parent_id contains NULL or the
ID of this record's parent to create a child. So "Section 1" (id=1,
parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1"
(id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).I would
like to copy this sucture to create 4 new sections but they need to
maintain their id/parent_id relationships BUT with new IDs. For this i
created the following stored procedure:----------------CREATE PROCEDURE [dbo].[CopySection] AS -- Declare a temporary variable table for storing the sections DECLARE @tblSection TABLE ( id int, parent_id int, caption varchar(max), ) DECLARE @newAgendaID int, @newSectionID int; DECLARE @tid int, @tparent_id int, @tcaption varchar(max);BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Copy the desired sections into the local temp variable table INSERT INTO @tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id; -- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID DECLARE c1 CURSOR FOR SELECT * FROM @tblSection ORDER BY parent_id FOR UPDATE OF parent_id; OPEN c1; FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption; WHILE @@FETCH_STATUS = 0 BEGIN -- Insert the new Section and record the identity INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@tparent_id, @tcaption); SET @newSectionID = SCOPE_IDENTITY(); -- Update the temp variable table with the new identity from the newly created real section in tblSection -- Update all temp variable records to point to the new parent_id UPDATE @tblSection SET parent_id = @newSectionID WHERE parent_id = @tid; FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption; END CLOSE c1 DEALLOCATE c1 END----------------The
critical "UPDATE @tblSection" part doesnt seem to update the temp
variable table with the @newSectionID (the actual section identity
obtained after inserting a real record into the tblSection table). So
in the end the inserted records into tblSection still point to the
incorrect parent_id instead of the copied record's parent_id.Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset? I've tried using both table variables and temp tables but no luck.
View 2 Replies
View Related
Sep 8, 2004
I have a DTS scheduled to run daily. It has run OK for the past few months, but now always fails, the following error appears:
dtsrun.exe - application error
the instruction at 'xxxxxxx' referenced memory at 'yyyyyyy'. The memory could not be 'read'. click on ok to terminate the program.
The dts itself runs fine if started manually, but always fails as a scheduled job, even if its started manually.
Help!!!!!
View 2 Replies
View Related
Nov 29, 2007
Hi Guys, I have a syntax error in my SQL SELECT Query that is driving me crazy. All I am doing is adding a conditional with a literal value and that is breaking the query.This Query Works: SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
This One is Broken:SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
ON ORDER_TANK_LINK.OrderID = 1
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
Obviously I haven't explained the design of the tables, but because of the fact that I have another query that is so similar working perfectly, I didn't think you'd need it.
The exact error is "Incorrect Syntax Near the Keyword 'ON'". OrderID is definately a value of the ORDER_TANK_LINK Table and is of type int.
Thanks in advance!
View 4 Replies
View Related
Apr 16, 2008
I'm trying to do a basic update query which is working on other pages but not on this page. Dim uid As Integer = CInt(Session("uid"))
Dim cmd As New SqlCommand("UPDATE [cvdata] SET [jobCompanyName] = @inputJobCompanyName WHERE [user_id] = @uid", strConn)
With cmd.Parameters
cmd.Parameters.AddWithValue("@inputJobCompanyName", inputJobCompanyName.Text)
cmd.Parameters.AddWithValue("@uid", uid)
End With
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
The funny thing is that if i remove inputJobCompanyName.Text and add a custom value (for example "test") it works.So it doesn't seem to read my updated textfield or something im clueless.Kind regards,
Mark
View 2 Replies
View Related
Mar 28, 2006
ok here's the deal...
I've got 2 identical DB's on the same server, one production, one test. No we don't have a test env, but at least I'm not testing on a prod DB (some people here do, trust me).
I've got a prod VB6 app that used SQL sp's. I've pointed the ADO connection string to the test DB while I make the changes I need to make, and I'll obviously change it back before I'm done etc.
I made some VERY minor changes to one of the sp's (added a variable, changed some stuff), tested thoroughly in query analyzer (runs with no errors in <1 sec), altered my ADO command accordingly and when I executed I get this:
run time error '-2147217871 (80040e31):
[Microsoft][ODBC SQL Server Driver] timeout expired
The connection to the server is fine, I've tested that - it trips on the execution of the command:
Set rstCalls = conHelpArchiveConnection.Execute(SQLQ)
so here's my question: The prod version of this app works like a charm, and the test app times out. I'd rather not toy with the connection timeout setting on the server. Any thoughts on what could be causing this?
Any help would be appreciated, I'm ready to throw in the towel. Well at least until tomorrow morning ;)
View 7 Replies
View Related
Dec 6, 2000
Where does sql server store the server names that show up in the drop down list for choosing a source or destination server?
View 3 Replies
View Related
Jun 10, 2007
Our application drives SSIS packages from ASP.NET web services. To alleviate some of the package load time overhead application caches SSIS Application object and several instances of "pre-loaded" packages in ASP.NET Application context. As needed the code uses cached SSIS Application instance to execute "pre-loaded" packages. Is this thread-safe?
View 10 Replies
View Related
Feb 2, 2006
Is it just me, or are features missing in Management Studio? I mean, where is the "Export Objects" option when exporting data? Why is it so difficult to register a remote server? Why do the object views suck? And most important, why is the documentation the worst software documentation I have EVER SEEN?!
Things that were easy in Enterprise Manager with Client Network Utility and Query Analyzer seem impossible, difficult, or maybe just hidden in Management Studio. If it is there, the documentation sure doesnt tell you how to find it.
Please someone tell me that it's just me. Tell me that the features are there but Im not doing something right. I feel like putting my head in between my door opening and banging the door against it.
View 2 Replies
View Related
Jul 30, 2007
Assuming I should be using values from temp inserted to insure correct record...
Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form
AFTER INSERT
AS
SET NOCOUNT ON
-- Checkbox Driven:
IF a_form.missingCheckbox = -1 THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven:
IF a_form.incorrectTxtbox <> 'na' THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on
Create Table a_form (
form_ID int Not Null,
parent_ID int,
missingCheckbox bit,
missingNote varchar(100),
incorrectTxtbox varchar(50),
replaceWithTxtbox varchar(50)
)
--Target table Trigger inserts into
Create Table b_items (
items_ID int Not Null,
form_ID int Not Null,
parent_ID int,
ItemTitle varchar(150)
)
View 5 Replies
View Related
Dec 13, 2006
in the below sql why is year(classdate) " + " a " + " MONTH(classdate) a math command giving me 2006 - 12 = 167
and not "2006/12" as text? please help me
cmdGetCat = New SqlDataAdapter("SELECT DISTINCT year(classdate) " + " a " + " MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ", conNorthwind)
View 4 Replies
View Related
Nov 18, 2007
How do i update a sql table so that the result cannot be less than zero?for example, lets say I have the column "Number"I have a sql update statement that subtracts 1 from number:"Update oTable SET Number = (Number - 1)"Except that Number cannot be less than zero. Is there a way to do this in sql statement so that I don't have to have a select statement just to check that Number is greater than zero to begin with? Thanks
View 4 Replies
View Related
Oct 26, 2004
Hello everyone:
I have a database for one of my websites, a picture rating site. Anyways, right now there are quicte a few tables, and I was wondering how to give the server a break and was wondering if this was possible:
Basicly I have a members table, and a votes table. Members will rate other users pcitures on a scale of one to ten, then the votes will be inserted into the votes table. The only problem with this is that calcuating all the votes a user has can put a straing on the server. I was wondering if it would be possible to create a math column in the members table that would automaticly figure out the users average and having it stored in a field in the members table, so all I would have to do is query the members average located in the mebers table, rather than tallying all the votes in the votes table for each member.
Hope this makes sense, a tutorial or any suggestions would be great!
Thanks
View 1 Replies
View Related
Feb 2, 2004
I have 2 questions.
1) Lets say we have a table
CREATE TABLE TEST
(
N INT
)
These table have 10 records - the numbers from 1 to 10.
I need 1 query ONLY which will update the table and make it with 100 records - the numbers from 1 to 100.
2) How with 1 query ONLY i have select only the prime numbers
View 4 Replies
View Related
Jan 16, 2006
I have some dates stored in a field named "visit"
In a select they show as the format:
2005-07-28 10:45:00.000
So I need to write a query that will select Visits that are more than 90 days old. I thought it might be something simple like:
Select * from patientVisit where ((getdate())-Visit>90)
But that is pulling all visits not just 90 day old ones.
How do I pull the current date/time and compute the cutoff date time that would be 90 days prior for my selection Where clause?
Thanks
View 3 Replies
View Related
Jun 30, 2006
I need to create a new column where I subtract one column by another.
I've been looking fo that for a while now, can someone explain how to do this?
View 7 Replies
View Related
Mar 31, 2006
Hi,How to find the list of SQL math functions in SQL Server 2005?ThanksKai
View 2 Replies
View Related
Nov 23, 2006
select convert(float,'1.2334e+006')1233400.0select convert(decimal(20,2),'1.2334e+006')Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric.can I set some options arithabort etc to have a workaround to thisproblem?Thanks.
View 3 Replies
View Related
Dec 1, 2007
I'm not a professional dba or dbd, but I'm proficient in the basics ofdatabase design and sql. I want to create a database of mathdefinitions, and I'm wondering how one would go about creating adatabase that contains mathematical notation (and I'm not just talkingabout basic symbols where I could get away with ascii code). I needto be able to insert a wide variety of mathematical expressions, fromfractions to integrals, into fields (just like you can enter in-linemath symbols in MS Word using equation editor). I have no clue how togo about this. Is it a matter of developing certain programmingskills/languages? Would such a capabliltiy be proprietary (dbms-specific)? Is it possible at all? Any help would be appreciated.Thank you.
View 3 Replies
View Related
Jul 20, 2005
Hi AllI'm trying to find a math function (if it exists) in SQL Server. If itdoesnt exist, then maybe someone can tell me what its called so I cando a bit more reading on itBasically I want to do this:Parameter Components1 12 23 1, 24 45 1, 46 2, 47 3, 48 89 1, 8and so onI'd like to be able to call a function and it would return true orfalse like sofunctionname(1, 9) = trueso 1 is a component of 9functionname(2, 9) = falseso 2 is not a component of 9functionname(4, 5) = trueso 4 is a component of 5If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,I'd appreciate it!Thanks in advanceSam
View 2 Replies
View Related
Jan 9, 2008
Below is my table layout, query, and results. I need to perform some date math to display how much time is elapsed between locations as patients are checked into each care_unit. I believe I could use the unique careunit_key but am not sure how to go about this.
[srm].[CDMAB_CAREUNITS]
[CAREUNIT_KEY] [decimal](37, 0) NOT NULL,
[EPISODE_KEY] [decimal](38, 0) NULL,
[CARE_UNIT] [char](20) NULL,
[ROOM_NO] [char](5) NULL,
[BED_NO] [char](5) NULL,
[DATE_IN] [datetime] NULL,
[TIME_IN] [char](6) NULL
select
cu.careunit_key,
cu.care_unit,
convert(nvarchar,e.ADMISSION_DATE, 101) as Admit_Date,
convert(nvarchar,e.EPISODE_DATE, 101) as Discharge_Date,
convert(nvarchar,cu.date_in,101) as date_in,
substring(cu.time_in, 1,5) as time_in,
convert(nvarchar,cu.date_in,101) +
convert(datetime,substring(cu.time_in, 1,5)) as date_time_in
FROM srm.episodes e
inner join srm.CDMAB_CAREUNITS cu on cu.episode_key = e.episode_key
inner join srm.item_header ih on ih.item_key = e.episode_key
inner join srm.patients p on p.patient_key = ih.logical_parent_key
where e.account_number = '11111777777'
CAREUNIT_KEY
CARE_UNIT
ADMIT_DATE
DISCHARGE_DATE
DATE_IN
TIME_IN
DATE_TIME_IN
1488831
3
12/10/2007
12/11/2007
12/10/2007
3:00
12/10/07 3:00
1498258
S4C
12/10/2007
12/11/2007
12/10/2007
5:54
12/10/07 5:54
1498406
SREC
12/10/2007
12/11/2007
12/10/2007
11:05
12/10/07 11:05
1498472
S6W
12/10/2007
12/11/2007
12/10/2007
12:37
12/10/07 12:37
View 3 Replies
View Related
Apr 19, 2007
Good day all
Does anyone know if there is such a quary that can be written which would add up(or any math functions) a line of cells (on different rows) similar to that of working with a excel document?
If so please steer me towards the correct syntax for this.
Regards
Rob
View 1 Replies
View Related
Mar 12, 2008
Hi,
I have a column coming from DB2. It is the time is stored as 6 decimals of a second. So the value I got coming in is 44846(DT_I8), which needs to be divided by 60 to get minutes (747 minutes remainder 26 seconds). Then divide 747 by 60 to get 12 hours remainder 27 minutes. Thus the time is 12:27:26.
I have got a dervived column doing (DT_R8)(SUBSTRING((DT_WSTR,8)PSCCLOGTIM,1,5)) / 60 but the answer I am getting out is 747.433333. Close, but not close enough. Now I assume my problem has something to do with the Math being done on actual numbers not Time based numbers.
Anyone got any ideas on where I am going wrong and what the expression should look like?
View 11 Replies
View Related