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