I have a query that is counting the number of applications gotten for the day.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.UserID,
COUNT (MerApp.ApplicationID)AS [Number of Apps]
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
INNER JOIN SalesTeams satm ON stm.SalesteamID = satm.SalesTeamID
WHERE MerApp.LastUpdateOn BETWEEN @strStartofDays AND @strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription
LastUpdateOn is the days the table is updated
Since it's just a 24hr count I should have to use BETWEEN in my where clause. What is another way to count for 1 day.
I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING: SELECT Active_Activities_temp.* FROM Active_Activities_temp LEFT JOIN ACTIVITY_CORE_LISTING ON Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv WHEREACTIVITY_CORE_LISTING.cde_actv is null ORDER BY prtcpnt_id So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.
The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.
What code can I implement to do what I need to do? Thanks so much.
Select Non Empty{[Measures].[Exposure Amount]} On Columns, Non Empty TopCount(Â (Â AddCalculatedMembers(
[Code] ....
As a result I only get 29 records. One record in the middle is missing. If I change to Topcount 31 I get 32 records, where the missing record is also included.
If I remove the Zero supression I receive 30 records as expected. For me the MDX looks good. I will try to reproduce on AW.
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic. Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned. DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20) SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central' SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
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
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
 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
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
1) Rows ordered using textual id rather than numeric id
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id
Result set is ordered as: 1, 11, 2 I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
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
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.
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
what is differance between conditions put in ON clause and in WHERE clause in JOINS????
see conditions that shown in brown color
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
another thing...
if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
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.
Hey guys, I'm a bit weak when it comes to doing ands and or's. I know what i want, but when I put it into statement, i dont get the results that i want. I have 3 fields in my where clause. ID, LW, and LWU. The code is as follows:WHERE (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID NOT LIKE '%6%') AND (ID NOT LIKE '%7%') AND (ID NOT LIKE '%8%') AND (LW <> 1) AND (LWU <> 'test') OR (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID IS NULL) AND (LW <> 1) AND (LWU <> 'test') I have a range of dates that I want to grab, in there I do not want any records where ID has 6,7,8 and I only want records where LW does not equal 1. UP to this point, it works fine. I get all the records that only return these values. However, the moment I add where LWU does not equal 'test'. it does not return the values I want. Furthermore, why can I not put this whole string into one and clause? I never understood why I had to create a second line following OR. the longer this query gets the more I get confused. Any help?
I have an insert statement that reads: SELECT AppointmentID, PatientNo, PatientSurname, PatientForename, ConsultantName, HospitalName, Date, CONVERT (varchar, Time, 8), AppointmentStatus FROM [Appointment] WHERE ([AppointmentId] = @AppointmentId) I also need to add another WHERE clause. This clause will mean that if the date is within 14 days of the actual date it will not ba able to be selected need help writing this not sure how to write it Thanks in advance Mike.
I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.
Here is what i have tried:
I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag. The table looks like this;
I am working on a project involving text searching. I created a fulltext catalog on the database and scheduled it for every one minute. I created a fulltext index on a table and added some columns. I scheduled it as the database catalog. I ran a simple query like this in the query analyzer but got an error message that the catalog does not exist!
SELECT * FROM tbl_extra_skills WHERE CONTAINS(ITSkills, 'Word')
hi alli need to create a sql statement that receives some values - my doubt is only about how to build that sql statementi've heard something about IN clause but could not apply it - could someone give any sample?First page: I have a textbox with some emails e.g. a@a.com, b@b.com, c@c.com etcSecond page: SELECT * FROM Table1 WHERE Field = ... IN ???thanks in advance
