.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

Converting daily snapshots into format for time series analysis

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server
 

I wasn't sure if this was an app question or a database question, so I'll start here first ...

Desired End State:
I'd like a process/app/database/whatever that converts daily inventory snapshots into one or two tables for easy analysis over time. Ideally, I'd like the solution to be efficient, flexible, and resource-sensitive.

What is the best way to go about this?

Current State/Background:
I'm working with a vendor who sends me daily Excel sheets with assets under management. The reports are based a snapshot of master records from the inventory system. For discussion, let's say a sample looks like this:

AssetID, Serial#, Status
123456, ABC123, Deployed
365494, D2-F39, Retired
B63489, 123GR2, Pending

The inventory is mostly static. Of the 40K rows in the spreadsheet, only 20 or so change from day-to-day. Further, once an asset is retired, its record never changes again.

Because they're complete snapshots of the master data, the spreadsheets are massive (over 40K rows, currently around 40 MB each). This makes them awkward to work with.

I have two main business requirements:
1) From today's spreadsheet, I need an easy way to identify the 20 rows that changed yesterday.
2) For a specific asset, I need to trac


View Complete Post


More Related Resource Links

Converting Time formats

  

Hi guys,

So ive been converting an asp website to asp.net. What i did was run the migration wizard in the old VS2003, then when importing it bk into vs2008, i belive it upgraded it agin. I have been plodding along correcting basic erros and have come stuck with this one. Originallany the site has two input types, one for date and one for time. the new script is below with error im getting. I manged to get the date correct but the time is more tricky. Any suggestions would be appreciated.

 

 

Date = FormatdateTime(IIF(IsDBNull(MyRs1.Fields.Item("").Value), Nothing, MyRs1.Fields.Item("").Value), DateFormat.ShortDate)

Time = FormatdateTime(IIF(IsDBNull(MyRs1.Fields.Item("").Value), Nothing, MyRs1.Fields.Item("").Value), DateFormat.ShortTime)

 

Exception Details: System.InvalidCastException: Conversion from type 'Double' to type 'Date' is not valid.

Source Error:

