Hi,
I need a query which will list out a username and the group he belongs to
something like this..
username groupname
-------- ---------
userA read_group
userB write_group
userc read_group
I think I have to join sysusers.gid with some other table...
kindly help..
Thanks,
Copernicus.
The title says it all. Given that I have created a login for a domain group, and a database user for that login. What I want to do is retrieving the domain username for the active user. USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.
The goal is to use domain group logins, while still allowing for logging what user performed which action.
Hi, I hope this is the right place to post this. I am pretty much stuck but think I am doing pretty good so far as I am getting more and more into SQL
using SQL Server 2000 here.
I want to be able to get the last username who replied to a topic. The "DateOfLastReply" works great, it gives me the last date of a reply to a topic so pretty much along with this, I want to get who it was that replied last. Any ideas? so far, I have this but it isnt correct:
SELECT Threads.[ID] 'ThreadID', ThreadName, DateOfCreation 'DateCreated',
(SELECT TOP 1 DateOfReply FROM Replies WHERE Replies.ThreadID = Threads.[ID] ORDER BY DateOfReply DESC) 'LastReplyDate',
(SELECT TOP 1 e.Username FROM Replies, Threads WHERE Replies.UserID = e.[ID] AND Threads.[ID] = Replies.ThreadID AND Threads.ThreadStarterUser = e.[ID] ORDER BY DateOfReply DESC) 'LastUserReplied', --HERE
(SELECT COUNT(ReplyID) FROM Replies WHERE ThreadID = Threads.[ID]) 'NumberOfReplies',
e.username 'UsernameCreator'
FROM Threads
INNER JOIN Users e ON
e.[ID] = ThreadStarterUser
I have a need to identify the name of a user and would like to access the NT userlist. Or failing a realtime access, can I achieve a periodic update of a SQL table with userid, username from the NT userlist?
TIA
Bob Morrow - Tennessee General Assembly
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
I put this together to export the user name /password to a csv file to test my SP to output the user name/password.
DECLARE @user_name varchar(50)
DECLARE @psswrd varchar(10)
SELECT @user_name ,@psswrd
FROM ngweb_bulk_enrollments
EXEC master.dbo.xp_cmdshell 'bcp NGDevl.dbo.ngweb_bulk_enrollments out C: est.csv -Sserver1 -T -t, -r
-c'
This works but I don't get the headers in the file. How can I include the headers?
Say a table of news (tblNews) like this
News_Id
News_City
News_Date
And a table of Cities (tblCity)
City_Id
City_Name
I created a view (vwNewsPerCity) where I am counting the number of new per
city/year/month
SELECT TOP 100 PERCENT COUNT(News_Id) AS iNoNews, YEAR(News_Date) AS iYear, MONTH(News_Date) AS iMonth, News_City
FROM dbo.tblNoticias
GROUP BY YEAR(News_Date), MONTH(News_Date), News_City
Now I would like a query returning me for a particular year and month for each cities the number of news and just the name of the cities when there is no news
I wrote this view:
SELECT City_Id, City_Name, iMonth, iYear, iNoNews
FROM vwNewsPerCity RIGHT OUTER JOIN
tblCity ON vwNewsPerCity. News_City =tblCity.City_Id
WHERE (vwNewsPerCity.iMonth = 2) AND (vwNewsPerCity.iYear = 2002) OR
(vwNewsPerCity.iMonth IS NULL) AND (vwNewsPerCity.iYear IS NULL)
Well, this does not work the way I want because in this case it displays all cities which have news or not in the year 2002 but not the cities which are having news only in 2001
So my question how is the right way to perform this ?
Jean-Luc
jeanluc@corobori.com
www.corobori.com
I have the query below
Code:
Select top 1 k.userid,sum(k.sales) as totalSales
from MyTable as k
where k.cat in (2,5,6,10)
group by k.userid
order by totalSales desc
result is like that
userid | totalSales
-----------------
11 233
i need user's full name with this result and other info from Users table
how can I rearrange the query for this
thank you
I am trying to use the inner join function and group by to provide a sum of information for a range of dates. Currently i am provided with each date as separate records using the script below.
quote: select salesmn.store, salesmn.salesman as employee_num, salesmn.sales, salesmn.spiffs, employee.first, employee.last
from
employee
inner join salesmn on (employee.login = salesmn.salesman)
where salesmn.dte between '1/1/2014' and '1/3/2014'
order by salesmn.store, employee.last asc
I want to use this code or something similar to provide a sum of the spiffs and sales for the customer.
quote:
Select salesman, sum(sales) as TotalSales, sum(spiffs) as Total_Spiffs from salesmn group by salesman
Hi,
I have a little problem with my joins and group by query.
I have two tables, A and B.
A has paymentnr, infoid and date.
B has a codetext, codeid.
A and B are related by infoid and codeid.
I want to get paymentnr and codetext for all with the latest date.
I tried with this but x.codetext does not display in the result-table:
SELECT paymentnr, x.codetext
FROM
(
select paymentnr, max([date]) as latestdate
from A
inner join
(select codetext, codeid
from B
group by codetext, codeid) x ON x.codeid = A.infoid
group by paymentnr
) Y
order by paymentnr
It is important to get both paymentnr and codetext, but i dont manage. I know why i cant get codetext out, but the problem i have is how to get around this problem.
Would appreciate any help.
I am MCDBA, I like your group, and i need to join it as a memberbut i do NOT know How .Please helpThank Youmaged.
View 4 Replies View RelatedIf i do the following one and want to run a inner join for the group by Valid ?????
go
Create table #rpt1
( recordNUM int NULL ,
History varchar(15) null,
Lastname varchar(20) null,
Firstname varchar(12) null,
)
Insert into #rpt1 Values (1, '123', 'Limb', 'Sub')
Insert into #rpt1 Values (1, '121', 'Limb', 'Sub')
Insert into #rpt1 Values ( 4,'124', 'Zimb', 'Kun')
Insert into #rpt1 Values (5, '125', 'Geroge', 'Khan')
Select * from #rpt1
Create table #rpt2
( recordNUM int NULL ,
HistoryNum varchar(15) null,
TownCode varchar(20) null,
Valid varchar(1)
)
Insert into #rpt2 Values (1, '123', '6','Y')
Insert into #rpt2 Values (2, '124', '5','Y')
Insert into #rpt2 Values (3, '444', '5','N')
Insert into #rpt2 Values (4, '666', '5','N')
Insert into #rpt2 Values (5, '125', '5','N')
Insert into #rpt2 Values (6, '555', '5','Y')
Insert into #rpt2 Values (7, '121', '5','N')
Hi,
In my database there are following two tables with sample data:
Customer_Product
=================
Branch Customer ProductID
B1 C1 P1
B1 C1 P2
B1 C2 P1
B2 C3 P2
B2 C3 P3
Product
=================
ID Name
P1 Prod 1
P2 Prod 2
P3 Prod 3
I need to write a query which can display the sum of each product for each branch. The required output is as follow:
Required Output
================
Branch Product Total
B1 P1 2
B1 P2 1
B1 P3 0
B2 P1 0
B2 P2 1
B2 P3 1
The Query I tried is:
SELECT Branch, ProductID, COUNT(*) as Total
FROM Customer_Product cp
RIGHT OUTER JOIN Product pd ON cp.ProductID=pd.id
GROUP BY Branch, ProductID
ORDER BY Branch, ProductID
But Right outer join act on Table level instead of Group. How can i generate desired output by keeping in mind that both Branch and Product are dynamic?
Thanks.
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
It's me again :)
So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:
CREATE PROCEDURE [dbo].[getAvailableTickets]
@eventId INT,
@standId INT,
@admissionDateId INT,
@concessionId INT,
@userId INT
AS
DECLARE @startyear DATETIME
DECLARE @endyear DATETIME
SELECT @startyear=CONVERT(datetime, '2006/01/01')
SELECT @endyear=CONVERT(datetime, '2006/12/31')
SELECT
[tblTickets].[id] AS ticketId,
[tblEvents].[id] AS eventId,
[tblStands].[id] AS standId,
[tblAdmissionDates].[id] AS admitDateId,
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities]. AS minBookingQuantity,
[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity,
MIN([tblQuotas].[quota]) AS Quota,
[B]SUM([tblBasket].[ticket_quantity]) AS History,
[tblTickets].[price],
[tblTickets].[availability]
FROM [tblTickets]
LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets].
LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id]
LEFT JOIN [tblQuotas]ON
([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
[B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]
WHERE 1=1
AND ([tblTickets].[ticket_open] = 1)
AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1))
AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1))
AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1))
AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
[B]AND ([tblOrders].[user_id] = @userId OR @userId=0)
GROUP BY
[tblTickets].[id],
[tblEvents].[id],
[tblStands].[id],
[tblAdmissionDates].[id],
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblTickets].[price],
[tblTickets].[availability]
GO
Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.
This is what's in tblBasket:
idticket_idquantityorder_iddate
152321/03/2006
262321/03/2006
3144421/03/2006
4154421/03/2006
551421/03/2006
Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".
When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?
And how do i get it to return all tickets regardless of whether you've bought them previously or not?
I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.
My query
select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town
How to stop getting null values?
portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB
I'm looking for distinct values like this:
portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...
In Sql Server
Code Snippet
CREATE TABLE t_contact
(
Id uniqueidentifier,
FirstName nvarchar(50),
LastName nvarchar(50),
TaskId uniqueidentifier
)
GO
CREATE TABLE t_task
(
Id uniqueidentifier,
Start datetime
)
GO
INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');
INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')
INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')
GO
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
GO
Result (Expected)
2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1
Result In Sql CE (UnExpected)
2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3
Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.
Thanks Adam
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table
[Code]....
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
View 3 Replies View RelatedI have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is callingPartyNumber. I am trying to SELECT a few fields including callingPartyNumber, and SUM(duration). Is it possible to group the results according to the callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)?
The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned.
SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
SUM(duration)
FROM Calls
GROUP BY callingPartyNumber
I cannot normalize the tables (implies that I have to work on one and only one table).Anyone...
I have the following sql statement below;DECLARE @val intSET @val = 1WHILE (@val <= 7 )BEGIN SELECT TOP(1) id, queueFROM itn_articles WHERE asection = @valORDER BY queue DESCSET @val = @val + 1END Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable
View 2 Replies View RelatedHi Guys,I have the following select sql (sql server) select statement SELECT
dbo.AlternativesAssessment.AlternativeNumber,
dbo.AlternativesAssessment.UserID,
dbo.AlternativesAssessment.MembershipValue,
dbo.AlternativesAssessment.CriteriaID,
dbo.CriteriaDefinition.CriteriaName,
dbo.CriteriaDefinition.DecisionSessionID
FROM
dbo.AlternativesAssessment INNER JOIN
dbo.CriteriaDefinition ON dbo.AlternativesAssessment.CriteriaID = dbo.CriteriaDefinition.CriteriaID
WHERE (dbo.CriteriaDefinition.SessionID = @SessionID)
ORDER BY dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.AlternativeNumber and it produces a result like this:In this case there are 4 alternatives all evaluated through 3 criteria "c1_Cost", "c2_Durability" and "c3_Reputation".My objective is to sum up all the MembershipValue's for each alternative.it shoud look like this:AlternativeNumber|CriteriaName|MembershipValue| 1 Cost 2314 1 Durability 214 1 Reputation 981 2 Cost 814 2 Durability 914 2 Reputation 381.etcDo you guys have an idea how I could achieve that?I played around with SUM's and GROUP By's for hours now but it does not seem to work.Many thanks,polynaux
I have a vendor table with vendor codes of six characters. The same vendor could have two codes (I'm new to this accounting system so I don't know why this is the case). If a vendor has two codes, the first character for one vendor code would be an "H", then the first character for the other code would be a "P", with the other 5 characters being the same (i.e. HCAMER and PCAMER).
We want to print only one 1099 for the vendor. So, if the vendor has two codes, I need to get a total for that vendor. Can this be done in a query?
Thanks.
Hi
I have a table in which column are id, date, lname, fname, add
In this table there are duplicate records but have different date for each duplicate record. I am trying to get just max(date) for each record.
when i try like these it works.
select id, max(date) from table group by id
but when i try something like these it does not work
select id, fname, lname, max(date) from table group by id, fname, lname
I get all the record.
I am writing these query from perl script. any help.
Hi,
Please help in writing a SQL query.
I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is
a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.
Now I want get sum of both for a particular manager on a daily basis. Like
SaleDate sum(Directsales) sum(TeammemSales)
if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.
How to achieve this task in query?
Any help is appreciated.
Thanks
M.L.Srinvas
I have a query that looks like this:
----------------------------------------------------------------------------
SELECT TOP 8 TABLEDATA.VW_DATA.COMPLETIONDATE AS COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
AVG(TABLEDATA.VW_DATA.DURATION) AS AVGDURATION,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE AS TEAMTYPE
FROM TABLEDATA.VW_DATA
INNER JOIN
TABLEDATA.MODELS ON
TABLEDATA.VW_DATA.MODEL_ID = TABLEDATA.MODELS.MODEL_ID
INNER JOIN
TABLEDATA.TEAMS ON TABLEDATA.MODELS.MODEL_ID = TABLEDATA.TEAMS.MODEL_ID AND
TABLEDATA.VW_DATA.TEAM_ID = TABLEDATA.TEAMS.TEAM_ID
INNER JOIN
TABLEDATA.TEAM_TYPE ON TABLEDATA.TEAMS.TYPE_ID = TABLEDATA.TEAM_TYPE.TYPE_ID
WHERE (TABLEDATA.VW_DATA.DURATION > (SELECT MIN(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
AND (TABLEDATA.VW_DATA.DURATION < (SELECT MAX(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
GROUP BY TABLEDATA.VW_DATA.COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL,
TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE
HAVING (TABLEDATA.VW_DATA.TYPE = 'Complete')
AND (TABLEDATA.VW_DATA.TEAM_ID = 27)
AND (TABLEDATA.MODELS.MODEL <> 'Model1')
ORDER BY TABLEDATA.VW_DATA.COMPLETIONDATE DESC
----------------------------------------------------------------------------
This query results in the following:
Complete 27 88 SITEA MODELT 80 AGG 2004-05-20 00:00:00
Complete 27 83 SITEA MODELT 80 AGG 2004-05-07 00:00:00
Complete 27 70 SITEA MODELT 80 AGG 2004-05-01 00:00:00
Complete 27 110SITEA MODELT 80 AGG 2004-04-19 00:00:00
Complete 27 87 SITEA MODELT 80 AGG 2004-03-17 00:00:00
Complete 27 110SITEA MODELT 80 AGG 2004-02-27 00:00:00
Complete 27 73 SITEA MODELT 80 AGG 2004-02-26 00:00:00
Complete 27 115SITEA MODELT 80 AGG 2004-07-02 00:00:00
What I need is one row per site, and I can get that when I exclude the completiondate field, but I need to sort on that field. How can I use the GROUP BY to return only one row with the team_id, avgduration, site, model, duration, and teamtype. I do not need to display the completiondate, I just need it for sorting. The avgduration column is supposed to be an average of the duration of each of the records.
Thanks...
Hi all,
I have 2 tables 1 is called Phonebrands the other is called phonetypes
is it possible to show all the phonetypes gouped by the phone brand?
Cheers Wim
I have this table .....
AID NAME DATE AMOUNT TYPE
1001 ABC 1/1/2006 120 AC
1001 ABC 1/1/2006 23 AC
1001 BC 1/1/2006 12 AC
1001 DC 1/1/2006 22 TR
1002 ZX 1/1/2006 21 DR
1003 ABC 1/1/2006 23 AC
1003 VF 1/1/2006 44 AC
Now I want a query which will give a result set of - between a specific date
i.e between 1/1/2006- 1/2/2006
AID NAME AC_AMOUNT TR_AMOUNT DR_AMOUNT
1001 ABC 143 0 0
1001 BC 12 0 0
1001 DC 0 22 0
1002 ZX 0 0 21
----
1003 ABC 23 0 0
One row for each name,that is group up by will be on AID and NAME.
Any help will be greatly appreciated...
This is a fairly compliacted query and I just can't seem to figure it out.
I will try to psuedocode it to see if anyone can figure out how to do it with sql.
Table Descriptions:
I have to join two tables. Houses and Inpsections. A house can have many inspections. A single inspection can only be done on 1 house.
Houses: HouseID, HouseDescription
Inspections: InspectionID, Inspection_date, House_Id, pass_inspection ("yes"/"no"),
What I need:
I need to have only 1 result per House. For each house, I need the results for the LATEST INSPECTION. That is pass_inspection, houseID, inspection_date for the last time the house was inspected ONLY (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet").
A query would be greatly appreciated.
Thanks,
DB
If you want to go further (which I will need to do) I need actually do this for each pair of House + problem. Houses and problems have a many to many relationship.
Problems: problem_id, problem_inspection
house_problems: problem_id, houseID
I have a table that has picktime, route_id, stlat and stlong columns.There might be multiple records having same picktime and routeid.
Select S.pickup_time, S.route_id from STARTTBL as S where Start_Dt= '7/11/2013' group by S.Route_id
(pktime) route_id stlat stlat
08:50 17 -32.01 102.98
09:50 17 -33.01 102.98
14:25 25
16:30 25
16:00 29
1.Is it possible to get the all the 4 columns where pickup_time is MAX for each Route_id
For the above data , the resulting data would be
09:50 17 -33.01 102.98
16:30 25
16:00 29