2 Grouped Counts On 1 Table

Oct 10, 2007

I am trying to get a count of a job received date and a job closed date from the same table. I need these counts to be grouped by which team they are for. This is what I have and it isn't working:

SELECT HEAT.dbo.Profile.PrimaryTeamName,
COUNT(CallLog1.RecvdDate) AS OpenCalls,
COUNT(CallLog2.ClosedDate) AS ClosedCalls
FROM HEAT.dbo.Profile,
HEAT.dbo.CallLog CallLog1,
HEAT.dbo.CallLog CallLog2
WHERE HEAT.dbo.Profile.CustID = CallLog1.CustID AND
HEAT.dbo.Profile.CustID = CallLog2.CustID AND
CallLog1.CallID = CallLog2.CallID AND
((HEAT.dbo.Profile.PrimarySupportGroupID = 'ATS') OR
(HEAT.dbo.Profile.PrimarySupportGroupID = 'ats'))
GROUP BY HEAT.dbo.Profile.PrimaryTeamName,
CallLog1.RecvdDate,
CallLog2.ClosedDate
HAVING (CallLog1.RecvdDate = CONVERT([VARCHAR](10), GETDATE(), 120)) OR
(CallLog2.ClosedDate = CONVERT([VARCHAR](10), GETDATE(), 120))

I can get both counts to work individually, but as soon as I try to get them to go together I get some very interesting returns. I am drawing a complete blank as to what to do. Any info would be very helpful.

Thanks

View 5 Replies


ADVERTISEMENT

Interactive Sort For A Grouped Table

Aug 24, 2007

I'm trying out the interactive sort feature (and have searched high and low for this answer), but can not make the sorting work if I have the data in the table grouped. If I remove the group, the sorting works fine but the output is not acceptable due to the duplicated records in the list.

Can someone steer me in the right direction?

View 1 Replies View Related

Update Table - Based On Sum Of Records Grouped?

Oct 18, 2014

I have the following table

Code:
10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906
60012014-09-01 00:00:00.000WRK1-Z-1280180
70012014-09-01 00:00:00.000WRK1-Z-9990306
80022014-09-01 00:00:00.000BH1-Z-0800480
90022014-09-01 00:00:00.000CHO1-Z-0800480
100022014-09-01 00:00:00.000WRK1-Z-0800480
110022014-09-02 00:00:00.000BH1-Z-0800480
120022014-09-02 00:00:00.000CHO1-Z-0800600
130022014-09-02 00:00:00.000OT11-Z-0800120
140022014-09-02 00:00:00.000WRK1-Z-0800600
150012014-09-02 00:00:00.000BH1-Z-1280480
160012014-09-02 00:00:00.000CHO1-Z-1280480

What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.

EG for 2014-09-01 for empref 001 the result would be

Code:
0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306

IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)

I can write a query to do this individually but how can I so this as a query for the full table.

Code:
declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate

View 2 Replies View Related

Update Table Based On Sum Of Records Grouped?

Oct 18, 2014

I have the following table

10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906

[Code] ....

What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.

EG for 2014-09-01 for empref 001 the result would be

0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306

IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)

I can write a query to do this individually but how can I so this as a query for the full table.

declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate

View 1 Replies View Related

Getting Table Counts

Jul 20, 2005

I want to get a resultset of every table in the database, with thecurrent record count of each. What is the easiest way to do this?I can get the list of tables with:Select s.name from sysobjects s where xtype = 'U'each s.name is a table name, but I'm not sure how to join a record countcolumn to the resultset.Thanks,RickN

View 4 Replies View Related

Script For Table Counts

Feb 13, 2006

I need a generic script that will query every table (minus system tables) within a database and generate record counts for each table.

Anybody have a script that will do this ???

Thanks in advance,
Nancy

View 6 Replies View Related

Inserting Counts Into A Table

Feb 20, 2007

Hi:

