Yearly Data From Selected Range
Apr 10, 2003
Hi all,
I have the Following Query
It is working fine if the first month is 01 and Second month is 12
but i want like this if the user selects 200103 to 200203 i want the
value of composite should be added from 200103 to 200203 in one row
and other row from 200203 to 200303.
like this if the user selects from 200004 to 200304 i have to get 3 rows..
that is Yearly data..so how can i modify the following query or any new query.
select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and '200312'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)
Thanks
View 2 Replies
ADVERTISEMENT
Jun 1, 2015
I have a simple table of yearly financials to transpose:
Year/Field      Financial1 Financial2 Financial3...
2014Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2013Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
....
The number of years available is dynamic. I would like to retrieve it as follows:
                    2014          2013       2012    Â
.....
Financial1Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial2Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial3Â Â Â 1000000Â Â 2000000Â 3000000$
What is the simplest way to achieve this?
View 7 Replies
View Related
Aug 11, 2013
Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
View 18 Replies
View Related
May 6, 2015
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
View 3 Replies
View Related
Feb 21, 2014
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
View 0 Replies
View Related
Jul 10, 2015
I have 4 Tablix and 2 of the Tablix get data from Server 1 and other 2 get the data from Server 2.I have set NoRowsMessage "=Data Not Available for the Selected Values"  for all the 4 Tablix.Now if data is not available from Server 1 then I must show "Data Not Available for the Selected Values" only once in the  outputbut now its appearing twice in the output because of the 2 tablix that had no rows.Similarly if data not available from Server 2 then it should show "Data Not Available for the Selected Values" only once in my output.If Data not avilable from all the Tablix then also i t should show only once as "Data Not Available for the Selected Values" in the report output.
View 3 Replies
View Related
Jun 19, 2007
Hi, all experts here,
I am wondering is there any way to select only a portion of a data set to train the mining model? In this case, I mean we dont need to split the dataset in advance, what I want to do is being able to select any random portion of a selected dataset to train a mining model. Any advices?
I am looking forward to hearing from you and thanks a lot in advance for your advices and help.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Oct 22, 2006
I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....
View 2 Replies
View Related
Oct 15, 2007
I have a SQL database which uses Access 2000 as the front end to do all queries. Currently i have a query that lists the amount an employee has spent on purchasing products i.e.:
Employee Name - Code - Description - Unit - Price - Month
Joe Bloggs - zzz - Pack of 10 CDRs - 1 - £3.99 - October
I would like to add another column that lists how much theyve spent so far in the current year. The employee name and Month has a parameters of =@Employee and =@Month so when the query is run i can enter the name of the employee and the month which then displays the results.
I tried to create a query where i entered Month under column (in access) followed by Total as this is what i used to do in an Access database but didnt work - how could the same be achieved in SQL?
Thanks in advance
View 10 Replies
View Related
Feb 24, 2007
Hello all! How can I get data from SqlDataSource for row selected in GridView?
View 3 Replies
View Related
Feb 12, 2006
Hi I have a problem that seems to be stupid… but still I cant solve it so please if you can help ASAP I'll appreciated.
Its 2 tables "Courses" and "Facilitators"
The Course table contain: Course_Id and Course_Name
The Facilitators table contain: Facili_Id and Facili_Name
The two tables have (many to many) relation between them.
So I split them into a 3rd table called "Trans" which contain the PK from each Table
That will be : "Trans_Course_Id" and "Trans_Facili_Id"
I put some data on each table… at least 3 records and related some of them in the "Trans" table
Now im looking for an SQL command that brings me All the "Facili_ID" and "Facili_Name" for a specific course. And only those who is not already selected by the same course?
Like if I have the data in the "Courses" tableid: 1 and Name: VB
Id:2 and Name: C#
And in "Facilitators" table:
Id:1 and Name: Adam
Id:2 And Name: George
Id:3 and Name: Sam
Now in the relation table "Trans"
Course_Id:1 and Facili_Id:1
Course_Id:2 and Facili_Id:1
Course_Id:2 and Facili_Id:3
Now I want the SQL Commands that brings me the he "Facili_ID" and "Facili_Name"
For Course_id "For example" and should not be selected by the same course…
That would be:
Id:2 And Name: George
Id:3 and Name: Sam
And the same for eash time I pass the course_id for the command
Thank you.
View 10 Replies
View Related
Sep 6, 2007
Hi,
I hope you can help me.
I've got a csv that I need to import. It's about 74 columns wide and 2500 rows long. What I need to do is select the first 3 columns from the columns from the csv and put them into one table and then put every 7 columns (plus the 1st column as the primary key) into a different table.
I've not explained that very well, basically it's like this...
A B C D E F G H I J K L M N O P
1
2
3
4
5
Table 1 Data
cs# A1
fe B1
cl C1
Table 2
cs# A1
desc D1
date E1
pay F1
amount G1
vat H1
total I1
cReq J1
I'm not really sure were to start. I want to import the data nightly into the tables. I've tried to use the import wizard but I can't seem to select the data I want and exclude the data I don't.
Steve
View 8 Replies
View Related
Apr 13, 2012
I am attempting to sum the gas production for each organization number, and separate gas production into each year as such:
Organization_Number20082009201020112012
103 774 7313868470
But currently I am having trouble displaying this, and it's coming out as such:
Organization_NumberMonth20082009201020112012
103 1 774731386847NULL
103 2 810656654674NULL
202 1 27262702293725122048
202 2 2913205120202064NULL
I want to remove the month and have one total per organization number, as well as remove NULL as show as 0.
Code:
select *
from yearproduction
pivot(
sum(Gas_Prod)
for Year in ([2008], [2009], [2010], [2011], [2012]))
as YearlyProduction
order by Organization_Number, Month
View 1 Replies
View Related
Nov 18, 2006
I've been trying to reinitialize the membership database, but something stops me.
I have tried deleting aspnetdb.mdf from the App_Data folder. This works fine, and then a new one is created when I use the asp.net adminstration tool. I can use the server explorer to view the table content (all null).
Although I can open the asp.net configuration page, the problem occurs when I select security. Then I get the error
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: Database 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727asp.netwebadminfilesApp_DataASPNETDB.MDF' already exists. Could not attach file 'C:Documents and SettingsJeff LeeseMy Documentsworldmy workcurrentcounsellorApp_DataASPNETDB.MDF' as database 'ASPNETDB_devjeffleese'.
All this seems to have happened after I modified my connection info in the web.config file:
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;
 AttachDbFilename=|DataDirectory|ASPNETDB.MDF;
 user instance=true;
 Integrated Security=True;
 Initial Catalog=ASPNETDB_devjeffleese;"
