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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

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

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

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.




View Complete Post


More Related Resource Links

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.


lookup table query

  

we are doing our cms migration. both cms's have their own articleID/contentID, we have a lookup table that contain the article URL and its content id.  Now we want to query that table on every single click to get its respective id. 

How and where i can implement this?



SSIS Package Transaction locks entire table

  
Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

Record is already exists in Dimension table but still Kimball SCD Component is Identifying it as a n

  
Hi I have loaded Dimension table. Now even if the record already exists in my dimension table , every time I run package Kimball method SCD component is identifying as a new record. Please advise. Thanks, Anuja

check if a table exists in SQL 2005

  
I'm trying to check if a certain table exists in a given database on a SQL 2005 Server. I've tried numerous times without any result.   can anyone point me in the right direction?

lookup: unable to allocate memory for hash table

  
Hi, I have been getting an error from a lookup component when I run it from the job agent. However, it works just fine from debug mode. the specific error is 0xC0208247: unable to allocate memory for hash table. I get the same error when I run this not in full-cach but in partial and no cache. Also, the default butter max row and buffer size in package are set to default. Does anyone have any idea.   Jon

Help in Building Fiscal Dates Table

  
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 ....   

Calculate Fiscal Year

  
HI Experts,  I need help in calculating Fiscal year for given Date.  My fiscal year begins on Oct 1st of every year and I also need to calculate Fiscal Day number of year,  EX: if Date in table is 2010-10-5 then my fiscal year start date should be 2010-10-01 and in my next column I should get day of year as 5    any help is greatly appreciated.  thanks, vishal   

Upgrading timestamp column to rowversion (alter table)

  
Hi, We are upgrading 2005 to 2008 and observed that time stamp data type is deprecated, and should be using rowversion instead. So I started doing alter table for a table with timestamp to rowversion and the alter table failed with the error "cannot alter column 'rv' to be data type timestamp. Can some one please let me know what I am missing here? thanks, Sivakumar

How to lock a table during the whole transaction?

  

In my procedure, I need first to run a merge table T using table S and then truncate table S. And other session might insert data into the table S during my execution of the procedure. So I'd like to lock the table in exclusive mode for the whole transaction, but how to do that?

 

If I only lock the table during the merge phase, if after merge completed but before truncate begin, another session inserted some data into it, then the data in S will be missing when run the procedure again.

 

begin

  merge T using S .....

 truncate table S;

end

 

 


SSAS 2000 - YTD for Fiscal and General Period

  

Hello Experts,

In SSAS 2000, I have 2 time dimensions;

  • Financial Period (Time type) = Year --> Month (with no "All Member")
  • General Period (Time type) = Month (with "All Member" being captioned as "Year Total")

YTD Actual = SUM(YTD(), Measures.Actual)

The idea is to have all years on row and all general months on column for YTD measures. The above YTD calculation is only working for Financial Period and not for General Period .

Pretty easy to do it in SSAS2008 with scoping but how do I do this in SSAS2000 environment?

Can you please help?

Thasnks in advance.

Pelican


Proposal transaction table

  

Hi All,

I'd like to know how to save the amount of transactions?

CheckBox CheckBox1 = LoginView2.FindControl("CheckBox1") as CheckBox;
        RadioButton RadioButton1 = LoginView2.FindControl("RadioButton1") as RadioButton;

        if (CheckBox1.Checked == true)
        {


            if (RadioButton1.Checked == true)
            {

                RequiredFieldValidator RequiredFieldValidatorCVC22 = LoginView2.FindControl("RequiredFieldValidatorCVC22") as RequiredFieldValidator;
                RegularExpressionValidator RegularExpressionValidatorCreditCardNumber = LoginView2.FindControl("RegularExpressionValidatorCreditCardNumber") as RegularExpressionValidator;
                RegularExpressionValidator RegularExpressionValidatorNameOnCard = LoginView2.FindControl("RegularExpressionValidatorNameOnCard") as RegularExpressionValidator;
                RegularExpressionValidator RegularExpressionValidatorCVC2 = LoginView2.FindControl("RegularExpressionValidatorCVC2") as RegularExpressionValidator;
                RequiredFieldValidator RequiredFieldValidatorCVC2 = LoginView2.FindControl("RequiredFieldValidatorCVC2") as RequiredFieldValidator;


                if (RegularExpressionValidatorCreditCardNu

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


Log to a table from within a stored procedure with transaction

  

I want to do the following:

Execute a stored procedure that starts a transaction, and from within that stored procedure call another stored procedure that is outside transaction.

The easiest to explain this is with a logging example (my situation is slightly different but it's the same principle)

So, from within a transaction writing a log to another table. However, when my procedure fails, all logging is gone because the transaction is rolled back.

The following example is an example (don't shoot me if it's not valid tsql)

CREATE PROCEDURE usp_X
AS

BEGIN TRANSACTION
INSERT INTO TABLE1 VALUES (NEWID(), 'abc')
exec usp_LogEvent('record created'
COMMIT TRANSACTION


CREATE Procedure usp_LogEvent
varchar(1

C# How to check prammatically if null value exists in database table (using stored procedure)?

  
Folks!

How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL,
UserID INT NULL,
Name NVARCHAR(50) NULL,
DateOfBirth DATETIME,
IsActive BIT,
Phone NVARCHAR(50) NULL,
Fax NVARCHAR(50) NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID),
CONSTRAINT UNIQUE_Phone UNIQUE(Phone),
CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
);
GO

INSERT INT

how to select the last transaction Detail in table

  

Hi all,

i am working on the migrating project now i want select list of records depending on the last date of transaction and i have to fetch one record for each account please help me out 

i have data as below

ac_no                trdate              balance ---------------

1                     2010-3-31           100     -------

1                     2010-4-1              50         ----------

1                     2010-4-5             500     ----------

2                     2010-3-31           200

2  &nbs

How can I check (true/false) if every single entry in table exists in other table?

  

Hi

I have 2 tables:

- Salesmen with Pk.SalesmenID
- Appointments with Fk.SalesmenID and busy(bit)

How can I check if every salesmen has at least one appointment

Am I on the right way with statement below, though I got syntax problems

If
(SELECT COUNT(distinct SalesmanID)FROM Appointments)
=
(SELECT COUNT(SalesmanID) FROM Salesmen)



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