I would like to count repeating field values in a table, and to insert the counts into the same table. I have managed by having a temp_table, into which I insert the values:

insert into temptable values (select count(*) as Count, fieldname from table group by fieldname)

I then do a join on table.fieldname=temptable.fieldname, and update table.count with temptable.count.

Is very cumbersome, and does not update counts when table changes.

Is there a way to put in a calculated member, or to put the vaues of auto stats into the count field?

TIA

Kar

View 4 Replies View Related

Multiple Counts From One Table

May 7, 2008

Hi, my code repeats the same correct count numbers for every person in a query. I need them to count differently for each person in a table. I am using Access 2007 for now. Thanks.

SELECT tblOutlookTask.AssignedTo,DCount("*","tblOutlookTask","PercentComplete=100") AS Expr1, DCount("*","tblOutlookTask","PercentComplete<>100") AS Expr2
FROM tblOutlookTask
Group By tblOutlookTask.AssignedTo;


I also tried this but it says 'missing operator on the second line' doesn't like the when in case maybe?

SELECT
COUNT(CASE WHEN PercentComplete=100 THEN 1 ELSE NULL END) as Completed,
COUNT(CASE WHEN PercentComplete<>100 THEN 1 ELSE NULL END) as Active,
COUNT(CASE WHEN PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-7 And DueDate<Date() And DueDate>Date()-7 THEN 1 ELSE NULL END) as Overdue,
COUNT(CASE WHEN tblOutlookTask.PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-14 And tblOutlookTask.DueDate<Date() And tblOutlookTask.DueDate>Date()-14 THEN 1 ELSE NULL END) as Offtrack
FROM tblOutlookTask;

View 5 Replies View Related

Validating Table Row Counts Across Servers

Feb 9, 1999

I periodically transfer large databases across servers and have had problems with tables on the target being empty (to rule out errant deletes by other developers). I'd like a way to do a table by table row count comparison between the two servers, similar to the way sp_compare_db (found here at Swynk) does on a single server. Even a method of referencing tables by including the server name would help.

View 1 Replies View Related

Frequency Counts From Huge Table

Mar 12, 2008

Hey guys,

I have a table with about 80 columns and 400 millions records. Each columns has different responses that I need to get frequency for. I need to get counts for each response from all the columns... I have a query that does it, but it will run forever... what is the best way to do so?

My starting query:

select res, sum(cnt) from
(
select col1 res, count(*) as cnt from table1 with (nolock)
group by col1
union all
select col2 res, count(*) as cnt from table1 with (nolock)
group by col2

........................

select col80 res, count(*) as cnt from table1 with (nolock)
group by col80
)a group by res

View 1 Replies View Related

Grouping By Month In Common Table Expression Counts Wrong

Sep 26, 2012

I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;

Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date

[code]...

I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.

View 3 Replies View Related

Integration Services :: Logging Record Counts To Execute Task For Table Update

Jun 20, 2015

Have an SSIS package running great in 2008R2. It generates several flat files based on inline database queries. The first step of the package inserts a record into a log stats table and the last step of the package updates this record with the package name, run time and execution status. Now I need to add the records counts for each flat file to the log table. 

Is there a way I can update one field for run counts with each of the counts for each file. So the [run counts] table column would look something like:

file1: 43522
file2: 645367
file3: 7883

Is it possible to store the record counts and flat file names in variables then concat them at the end when updating this record?

Or, is a better way to just insert/update a new record for each flat file step and log the counts for that file for its own record?

In either case, how I can capture the file count and pass that to the update statement.

View 4 Replies View Related

Grouped By Month

Jul 12, 2006

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?

View 1 Replies View Related

Get The Max() From Grouped Set With Varchar

Jan 16, 2008

I want to grab the Max footage in this query, there are two values for each group, so i should be able to get the bigger number. The only problem is, its in varchar format, so for some reason its only grabbing the ones with the highest number to the left. So instead of grabbing 12ft, it says 7ft is the max. What am i doing wrong. is there another way to go around this. This is supposed to be a subquery for a a much bigger query.



Code Block
SELECT MAX(Qry_Questions.Response) AS Max_Footage,
CONVERT(varchar, dbo.Qry_Questions.Date, 110) AS shortDate,
Qry_Sales_Group.salesperson_purchaser_code,
Qry_Questions.StoreName,
MIN(Qry_Questions.Response) as Min_Footage

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer AS Customer

ON Customer.Customer_Code = dbo.Qry_Questions.Customer COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Category AS CusCat

ON CusCat.Customer_Category_Id = Customer.Customer_Category_Id

WHERE (dbo.Qry_Questions.[Question Code] IN ('SN017', 'SN015')) AND (CusCat.Customer_Category_Id = 6201)
GROUP BY CONVERT(varchar, dbo.Qry_Questions.Date, 110), Qry_Sales_Group.salesperson_purchaser_code, dbo.Qry_Questions.StoreName






This displays this:
Max Footage Date store name Min Footage
8ft 09-07-2007 12140 PETCO #1437 8ft
8ft 09-10-2007 12069 PETCO #698 8ft
8ft 09-11-2007 12106 PETCO #1916 8ft
8ft 09-11-2007 12108 PETCO #683 4ft
8ft 09-13-2007 12140 PETCO #918 12ft

View 5 Replies View Related

Display Grouped Records

Apr 11, 2008

I have the following sql table and would like to group the results by "StoryTitle" to display in a datalist. The Storytitle field in the datalist is a LabelID     StoryTitle       StoryAuthor      Rating     StoryID    Comments
1      About Me       goodyone            6          20           Great Story
2      About Me       goodyone            5          20           Love your work
3      Hello World    magicme             6          26           What a Story
4      Hello World    magicme             7          26           This Reminds me of...
I know i have to do something in the SQL Datasource statement. Not sure how to do it. here is my statement below
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BrillConnectionString1 %>"
SelectCommand="SELECT * FROM [iaw.comments]">
</asp:SqlDataSource>
 

View 5 Replies View Related

Pains Of A Grouped Subquery

Feb 8, 2000

I have a query that will not run in Transact SQL:

SQL = "SELECT email
FROM Contacts
WHERE email = ANY (
SELECT email
FROM Contacts
GROUP BY email
HAVING COUNT(email) > 1)"


It is supposed to return all of the records that have duplicate email addresses (i.e. the subquery finds the duplicate addys and the main query finds all of the records with those emails.)

Help would be great.

View 2 Replies View Related

Updating Grouped Rows...

Jul 9, 2004

