Aggregating One Table And Putting Results In Another Table

Aug 15, 2012

I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.

Table 1: tblResourceAllocation
Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]

Table 2: tblGRFM
ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)

I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name
I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.

Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)

I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date

Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.

View 2 Replies


ADVERTISEMENT

Putting Data Into Table B From Table A Without Using A Cursor

Aug 24, 2006

on SQL Server 2005, no SP.

Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so:

declare @HTR money
declare @Uniqueid varchar(15)

declare cur_HTR cursor for
select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid

open cur_htr
fetch next from cur_HTR into @Uniqueid, @HTR

while @@fetch_status = 0 begin
update com_hpd_helpdesk_history
set total_hours_to_resolve = @HTR
where case_id_ = @Uniqueid
and dateasat = @dateasat
fetch next from cur_HTR into @Uniqueid, @HTR
end

close cur_htr
deallocate cur_htr
...

This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?

View 10 Replies View Related

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

Aggregating Member Properties Or Not Aggregating Distinct Data

Feb 23, 2005

I have an employee, who received an initial starting bonus of $50k.
This value will be static from day 1 to day n and will never change. I
want to see the intial starting bonus, but any sets higher than the
employee dimension will need to aggregate the starting bonus. Is there
an easy way to do this?

If I just look at the data from an employee's perspective, I can do
this by making the measure a Min, Max, or Avg Aggregate function. But,
if for instance I want to view the data from the perspective of
departments, it would need to sum() the data instead (which min/max/avg
don't do).

If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.

View 4 Replies View Related

Putting Images In An Unpopulated Table.

Sep 1, 2005

Here's what I've got.

We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.

Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?

View 1 Replies View Related

Putting Table Structure Into Word Using Procedure

Jan 22, 2006

Hi there

I have nearly 1000 table. I need all the table structure in word document. Is it possible to take table structure using Stored Procedure. If it is How to do that.

Thanx in advance

by
yasi

View 8 Replies View Related

Putting Data From DataTable/DataSet Into SQL Server Table?

May 5, 2005

Hello,
I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater.
Dim ds As DataSet = New DataSetDim dtTableName As DataTable = New DataTable("dtTableName") dtTableName.Columns.Add("Description")dtTableName.Columns.Add("ItemNumber")dtTableName.Columns.Add("Quantity")dtTableName.Columns.Add("Price")ds.Tables.Add(dtTableName)
What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table?
Thanks!James

View 1 Replies View Related

Putting These Results Toghether

Nov 27, 2002

The two queries:

set dateformat 'dmy'
select count(c.id), e.name
from call c left outer join employee e
on c.req_id = e.id
where c.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(c.id) desc

set dateformat 'dmy'
select count(ch.id), e.name
from call_hist ch left outer join employee e
on ch.req_id = e.id
where ch.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(ch.id) desc

the results:

42NULL
34Dirk Deloof
13Annick Leirman
11Ronny Loosen
9Geert Benoot
9Nicole Ferrari
8FLOCK
8Mosselmans Christoph
7Geert Pets
7Mireille Dutrieue
6johan
6Laurent De Schrijver
5Jeanette De SChrijve
5Marc De Vlieger
5minerva
5Pascal Saesen
5Rik Haghebaert
5Sonja Van Kerckhove
4Bcatron
4Luc Willems
3Brigit Brocken
3euroadmin
3Francine Kopp
3Luc Steyaert
3Marie-Rose Buysse
3Marnix Van Steirtege
3Mattias Denys
3Pieter Frooninckx
3Reserve
3Rik De Scheemaecker
3Thierry Linard
2Carlos Van Alboom
2Dorine Sierens
2Els Poelman
2Jean Claude Vermeir
2Katrien Colman
2Kim Impens
2Kris Lejeune
2MEDreserve01
2Roger De Wilde
1Agnes Lebon
1Carla Van Den Broeck
1Eric Vlaeminck

and

118NULL
58Marie-Rose Buysse
47Dirk Deloof
45Ronny Loosen
43Annick Leirman
41Geert Pets
38FLOCK
38Pascal Saesen
28Teamleiders afwerkin
24Kim Impens
22Ilse Soetens
22Rik Haghebaert
22Severine Balduck
21Teamleiders print
20Mosselmans Christoph
20Jeanette De SChrijve
19Geert Benoot
19Francine Kopp
18Geert Meuleman
17Rik De Scheemaecker
16johan
16Katrien Colman
15Gaby Eloot
14Kris Lejeune
14Gilbert Callebaut
14Laurent De Schrijver
13Els Poelman
13Luc Steyaert
11Marnix Van Steirtege
10Frans Hoogewijs
10Sonja Van Kerckhove
10Dorine Sierens
9Eric Vlaeminck
9Thierry Linard
7Frederic Denis
7Michel Poppe
6Carla Van Den Broeck
6Pieter Frooninckx
5Katlijn Poleyn
5MEDreserve01
5Mireille Dutrieue
5Agnes Lebon
4Guido Antoin
4Onderhoud
4minerva
4Jeanette Van Brussel
3Roger De Wilde
3Sofie Gabriels
3Verf2
3euroadmin
3Marc De Vlieger
2Luc Willems
2MEDRESERVE07
2Regina Decoster
2Monique Kohl
2MEDRESERVE04
2Portier
2Bcatron
2Pierre Hanet
2Tgabriels
2Isabelle Torrelle
2Nicole Ferrari
1Robert Zwaak
1Carlos Van Alboom
1testuser
1Brigit Brocken
1Reserve
1Opleiding
1Verf

How do I put these results in one? I need not two but one Query.
Please help me. Thanks

View 2 Replies View Related

Putting Query Results In A File

Sep 23, 1999

Hi,

In 6.5 you could run a query and then make a flat file with whatever format
you wanted.

I cannot find that option in 7.0. Can someone please tell me where it is????


Thanks,
Dianne Watson

View 5 Replies View Related

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?

Jun 25, 2007

Hi, all experts here,



I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.



With best regards,



Yours sincerely,



View 11 Replies View Related

Power Pivot :: Measure Results Limited By Fact Table Dates Instead Of Date Table

Sep 17, 2015

I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):

StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)

Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?

View 6 Replies View Related

Trouble Putting Sub Report Results In Columns

Apr 30, 2008

Dear all,

I have a sub report that returns a variable amount of results. On occasion many results will be returned and cause one or more extra pages to be created.

The results from my sub report are not very wide so what I would like to do is have the results roll over from the left hand side of the page to the right hand side of the page and then continue this pattern on sunsequest pages until all results are displayed. Very similar to newspaper columns.

Is this possibe? If so how can I go about doing this?

Many thanks in advance,

Trevor Keast.

View 1 Replies View Related

SQL 2012 :: Delete From One Table Based On Results Of Other Table

May 28, 2015

I have this table:

with actividades_secundarias as (
select a.*, r.Antigo, r.Novo, rn = row_number()
over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo)
from ACT_SECUNDARIAS a inner join
((select

[Code] ....

I want to make a delete statement like this:

select * into #table1 from actvidades_secundarias where rn>1
Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae

But it seems that I cant delete like this.

View 5 Replies View Related

Selecting SUM From One Table As Field In Results Of Rows From Another Table.

Nov 20, 2007

Hi,
These aren't my tables, but lets dumb it down for my sake!

I have 2 tables:

CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount

There are several records in TransactionInfo for each record in CompanyInfo.

I want to:

SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)

How can I do this? Can I?

View 4 Replies View Related

How To Store My Results To Another Table

Dec 3, 2007

Hi guys, now i was able to get the results of the query i made. and the remaining problem now is how can i store the results to my database? here's the my code: protected void btnGenerate_Click(object sender, EventArgs e) { _con = new SqlConnection(database.evgConnection()); _comm = new SqlCommand("EV_usp_SearchNoticeLocation", _con); _comm.CommandType = CommandType.StoredProcedure; _comm.Parameters.AddWithValue("@Locationid", ddlLocation.SelectedValue); _adapt = new SqlDataAdapter(_comm); DataSet objDataset = new DataSet(); _adapt.Fill(objDataset); GridView1.DataSource = objDataset; GridView1.DataBind(); foreach(DataRow dr in objDataset.Tables[0].Rows) { Response.Write(dr["Docownerid"].ToString()); } } how can i store all the Docownerid to another database table? 

View 1 Replies View Related

Lookup Results In Another Table

Jul 11, 2013

I have a table "Ratings" in which I store rating categories like the following:

FROM TO Rating
1 10 1
11 20 2
21 30 3

In another table "Results" I have the actual results like:

Employee ID Results Rating
120 6 ?
130 18 ?

Now I would like calculate the rating in the results table in the rating column (where "?" in the above example)

How can I do that with an SQL update command - maybe using a Case statement - but I don't know how to do that by looking up values from another table...

View 5 Replies View Related

Getting Results To Display On A Table

Jun 10, 2006

Hi this is the first time i am using sql and i have a problem with displaying the data in the way i want.

i have the following table:

EmpID Cust_Name

1 Mary
1 John
2 David
2 John
Annd i am trying to get it to display like this on the UI

EmpID Cust_Name
1 Mary, John
2 David, Andrew

is there anyway to do this?

View 1 Replies View Related

Getting Two Query Results Into 1 Table

Nov 29, 2007

SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG
FROM dbo.S_PATIENTS
WHERE (PATNT_REFNO_NHS_IDENTIFIER IS NOT NULL)



SELECT dbo.S_PATIENT_IDS.END_DTTM, dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
FROM dbo.S_PATIENT_IDS LEFT OUTER JOIN
dbo.S_REFERENCE_VALUES ON dbo.S_PATIENT_IDS.PITYP_REFNO = dbo.S_REFERENCE_VALUES.RFVAL_REFNO
WHERE (dbo.S_PATIENT_IDS.ARCHV_FLAG = 'N')

i want to select the results from the querys into one table how would i do this ?

View 6 Replies View Related

Joining Two Results From Same Table

Apr 9, 2008

Dear friend, the following is my query.

i am retreiving from the same table with different condition

1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'

2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'

this two query gives resulsets

i need to join this two resultsets. please give me sample query to join this two result sets

View 7 Replies View Related

Pivoting A Results Table MSSQL

Nov 12, 2007

I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber    Product       OrderQuantity---------------        ---------------    ----------------------0608                Prod1          30608                Prod2          120608                Prod3          2 What I am after is for the results to display something like this.OrderNumber    Prod1   Prod2   Prod3 
---------------        ---------   ---------  ---------
0608                3           12       2 This is using SQL Server ver 8.0  

View 3 Replies View Related

How Do I Store Results In TEMP TABLE?

Jul 7, 2005

I'm writing a search engine and I want to make a search and then, after
I've recieved the results, put them into some sort of temporary(in
mememory) table so that I can do another query on that temporary table.

