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


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

Help in Building Fiscal Dates Table

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
 
hi Experts,  I'm trying to build fiscal periods table. I have Calendar Dates of year 2007  in 1 table and just start Dates of Fiscal weeks in another table, can any one suggest me about building Fiscal table containing entire year using the 2 tables.  thank you ....   


View Complete Post


More Related Resource Links

Building a 3-Tier App with Silverlight 3, .NET RIA Services, and Azure Table Storage

  
Silverlight 3, .NET RIA Services, and Windows Azure Services Platform makes a 3-tier Cloud application easier to build: Silverlight 3 as presentation tier, .NET RIA services as the business logic and data access tier, and Windows Azure Table as the data storage tier. The sample application in this article demonstrates the architecture with a simple Survey application with all these technologies working together from Windows Azure.

Building Fact and Dim table

  
How to build Fact and Dim tables for below requirement. Fact records (approx 800k) has to be analyzed every day w.r.t AgeGroup's Every record in fact will have a DOBSID and age/agegroup should be calculated every day based on getdate()

how to load dates in a table in a range

  
Hi i have a table DATEDESC with columns [FullDate] [DateName] [YearName] [MonthName] [DayOfTheWeek] [YearNumber] [MonthNumber] [WeekNumber] [DayNumber] SAMPLE DATA OUTPUT FULL_DATE DATE YEARNAME MONTH_NAME DAY_NAME YEAR MONTH_NUMBER WEEK_WITHIN_THE_MONTH WEEK_WITHIN_THE_YEAR DAY_NUMBER 2010-09-09 24:31.9 2010-09-09 NOT LEAP YEAR September Thursday 2010 9 2 37 9   I need to load a series of dates into this tale by giving a start and and end dates range. Lets say i need to load 10yrs of date's data into this column, i need to load all the dates into this table and get those respective column. The query i had to populate the sample data was with the current date. But i am not sure how to do this for a bunch of data when it is in a range. SELECT GETDATE() AS FULL_DATE, Convert(varchar(10), getdate(),110) as DATE_NAME, CASE (YEAR(GETDATE()))%4 WHEN 0 THEN 'LEAP YEAR'ELSE 'NOT LEAP YEAR' END AS [YEAR_NAME], DATENAME(MONTH,GETDATE()) AS [MONTH_NAME], DATENAME("DW",GETDATE()) AS [DAY_OF_THE_WEEK], DATENAME(YEAR,GETDATE()) AS [YEAR_NUMBER], MONTH(GETDATE()) AS MONTH_NUMBER, DATEPART(DAY,GETDATE() -1)/7+1 AS [WEEK_WITHIN_MONTH], DATENAME("WEEK",GETDATE()) AS [WEEK_WITHIN_YEAR], DAY(GETDATE()) AS  DAY_NUMBER So, could you please help me in build this query. Thank you.

trouble getting dates right when importing Access table

  
I was given a bunch of data in Access and am putting it in SQL 2005 for an ASP.NET web app. Tryingto do a query to select only records where the current date is after the "postDate" field and on or before the "expireDate" field. WHERE (postDate <= CURRENT_TIMESTAMP) AND (expireDate >= CURRENT_TIMESTAMP) I'm getting no records returned even though there are some records that qualify. Tried using CURDATE() and NOW(), no difference. At this point I think it may be because the dates are not in the correct format. The columns were originally "Date/Time" format in Access and when I imported them into SQL using the DTSWizard, the datatype for those columns became "smalldatetime." Dates are in the format M/DD/YYYY HH:MM:SS. I'm guessing the query won't work because SQL is expecting YYYY-MM-DD. How do I fix this? Been spending a depressing amount of time running in circles with this.

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

  
How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

Building Cubes with less granularity than the underlying fact table

  

If I have a fact table with a geography dimension where zip code is the primary key in the dimension table and the foreign key in the fact table, is there a way to create a cube where the leaf level in the geography dimension is at a higher level, say City (or State), even if City (or State) is not an attribute on the fact table?

Alternately, if I have a fact table with dates but I only want monthly summary data in my SSAS cube, is there a way to make the Month level the leaf level member of the Time dimension in the cube without adding a Month foreign key column to the fact table?

It would seem to me that people would want to do this wiht some frequency; however, I can't find a way to do it in the SSAS documentation.  This link seems to suggest that the grain of the cube has to be basically the same as the grain of the fact table in all cases: http://msdn.microsoft.com/en-us/library/ms166573.aspx

 