Line 74: 
Line 75: 'TODO Need to correct the format 
Line 76:   Time= FormatDateTime(IIf(IsDBNull(MyRs1.Fields.Item("").Value), Nothing, MyR

Converting UTC to another Time... in XSLT SharePoint designer using ddwrt

  

I am not a programmer, nor do I know any coding languages.  I have a slightly above average understanding of how writing code works, so please keep that in mind when responding.... 

I am using a list in SharePoint; when the list is updated, I want to display "UPDATED w/ Date and Time".  I cannot figure out how to display the Local Afghanistan time vice UTC or Zulu time. I am trying to figure out what to add in to change the time displayed +4:30 hours.  I am currently using the following code in XSLT with SharePoint Designer:

<xsl:if test="ddwrt:FormatDate(string(@Modified), number(1033), 1)=ddwrt:FormatDate(ddwrt:TodayIso(), number(1033), 1)">

   <xsl:if test="ddwrt:FormatDate(string(@Modified), number(1033), 1)!=ddwrt:FormatDate(string(@Created), number(1033), 1)">

   <Span style="background-color:maroon;color:white">UPDATED <xsl:value-of disable-output-escaping="no" select="@Modified" /></span>

   </xsl:if>

(/xsl:if>


Video: Converting Word Documents to PDF Format Using SharePoint Server 2010 and Word Services

  
Watch this visual how-to video as you learn how to programmatically convert Word documents to PDF format on the server by using Word Automation Services with SharePoint Server 2010. (Length: 6:15)

Showing available time slot in table format

  

I'm currently developing a system for booking of discussion room in a college. This system will allow the staff to help the students to make booking in advanced as well as for instant walk in usage.

I've got a table to keep track of bookings, another for walk in and one for time slot.

For the time slot table, it includes start time slot and end time slot, which is 30 minutes interval for each.

What i'm currently facing is that the system could not display the correct available time slot. I think it's the formula problem which I still can't get it solved.

Example:

If the booking time is 8.30am - 9.00am, I have no problem showing the 1st slot as N/A.

But if the time is 8.45am - 9.15am, the system only update the 1st slot as N/A while the 2nd slot still remain as AVAILABLE which is wrong. Because since the time ends at 9.15, I would like the slot 9.00am - 9.30am to be N/A as well.

This would be my code:

for (int x = 0; x < countTimeSlot; x++)
{
     if (int.Parse(arrEndTimeSlot[x].ToString()) > intBookedStartTime && int.Parse(

Time series algorithm parametrs

  
Hello everybody! I have the following questions to You: I have mining model with data by working hours(every day from 08AM-05PM). What PEREODICITY_HINT I have to use? {24} or {9} The second problem is that on the chart I see historical data wiht axis format 08AM-05PM, but in prediction part the periods is 00-24. It is not correct! I want to see the same 08AM-05PM periods too!   Help me please!   Thank You!  

what's the Best practice about exploiting the time series predections history?

  
Hi all, We are predicting our revenue with two frequency : The first is monthly , the second is weekly with the same parameters daily . The first aimed to have a big picture of our performance during the month , the second is more operational and it's used to drive operational action. So every day we train the second structure with all data and  MTS predicts different value each day basis on the new data used in training. Example: Day of process : 05/07/2010 data used to train : 01/01/2002 ---->05/07/2010 values predicted between : 06/07/2010------>31/07/2010 the value for 18/07/2010 : 35000 $ ------------------------------------------------------------------- Day of process : 10/07/2010 data used to train : 01/01/2002 ---->10/07/2010 values predicted between : 11/07/2010------>07/08/2010 the value for 18/07/2010 : 42000 $   Certainly The second method (predict daily with new training data)  value will be more accruate but it can't be used to drive strategy because it's change every day. Any reflex about this ? saving all data (all prediction for all series every day for the 30 coming steps)? or update coming value with the actualised prediction(the manager will be confused )  

How do I format a date time field in a CQWP to YY-MM-DD ?

  
Hi. I have been trying to get the date-time to look good for some time now, but have failed. I have a Content Query web part that picks up article pages from a page library. The pages contains a field called 'article date'. I want to show just the 'Title' and the 'Article date'. Doing this default gives me: My title 10-09-03 00:00:00 I want it to be: My title 10-09-03 Is this possible? I'm starting to realize that I probably have to edit the xsl styles for this webpart but if thats the answer, how do I do that? ThanksThomas Balkeståhl - Technical Specialist - SharePoint

TIME displayed in 24 Clock Format

  
I have a time field that is displayed via Reporting Services 2005, I want the TIME value to be displayed in 24 clock format. For example, 02:22:00 should be 14:22:00 How is this possible?? Thanks in advance

using Custom Formatter when format is unknown at design time

  
I have class "CF" that implements IFormattable interface. I can convert it to string var cf = new CF(); string str = cf.ToString("MYFORMAT", CultureInfo.InvariantCulture) or str = String.Format("{0:MYFORMAT}", cf);   the problem is that "MYFORMAT" is a parameter. so i can still use string fmt = "FORMAT"; string str = cf.ToString(fmt, CultureInfo.InvariantCulture) Is it possible still use String.Format for formatting? That doesnt work str = String.Format("{0:{1}}", cf, format)      

Time Format

  
Hi,Im trying to format time to this 10:00 AM. I tried:Eval("doma","{0:d}")Eval("doma", "{0:HH:mm}") Eval("doma", "{HH:mm}")Eval("doma", "{0:HH:mm tt}")  But it tells me:Exception Details: System.FormatException: Input string was not in a correct format.I think the problem might be that I'm use TIME form in SQL 2008 and maybe its expecting a long date/time format, maybe? But in any case it still should format at time, right? 

Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

Advice on collecting data in a time series

  
Hi everyone,  I just wanted to get some ideas of what you would think be the best way to collect data/numbers that are part of a time series?  Let's say I'm collecting monthly data from the users, related to some product and I'd like to be able to provide them a simple and efficient way of entering these numbers based on some month end period.  So for instance on 6/30/2009, they could enter some numbers for a set of data points that pertain to that product.  Would one of the data controls (such as GridView or DetailsView) be sufficient to do this?  I know the GridView isn't so much able to save data but I believe the DetailsView has some functionality for that. In the end, I'd really like to provide a seemless way to do show this and ability to enter and save this.  Any ideas would be appreciated.Thanks

converting date format from (Wed 1 Sep) to (2010-09-1)

  
Hi All,  I have a data in the excel file and I need to import the data into sqlserver. I can do this bit by importing data into a temporary table and then from that temp table to import/insert into my required table. its works fine. The problem I have, in the excel file I have a one column showing date's (format: e.g. Wed 1 Sep) and I am having a difficulty to import that column. The format of the date is bit different than the normal date. It works fine with the normal/standard date format. In my file i have a date format is  Wed 1 Sep and I need to convert this format to 2010-09-01. Although, year is missing but I can add this manually at the time of convert or just about before to convert.  Any suggestion are welcome or if you know any good way to import data from excel to sqlserver please share.  thanks for your help. Regards, Athar

T-SQL query, average of daily time periods over a date range

  

Hi,

I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...

 

SELECT

count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

FROM
Calls
INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

SSRS 2005 Render Format Excel Issue with Time Field

  

I have a report that uses a datetime field but gets populated only with the time. So, in SQL db, it gets stored with a default date value of 1899-12-30 12:01:01 with the correct time filled in. Report in SSRS Preview mode displays the value just fine when I use the Time format in Layout mode. I build and deploy and able to view the report in a web browser with no problem displaying the time field. But when I subscribe to the report and choose Excel render format, when I open up the Excel file from the email that was sent, I get negative decimal values. I have tried every possible Time format in SSRS but I get the same result. I also tried just the default and still gives me negative decimal values that show up as ####### in Excel.

Any help will be greatly appreciated. Thanks


Optimizing Data Mining processing time for a Time Series model in SQL Server 2008 R2

  

Does anyone have any references around optimizing the initial processing time for a data mining model? Books, blogs, etc. I have the Wiley “Data Mining with SQL Server 2008” book, and while I’ve learned a lot from it, it doesn’t seem to cover much around trouble shooting things like processing time. I also have a few other books that have a chapter or two on data mining, but again just basic “here’s what it is and how to set it up”, nothing that quite covers trouble shooting or optimization. I’ve also checked out the various Data Mining blogs/sites.

 

I’ve got a Microsoft Time Series model I’m basing on a Cube. Very simple, trying to forecast sales. I have one dimension which is the list of products (about 1600), a second which is the time dimension, finally the measure is daily sales figures for each, about 3.2 million rows in total. On a brand new server, with two quad core processors and 16 gig of ram it took 40 hours to process. Seems rather high?

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend