My goal is to take the btb-date and store it in the same table I loaded the seperate nodes to. Currently I am loading the req-header, req-property-address, and req-borrowers.
This date will be static in that it will remain the same for every record. My goal is to read it in and store it along with each record. Hope someone can give me some help.
hi my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.
i want to read the file in my root folder excluding the bin path
I have a SQL DB with a column called time_occurred that is formatted like ( 7/28/2004 7:10:30 AM ).
What I need to do is run a report based on just the month day and year portion. I am using the calendar control so mins, sec and milliseconds are not available nor do I really need them.
I am running the Query with this Stored Procedure
ALTER PROCEDURE dbo.Prodecure1 ( @WhereClause varchar(8000) )
AS
-- Create a variable @SQLStatement DECLARE @SQLStatement varchar(8000)
-- Enter the dynamic SQL statement into the variable @SQLStatement SELECT @SQLStatement = 'SELECT column1, column2, etc...' + @WhereClause
I'm currently using the SQL to find records older than todays date in the SSD_SED field. I'm having to update the date manually each day. Is there a way I can automate this?
I have a requirement to be able to select and group records by the date portion of a datetime field. ie ignore the time when grouping so that all records lodged on a particular day are seen together.
I have been able to do this by
- converting the datetime data to the number of days since a given date - inserting this into a temporary table - retrieving the the data from the temporary table - convert the data back to a date using DATEPART to display dd/mm/yy
This then gives me the data grouped as required but seems to be a very difficult solution - Is there an easier way??
I need to identify time spans where members identified as having a condition have NOT had any of 5 specified services in the past 12 months. I have a table (DiabStrata) that identifies time frames for which my data shows a member as having the condition, and I have 5 separate tables with the dates of the relevant services.
I can easily identify when a member hasn't had the service at all, or is lacking it at the start or end of the time frame for which they have the condition, but I'm hitting a wall on how to deal with gaps between the minimum and maximum identification dates.
insert DiabStrata( select '1',1,'20060101','20070302' union select '1',1,'20070803','20080804' union select '2',1,'20020101','20080503')
insert hba1c(
select '1','20060301'
union
select '1','20070301'
union
select '2','20050101')
--Missed Service Begin select * into #eval from DiabStrata where strat=1
delete #eval from #eval left join hba1c on #eval.memberid=hba1c.memberid where hba1c.memberid is null --repeat for other indicators
update e set stratstart=min(dos) from #eval e join hba1c on e.memberid=hba1c.memberid having min(dos)>stratstart
update e set stratend=max(dos)+365 from #eval e join hba1c on e.memberid=hba1c.memberid having max(dos)+365<stratend
delete from #eval where stratstart>stratend --repeat for other indicators Desired output is into DiabStrata with a strat of 2 for the time frame for which they have strat 1 but do not have all 5 services within the prior 365 days. MID Strat StartStrat EndStrat 1 2 1/1/06 - 2/28/06 1 2 3/2/08 - 8/4/08 2 2 1/1/02 - 12/31/04 2 2 1/2/06 - 5/3/08
I'm trying to translate this portion of VFP code into LINQ query:
select COUNT(ID) as conflicts from dbo.max4sale where <<thisform.cWhere>> AND Start_Time >= <<VFP2SQL(m.ltBegin + m.lnStartTime)>> and Start_time <= <<VFP2SQL(m.ltEnd)>> AND CONVERT(varchar(5),Start_Time,108) <= <<VFP2SQL(m.lcEndTime)>> AND CONVERT(varchar(5),End_Time,108) >= <<VFP2SQL(m.lcStartTime)>> <<m.lcDays>>
Here is my non-working attempt:
var startTime = new DateTime(1900, 1, 1, beginDateTime.Hour, beginDateTime.Minute, 0); var endTime = new DateTime(1900, 1, 1, endDateTime.Hour, endDateTime.Minute, 0); var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList(); if (daysOfWeek.Count() < 7) // not all days of the week selected
[Code] .......
First of all, I see a bug in my logic now as the first part of the query I need to do all the time and only the second part if the count < 7. But that's not my problem - I can not figure out how to make times comparison only using LINQ. Ideally I think I'd like to have cast(start_time as time) >= @p1 as a result to be executed by LINQ.
BTW, I am only getting the error in run-time that Parse can not be interpreted. So, I need to figure out another way of making LINQ recognize my intent of checking time portion of the date only.
I have a task for which I have to load csv file from a shared directory into sql table. Right now I'm stuck with a road blocker, The issue is the shared drive contains all the history files as well and I have to pick only the latest file. But I cannot identify latest file based on the file name because it doesn't contain any date in the file name. However by seeing file properties I can pull latest file.
Sample file name: XXX_XXX_XXX_XXX_XXX-5814201.csv
Is there any way we can automate this in SSIS with file properties and picking the latest one?
Code: SELECT P.ID, P.QTY, T_DATE FROM PARTS P INNER JOIN INVENTORY I ON P.ID = I.PART_ID WHERE P.QTY > 0 AND I.CODE = 'R' GROUP BY P.ID, P.QTY, I.T_DATE HAVING MAX(T_DATE) !> DATEADD(MONTH,-12,GETDATE())
[Code] ....
I am trying to make the sql only pull records where the highest date stored is older than a year ago.
So, based on today's date 12/21/2012:
if the highest (max) T_DATE is 3/12/2012 => don't pull that record if the highest (max) T_DATE is 11/30/2011 => pull that record into the recordset
the statement is off, it is pulling records that should not be included.
Ok... Here is what I want to to: I have a number of tables, but this query concerns only two of them: Employee, Disposition, with a one to many relationship on the phone number.. Employee has personal info, and dispostion is a large table that keeps track of transactions. There is a unque_id for every transaction.
I want to pull some information. A date is assciated with each trans action on the disposition table. I want to be able to pull up all the unique employee phone numbers from a disposition table but only the latest date they made a transaction. My result should be only one phone nbr, one transaction number, and the date of the last transaction, per employee phne_nbr. But I can't seem to be able to pull this.
This query gets me the max date called per phne_nbr:
select 'Max'= max(dte_call), phne_nbr into TestTable from Disposition group by phne_nbr
The problem arises when I need to get the unique_id for that transaction, as I need it for other queries...The code below does not work...I get duplicate records and so forth.
select 'Max'= max(dte_call), phne_nbr, unique_id into TestTable from Disposition group by phne_nbr, unique_id
This shouldn't be that hard!! (((I tried a variation usin select distinct with the filed I wanted to be distinct in () otherfield, other field... but the distinct is not limited to the field in (), it gets the distinct record for the entire select ))) I must be missing something simple here. Can anyone help!!!
I am currently working in the sql server 2000 environment and I want towrite a function to pull all dates within a given date range. I havecreated several diferent ways to do this but I am unsatisfied withthem. Here is what I have so far:declare @Sdate as datetimedeclare @Edate as datetimeset @SDate = '07/01/2006'set @EDate = '12/31/2006'select dateadd(dd, count(*) - 1, @SDate)from [atable] vinner join [same table] v2 on v.id < v2.idgroup by v.idhaving count(*) < datediff(dd, @SDate, @EDate)+ 2order by count(*)this works just fine but it is dependent on the size of the table youpull from, and is really more or less a hack job. Can anyone help mewith this?thanks in advance
We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root
iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.
I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.
Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)
The end result of my view on the above data should return:
Id FilePath 5 C:ProgramFilesExcel.EXE 6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file. Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?
Hi, Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor 1.1) Within File Connections Manager Editor; - Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC" and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; - Input Column as INTERCHANGE_NET_APP_DATE and Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
Logged on local Administrator on one of my sqlserver 2000 "ABC" and registered 5 sql groups and linked 50 sql2k servers from MSDE to Standare (all sp3). Now, I need a different login on this machine "ABC", how could I get back all the SQL Enterprise Manager Console Root Setting and connections like before?
Why does XML Source in MS VS 2005 Integration Services Data Flow Sources XML Source not pick up the root element?
The problem is the following example: <root createddate="2008-01-01"> <elements> ... </elements </root>
The XML Source picks up everything after the first element which is the root element but it does not seam to pick up the root element where there is a need for the createddate attribute.
Is there a soultion for this problem or a workaround?
What i need is to find a way to retrieve the value of the attribute belonging to the root element (value of @Date) in SSIS. Tried it using Xpath but don't know how i should configure the xml task editor or if maybe there's any other way to perform such a task.
I have a 'need an opinion' question. I've always installed the system databases (master, msdb, model, tempdb) on the root drive (c:) with the sql application. Then I was thinking, the only things that really should be on the root drive is the sql app itself (and any other required apps). So...my question is, do y'all install the master, etc on a separate drive other than the root, ie d: or e:? The reason I ask is that if the tempdb database has alot of activity, and the database resides on the root drive, could it affect sql performance?
Just wondering. I just started working in a 'virgin' sql environment (setting up sql servers from scratch), and want to have sql installed in the best possible way for performance.
Documentation that supports the placement of Tempdb files on the root of a drive, i.e T: instead of T: empdb. I am positive this is not a best practice, but when challenged could not find any documentation that would support that view.
hi, guys Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.
Hi everyone! I have a configuration file, I want to place it in somewhere of SSIS package to extract configuration information. But i dont know where is root directory. can you help me? Thanks you very much
Configuration: SQL Server 2005 Enterprise edition on Microsoft Cluster. Machines named Node1, Node2 and NodeV (Virtual Node). Best practices require SSRS be deployeed on an IIS6.0 server. Reporting services was installed on the Web1 machine and pointing back to the database on the NodeV platform. Everything appears to run fine in Reporting Services. However, when I use the ReportViewer control from within a webpage, the basic control appears along with the message "root element is missing". Using the same code, I point the ReportViewer control to a different server Web2 where Sql server standard, IIS and RS are installed and the report renders properly. What am I missing in the configuration of the Web1 box as far as IIS or SSRS are concerned?