I saw something about  temp tables (in T-SQL)  in the
help guide, but I still want to know how to do a query on a regular
table and then store those results in a temporary table to perform a
query on that. Could anyone show me some example or something?

View 3 Replies View Related

Writing Query Results To New Table

Dec 4, 2000

I have written several scripts to pull in nested info to the analyzer window. How do I get this data to write to the new table I have created in the database? here is the current script:

select Hierarchy_List.Hierarchy_Label as Hierarchy_Name,
Hierarchy_List.hierarchy_ID as Hierarchy_ID,
Hierarchy_List.Parent_ID as Parent_ID,
frequency_item.manufacturer as Motor_Make,
frequency_item.model as Frame
from hierarchy_list full outer join Frequency_item
ON HIERARCHY_LIST.HIERARCHY_ID = frequency_item.HIERARCHY_ID
where parent_id in (select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id=0)
and parent_id<>0) and parent_id<>0) and parent_id<>0)
and parent_id<>0 and frequency_item.description = 'motor'

I need to move this data to the VAER.Al_Machines and the column names are the same. I can move data via DTS, but it won't work on this because the nested info. Is there a script addendum I can add to this to execute both the search and the transfer in one job so I can automate it? Thanks for any help.

Mick Flanigan

View 2 Replies View Related

SQL Results Table Won&#39;t Display In Netscape

Mar 13, 2001

Hi,
I made a table to display information from northwind database. It displays fine on IE5.0 but will only show the first line in netscape.
Do you know what it may be???

View 2 Replies View Related

Populating A Table With The Results Of A SQL Query

Jul 12, 2004

