Yearly Production - Total Per Organization Number

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


ADVERTISEMENT

Yearly Total

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

Rolling Sum To Find Total Of 3 Previous Months Gas Production

Nov 5, 2012

I'm in need of creating a rolling sum to find the sum of 3 previous months gas production. Below is an example of the basic data I will be using to find the sum.

OperatorIDRRCID YearReported MonthReported YearCode DateReported GasProduction
71054993207201222012022012-02-01 89
71054993207201212012012012-01-01 721
710549932072011122011122011-12-01 353
710549932072011112011112011-11-01 409
710549932072011102011102011-10-01 544
71054993207201192011092011-09-01 707
71054993207201182011082011-08-01 506
71054993207201172011072011-07-01 536
71054993207201162011062011-06-01 472

I want the sum of the Year & Month of 2012-2 to be the sum of the gas production for 2012-1, 2011-12, and 2011-11.

View 1 Replies View Related

Total Number Of Records

Sep 27, 2001

Hi everbody,

I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack

View 2 Replies View Related

Total Number Of Records

Sep 27, 2001

Hi everbody,

I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack

View 1 Replies View Related

Total Number Of Customers

Apr 29, 2008

hi,
I have a person table with a field createdate and personid,

how can I display how many customers existed in the table by date.

eg.
1/4 200
2/4 250
3/4 256
4/4 280


regards,
amie

View 4 Replies View Related

To Get The Total Number Of Databases In A Server

Dec 5, 2007

Hi,
I want to create a web application which will give the information of the total databases in a server or local machine.  also I want to know about the number of tables, its properties in each databases. Pls help me.

View 4 Replies View Related

How To Get Total Number Of Variables Using Scripttask

Nov 14, 2006

I want to loop thru all the variables in my package and set number of variables that had variable-name begin w/LOCAL, so I can use the name to generate a dynamic SQL query for the next EXEC SQL Task. any one know how to do this.





View 7 Replies View Related

Count The Total Number Of Results Returned

May 17, 2007

Iam using front page to dispalay my results.

At the bottom it shows me 1/10 i.e 1st page of 10 pages.

but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).

or it would be really good if i get count of both no. of recors as well as no. of pages.

View 2 Replies View Related

Show Number Of Values As % Of Total Records..?

Feb 15, 2007

HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen

View 3 Replies View Related

Reset Total Page Number In A Group

Sep 26, 2006

I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX. Code for page of total pages

="Page " & Globals.PageNumber & " of " & Globals.TotalPages



EX. Code to reset within a group
Custom Code:
Shared offset as Integer
Shared currentgroup as object

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If not (group = currentgroup)
offset = pagenumber - 1
currentgroup= group
end if
return pagenumber - offset
end function

=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)

What I need is code for a combination of the two...to display code for page of total pages that resets within a group.

Any help is greatly appreciated.
Thanks!

View 4 Replies View Related

Total Number Of Rows Output To A Textbox

Dec 5, 2007

Hi,

Im trying to output the number of rows in a table returned in a report, into a textbox for a total record count

How would I do this ?

thanks

View 2 Replies View Related

Number Of Total Connections In SQL Express 2005

Aug 31, 2006

number of connectios tcp/ip..........I have a 3 person but the 4 does not connect????

View 1 Replies View Related

Total Number Of Reads = 48000000 In SQL Profiler!?

Apr 17, 2007

I'm using an application that produce 48000000 reads for one stored procedure and 170 seconds to complete. The same procedure when executed in SQL Analyzer takes only one seconds and 10000 reads.



What is happening here? Where should I look to solve this problem?



Thanks

View 1 Replies View Related

How To Determine Total Number Of Pages At Runtime?

Sep 5, 2007

It's no secret that the number of pages in a rendered report varies depending on the format. I have no problem getting the total number of pages for reports rendered in image formats from the web service, but I can't figure out how to get the number of pages for reports rendered in HTML.

I've always been under the impression that the Report Manager that ships with SSRS uses the same web service (reportservice.asmx) and IT can get the number of HTML pages, so it has to be possible.

Does anyone know how to do it?

View 3 Replies View Related

Counting Total Number Of Queries Executed Within The Page

Aug 25, 2007

Hi everyone,Does exist an easy way to count the actually number of queries executed within a page?I've searched here and in google but found anything...Thanks in advance! 

View 3 Replies View Related

SQL Server 2008 :: Get Total Number Of Attachments Of Group ID

Sep 10, 2015

Copy and paste the code to review sample:

Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)

[Code] ...

I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.

View 9 Replies View Related

Transact SQL :: Total Number Of Days Between Two Dates By Year?

May 6, 2015

I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year

Table:

Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015

My expected result.

2013 - 365
2014 - 181
2015 - 365

