Convert Fiscal Data From Row To Column

Mar 18, 2008

I need to take multiple rows in a table that store fiscal data and denormalize into one row with all the fiscal information in it. any suggestions on how to approach this creatively - lots of data will make it important to make this efficent, will need to run each year so I would like to make it portable.

Fiscal data table
companycode 1 1 1
fiscalyear 2008 2008 2008
fiscalperiod 200801 200803 200802
amount 101 301 201

result set
companycode 1
fiscalyear 2008
fiscalmonth1_amount 101
fiscalmonth2_amount 201
fiscalmonth3_amount 301
fiscalmonth4_amount

..
fiscalmonth12_amount


thanks

View 6 Replies


ADVERTISEMENT

How Can I Add A Fiscal Year Column To A View

Oct 6, 2007

I have a view that shows payment amount, payment date. I need to add a column to the view that shows what fiscal year the payment belongs to.
How can this be done?
I do have a table that has the fiscal start and end in it, tblGlobal with fields FiscalYearStart and FiscalYearEnd.
 

View 16 Replies View Related

Query For Fiscal Year Data

Mar 17, 2014

I am using the query below and it is working great to give me montly averages. I need to find the fiscal year to date averages for the same data. I think all I should need to change is the Select and Group by sections but I am having no sucess.

SELECT Format([CallDate],"yyyy mmm") AS [Month], Count(DailyCallStats.CallDate) AS CountOfCallDate, DailyCallStats.Agent, Avg(DailyCallStats.Inbound) AS AvgOfInbound, Avg(DailyCallStats.Outbound) AS AvgOfOutbound, Avg(DailyCallStats.TotalCalls) AS AvgOfTotalCalls, Avg(DailyCallStats.AvgWrap) AS AvgOfAvgWrap, Avg(DailyCallStats.ParkTime) AS AvgOfParkTime
FROM DailyCallStats
GROUP BY Format([CallDate],"yyyy mmm"), DailyCallStats.Agent, Format([CallDate],"yyyy mm")
ORDER BY Format([CallDate],"yyyy mm");

View 4 Replies View Related

Retrieving Data Using Fiscal Years

Oct 14, 2007

Hi, I would like to select data from my source system based on fiscal years. I don't want to hard code anything, e.g. select * from person where startdate > 01042005. I want to try and use a store proc or function. The main requirement is to always retrieve data from source tables for the last two fiscal years (based on todays date - getdate). Our fiscal year starts the 1st April. Therefore, If todays date is 14 October 2007, I would like to select all persons whose start date was greater than 01 April 2005. Is there an easy way to do this in sql server 2005 without hard coding dates?

Gurj

View 3 Replies View Related

T-SQL (SS2K8) :: Convert Column Data Into Comma Separated Data?

Mar 14, 2014

I have data in the below format .

NameValuecategory
AAA510
BBB510
CCC510
DDD512
EEE512
FFF512

I want the result in the below format

NAMEValuecategory
AAA,BBB,CCC510
DDD,EEE,FFF5120

I have tried stuff but all six values(AAA...FFF) are coming in one row , however i need them as per the category.

View 2 Replies View Related

SQL Server 2012 :: Convert Column Data Into Row Data?

Dec 12, 2014

I have data like this in a table

ID test_date Score
001 4/1/2014 80
001 5/4/2014 85
001 6/1/2014 82

I want to convert the data into a row like this:

ID test_date1 score1 test_date2 score2 test_date3 Score3
001 4/1/2014 80 5/4/2014 85 6/1/2014 82

How can I do that with T-SQL?

View 1 Replies View Related

Convert Row Data Into Column

Jun 16, 2006

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below


Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7


Can anyone provide me the tips/solution.

View 14 Replies View Related

Convert Data In Image Column

Dec 10, 2014

I have a string stored in a column of type image that I need to do string operations with in order to decode the containing infomation.

data [image] = 0x07FD0707FD0102F001000054004C005300410000000054004C00530041000000FF...

I can't cast or convert to nvarchar(max), varchar(max) as I always get an error. Explicit conversion from data type image to varchar(max) is not allowed.

I tried CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), DATA))

Which doesn't throw an error but seems to interprete the hexadecimal code to ascii characters and therefore is useless (ýýð...)

View 3 Replies View Related

Convert Column Of Data Alphanumeric To Integer

Nov 14, 2013

I need to convert a column of data from alpha numeric to integer.

I am only querying the tables i.e. i don't have access to actually change the data tables themselves.

CAST or CONVERT throws up an error. Are there any other commands i can use at the query stage?

The data I need to convert is always actually a number. i.e. even though it is recognised as alpha numeric, the figure is a number.