Creating Table of consecutive dates

  

I'm trying to create a table with one field (SmallDate&Time)

I want this table to have consecutive dates beginning roughly 01/01/2008

through 12/31/2013.

I think I need a Stored procedure to do this but am not sure.

It may even be doable from an SQL Query.

Any help would be appreciated.

 

Regards

 

Tattoo

 

 

 

 


Multiple companies and fiscal calendars in date dimension table

  
We are creating an application where different companies can have their own fiscal calendar starting on different dates. For example one company’s fiscal year may start in April and some others in Sep. Also fiscal calendars may start at any date. (E.g. 29 Sept).

These fiscal calendars would be created for parent companies. We have added the parent company ids to the date dimension table. Please suggest the correct way of setting the attribute relationship in the above scenario. If this is not the right method to do this, please suggest the correct design approach for achieving this.

 

Thanks in advance,

 

Hamlin Stephen


query wich returns records for each day between start date from table 1 and mutation dates in table

  

Hello,

I have to tables with dates, table Employments with all employements of all employees, with start date and end date, and a table Mutations with mutations (e.g in salary) with start dates.

Now I try to write a query which returns a record for each day an employee is in employment, with the correct salary. SO at first, it should return all days between the employment start date and the first mutation date, then the number of days between the first mutation date and the second mutation date etcetc, and at last the number of days between the last mutation date and the employment end date. The number of mutations varies for each employment, and employees cna have multiple employments (history, so not at the same time) in the employments table.

How to do this?


getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


expand table with date range for each dates

  

Hi, 

 

         I have a scenario to expand the resultant table with dates column,.

 

For ex:

select patientid, patientname, servicefrom, serviceto from appointment

will result the following table

 

patientid      patientname      servicefrom      serviceto

  a1                alex               02/05/2010       02/08/2010

  a2                brad              02/09/2010        02/09/2010

  a1                alex               02/07/2010       02/10/2010

 

i want to write a query to expand the tables like

 

patientid      patientname      servicefrom      serviceto           eachdates

  a1                alex               02/05/2010       02/08/2010      02/05/2010

 

How filter data from a webservice that returns a table by comparing dates

  

I have an infopath form that takes in brings in an entire table and I want to filter it on datetime by comparing records from a time span of dates that have already been assigned.  I have tried everything from doing it from the within infopath using equations to filter the datasource and also doing it programmatically.  I am to the point where I think it has to be done programmically (C#).  Here is an example of the XPath code I was trying:

(xdMath:Avg(xdXDocument:GetDOM("ISSOrbits")/dfs:myFields/dfs:dataFields/d:ISSOrbits/@Apogee[(number(translate(xdXDocument:GetDOM("ISSOrbits")/dfs:myFields/dfs:dataFields/d:ISSOrbits/@StartTime, "T-:+", "")) >= number(translate(/my:myFields/my:MissionData/my:LaunchMissionDateTime, "T-:+", "")))]))

It seem that only the first record (node) from ISSOrbits gets compared and not the whole dataset.

Please show me how to do this programmatically in C# or if there is way to do it without code.

Thanks


Charles Picco

P.S. I forgot to mention that table is brought in through a webservice that cannot filter data.

 


Export Visio Database Table Names to Excel

  
If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Building a Searchable Phone Directory with Windows SharePoint Services

  
I want to continue to build out the intranet site we've started in our previous articles. A standard item in every intranet is a staff/employee list. Most companies implementing Windows SharePoint Services (WSS) are large enough to have a database that contains all employees. We will use the aggregation features of SharePoint to include data from this database into our intranet.

Building a grouping Grid with GridView and ASP.NET AJAX toolkit CollapsiblePanel

  
I've posted an update to this post to apply on demand loading of Details data using ASP.NET AJAX PageMethods. I'm demonstrating the usage if CollapsiblePanelExtender client events on the new post. Check it out.

I admit, Matt Berseth is really inspiring me! Few days ago I pass through 2 of his great posts:

.Surrounding your Data Table and Dialog Controls with ComponentArt Styled Panels
.Building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls
The first one is pure design tips one which I used for making good presentation of my demo here. The 2nd post is actually the idea.

Insert value using Table Value Functions

  
a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.
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