SQL Syntax Help (Count, Sum, Etc.)
Jul 18, 2007
Hoping someone does this 50 times a day and will know the answer.
I need to build a query that groups and summarizes my data. Let's say table like this:
int ID
int clientType (keyed to a table with two columns [int a_ID, varchar a_clientType)]
int requestType (keyed to a table with two columns [int b_ID, varchar b_requestType)]
The result of the query should spit out by Client type, and with a count of how many rows of each Request Type:
Client Type A
[Request Type 1, # rows with Request Type 1]
[Request Type 2, # rows with Request Type 2]
Client Type B
[Request Type 1, # rows with Request Type 1]
[Request Type 2, # rows with Request Type 2]
I'm still reading up on grouping and sum (though sum seems to need a field to total numbers - I need a count of row of Request Type X/Y/Z
Any tips would be appreciated
Thanks in advance
Dave
View 14 Replies
ADVERTISEMENT
Feb 13, 2006
IN the following query i have to get the sum of 3 columnsfrom the case statement(Builders ,Associates ,Affiliates ) in the last column.I tried sum(Productid) but it is multiplying the count by 22,23,24 respectively.
I know i have been posting this again just to get the attention of the gurus.I have come
very close to my results but this is the last hurdle i have to get thru.
Thanks for your help
select
isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),' ') Firstname
, isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname
, RecordType = isnull(replace(Ltrim(Rtrim(S.Product)),',',''),'')
, case ProductID
WHEN 22 then count(S.Product)
WHEN 23 then 0
WHEN 24 then 0
END AS Builders
, case ProductID
WHEN 23 then count(S.Product)
WHEN 22 then 0
WHEN 24 then 0
END AS Associates
, case ProductID
WHEN 24 then count(S.Product)
WHEN 23 then 0
WHEN 22 then 0
END AS Affiliates
, Total = sum (????)
FROM vwpersons p with (nolock)
join vwSubscriptions S with (nolock)
on S.RecipientID = P.ID
where (
p.firstname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
or
P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
)
andS.ProductID in (22,23,24)
AND S.StatusID in(1,2,3,11,12)
and ((len(p.firstname)>2 or len(p.Lastname)>2))
and ((len(p.Lastname)>2 or len(p.firstname)>2))
group by P.Lastname, S.Product,S.ProductID,S.ProductID,P.Firstname
having count(P.LastName)>=1
order by 2
View 2 Replies
View Related
Mar 19, 2004
I need your help:
I want to get the total number of expired messages of two tables.
But if gives me this error:
Line 17: Syntax error near '@Count'
What did I do wrong? Am I not allowed to add two Integers??
CREATE PROCEDURE Portal_GetExpired
(
@Count int OUTPUT
)
AS
SELECT Count(*) as x
FROM Portal_Announcements
WHERE ExpireDate < GetDate()
SELECT Count(*) as y
FROM Portal_Events
WHERE ExpireDate < GetDate()
@Count=x+y
GO
View 3 Replies
View Related
Jul 23, 2005
Does anyone have any recommendations on how to solve the following?I would like to have a query that selects ALL columns from a database,however only records that have a count of a certain column which isgreater than 1 when the results are grouped by a few columns. I knowthe following query doesnt work (because it contains items in theSELECT that arent in the GROUP BY), but its the jist of what I need todo.select a,b,c,d,e,f,g,hfrom table1group by a,b,c,dhaving count(e) > 1Can anyone help me out with this?
View 3 Replies
View Related
Jul 20, 2005
Field Names: NOs Code Code1a UniqueID61 10 888 1062 10 888 1163 10 888 12Logic: If Count(code >1) & Count (Code1a >1)Update the (Nos) to EQUAL the same Value.ALL the Nos for the above examble should be the same value forall three records whether it's 61 for all three records of anyof the other two numbers, it doesn't matter as long as the equal the same value.How can this be done via sql?
View 5 Replies
View Related
Sep 21, 2004
I'm stuck. This is in C#.
I am making the following query:
string query = INSERT INTO region_info(prefix, region, last_update) VALUES ('" + RegionInfo.Prefix + "', '" + region + "', ???TIMESTAMP???);
and then executing
query = query.Replace("???TIMESTAMP???", "'" + DateTime.Now.ToString("yyyyMMdd") + "'");
Thus, an example query is:
INSERT INTO region_info(prefix, region, last_update) VALUES ('907209', 'Alaska-Juneau', '20040921');
When I execute this query through my program(uses ADO.net), I get a "COUNT field incorrect or syntax error" exception, but if I run this same query through the query analyzer, it works fine.
Any ideas on what'z going wrong?
Thanks
View 5 Replies
View Related
Aug 6, 2006
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
View 5 Replies
View Related
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
View 3 Replies
View Related
Dec 14, 2003
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
View 3 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
View 5 Replies
View Related
Jul 3, 2013
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
View 9 Replies
View Related
Nov 26, 2007
I use SQL 2000
I have a Column named Bool , the value in this Column is 0�0�1�1�1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much
View 5 Replies
View Related
Jul 23, 2005
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies
View Related
Aug 21, 2007
The 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
View 1 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
May 27, 2008
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
View 2 Replies
View Related
Aug 28, 2007
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies
View Related
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 Replies
View Related
May 22, 2008
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
View 5 Replies
View Related
Apr 2, 2007
Hi, i am trying to write a mulitple sql statement. basically i have 5 fields to search:
User ID
FirstName
LastName
Department
Site
i would like to search the records of the database with any of the fields above, so a user can specify a last name of "smith" and a department of "finance" which would return all the smiths in the finance department. or if a user enters "john" all the johns from any department or site would appear. How would the sql statement go like for this? and could i bind a tableadapter to a datagrid to view the results?
Any help would be appreciated. Thank you.
View 7 Replies
View Related
Aug 8, 2007
cmd.CommandText="Insert INTO personaldet(Firstname,Lastname,Username,password,dob,) values '('"+txtfname.Text.Replace("'", "''").ToString()+"','"+txtlname.Text+"','"+txtusername.Text+"','"+txtpassword.Text+"','"+txtdob.Text+"')'";
always says syntax error ')'.
check this plz
View 4 Replies
View Related
Nov 11, 2007
Hello, I have at sp that return a value:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getLastActivityDate]
(
@userid nvarchar(256)
)
AS
DECLARE @ret nvarchar(256)
SET @ret = (SELECT CAST(LastActivityDate AS nvarchar)
FROM aspnet_Users
WHERE UserName = @userid)
RETURN @ret
The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @ret?
// Tomas
View 2 Replies
View Related
Feb 24, 2004
I just cannot get the syntax right for this: (The problem is in my TSart and TEnd)
Dim SQL As String = "Select DateEntered FROM tblTasks Where [DateEntered] Between " '" & TStart & "'" And "'" & TEnd & "'" And [ID] = " & _
IDSent
Thank you for any help,
View 1 Replies
View Related
May 17, 2004
HI All,
I am writing some code where i want to get a list of all the company names that start with say, M...
here is what i have:
Dim M_company, companyID_M
Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )
but this is only returning the first company in the list that starts with M. Can anybody help with getting it to return more than one value???
thanks,
Leissa
View 4 Replies
View Related
Jan 6, 2005
I'm a 3GL guy not familiar with SQL that much. What I try to add a record to shopping cart and assign a line number for each record. If the same item already exist, instead of adding a new line I'd like to add to existing line. I use to following code and I got two lines created even I was only adding the very first first. The following are the code I wrote, would anyone please give some hints. Thx.
ALTER PROCEDURE dbo.AddItemToCart
(
@Cart nvarchar(50),
@ItmID int,
@Qty int,
@ItmTyp char
)
As
Declare @AllItmCount Int
SELECT
@AllItmCount = Count(ItmID)
FROM
ShoppingCart
WHERE
Cart = @Cart
IF @AllItmCount = 0 /*new shoppong cart*/
INSERT INTO ShoppingCart
( Cart, Qty, ItmID, ItmTyp, Line )
VALUES
( @Cart, @Qty, @ItmID, @ItmTyp, 1)
ELSE /*some item entered */
DECLARE @CountItems int
SELECT
@CountItems = Count(ItmID)
FROM
ShoppingCart
WHERE
ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
IF @CountItems > 0 /* same item entered before then add together */
UPDATE ShoppingCart
SET Qty = (@Qty + ShoppingCart.Qty)
WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
ELSE /* Find the last line number */
Declare @LastLine Int
SELECT @LastLine = Max(Line)
FROM ShoppingCart
WHERE Cart = @Cart
/*Add a new line */
INSERT INTO ShoppingCart
(Cart, Qty, ItmID, ItmTyp, Line)
VALUES
(@Cart, @Qty, @ItmID, @ItmTyp, @LastLine + 1)
View 6 Replies
View Related
Apr 23, 2005
Hi all,
I'm trying to build a simple forum. I want to display a list containing the forum names and last post date and last post author. I can view the date, but can't figure out a way to see the author. Here's the query i use:
SELECT ForumID, ForumTitle, ForumDesc, ForumTopics, ForumReplies, (SELECT MAX(AddedDate) FROM (SELECT ForumID, AddedDate, Author FROM dbo.Forums_Topic UNION ALL SELECT ForumId, AddedDate, Author FROM dbo.Forum_Reply) AS dates WHERE (ForumID = dbo.Forums.ForumID)) AS ForumLastPostDateFROM dbo.Forums
Any suggestion on how to show the author of the last message as well? Thanks in advance.
View 27 Replies
View Related
Dec 6, 2005
i want to find some repeatable data in a column,is there a sql syntax here?Thanks in advance.
View 1 Replies
View Related
Jan 7, 2002
Hello,
I am trying to create a view, but I am having trouble with the syntax (go figure!). It seems that as I reference more and more tables, my result set becomes smaller. For example, I want to return the state for all my customers then everything is fine, but when I want the state plus the site type (home, office, etc.) then I return fewer results. What is would be the correct syntax to use to return all the rows even if some of the columns have NULL?
SELECT TimeZone.LongZone, SiteMain.SiteNumber,
SiteType.SiteTypeDescription
FROM SiteType INNER JOIN
TimeZone ON SiteType.id = TimeZone.id INNER JOIN
SiteMain ON TimeZone.id = SiteMain.TimeZone AND
SiteType.id = SiteMain.SiteType
View 1 Replies
View Related
Feb 5, 2002
Can someone help me with the syntax for an if else statement such as:
select (if column(a) > 1) then print '+' else print '-')
,column(b)
,column(c)
,etc.
from table...
BOL doesn't seem to be much help for this
Thanks in advance!
CP
View 1 Replies
View Related
May 15, 2002
I have some problem for construction Sql statement to execute in asp pages.
In the table the data is stored in such a way that
Worknote Id,IncidentId, SeqNo , Desc1,Desc2.
1111 2004 1 - Test1 Test2
2222 3000 1 - Test1 Test2
2222 3000 2 - Test3 Test3
I want to take the maximum of IncidentId (3000) and concatinate the description (Test1 TEst2 Test3 Test4)
I dono how to construct SQl to fetch records . I tried some way , it gives only the last row.
SELECT iWorkNoteID, SeqNum, vchWorkNote1, vchWorkNote2 from incidentworknote where iWorkNoteID = (Select max(iWorknoteID) from incidentworknote where iIncidentID = " & iIncidentId & ")"
Can anyone helop me to find out.
View 1 Replies
View Related
Jun 5, 2000
I have a sql database which is not a backed up copy.....just a copy of the dbf file. I have tried everything in the enterprise manager to get the file re-introduced to no avail. I was told that you could execute the DB in the query analyser with the following
execute sp_attach_db @filename
but it does not like the syntax....can anyone please help.....
View 1 Replies
View Related