I just need it to be converted to an integer so i can SUM it etc.

View 8 Replies View Related

Transact SQL :: Convert Varchar (max) Column Data

May 20, 2015

I have a column in table which stores the actual signature from the singature pad. In the table it is stored as varchar(max) datatype and sample data looks like below. Below data is the actual signature of the person.

CREATE
TABLE #test
([Signature]
VARCHAR(MAX))

[code]...

How do i decrypt this data so i can print actual signature on my report.

View 2 Replies View Related

SQL Server 2012 :: Table With Column Data - Convert Varchar To Int

Jul 8, 2015

I have a table with column "Data" as VARCHAR, with entries like below.

1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68

I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.

SELECT CAST(dataAS INT) FROM record_tab

getting below error

Conversion failed when converting the varchar value 'A1'

View 9 Replies View Related

How To Convert String Data Type To DateTime In Derived Column Control In SSIS Package

Jun 26, 2006

Hi ,

I am Using Derived column between Source and Destination Control. the Source input column PriceTime is String Data type. but in the Destination is should be a DATE TIME column. How to Convert this string to DateTime in the Derivied Column Control.

I already tried to in the Derived column control

PRICEDATETIME <add as new column> ((DT_DBTIMESTAMP)priceDateTime) database timestamp [DT_DBTIMESTAMP]



But still throwing Error showing type case probelm



Pls help me on this



Thanks & Regards

Jeyakumar.M






View 23 Replies View Related

Integration Services :: Column A Cannot Convert Between Unicode And Non-unicode String Data Types

Aug 7, 2012

I am following the SSIS overview video- URL...I have a flat file that i want to import the contents onto a SQL database.I created a Dataflow task, source file and oledb destination.I am getting the folliwung error -"column "A" cannot convert between unicode and non-unicode string data types".in the origin file the data type is coming as string[DT_STR] and in the destination object it is coming as "Unicode string [DT_WSTR]"I used a data conversion object in between, dosent works very well

View 5 Replies View Related

Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation

Mar 5, 2007

Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.

When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.

When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.



Any help in this will sincerely be appreciated.


Thanks

View 7 Replies View Related

Fun With Fiscal Dates

Mar 28, 2008



I have a summary report that displayes data on a yearly basis. Currently I am filtering the MDX query with:


SELECT ...
FROM

( SELECT ( STRTOSET(@DateShippedYear, CONSTRAINED) ) ON COLUMNS FROM ...)

This works fine if the user wants to see the report by calander year. I am not sure what to do when the client wants to see a report by fiscal year. Each division in the company may have a different fiscal year. I have a dimension which lists division name and fiscal start month. Is there any way massage the data based on another parameter? I do something like that in other reports where I use a parameter to select how many months the user wants to display:

...

, lastperiods(@RollBackNum,STRTOMEMBER(@DateShippedYearMonth,CONSTRAINED))

In this case I would have to parce the parameter to get the month start number.


Or would I be better off filtering the data as it is returned to the table or matrix? Here is logically what I'd want to do:

IIf(Parameters!FC.Value = 2 OR Parameters!Division.Value='All'),
IIf(Fields!Year.Value=Parameters!Year.Value,"true", "false"),
IIf(Fields!Month.Value>= Code.FMonth(Parameters!Division.Value) AND Fields!Year.Value = Parameters!FYears.Value-1 OR Fields!Month.Value < Code.FMonth(Parameters!Division.Value) AND Fields!Year.Value = Parameters!FYears.Value, "true", "false"))

I don't see a place to type in a filter statement like that.......Perhaps a WITH clause in my MDX code which filters each row as "ture" or "false" based on the criteria......

View 3 Replies View Related

Convert Column And Update Column

Jun 20, 2000

Hello,

I'm using SQL Server 7. I have an invoice table. The invoice table has a datetime column called InvoiceDate. The InvoiceDate column contains the following date format:
5/3/00
I would like to use the InvoiceDate column to update the char (6) column called zInvoiceDate as a formatted date field like yymmdd.

The following syntax did not work:
SET zInvoiceDate = Convert([ARInvoiceID],GetDate()12)

Any suggestions please :)
Thanks,
Denise

View 4 Replies View Related

How To Select Fiscal Year?

Mar 13, 2007

