How to write a query to count AGE by category (Exec or non Exec)
sample are as follow:
----Age Group---------
Category: Age <25 age 25-35 age 45-50 Total
Exec 2 1 3
Non Exec 10 5 5 20
Grand total: 23
i am using this code :
SELECT MAX(user) AS lastuser, category
FROM journal
GROUP BY category
HAVING (COUNT(category) > 1)
it works but returns 1 line by category >1
i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1
if 1 category has only 1 user i must not keep it
i am not shure to be clear :-)
thank you for helping
Hi,
I have a simple table:
Categories
----------
CategoryID
ParentID
Name
I want to associate my Products to a category so that I can search by category....ok. If I just have a simlpe table:
CategoryProducts
-------------------
CategoryId
ProductId
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I
click on Self Help while browsing, I want to see all items underneath
Personal Development, Spiritual and Meditiation. So my question
is is this a good way to store the product-category relationships, or
should I put many entries into CategoryProducts to keep the queries
simlpe and faster? Are they faster doing it this way? In this way there
would be 4 entries for a product in meditation. My personal idea
is that adding all entries up a tree arm of a category path will be
cumbersome to manage, but it does solve the problem of clicking on Self
Help and seeing all products that exist within sub-categories. I
am sure an SQL query would be able to work this out, but I dont know if
performance would be something to consider on an ecommerce site? Are
there any patterns fo rthis stuff - seems a reasonably repeatable
pattern for business sites?
Thanks,
jr.
Hello,
I'm trying to select the maximim value for certain zip codes, for example my table looks like this:
ZIP Category Value
90210 S1 5
90210 S2 10
90210 S3 2
90210 S4 3
10221 S1 24
10221 S2 3
And I want the end result to be each unique zip code with the category having the maximum value, for example 90210 S2, 10221 S1, etc
hiiiiiiiiii plz help me........urgent
my table is as follows
CREATE TABLE [dbo].[tbl_photo_gallery](
[image_id_int] [int] IDENTITY(1,1) NOT NULL,
[image_caption_vchr] [varchar](100) NULL,
[image_path_vchr] [varchar](200) NOT NULL,
[photo_cat_id_int] [int] NOT NULL,
[posted_by_vchr] [varchar](45) NOT NULL,
CONSTRAINT [PK_tbl_photo_gallary_1] PRIMARY KEY CLUSTERED
(
[image_id_int] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
now i wanna to get a randomly selected image_path_vchr(image path) from each photo_cat_id_int(category)
i m doing this:
select TOP (1) image_path_vchr from tbl_photo_gallery where photo_cat_id_int=1 ORDER BY NEWID()
to get random image_path_vchr from category 1....only single category.
but i wanna to use a simple query to get random image_path_vchr from each category without using any temporary table.....
plzzzzzzzz help me out......... thanks in advance
Given the data below, I have a couple needs:
1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)
2) Query to determine if any date ranges of the same category overlap
declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'
[code]....
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
View 1 Replies View RelatedThe following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
HelloI am using sql server 2005.I have two tables as described below.Table1UserID UserSales---------------------1 102 133 174 195 216 107 128 119 3110 2311 2412 1013 16Table2UserID Country----------------------1 Canada2 Canada3 Canada4 Canada5 Canada6 USA7 USA8 USA9 USA10 USA11 UK12 UK13 UKI want to get top 2 UserSales for each country and remaining should bedisplayed as Total as Others for that country.Can someone please help me with this query?RegardsAmit
View 1 Replies View RelatedLet's say you a 1000 records in the Employees table, who are spread over 40 different cities.
How would you get a breakdown of how many employees in each city ?
Do I have to loop with a Count(*) for each CityID, or something ?
There must be a more straightforward method.
hi.
I have a table to store salesman's performance..
as you can see at structure.jpg, Tom sold total 18 Processors (5 different modals) and sold 11 mainboards (4 different modals)
I want to report that
what are the TOP 3 Processor and Mainboard modals that Tom sold with the same query
I tried different group by methods. also subqueries.
I could't do
you see the results I need at result.jpg
What are the thoughts on using a categoy table? What I mean by this is to have a table used to hold simply a category identifier for other table(s).
Eg:
Hardware Table
Hadware (PK) | Category (FK) | Description | Finish | etc....
------------------------------------------------------------------------------
P1 | Plate | 4"x4"x1/4 plate | Painted |
HardwareCategories
Category (PK)
-----------------
Plate
Channel
Angle
Should this be done or simply provide a category field in the hardware table? I like the extra table because it kind of ensures that a user doens't add a plate with a category value "Plate", another user adds "plate", and another user adds "Plates", etc....
What are your thoughts if any?
Mike B
I'm trying to remove a read only/stand by database that I believe was a messed-up attempt at log shipping. When I go through the interface it says 'cannot remove database because it is set up for replication'. If I try and 'directly update' the sys tables (yea I know you can't do that anymore) I get the error:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
So my dilema is I just want to delete a database that was attempting to do log shippinig and is now stuck in read only/standby. How can this be done in SQL 2005?
Thanks,
Phil
I have a pretty simple query:
Select title, avg(score) from votes group by title order by avg(score) Desc
This returns a result like this:
Abbey Road 4.0The White Album 3.5Meet the Beatles 3.0
The values in the score field are always 1, 2, 3, 4, or 5
What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this.
Abbey Road 4.0 0 0 1 1 1 The White Album 3.5 0 1 0 0 1Meet the Beatles 3.0 2 2 2 2 2
I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets.
Any suggestions would be greatly appreciated,
Chris
I am trying to count a column in my tbl. I have a table with the following, What i am trying to do is count the number of days of dtAttendance for an individual where bitpresent = 1. So this individual should have 2 days of attendance. I wrote some code belwo but it does not seem to count correctly, some it does and some it does not.
intAssignedPersonnelID intUICID strSSN dtAttendance bitPresent intDrillStatus intMileage bitDatePayed210 1 333333333 5/26/2008 1 2 210 1 333333333 5/27/2008 1 2 210 1 333333333 5/28/2008 0 2
& "(select count(*) from saddotnet.dbo.tblAssignedPersonnel h where p.strSSN = h.strSSN and h.dtattendance <= p.dtattendance) " _
& "as count_attendance_as_at_dtattendance, p.dtattendance, bitpresent, intdrillstatus, " _
& "'<input name=chbxPresent' + convert(varchar(10), p.intAssignedPersonnelID) + ' type=checkbox>' as 'theCheckBox' " _
& "from cms.dbo.tblSIDPERS as s INNER JOIN " _
& "saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM where sidstrSSN_SM = left('" & SSN & "', 9)"
If I want to find out how many items of a field is in a table like Type1, Type2... to build a table like
Type1, 10
Type2, 25
...
Is is better to open the table -> Select Type From Table
and then go down each row and record the count of a type or is it better to
Select Distinct Type from table to get the types and then do each type's select to get the count
Select Count (1) from table where type = 'Type?'
Thanks,
Frank
I tried searching, but no luck.
I am trying to return a row count of a query...
I have a table that is grouped by a date. Some records have the
same date. I am just trying to return the total row count of the
query.
Here is the sql:
SELECT ActiveWeek
FROM wcWARS
WHERE CreatedBy = 9034 AND FlagComplete = 1
GROUP BY ActiveWeek
I just want to return the total grouped column.... I thought Oracle had
a ROWCOUNT function that returned the total rows of the query.
I was unable to find one for SQL Server.
**Note: I already tried @@ROWCOUNT ~ This returns the total rows of the table.
Thanks
Larry
Hello All,
I have two tables A and B.
A and B have column x in common. The relationship between A and B is one to many.
I would like to count the number number of x's in table B and group by the x and then join this result back to table a.
My attempt :
SELECT COUNT(T1.x), T1.x, A.meeting_invite_idFROM (SELECT meeting_invite_idFROM REMINDER) T1, AWHERE A.meeting_invite_id *= T1.meeting_invite_idGROUP BY A.meeting_invite_id
Anyone with any ideas.
hello people... sorry im new with this stuff, cant get it right... i need help...
suppose i have a table that has the stores in columns and and order count as a row:
Store1 Store2 Store3 Store4
ordercount 100 null null 30
assume that there are many stores listed in the table... how can i have the number of stores that has an ordercount?... ive tried some mdx queries but i still cant get it right...
Hi
I have two tables, Sales Headers and SalesLines. The SalesHeaders table will hold basic details of a sale, and the Sales Lines table will hold details of the items in the sale.
An example of tables are as follows:
SalesHeaders:
Sales_Ref, OrderDate, Customer_No
111,01/05,2008,301
112,01/05/2008,333
113,01/05/2008,309
114,03/05/2008,306
115,03/05/2008,309
...
SalesLines:
Sales_Ref, PartNumber, Qty
111,e4334,3
111,d434,1
111,r555,20
112,r332,1
113,f442,5
114,d332,2
114,s324,7
114,s432,5
114,l567,2
115,p098,10
What I am trying to achieve is finding a count of SaleHeaders where it has only 1 item in the SalesLines table. ( I hope this makes sense)
I tried using the follwoing query:
SELECT
sh.[Sales_Ref]
,COUNT(sl.[Sales_Ref])
FROM dbo.SalesLines AS sl
LEFT JOIN dbo.SalesHeaders AS sh ON sl.[Sales_Ref] = sh.[Sales_Ref]
GROUP BY
sh.[Sales_Ref]
HAVING COUNT(sl.[Sales_Ref) = 1
But this returned all the record with a count of 1. Is it possible to just return the number?
Thanking you in Advance!
I have 3 tables with 1 to many relationship as discribed below
Table A
CompanyId CompanyName
1 ABC
2 BCD
3 XYZ
4 TTT
Table B
CompanyId LocationId
1 a
1 b
2 c
3 d
Table C
LocationId DepartmentId
a it
a hr
a el
b gh
c tr
I want to write a query scan the Table A to display row in table A and # of Location and Department belong to the Company, like this:
CompanyIdCompanyName# of Location# of Department
1ABC24
2BCD11
3XYZ10
4TTT00
Please help me
Thanks
This query gives me part of what I want:
SELECT
TS_EXEC_STATUS as 'Status',
COUNT(*) as 'Total',
FROM TEST
WHERE TS_EXEC_STATUS <> ''
GROUP BY TS_EXEC_STATUS
ORDER BY TS_EXEC_STATUS
This query returns this:
Status Total
Failed 3
No Run 132
Not Completed 6
Passed 106
But I would like two more lines so the results are like this:
Status Total
Failed 3
No Run 132
Not Completed 6
Passed 106
Total 247
Percent Passed 42.9
I don't know how to get the last two lines with the Total of 247 and the Percent Passed of 42.9 (106/247).
I have a query as below:
---
select distinct
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi as "Document Reference No.",
csd1.csd_labl + ' ' as "Description",
csd1.csd_issu as "Docu Dt",
csd1.csd_altr as "Alternate Number",
csd2.csd_altr as "Transmittal Reference No.",
csd2.csd_issu as "TIssu Dt",
trd1.trd_recd as "TRecd Dt",
apr1.apr_reqd as "RReqd Dt",
case when apr1.apr_stat is null then 'Unknown / Pending / No Reply' else apc1.apc_libe end as "Document Status",
csd3.csd_altr as "Reply Reference No.",
csd3.csd_issu as "RIssu Dt",
trd2.trd_recd as "RRecd Dt"
from
E0437csd csd1,
E0437tra tra1,
E0437csd csd2,
E0437trd trd1,
E0437apr apr1,
E0437apc apc1,
E0437tra tra2,
E0437csd csd3,
E0437trd trd2
where
csd1.csd_orig = 'BS' and
csd1.csd_subj like '%WN1%' and
csd1.csd_type = 'D' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra1.tra_part = 'PARSO' and
tra1.tra_type = 'A' and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_cc = '0' and
trd1.trd_part = 'PARSO' and
apr1.apr_docu = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
(apr1.apr_stat = apc1.apc_code or apr1.apr_stat = '') and
tra2.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra2.tra_part = csd1.csd_ownr and
csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi = tra2.tra_trno and
trd2.trd_trno = tra2.tra_trno
order by
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi
---
I need to count the resulting records of the query.
How do i have to do this, could you please tell me ...
I am new to SQl and I tried but could not solve it ...
Thank You :)
Jari
Computer Engg
Hi All,
I have the following SQL query:
select temp.emp_id, temp.rownum
from
(
select emp_id, row_number() over (order by emp_id) as rownum from employee
) temp
where temp.rownum <=10
group by temp.emp_id
I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.
hi! I am new to this whole SQL language...
had a question about using a count function or if i shuld even be using a count function for this...
Data I have to work
with
I would like my result set to look like this:
ID ID2
ID ID1Count ID2Count ID3Count ID4Count
A
1
A
2
2
2
3
A
1
B
1
0
3
1
A 2
A 3
A 2
A 4
A 4
A 4
A 3
B 3
B 4
B 1
B 3
B 3
I am stumped.... ANy help would be great... thanks!
nick
Hi
I had posted this question earlier but could not get the solution, may be i was not clear with the doubts i had
I need to count the no of students for the different Intervention field which are like 14 different types
against the field gender (male and Female )and field Ethnicity (horizontal field headers) 5 different types below
A student can be Male and Hispanic Type 1
also
Male Hispanic Type 2
Male Hispanic Type 3
So his count is made in three places
I need to do this
for the whole District level
then for each Center under District level
then for each school under Center level
DISTRICT SUMMARY
Intervention
Total
Male
Female
Asian
Black
Hispanic
Indian
Multiracial
White
TYPE : 1
7
3
4
4
2
0
0
0
1
TYPE :2
6
3
3
1
0
4
0
0
1
TYPE : 3
3
1
2
1
2
0
0
0
0
TYPE : 4
5
2
3
2
3
0
0
0
0
TYPE : 5
1
0
1
1
0
0
0
0
0
TYPE : 7
2
1
1
0
2
0
0
0
0
TYPE : 8
1
1
0
0
0
1
0
0
0
TYPE : 9
2
1
1
0
1
0
0
0
1
TYPE : 14
1
1
0
0
1
0
0
0
0
Please help
Thanks
I need to get a query result from a Derived Table that I have. Here is an example of the data from my Derived Table, based on AdvID. The following is a result of students that are in relation with the advisor.
studentPIDM AdvPIDM AdvID AdvFirstName AdvLastName Sprhold StvHlDesc
123456 14000 N12345678 John Smith 01 Letter Sent
123456 14000 N12345678 John Smith E1 Library
654321 14000 N12345678 John Smith NULL NULL
134567 14000 N12345678 John Smith 01 Letter Sent
134567 14000 N12345678 John Smith AM Admin Hold
134567 14000 N12345678 John Smith E1 Library
155544 14000 N12345678 John Smith NULL NULL
233555 14000 N12345678 John Smith NULL NULL
What I want, is to get the count of students that have holds, and the count of students that do not have a hold. From the data above, the students that do not have a hold have a NULL value on sprhold column. Also from this data, you can see that some students have multiple holds, but they should be counted as only once when the Count is performed. The desired result would look like the following:
AdvPIDM
AdvID
AdvFirstName
AdvLastName
StudentsWHolds
StudentsNoHolds
14000
N12345678
John
Smith
2
3
Some Sample Data
Code Snippet
Declare @t Table
(
StudentPIDM INT,
AdvPIDM INT,
AdvID nvarchar(15),
AdvFirstName nvarchar(15),
AdvLastName nvarchar(25),
sprhold nvarchar(3),
stvhldesc nvarchar(50)
)
Insert INTO @t Values (123456, 14000, 'N12345678', 'John', 'Smith', '01', 'Letter')
Insert INTO @t Values (123456, 14000, 'N12345678', 'John', 'Smith', 'E1', 'Library')
Insert INTO @t Values (654321, 14000, 'N12345678', 'John', 'Smith', NULL, NULL)
Insert INTO @t Values (134567, 14000, 'N12345678', 'John', 'Smith', '01', 'Letter')
Insert INTO @t Values (134567, 14000, 'N12345678', 'John', 'Smith', 'AM', 'Admin Hold')
Insert INTO @t Values (134567, 14000, 'N12345678', 'John', 'Smith', 'E1', 'Library')
Insert INTO @t Values (155544, 14000, 'N12345678', 'John', 'Smith', NULL, NULL)
Insert INTO @t Values (233555, 14000, 'N12345678', 'John', 'Smith', NULL, NULL)
Select * from @t
I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below
I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4
STATUS
* 0 1 2 3 4
+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)
region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)
-------region 8
+school count(id) count(id) count(id) count(id) count(id) cnt(id)
The fields are in the same table
employee ID region status
A 1 1
B 1 0
C 2 3
Please help
THANKS
Hi everyone -
I am building from the Time Tracker Start Kit, trying to get a better feel for how MS thinks we should do things.
In my editing, I have built a new Stored Procedure, trying to pull the newest entries for a specified person.
I have the following that will return all entries for a specific person, sorted by date, newest first:
SELECT
EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
ManagerUserID, TT_Categories.Abbreviation AS CatShortName
FROM
TT_EntryLog
INNER JOIN
TT_Categories
ON
TT_EntryLog.CategoryID = TT_Categories.CategoryID
INNER JOIN
TT_Projects
ON
TT_EntryLog.ProjectID = TT_Projects.ProjectID
WHERE
UserID = @UserID
ORDER BY
EntryDate Desc
This will return something like:
EntryLogId Description Duration EntryDate ProjectID CategoryID CategoryName ProjectName ManagerUserID CatShortName
14Can type date in date... .00 2004-02-04 10:28:00116Pros ITS Project Management App 3 Pros
12Changed "Entry Date"... .00 2004-02-03 13:28:00116Pros ITS Project Management App 3 Pros
13Added default button ... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
11Removed hours per p... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
6Isn't this cool .00 2004-02-02 00:00:00229Pros Knowledge Base 3 Pros
9Added week-by-week... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
10Fixed Update comma... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
1Built initial framewor... 6.00 2004-01-30 00:00:00116Pros ITS Project Management App 3 Pros
5Adding up to 8 hours... 2.00 2004-01-30 00:00:00229Pros Knowledge Base 3 Pros
3Debugged - fixed a f... 1.00 2004-01-23 00:00:00229Pros Knowledge Base 3 Pros
What I would like to accomplish is to return only the newest entry for each ProjectID (so in the above example, there would only be 2 entries, EntryID 14 and 6)
Any ideas?
Thanks in advance-
Hello,
I am trying to select the last entry (by date) for each category in a table.
For example, if my table had the following fields;
id, category, product, datePosted,....
...how would I select the last product for each category posted by date?
Any guidance is appreciated.
Thanks,
AC
Probably a simple solution, but can't find it my brain right now!
I was able to get a running some to work, but i cant seem to get it to restart for each category... for example this is what I have...
ID Name Return Run. Sum
1 aaa 0.1 0.1
2 aaa 0.2 0.3
3 aaa 0.3 0.6
4 aaa 0.1 0.7
5 bbb 0.5 1.2
6 bbb 0.4 1.6
7 bbb 0.1 1.7
8 bbb 0.3 2
9 bbb 0.2 2.2
How do i get it to start over at bbb??? Here is the code in access 2010:
Format(1000+(DSum("Return","Monthly Performance Dates","[ID]<=" & [ID] & "")*1000),"00.00")