I am new here, and I am sure this is a simple query, but im being forcefed database chores from my job, so i have to teach this stuff to myself/get help from places like this,
I need help with a query,
lets say that there are columns a,b,c,d,e,f,g
if columns c,d,e are the same, than I want the info in column g changed to the info in column b in the first record of that group
the reason I am doing this is,
I have like items (sku's) grouped in my database, and i want to create a blanket part number for skus that have matching descriptions which is the information in colums c,d,e,
I want to link them to the part number of the first product with that description, and add that part number in a new column at the end of the grouped SKU's record

this is what i start out with

a b c d e f g
2 4 5 6 9 8
2 5 5 6 9 9
2 7 5 6 0 5
1 2 3 4 5 6
1 3 3 4 5 7
1 4 3 4 5 8
1 5 3 4 5 9

i want to end up with
a b c d e f g
2 4 5 6 9 8 4
2 5 5 6 9 9 4
2 7 5 6 0 5 7
1 2 3 4 5 6 2
1 3 3 4 5 7 2
1 4 3 4 5 8 2
1 5 3 4 5 9 2

View 2 Replies View Related

Data Grouped By 20 Minutes?

Nov 28, 2005

Hi,I want to get the count of rows per each 20 minutes, is that possible?Is there a date function or any other function that I can use in Groupby clause, which will group the data of every 20 minutes and give methe count?Thank you.Vidya

View 3 Replies View Related

How To Get Last Records In Grouped Query.

Jan 23, 2007

Hi,I want to create query where I could group records by quarters, and getthe last record in each group.e.gCreate Table MyTable(Value [float],date[datetime])Insert into MyTable (value, [date]) values (100, '1-1-2000')Insert into MyTable (value, [date]) values (110, '1-2-2000')Insert into MyTable (value, [date]) values (120, '1-3-2000')Insert into MyTable (value, [date]) values (130, '1-4-2000')Insert into MyTable (value, [date]) values (140, '1-5-2000')Insert into MyTable (value, [date]) values (150, '1-6-2000')Insert into MyTable (value, [date]) values (160, '1-7-2000')Now I would like to get this data grouped by quarter, and get the lastvalue from each quarter. So here I would like to get result like this(120, q1 -2000)(150, q2 -2000)(160, q3 -2000)I know how to create aggregate functions but I have problem with gettingthat last record from each group.*** Sent via Developersdex http://www.developersdex.com ***

View 2 Replies View Related

How To Obtain Just The Last Record Grouped By

Jul 20, 2005

Sorry for my englishI have a table that contains data of career about the person (staff)like this ...EMATREANIDEMEIDEGIIDecc. ..ecc. ..ecc. ..ecc. ..1199912312002123111200311000321999123120021231122003110003419991231200073115199912312001131161999123120021231162003110003719991231200212311whereEMATR is a not unique key (person ID)EANID, EMEID, EGIID (assembled) are the date of the last advance ofcareerI want extract from the table below only the actual position,therefore a view that return this rowsEMATREANIDEMEIDEGIIDecc. ..ecc. ..ecc. ..ecc. ..1200311000322003110003419991231200073115199912312001131162003110003719991231200212311I hope of to have been explainThank you from Maximiliano (italy)RE-Sorry for my english--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

View 1 Replies View Related

How To Match Grouped Rows In MS Sql

Apr 8, 2008

Hi,

Data in my table is loking like this.













InvID
ItemInputDtTime
SrNo
ItemId
Rate

Qty
GroupID

8252
07-04-2008 15:51
1
001138
9.99
1
1

8252
07-04-2008 15:51
2
000009
0.5
1
1

8252
07-04-2008 15:51
3
000016
1
1
1

8252
07-04-2008 15:52
4
000207
NULL
1
1

8252
07-04-2008 15:52
5
000203
NULL
1


1



8252


07-04-2008 15:52
6
001138
11.9
1
2

8252
07-04-2008 15:52
7
000016
1
1
2

8252
07-04-2008 15:52
8
000009
0.5
1
2

8252
07-04-2008 15:52
9
000207
NULL
1
2

8252
07-04-2008 15:52
10
000203
NULL
1
2

8252
07-04-2008 15:52
11
001138
11.9
1
3

8252
07-04-2008 15:52
12
000009
0.5
1
3

8252
07-04-2008 15:52
13
000008
0.5
1
3

8252
07-04-2008 15:53
14
001106
5
1
4

8252
07-04-2008 15:53
15
001000
10
1
5

8252
07-04-2008 15:54
16
001202
10
1
6

8252
07-04-2008 15:54
17
001117
13.9
1
7

8252
07-04-2008 15:54
18
001113
NULL
1
7

8252
07-04-2008 15:54
19
001117
13.9
1
8

8252
07-04-2008 15:54
20
001113
NULL
1
8

8252
07-04-2008 15:54
21
001117
13.9
1
9

8252
07-04-2008 15:54
22
001115
2
1
9




same colored items are grouped by GroupID. Each group contains ItemID, Qty and rate.
How can i compare IteamID, Qty and Rate of each group with other group's ItemID, Qty and rate?
OR
How can i get number of groups with same ItemID, Qty and rate?


All I need to do by T-SQL


Thanx

View 14 Replies View Related

Grouped Information From Two Tables

Sep 21, 2007



Hi,

I have two statements which when I join by a union statement give the folowing:

2005 11 0.000000
2005 12 0.000000
2006 1 0.000000
2006 1 50813.058500
2006 10 0.000000
2006 11 0.000000
2006 12 0.000000
2006 12 63224.511250
2006 2 0.000000
2006 2 59164.234500
2006 3 0.000000
2006 4 0.000000
2006 5 0.000000
2006 6 0.000000
2006 6 82442.570750
2006 7 0.000000
2006 7 61809.497750
2006 8 0.000000
2006 9 0.000000
2007 1 0.000000
2007 2 0.000000
2007 3 0.000000
2007 4 0.000000
2007 5 0.000000
2007 6 0.000000
2007 7 0.000000
2007 8 0.000000


What I want is to merge the values
2006 1 0.000000
2006 1 50813.058500

into one row, the months with zero figures are required.

Thanks

View 1 Replies View Related

Show All Grouped Rows

Aug 22, 2007

So i have a data table with a group in it.

I want a row for every item and i'ts count displayed. the problem is, if the count is 0, the row is not displayed.


how can i make all the rows show?

View 3 Replies View Related

Top 10 Chart Grouped By Month

Jan 19, 2007

Hello,

I have a bar chart that I need to show the top 10 items by Month. What I get currently is a chart by month with the total Top 10 Items (rather than the top 10 per month). I have tried adding a scope onto my Value expression but that didn't help.

=countDistinct(Fields!incident_ref.Value, "catGrpDate")

Please Help!

View 1 Replies View Related

Select Into Parameters Grouped Data

May 20, 2007

Hi all,

This is probably one of the easier questions you get, but I have brain freeze and just can't do it and very rusty.

How can i assign the the values from sql below into variables e.g I want to get all new, pending and cancelled Leads from the rows returned which will come from the status id.


SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
FROM [dbo].[tLead] L
Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
Inner Join dbo.tClick C on linkClick = ClickID
WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
Group by LeadStatusID
Order by LeadStatusID asc

Cheers

View 10 Replies View Related

Querying For Calendar, Grouped By Hour?

Jul 20, 2005

I've been scratching my head on this for quite awhile and it has me stumped.I hope to define a query which I can use to fill a "day planner" type ofcalendar. Although I've see a lot of these, only one has had what I thinkis a really nice feature - it collected into groups all events thatoverlapped into contiguous blocks of time. The net result of this is thatit becomes possible to output a calendar (html table) that is much lesscluttered. So I want to use this same idea for my own little project.The trick is that events may (or may not) start and end such that theyoverlap (completely or only at one end). I only am concerned with events ona given day.My "events" table contains eventtitle, date,starthour and endhour. Hoursare numbered from "0" to "23". I also have a lookup table of the "hours ofthe day" with which I did a JOIN to include the "missing" hours (where therewere no events - making a query that returned events for each hour and nullsfor each hour that had no event scheduled for it. But this makes too many"blank" rows, which is part of the clutter to which I referred.I've been able to construct queries that work in some cases, but not all.I've reread my copy of Celko's SQL For Smarties and came close, but nocigar. Where he discusses hotels and room-nights is part of the solution Ineeded, but my need goes beyond that quite a bit.Basically, I need to calculate one or more "spans" that contain contiguousgroups of start/end times. By knowing the number of hours spanned, I canthen use that for a <TD rowspan='n'> to collect my data like I want.I seem to keep hitting all around the solution. Maybe there isn't one (thatis purely a SQL solution). Or maybe I'm just looking at the problem thewrong way.So I thought I'd see if anyone here might point me in the right (or at least"new") direction. I've been looking at this for so long, I'm probablyoverlooking some simple and obvious trick to do this. Or maybe I'll getlucky and someone has seen or done exactly this already and can provide asolution?Incidentally, I've avoided utilizing a stored procedure or making a numberof temp tables to collect intermediate results, as I might need to port thisto a "dumb" database that does not provide such facilities. Maybe that'simpractical?thanks in advance,--

View 3 Replies View Related

Unable To Clear Grouped Report

Mar 16, 2007

I had an SSRS 2005 report that had the lowest level grouped instead of printing a detail line (the reports display summary tables that have all the necessary grouping already applied). Every time I tried to clear the grouping, the report designer crashed. In the end I created the report again from scratch.

Should I have been able to clear the grouping or is the conversion from a detail line to grouping, a one-way process?

View 4 Replies View Related

Aggregate Data Grouped Lists

Apr 15, 2008

Hello,

I`ve created a table in a list that is grouped by years. Result is a Report, that consists of several years (List grouped by years, each containing the table with the data of the year).

Now I want to aggregate some numbers of the table on each page (year) of the list.

The sum()-function - doesn't matter which scpoe I use - always aggregates only the values of the actual list (Year), not considering the years before.

Can anyone help?

Thanks a lot -

Markus

View 2 Replies View Related

Get, Grouped, The Records With Highest Value In A Given Field

Apr 12, 2008

Hello all,

Here is an SQL Server 2005 table that lists player scores:





Code Snippet

+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 7 | 01/04/08 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Liz | 25 | 25/12/07 |
| Liz | 11 | 10/04/08 |
| Mark | 11 | 22/03/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
I'd like to get, for each player, his/her best performance, including the date. Concretely, my SELECT query should return:





Code Snippet

+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 25 | 25/12/07 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
does someone have any idea ?

Thx

View 3 Replies View Related

Getting Top 10 In Drilldown Matrix Report - Grouped

Jun 14, 2007

Hello,



I have a drilldown matrix report similar to the one below...state - drilldowns to sales person which lists the sales for a particular product...in each state there are usually more than 10 names, however i would only like to see the TOP 10 name appear for the sale off that product



My SQL is as follows



SELECT Sales,Consultant, ProductNo, State, Sum(Value) As SumOfValue,

From tblSales
where productNo = 2000

ORDER BY SumOfValue Desc;



If I put in the select TOP 10 it returns only the top 10 for all the states however i need the top 10 off EACH state, is there a way to filter this..or better yet a sort button which will show the top 10 only.



Sales for product

NSW charlie brown 25

bob snow 20

william tell 10

....50 sales reps..etc



WA Charles manson 34

fiona apple 20

peter 1 ....more sales reps etc

View 2 Replies View Related

Matrix Grouped By Day Part And Date

Feb 13, 2007

I am wondering has anybody ever created a Matrix in a report grouped by Day Part (10-2 Morn, 2-6 Aft, 6-10 Eve etc) and Date?

I would like to see a report with an output such as

13/02/2006 14/02/2006 Total
Morn Aft Eve Morn Aft Eve
Mr A 2 4 5 2 6 2 21
Miss B 8 8 1 1 4 5 27

I have a DB table which records the datetime each time a viewer changes TV channel. The report is a summary by user of each channel change By Day Part with Each Date

I can do this easily enough by just grouping on Date but now require another level of detail.

View 3 Replies View Related

Getting Order Number Into Query Grouped By Something

Apr 30, 2008

I have a table of transaction that includes student ids and dates. I need to select all records from the table and include a new value that is the sequential transaction numbered for each student with the oldest transaction for each student being numbered one, the next oldest numbered two and so on. So the result should look like student1, 10/1/2000, 1, student1, 10/15/2000, 2, student1, 2/12/2001, 3, student2, 9/1/1999, 1, student2 10/2/2000, 2, student2 , 12/15/2000, 3, student2, 11/4/2001, 4 and so on.

View 8 Replies View Related

SQL Server 2008 :: Grouping By Records That Are Already Grouped?

Jul 27, 2015

I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...

id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed

Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.

id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed

View 1 Replies View Related







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