strSQL = "SELECT DISTINCT YEAR(Date1) As Year FROM WorkShopReq WHERE Year(Date1) > 1999 ORDER BY 1"oRsYears.Open strSQL, DB_CONNECTION'If no years has been specified select the last oneIf intYear="" then    While not oRsYears.EOF        intYear = oRsYears("Year")        oRsYears.MoveNext()    Wendend if'Move to firstoRsYears.MoveFirst() The above code works and displays the data for the Years from 1999 onwards which get inserted into a drop down box in the web page from where the user selects the year and accordingly the all the data for that year displays on to the web page.  * Field Date1 is smalldatetime  Now I want to select the data between fiscal year.  e.g. 6/1/year(date1) to 5/31/year(date1)+1 So i edit the strSQL statement strSQL = "SELECT DISTINCT YEAR(Date1) As Year FROM WorkShopReq WHERE Date1 BETWEEN 'Year(Date1)/5/31' AND '(Year(Date1)+1)/6/1' ORDER BY 1" I get this errorError Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting character string to smalldatetime data type./wsr blue/Default.asp, line 24 What am i doing wrong.      

View 2 Replies View Related

Fiscal Year Search

Nov 29, 2006

HiI am trying to perform a search that will return records based on afiscal year search of the bill_Date. The user gives the year then Iwant to search based on the fiscal year (July 1 - June 30) for the yeargiven. The table looks like thisBill Tableid_Num bill_date bill_amount23 7/1/2005 500.0033 12/2/2005 600.0044 3/3/2006 700.00I have triedSelect Bill.id_num, Bill.bill_date, Bill.bill_amountfrom Billwhere Bill.bill_date BETWEEN 7/1/ + @year and 6/30/ + (@year +1)Plus a variety of other fruitless concoctions....but nothing seems towork. Any help would be appreciated.

View 4 Replies View Related

Converting Fiscal Month

May 15, 2008

Hello,
I'm having a problem with converting calender months to fiscal months.
For April (04) calender month, I have 11 as my fiscal month like below.


[FISCAL CALENDAR].[DAY].&[11]&[2008-04-15T00:00:00]

Here is a parameter 'param' which is a 'datetime' datatype, so i'm basically passing a calender date , so i wrote the query as following:


="[FISCAL CALENDAR].[DAY].&[" + (Month(Parameters!param.Value)+7).ToString + "].&[" + CDate(Parameters!param.Value).ToString("s") + "]"

But it is wrong as I get results like the following:

For April (04), I get 11, which is correct.


[FISCAL CALENDAR].[DAY].&[11].&[2008-04-15T00:00:00]

But for June (06), I get 13 when I'm supposed to get 1.

[FISCAL CALENDAR].[DAY].&[13].&[2008-06-15T00:00:00]

How do I change my query? Please help. I'm struggling with this since yesterday.

View 4 Replies View Related

Add Symbol To Column Values And Convert Column Values To Western Number System

Feb 12, 2014

I want to add $ symbol to column values and convert the column values to western number system

Column values
Dollar
4255
25454
467834

Expected Output:
$ 4,255
$ 25,454
$ 467,834

My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application

COST is the int datatype and needs to be changed.

View 2 Replies View Related

Dynamic Sql To Loop Over Fiscal Years

Jan 6, 2004

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...


expected output....

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
.... ....

View 4 Replies View Related

Spreading Dollars Across Fiscal Period

Jul 19, 2007

I have select statement that returns data on a construction project.
I have a start date, end date, and forecasted cost for each task in the project. I need to create a table that spreads the dollars in a linear fashion broken down by fiscal period.

I have:

task_id, start_date, end_date, cost
1, 9/15/2008, 12/15/2008, 3000
2, 7/1/2008, 12/15/2008, 550

I need

task_id, fiscal_period, cost
1, 200803, 500
1, 200804, 1000
1, 200805, 1000
1, 200806, 500
2, 200801, 100
2, 200802, 100
2, 200803, 100
2, 200804, 100
2, 200805, 100
2, 200806, 50

I can do the math to properly calculate the dollar amount, I am having trouble creating the statement that will process through each row of my select statement and insert multiple rows into the new table.

View 4 Replies View Related

Convert Row To Column?

Apr 9, 2006

 Hi, I have a row with 53 columns. All but the first three are of the same datatype. I want to count the number of columns, out of the last 48 in that row, that have the same value. Is there a nifty way to do this with SQL or T-SQL?
Sample row:
a,b,c,1,1,1,2,1,2,2,2,3,3,3,4,4, etc
The values are not static. I want toknow how many 1's how many 2's etc.
Of course within the app I could for example put the row in a .net datarow and loop through the columns.
Thanks, B.

View 5 Replies View Related

Convert Column Value To Row

Jan 20, 2012

I want to convert Single Column values to Row.

Table Original Content:

Code Amount Expenditure
10027 5000.00LOCAL CONVEYANCE
10027 320.00LOCAL CONVEYANCE
10116 1589.00TRAVEL EXPENSES
10095 350.00LOCAL CONVEYANCE
10095 1215.00TRAVEL EXPENSES