providerName="System.Data.SqlClient" />
</connectionStrings>
So, two questions:
1) What are the 
 sections about? They seem to have been inserted automatically at some point, and I wonder if its just formatting info or should I remove them?
2) How can I get security going again. How do I deal with the 'already exists' problem that prevents 'attaching' ?
Would greatly appreciate guidance in how to start over...
View 1 Replies
View Related
Mar 3, 2008
Hello,
When I am using DISTINCT in the SELECT clause for a table having a column of datatype "ntext" in SQL 2000 as well SQL 2005, it fails giving the error as "ntext data type cannot be selected as DISTINCT". This is coz
"The text, ntext, or image data type cannot be selected as DISTINCT."
But I am able to insert into a temporary table having a column whose datatype is nvarchar) using this SELECT clause in SQL 2000. Please advice me how this happens. If I am not worng, implicit conversion has happened. But this insert fails raising the same error in SQL 2005. Please help me to find the difference
thanks in advance!
View 3 Replies
View Related
Feb 25, 2014
I'm using this query to to calculate yearly finance values.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance]
Now I need to multiply the [FinanceValue Variance] * 2.50 and for that how can I use the alias name as column in the query. I tried this but it says invalid column name.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance], [FinanceValue Variance] * 2.50 as [NewVariance] from Finance
SumofVariance output will be like 5690.5893656 Also how can I show the SumofVariance to round off 4 decimal places like this 5690.5894.
View 1 Replies
View Related
Jul 31, 2007
I have this Stored Procedure:
Create PROCEDURE ListEventas If MONTH(GetDate()) <= 6 Begin SELECT EventTitle, EventDuration, (CASE WHEN MONTH(StartDate) = 1 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jan', (CASE WHEN MONTH(StartDate) = 2 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Feb', (CASE WHEN MONTH(StartDate) = 3 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Mar', (CASE WHEN MONTH(StartDate) = 4 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Apr', (CASE WHEN MONTH(StartDate) = 5 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'May', (CASE WHEN MONTH(StartDate) = 6 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jun'FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)WHERE YEAR(StartDate) = Year(GetDate())group by EventTitle, EventDuration,StartDate,EndDateEnd Else BeginSELECT EventTitle, EventDuration,(CASE WHEN MONTH(StartDate) = 7 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jul', (CASE WHEN MONTH(StartDate) = 8 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Aug', (CASE WHEN MONTH(StartDate) = 9 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Sep', (CASE WHEN MONTH(StartDate) = 10 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Oct', (CASE WHEN MONTH(StartDate) = 11 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Nov', (CASE WHEN MONTH(StartDate) = 12 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Dec'FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)WHERE YEAR(StartDate) = Year(GetDate())group by EventTitle, EventDuration,StartDate,EndDateEnd
When I execute it in the SQLExpress, the result returned as expected. But when I bind to Gridview I got this error:
A field or property with the name 'Jan' was not found on the selected data source.
How do I solve this?
I am using drag and drop SQLDatasource to call the Stored Proc and VB.net is the language. Thanks
View 4 Replies
View Related
Sep 15, 2004
Hi,
I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.
I tried using bcp Command as follows..
bcp "select colA,colB,colC,colD from A" queryout "c: empA.dat" -t" " -r"" -c
I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.
This is very critical. Appreciate your help.
Thanks,
Ramya.
View 3 Replies
View Related
May 28, 2008
I'm having a problem building a query for an application I'm writing. Basically, there are a bunch of records in a table, and some of them may have the same identifier code. I only want one of each identifier code. The table looks like this:
Memo
---------
Msg_Identifier varchar(60)
Memo_ID int (pk)
Subject varchar(64)
Message text
Priority int
Deleted int
My query:
SELECT DISTINCT Msg_Identifier, Memo_ID, Subject, Message, Priority FROM Memo WHERE Deleted = '0'
When I run this query, I get "The text data type cannot be selected as DISTINCT because it is not comparable."
I only want Msg_Identifier to be the distinct field, and it's obviously not set up as text. I've used SELECT DISTINCT in the past but I don't ever recall hitting an issue like this. What's the best way to do it?
View 8 Replies
View Related
Nov 23, 2015
Having table like below. Here want to cumulative the values quarter and half yearly wise...
declare @table tableÂ
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )
[Code] ....
Like wise the data should added...
View 3 Replies
View Related
Jan 4, 2008
hi,
i am generating report for my project.
my table goes like
ID VASID VASSID JanMail JanVisit JanPhone JanComment FebMail FebVisit FebPhone FebComment................. DecPhone
1 25 4 True False False Mail me False True False Visit me
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively..
once user check on Mail and select a month suppose January in my report,the report should generate only January Comments.
here user can select any number of months.if user selects january,february and march, report should generate respective months Comments.
Please help me in writing SQL Query to get the comments.......
if any one know the solution for the above problem,Please help me
Thanks & Regards,
View 2 Replies
View Related
Aug 24, 1999
I want to check a table to see what rows have been updated today, then write to a text file some data from the selected rows; then I want to automate this (DTS package? TSQL stored procedure job?) to run every night at midnight. Is the DTS Wizard the best way, that's what I did, but have not confirmed that it is writing a new text file every day, and does each new version write over the old one?
View 1 Replies
View Related
Dec 4, 2007
Is it possible to import data from an Excel spreadsheet using OPENROWSET or OPENDATASOURCE without having to explicitly define the filepath of the source file? Currently, I have this piece of code within a sproc:
INSERT INTO [dbo].[ProductionRequirementDetail]
([ProductionRequirementHeaderID], [SKU], [Quantity])
SELECT @ProductionRequirementHeaderID,
[SKU],
[LAMPS]
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=C:WeeklySchedule.xls', 'SELECT * FROM [Master$C5:Q65536]') AS XL
LEFT JOIN [dbo].[PartMaster] ON (RIGHT([XL].[CODE], 7) = [PartMaster].[SKU])
WHERE [SKU] IS NOT NULL
AND [CODE] IS NOT NULL
AND [LAMPS] IS NOT NULL
AND [LAMPS] > 0
AND [LampTypeID] = @LampTypeID
I would like to remove the hardcoded reference 'Database=C:WeeklySchedule.xls' and replace it with a parameter for the filepath. Is this possible? This is in SQL Server 2000. Also, if there is a way to do this with DTS I'd be open to doing it that way too.
View 8 Replies
View Related
Jun 21, 2006
The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column.
Thanking in advance :)
View 3 Replies
View Related
Jul 2, 2015
I have to calculate the Total number of days present and absent for a singel student.
AS of now i have 3 tables.
1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]
So now i have loaded all the data into the table.
I can fetch the counts for total present and absent
Query i have tried is
Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
Begin
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
View 7 Replies
View Related
Apr 4, 2007
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies
View Related
May 28, 2008
Hi
I am very new to analysis services and using MDX.
I want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
----
Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The case statement will take paramter values when finished
----------------end of code portion
Is this possible or is it suppose to 'split' the columns when moving to SSRS.
Thans in advance
Dev environment - SQL 2008 Feb CTP, VS 2008
View 5 Replies
View Related
Jan 7, 2011
Currently, I have a report that takes two parameters: Â StartDate and EndDate. Â
I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well with StartDate and EndDate because the parameter is static. Â What is the most elegant way to implement this change?
View 5 Replies
View Related
Apr 30, 2008
Hi,
I need help with dates for my weekly report. Week starts on Sunday and ends on Saturday. In my view I have list of dates, how can I make a filter where dates should be within the week range. Example if I have, April 6, April 11, April 14, April 15. Week 1 should have April 6 and April 11 since week date is from April 6 to 12. Then April 14 and April 15 is for the next week. Thanks
View 14 Replies
View Related
May 4, 2008
Hi,
I have a where statement that covers the date range May 4 - May 12. However, it moves or rolls out every week. Is there a way to make it moves on May 13.
Here's my statement :
WHERE (Reviewdate >= DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))) AND (Reviewdate <= DATEADD(dd, 13, DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))))
View 8 Replies
View Related
May 27, 2008
Hi Everyone,
I am new to reporting servies and need some help.
My dataset is doing a simple select Details,amount,paidindate, from table where paidindate>=startdate <=enddate.
I have done a matrix report I would like add YTD as well as amount in each month. i.e
| Month + Year | YTD
Details | =amountmonth |=Amount for 01/04/year (finacial
year) to displayed month
having problems doing the YTD as it need to pull a different paidindate range.
Thanks,
John
View 7 Replies
View Related
Aug 16, 2006
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies
View Related
Jun 28, 2007
I want to know what the best datatype is for a situation like this e.g.if I have a field "age" and the data has a range i.e. 22-30, 31-50 etc, what is the best data type to use for this scenario.
Similarly if I have a field that whereby you use a range for example 1-2 in one record but in another you get an integer value of 0 for instance, what again would be the best datatype.
Many thanks
View 5 Replies
View Related