Is there any way i could take the first 50 results of a sql query and store them into 1 table in access and take the next 50 and store them into a second table in access? Is there any SQL statement that will direct where the output gets directed
to?

OR is there a way to have the sql reults paird up with a autonumbered ID?

Doc

View 2 Replies View Related

Create Table From Query Results

Jul 10, 2007

Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1


ID col1 col2

1 a a
2 b d
3 c f

this would be my new table named table2

ID col1 col2 col3

1 a a aa
2 b d bd
3 c f cf

this new table has an additional column by concatenating col1+col2
tried this procedure but is not working

CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2

thanks

View 2 Replies View Related

SQL Agent Job - Logging Results To A Table.

Oct 11, 2007

Is it possible to log the results of a SQL Agent job to a specific table?
I see there is a tick box for logging to a table for each step in the job but it does not allow me to tell it which table I want it to log to.

Can someone give instructions on how to log a SQL Agent job and/or steps within the job to a specific table?

Thanks

View 2 Replies View Related

Combining Two Field Results From Same Table

Mar 19, 2012

My application saves customer email addresses in two different fields in ym table

How do i combine two fields from the same table in a select statement?

I've tried the following:

Select EmailAddy + ', ' + FriendsEmail AS Expr1
FROM dbo.Contacts

But all I get are the results from one field.

View 4 Replies View Related

Return All Results From Left Table

Apr 16, 2013

I have a table that lists states that I need to see order info from. I am attempting to create a SQL query that displays the production data for those states. So if the state is in my saleState table I want it to show in my query result even if there were 0 sales to that state. Below is the syntax I am trying, but if the count is 0, it is not returning the sales state it is omitting it.

Code:
Select a.state, COUNT(b.recordID)
From saleState a left outer join tblSales b
On a.state = b.state
And b.orderDate between '01/01/2012' AND '12/31/2012'

Need to point out the error in my query syntax?

View 14 Replies View Related

Do QA Results Into A Temp Table Automatically?

Apr 3, 2008

Does anyone know if the results from a query run in QA automatically go into a tempoary table? For instance if ive just run a query that took 5 minutes but it was a normal select query and after it ran i realised i wanted to query the results could i run a simple command to mke a copy of the result set rather than run the original query into a temp table that would then take a few minutes again?

View 1 Replies View Related

Results Of Union Into Temp Table

Apr 3, 2008

This may be a dumb question, but I can't seem to get the syntax right. I have two temp tables that have the same columns, I want to do a union on them and store the results in a temp table. Any ideas?

Ie.

select * from #tmpTable1
union
select * from #tmpTable2
into #tmpTable3

Thanks!!!

View 4 Replies View Related

How Do I Save My Query Results Into New Table

Apr 29, 2008

How do I save my query results into new table.... The ORIGINAL COLUMN Of course before parsing--- But the only data I want is in the three no name columns---(NO Column Name),(NO Column Name),(NO Column Name)I don’t want the original column saved back but I think it existing in the final query is blocking my Insert Into---

View 2 Replies View Related

Creating Table From Query Results

Feb 6, 2014

I have this query;

select c.name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
where t.name= 'Hosea_tblDATA_NOTES'and c.is_identity = 0
order by column_id

and I get these results;

NOTE_ID
NOTE_DESCRIPTION
NOTE_TEXT
NOTE_STATUS
NOTE_STARTDATE
NOTE_ENDDATE
NOTE_AUTHOR
NOTE_LASTUPDATE

I was thinking if it is possible to create a table from the results of this query, my table name(in this case 'Hosea_tblDATA_NOTES') will be a parameter, that whatever table name I pass, with the results I get I will be able to create another table, assuming all data types are varchars, but it should be generic.

I want to create a new table with the about of that query, that output has to be my columns.

View 1 Replies View Related

DBCC INPUTBUFFER Results Into Table

May 10, 2007

Does anyone know if there is a simple way to get the results of a DBCCINPUTBUFFER() request into a table? I have a process for monitoringactivity that will give me the results of sp_who2 into a temp table,and want to scroll through the active connections and get the inputbuffers into another table for review:Insert into #TmpWhoexec sp_who2 'active'Something like that with the dbcc command.I am using SQL 2000 SP4.Thanks,Tom

View 3 Replies View Related







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