Expected Output :

CodeLCTE
100275000.00NULL
10027320.00NULL
10116NULL1589.00
10095350.001215.00

View 1 Replies View Related

Convert Row To Column

Dec 16, 2013

In the below query i would like to convert the startdate column as a column i.e i want to display all the days of the month as column and their respective data as a row.

WITH DaysOfMonthCTE
AS ( SELECT DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 0) AS StartDate ,
DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 1) AS EndDate
UNION ALL
SELECT DATEADD(day, 1, StartDate) ,
DATEADD(DAY, 1, EndDate)
FROM DaysOfMonthCTE

[code]....

View 3 Replies View Related

How To Convert A Row To A Column

Aug 2, 2007



hi, I have a requirement as stated below

Convert one row in a table to a column:

Input






Name

ID

Date

Profile

Manager


John

12

1/1/1900

Admin

12


Cary

1

1/1/1900

Admin

12


Output






Name

John

Cary


ID

12

1


Date

1/1/1900

1/1/1900


Profile

Admin

Admin


Manager

12

12



I thing i have to use pivot transformation in the data flow but i am not sure how to configure this. Can anyone suggest me how to implemet this or configure this!



Thanks in advance!

View 3 Replies View Related

Convert One Column To Only Row

Oct 24, 2015

I have a column with different number of rows.

Names
Juan
Pedro
Adrian

How Can I convert this column to a only row: 

for example...

Juan Pedro Adrian

View 8 Replies View Related

Running Script On Fiscal Or Calendar Date Whichever Is Later

Apr 6, 2015

I am working on creating a new report that has to get its date from several different tables. Some of these tables update at the beginning of the fiscal month, others at the beginning of the calendar month (). My goal is to develop a stored proc that will run once a month, get all the data from the various sources, and insert into a history table. An SSRS report will generate off of the data in this history table so users can see a running history of the data. I am having trouble creating a way to determine this though. Fiscal months always start on a Sunday. We have a calendar table that translates each date into the fiscal day, fiscal month, etc. Changing the legacy scripts to run on fiscal instead of calendar is not an option.

Idea 1: Wrap the script in an IF statement that will determine the date of last day of the previous calendar month, and the date of the last day of the previous fiscal month, and if current date is greater than both of those, then run the script. But this runs into issues because the current date could be in a different calendar month than fiscal month causing the idea of "previous month" to be incongruent.

Idea 2: Find the current day of year, then if this is greater than last day of the previous fiscal and calendar months, run script. Issue this this is in january, fiscal day might be 2 but calendar day might be 364, so it will never run.

View 4 Replies View Related

Convert The Rows Into Column

Dec 11, 2007

I want to convert the row value as column name
example:-->
value       ratio
3           4.166666666661          1.315789473680          00          0
To :->
value     ratio        ratio1   ratio2   ratio3
3          4.166      1.315    0          0
any ideas?
 
 

View 1 Replies View Related

To Convert Timestamp Column

Mar 11, 2002

Hi!
What correct statement should be to convert timestamp column to datetime.
I wrote:
select convert(datetime, update_date)
from table_name

and it doesn't work properly.

Thank you,
Elena

View 1 Replies View Related

Convert Column To Money

Mar 20, 2014

cast(round([YearlyProfit]-[MonthlyProfit],0)as int),
cast(round(isnull(sum(SalesProfit]),0),0) as int

How can I convert these columns to money so that I can display the result with comma included.

View 2 Replies View Related

Convert Row Of Table To Column

Jan 17, 2007

tblname rowno colname colvalue
emp 1 Title Mr
emp 1 firstname XYZ
emp 1 lastname M
I want to get as

title first_name lastname
Mr XYZ M

View 1 Replies View Related

CONVERT TO DATETIME COLUMN

Apr 16, 2008

HOW DO I CONVERT DATA AND TIME COLUMNS TO DATETIME COLUMN.
I HAVE A REQUIREMENT TO FIND OUT THE MAX AND MIN OF DATE AND TIME COLUMNS WHICH ARE TWO SEPARATE COLUMNS ALL TOGETHER.
I HAVE DATA IN COLUMN1 AND TIME IN COLUMN2
HAVE TO CONCATENATE BOTH THE COLUMNS TO GET THE MAX AND MIN TO DATE.


EXAMPLE I TRIED TO DO :
SELECT MIN(convert(datetime, VH_DATETIME,121),
MAX(convert(datetime,VH_DATETIME, 121))
FROM (SELECT TOP 10 INPUT_DATE+INPUT_TIME AS VH_DATETIME FROM ADMINDB.dbo.SSIS_VISIT_HIST) VH


Please help me in resolving this issue. Thank you

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved