Simple (for Some) 2 Table Summary Query
Jul 10, 2006
...but apparently not me, I'm very new with this T-SQL stuff and am seeking the advice of the seasoned pros at this forum.
Description on my SQL-5 Environment:
Table I Sales: Prod_ID, Prod_DT, Sales_DT, Buyer_Name, Buyer_State
Table II Repairs: Prod_ID, Prod_DT, Sales_DT, Repair_DT
These 2 tables are joined by the common key Prod_ID & also and share the product's production & sales dates. What I would like to do is produce a rate summary similar to description below.
Production_YYYY, Production_MM, Sales_Cnt, Repairs_Cnt, Repair_Rate((Repairs_CNT/Sales_CNT)*100)
Important to remember that not all products experience repairs, so the basis for Sales_CNT needs to be the Sales Table, even thou Prod_DT also appears in Repairs Table.
It's simple enough for novice like me to produce 2 tables independently and then merge back those resulting tables into the single table output described above. But my question is how do I write a single SQL "SELECT" request that will produce the results into just a single table.
Thanks in advance for your help!
View 6 Replies
ADVERTISEMENT
Jan 18, 2007
I have a table which contains a sports schedule:
team1_id,team2_id,result1,result2
result1 is for team1_id
result2 is for team2_id
I want to run a query to get summarize the two id fields with their assoiciated results:
For Example the dataset might look like this:
row 1 - id1=20,id2=30,result1=1,result2=3
row 2 - id1=30,id2=20,result1=2,result2=2
row 3 - id1=20,id2=40,result1=3,result2=1
row 4 - id1=40,id2=20,result1=1,result2=3
how do I build the query to merge the ids and thier respective results into one countable dataset
View 3 Replies
View Related
Dec 9, 2004
I am trying to sum up sales for employees and get the employee with the highest sales in one query. The query I have below works but it doesnt get me the EmployeeID. Assume all the fields are in the same table. If I try to do something like this it bombs on me: "MAX(SUM(OrderAmount)"
SELECT Max(OrdersSum) AS MaxOrders FROM (SELECT SUM(OrderAmount) as OrdersSum
FROM Orders
GROUP BY EmployeeID);
Thanks in advance!
View 1 Replies
View Related
Apr 10, 2007
Trying to create a summary table of current product table, so that they is only 1 line for each product (even if the product is in both warehouses - in this case take table for warehouse 1) Therefore standard case statement becomes:
CASE WHEN exists (select product from stock where warehouse = '02' and product = stock.product and product in (select product from stock where warehouse = '01'))
ProductDescription = (select distinct long_description from stock where warehouse = '01' and product = stock.product)
ELSE
ProductDescription = (select distinct long_description from stock where product = stock.product and (warehouse = '01' or warehouse = '02'))
END
Is there another way of writting this, instead of repeating the above code for each column in the table?
THANKS
View 2 Replies
View Related
Aug 2, 2015
I'm trying to make a summary daily production report on the data below:Want to summarize the data with the sum of the Correct Weight between start and end date.
eg. of summary.
Recipe Name Total Weight
Hedge Shears - Lasher/kudu
500
Grass Slasher
200
eg.
But it needs to summarize when selected between start and end date.
RecipeName
CorrectWeight
CurDateTime
Weight
[code]....
View 6 Replies
View Related
Mar 9, 2006
I have 1 table:
ID pagename datevisited
1default01/01/2006
1info01/01/2006
1default02/01/2006
1info02/01/2006
1summary02/01/2006
2default02/01/2006
2info02/01/2006
2summary02/01/2006
I need to run query for ID who didn't visited a summary page(per day)
How do I do this?
View 1 Replies
View Related
Aug 25, 2005
I have a database with three tablestbl_listings - listings of houses on for saletbl_intersted - table which tracks if a user is interested in thelisting, it has two columns mls(the key for tbl_listings) and user(userlogin)tbl_review - table which trackes if a user has reviewed the listing.Like tbl_interested it has two columns (the key for tbl_listings) anduser(user login)How can I create a query on tbl_listings for reocords reviewed by oneuser?I am trying to create a query for listings that are revied by useruserid. I am using the query below. It works fine unless there is arecord in tbl_interested for a differnt user.In reality I am calling this query from the web. On the website I havean intersted dropdown with the choices All, interested, not interested.The website also has a reviewed dropdown with all, reviewed and notreviewed.I am using the query below as a starting point. my query works finewith one user, but if a user2 enters a record in tbl_intersted itthrows off the left join for user1. How can I fix this?SELECT COUNT(B.reviewed) AS review_count,Count(B.mls) as mls_count,A.mls,FROM mls.tbl_listings ALEFT OUTER JOIN mls.tbl_review B ON A.mls = B.mlsLEFT OUTER JOIN mls.tbl_interested D ON A.mls = D.mlswhere (B.reviewed = 'userid') and ((D.interested is null) or(D.interested = 'userid'))----My query works fine if there is one user, however once user2 reviews arecord from tbl_listing user1
View 2 Replies
View Related
Feb 22, 2007
Hi,
I have a summary table like this
Field1
Field2
Field3
Field4
Field5
AAA11
value1
value2
value3
value4
value5
AAB23
value6
value7
value8
value9
value10
BCD14
value11
value12
value13
value14
value15
GFD12
value16
value17
value18
value19
value20
SDL25
value21
value22
value23
value24
value25
AUD56
value26
value27
value28
value29
value30
BER11
value31
value32
value33
value34
value35
Columns are obviously fixed, but not rows.
I want to show this data using lables and SqlDataReader for report purpose like;
Label1.text=dr("value16").toString( )
Label2.text=dr("value28").toString( )
Label3.text=dr("value31").toString( ) etc
Do you have any idea how i can do it or am I approaching it in the wrong way????
Thanks.
Michelle
View 1 Replies
View Related
Apr 17, 2008
Hi Guys,I have the following query: SELECT DecisionSessionID, RuleID, Operator1, Criteria1, AttributeForCriteria1, Operator2, Criteria2, AttributeForCriteria2, Operator3, Criteria3,
AttributeForCriteria3, Result
FROM myTable
WHERE (SessionID = @SessionID) Now I want that instead of the complicated Criteria1, Crieria 2 and Criteria 3 values the users sees the names whichare defined in myCriteria. I though the query might then look like this SELECT
DecisionSessionID,
RuleID,
Operator1,
Criteria1 (SELECT CriteriaName FROM myCriteria WHERE Criteria1 = CriteriaID),
AttributeForCriteria1,
Operator2,
Criteria2 (SELECT CriteriaName FROM myCriteria WHERE Criteria2 = CriteriaID).
AttributeForCriteria2,
Operator3,
Criteria3 (SELECT CriteriaName FROM myCriteria WHERE Criteria3 = CriteriaID),
AttributeForCriteria3,
Result
FROM dbo.aspnet_FuzzyRules
WHERE (DecisionSessionID = @DecisionSessionID) I am not very familiar with the sql syntax and as you can imagine the above psyeudo version is not really working.Do you know how the correct syntax for such a nested query would look like?many thanks,polynaux
View 2 Replies
View Related
Mar 1, 2006
I am trying to write a simple search page that will search all the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:
SELECT Table.*
FROM Table
WHERE * = @USERINPUT
But thats not working. Can someone help.? Thanks..
View 1 Replies
View Related
Jan 5, 2012
I want to produce a summary table of amounts per status per user.
I have 2 tables:
Invoices:
Code:
user_id, amount, status
1, £10, S
2, £20, P
3, £30, P
3, £40, E
Users:
Code:
user_id, name
1, user A
2, user B
3, user C
And I want to produce a summary table like this:
Code:
S P E Total
user A £10 £10
user B £20 £20
user C £30 £40 £70
What I have is:
Code:
SELECT Users.name,
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E')))
FROM Invoices
LEFT JOIN Users ON Users.user_id = Invoices .user_id
GROUP BY Invoices.user_id, Users.name
ORDER BY Users.name
This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory.
View 9 Replies
View Related
Jul 4, 2014
I have a table census data containing the number of people having ages ranging from 0 to 120 years. Year 0 is in a column called F_0 and year 120 is in a column called F_120 with everything in between.
For example, when I create a script for the table the column for year 0 appears as: [F_0] [numeric](38, 8) NULL
I've already created 2 summary columns (people 0 to 18 years old and people 19 to 59 years old).
I follow the same methodology to create a summary table for people from 60 to 100 years old, but all the calculated values are NULL.
So the following is successful:
[Under_19] AS (((((((((((((((((([F_0]+[F_1])+[F_2])+[F_3])+[F_4])+[F_5])+[F_6])+[F_7])+[F_8])+[F_9])+[F_10])+[F_11])+[F_12])+[F_13])+[F_14])+[F_15])+[F_16])+[F_17])+[F_18]) PERSISTED,
But this is not:
[Over_60] AS (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((([F_60]+[F_61])+[F_62])+[F_63])+[F_64])+[F_65])+[F_66])+[F_67])+[F_68])+[F_69])+[F_70])+[F_71])+[F_72])+[F_73])+[F_74])+[F_75])+[F_76])+[F_77])+[F_78])+[F_79])+[F_80])+[F_81])+[F_82])+[F_83])+[F_84])+[F_85])+[F_86])+[F_87])+[F_88])+[F_89])+[F_90])+[F_91])+[F_92])+[F_93])+[F_94])+[F_95])+[F_96])+[F_97])+[F_98])+[F_99])+[F_100])+[F_101])+[F_102])+[F_103])+[F_104])+[F_105])+[F_106])+[F_107])+[F_108])+[F_109])+[F_110])+[F_111])+[F_112])+[F_113])+[F_114])+[F_115])+[F_116])+[F_117])+[F_118])+[F_119])+[F_120]) PERSISTED,
View 2 Replies
View Related
Jul 9, 2001
In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index):
SELECT * FROM TABLE
it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance?
Thanks in advance.
TH
----------------------------------
SP_CONFIGURE's RESULT in MY SERVER
----------------------------------
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0
Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0.
Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.
View 4 Replies
View Related
Oct 19, 2015
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
Month Count
July
August
September
October.
View 3 Replies
View Related
Jun 18, 2008
Hi,
Once again some doubt!
I have a query as below -
Select TeamName ,
SUM(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3' GROUP BY TeamName ORDER BYTeamName
This basically creates a table where I have team names as rows, month names as columns and team strength as each value.
Now I want to add one row at the bottom which gives a summary which basically calculates all the values in that column. I am displaying this on web where I can do this using gridview but the problem is I am using the same gridview for 2-3 different queries which have different columns so the rowdatabound method can not be used.
How can I get aggregate row at the end of table from this table?
Thanks
View 12 Replies
View Related
Oct 1, 2007
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
View 2 Replies
View Related
Jan 7, 2008
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
View 2 Replies
View Related
Mar 18, 2008
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
View 2 Replies
View Related
Apr 21, 2005
Hi All
Can anyone tell me what this simple SQL query would be:
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
View 4 Replies
View Related
May 28, 2006
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on:
I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD.
When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?
View 2 Replies
View Related
Mar 7, 2003
Hi,
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name
Gates || Bill
Boyce || Mike
Gates || Bill
Gates || Phil
Results I want:
Last Name || First Name || Count
Gates || Bill || 2
Boyce || Mike || 1
Gates || Phil || 1
Thanks a lot,
Heta
View 2 Replies
View Related
Aug 21, 2004
Hi,
I have a very simple question:
declare @treshold int
set @treshold = 10
SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold
I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?
I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?
thanks
View 2 Replies
View Related
Jan 17, 2002
Hi,
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
Thanks in advance for your help
Kevin
View 1 Replies
View Related
Oct 21, 2006
This is my query...I am attempting to only return the records for each
sales_contact with the greatest issue_id
select
sc.sales_contact_id
, idd.issue_id
, sr.code
from
sales_contact sc
, invoice i
, invoice_line_item il
, sales_region_special_section srss
, sales_region sr
, issue_date idd
where
sc.sales_contact_id = i.sales_contact
and i.invoice_id = il.invoice
and srss.sales_region = sr.sales_region_id
and il.issue_date = idd.issue_date_id
and srss.invoice_line_item = il.invoice_line_item_id
order by
sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id
for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC
B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are
situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
thanks in advance for your help.
View 1 Replies
View Related
Jan 7, 2005
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
Code:
-----
| ID |
-----
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 4 |
------
Would return something like:
Code:
----------------
| ID | Count |
----------------
| 1 | 3 |
| 2 | 1 |
| 4 | 2 |
-----------------
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
View 3 Replies
View Related
Sep 26, 2006
OLD_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____0
NEW_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____1111
(end result)
FINAL_TABLE
43434_____9258_____9496_____1111
I have two tables ... I am trying to get only the rows that are different.
View 2 Replies
View Related
Sep 19, 2005
Hi folks,
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0
if count(*) from question where serialno > $serial != 0
else
select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
View 5 Replies
View Related
Sep 1, 2006
Hi,
This is probably a simple SQL statement question, so please bear with me.
Basically I have a CustomerMaster with the following fileds
1) CustomerID
2) CustName
3) Address
4) ResTelNo
I want retreive the CustName,Address and ResTelNo if the ResTelNo has occured in the table more than twice.
Could someone please help with this. I would like to use the same query in MS Access as well.
Thanks in advance for your help.
View 2 Replies
View Related
Sep 17, 2004
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like
SELECT Count(*) AS Expr1
FROM Table1
WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
thanks
View 4 Replies
View Related
Feb 29, 2008
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate
,OutEndDate,OutStartTime,OutEndTime,OutID
FROM vwOutOfOffice
LEFT OUTER JOIN vwStaffList ON OutUser=StaffID
WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example
Current date=29/02/2008
Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of date)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
View 2 Replies
View Related
Nov 29, 2004
I have a table that looks like this:
c1c2
1a
1b
1c
2a
2b
2c
2d
3a
3b
3c
3d
3e
I want a result set that looks like this:
c1c2c3
1a1
1b2
1c3
2a1
2b2
2c3
2d4
3a1
3b2
3c3
3d4
3e5
Basically, grouping/numbering the rows by column c1. The number of distinct c1 columns and distinct c1,c2 columns is varying.
View 3 Replies
View Related
Oct 2, 2005
I have a table like
create table sample (Column1 , Column2 int)
I want to select say 3 or 4 Column1's for each Column2 with out using a cursor.
Can u help
View 1 Replies
View Related