Note:

Date range can span b/w  multiple years

Date ranges will not overlap

I just want the total number of days covered by the range for each year.

Is there any simple way to do this calculation.

View 9 Replies View Related

SQL 2012 :: Get Total Number Of Each Distinct Value In Column And Write To Another Table

Jul 14, 2014

How can i get the total number of each distinct value in the column and write it to another table e.g.

MyTable

Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple

Result

Fruit Count
Apple 3
Banana 2
Watermelon 2

View 9 Replies View Related

SQL Server 2008 :: Divide Total Rate By Number Of Records

Jun 22, 2015

my table includes detailed records with total Rate repeated in each record:

CREATE TABLE Table1
(
Providerid varchar (6) NOT NULL,
Providername char (30) NOT NULL,
Clientid varchar (15) NOT NULL,

[code]....

View 1 Replies View Related

Script File - Get Total Number Of Orders In Month (July)

May 5, 2015

I am trying to create a script file that will get me the total number of orders in july. How exactly would i say july because i know my syntax is wrong and I would be using sum instead of count right?

What i tried

use Cis11101_Northwind
Declare @Julycount int
Set @Julycount= (Select sum(*) From orders Where OrderDate = 'july')
print 'The total orders for july is ' + Cast(@JulyCount as varchar)

View 4 Replies View Related

Which Is The Most Efficient Query To Find Out The Total Number Of Rows In A Table?

Mar 30, 2006

which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query

View 10 Replies View Related

Displaying Total Number Of Rows In A Report In Page Header.

Jun 20, 2007

Hi,



I have requirement to display Total number of Rows in a Report in Page Header.



I have written the following code in Page header it shows RowCount for the Page only.

=Count(ReportItems!textboxInTableCell.Value)



Can anyone please help on this?



Regards

Raghav





View 3 Replies View Related

Integration Services :: Creating A Column With Total Number Of Records In Dataset For Each Row?

Aug 17, 2015

I have a transformation where final result set give me 25 rows of data. Now before I put into destination table, I need to add another column which will show how many total records we have. Like.

My dataset:

A  20 abc
B 24 mnp
c 44 apq

Now I need to add another column within my transformation before I store the result set to destination like this:

A 20 abc 3
b 24 mnp 3
c 44 apq 3

Here. new column gives count of total rows in our dataset which was 3.

How can I achieve this? Can I use derive column to this?

View 6 Replies View Related

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 Replies View Related

Re-organization?

Dec 24, 2007

what is re-organization.how do i implement it ,what are the steps which i need to take care if it is a production server

View 4 Replies View Related

Disk Organization

Oct 30, 2007

I understand the log files (LDF ) and data files (mdf ) should be on a different drives . I believe it leads to greater availabilty and speed . Are there any other reasons for to keep this on a separate drive.

Also what considerations I should take care while creating a database of around 100 GB . (use of filegroups , growth % etc ). Is there any connection of number of users to number of disks SQL data file to be spread to . Also do I need to take care (through hardware / software for a Quad core CPU ) to take full advanage of Quad core CPU.

View 4 Replies View Related

Organization Tree

Oct 14, 2005

Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks

View 2 Replies View Related

Report Server Organization

Sep 18, 2007



I have several sub reports and some graphics I don't necessarily want the users to see or open. Is there a way to hide items from view but still have them available for execution? I tried creating a sub folder and moving the sub reports but the calling reports couldn't find them.

Thanks!

View 1 Replies View Related

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 View Related

Query To Generate Hierarchy In Organization

Jun 8, 2014

I need to write a sql that generate the hierarchy in an organization.

Below an example

emplid empname supervisor_id superv_name
1 subu null null
2 vid 1 sub
3 ram 4 satis
4 satis 2 vid

I need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.

Whoever joining new to org the hierarchy

empid empname supervisor_id superv_name
3 ram 4 satis
4 satis 2 vid
2 vid 1 subu
4 satis 1 subu
3 ram 1 subu
3 ram 2 vid
5 kumar 1 subu
5 kumar 4 satis
5 kumar 2 vid
1 subu null null

View 3 Replies View Related

Deploying SQL Express Throughout The Organization/Enterprise

May 17, 2007

What are some recommended methods for deploying SQL Express throughout an entire organization? It appears that Active Directory/GPO deployment is out of the equation.



Thanks,

Shane

View 5 Replies View Related

In A Typical Organization Who Would Supprt SSIS

Aug 22, 2007

In a large organization who would typically be tasked with support ing SSIS - the database team, an application team, etc... SSIS is new to our Organization, we are attempting to put it into the correct organization. We current have SSRS that is supported by an application team, the SS DB is supported by the DB team.

Thanks

View 4 Replies View Related







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