Count Not Retunring A ZERO When Not Matches Are Found
Sep 13, 2005
I am pretty new to writing my own queries in SQL and am tied to Access unfortunately. Here is my query as it stands:
SELECT Count(*) AS COUNT_ACT_2_ATTEND
FROM [Main Table]
WHERE ((([Main Table].ACT_2_ATTEND)="Always" Or ([Main Table].ACT_2_ATTEND)="Frequently" Or ([Main Table].ACT_2_ATTEND)="Sometimes" Or ([Main Table].ACT_2_ATTEND)="Never" Or ([Main Table].ACT_2_ATTEND)="N/A"))
GROUP BY [Main Table].ACT_2_ATTEND;
However, if there is not match it won't retun a zero, it just returns no result and so I cannot tell which counts correspond to which matches
Does that makes sense?
View 2 Replies
ADVERTISEMENT
Nov 24, 2007
Hello. I'm quite new to SQL so have included as many details as i can think of here. The scenario is a wordsearch style puzzle. The user can select their answers in any order and these answers are stored in a table. I need help with the UPDATE statement to compare the users answers against the correct answers for that puzzle.
(Note: In the actual scenario there will be 10-15 answers per grid, but i have reduced the number to make testing easier - hopefully the code for a working UPDATE statement will be scalable to account for grids with different numbers of answers etc.)
The Tables:
-- These are the correct answers for a given grid (gridid).
-- Due to the nature of the puzzle, answers can be in any order.
-- Each level may contain one,two,or no 'bonusanswers' which are harder to find, so these are scored separately so bonus points can be awarded.
CREATE TABLE correctanswers
(
gridid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
answer1 char(15),
answer2 char(15),
bonusanswer1 char(15),
bonusanswer2 char(15)
)
-- These are the user submitted set of answers 'answerid' for level 'gridid'.
-- Answers may be submitted in any order.
CREATE TABLE useranswers
(
answerid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
gridid smallint,
firstanswer char(15),
secondanswer char(15),
thirdanswer char(15),
fourthanswer char(15),
)
-- A user (userid) submits their answers which get stored as answerid.
-- This table shows the scores for each set of answerid's the user has submitted.
-- A high score table for both the individual user, and all users may be created using this table.
CREATE TABLE userscores
(
userid smallint,
answerid smallint,
mainmatches smallint,
bonusmatches smallint
)
The Test Data:
-- sample test data
-- 2 users userid's '1' and '2' each have two goes on level1 (gridid 1)
-- correct answers for gridid 1
INSERT INTO correctanswers (answer1, answer2, bonusanswer1, bonusanswer2) VALUES ('cat','dog','rabbit','elephant')
-- user submitted answers for gridid 1
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','rabbit','horse','cow')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','cat','elephant','horse')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'rabbit','cat','elephant','donkey')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'horse','cat','dog','sheep')
-- scores for users attempts - columns 3 and 4 needs calculating
INSERT INTO userscores VALUES (1,1,null,null) -- one main answer and one bonus answer, so null,null should be 1,1
INSERT INTO userscores VALUES (1,2,null,null) -- two main answers and one bonus answer, so null,null should be 2,1
INSERT INTO userscores VALUES (2,3,null,null) -- one main answer and two bonus answers, so null,null should be 1,2
INSERT INTO userscores VALUES (2,4,null,null) -- two main answers and no bonus answers, so null,null should be 2,0
I have included the correct new table values for the sample data - basically filling in the two null fields in the 'userscores' table.
I haven't used SQL much but from the little i know then i think the answer will include JOIN, COUNT and IN statements as part of the UPDATE statement...but i haven't a clue where to start with the order/logic etc.
I have looked for sample solutions myself but all the examples i have found so far are to do with exact matches between two tables, whereas in my scenario i need to know how many matches, irrelevant of the order.
Many thanks.
Roger
View 6 Replies
View Related
Sep 13, 2007
I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.
This is my code:
select d.lwia,
cast((d.closegoal + d1.opengoal) as float)denominator
from
(
select yg.lwia,
cast(count(yg.appid)as float) closegoal
from dbo.wiayouthgoals yg
where yg.lwia = @RWB
-- Attained a goal in the timeframe timely or untimely
and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @EndDte) -- Parm date for end of time frame needed
-- Goal due but not attained
or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @EndDte -- Parm date for end of time frame needed
and yg.youthattaingoal <> 1))
group by yg.lwia
)d,
(
-- Closure with open goal
select cast(count(yg.appid)as float) opengoal
from dbo.tbl_caseclosure cc,
dbo.wiayouthgoals yg
where yg.appid = cc.col_idnum
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @EndDte -- Parm date for end of time frame needed
and yg.youthattaindate is null
and yg.lwia = @RWB
group by yg.lwia
)d1
)d2
View 3 Replies
View Related
Mar 25, 2008
Hi!
I'am new to this forum and would apreciate any feedback on my problem.
I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299.
The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.
The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".
CODE:
SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall
FROM
(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week,
ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average
FROM SERGEL_PREPAID INNER JOIN
TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE
WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD')
GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP)
HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1
GROUP BY uke, all aar
NB: Using SQL Server 2005. Any tip or solution will be a big help
Best regards Gard S
View 2 Replies
View Related
Apr 17, 2007
i dont know why this has slipped my mind but how would i say,
if @test = 'a' or @test = 'b' or @test = 'c'
in a shortted way?
View 1 Replies
View Related
Apr 26, 2008
Hello.I have a select that returns some SubCategories.SELECT SubCategoryID from SubCategories SCATINNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryIdWHERE SCAT.ParentCategoryId = X Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.What code i need to write toI think .. Select * from Items AS IT WHERE IT.Subcategory = ???I don't know, anyone can help meThanks
View 4 Replies
View Related
May 18, 2007
Hi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.
CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );
My statement
SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;
the result is
ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5
The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?
View 3 Replies
View Related
Mar 25, 2008
A standard IN operation is like doing a series of OR statements in your WHERE clause. Is there anything like an IN statement that is like using a series of AND statements instead?
I tried looking into the ALL operator but that didn't seem to do it or else I just couldn't figure out how to use it correctly.
View 7 Replies
View Related
Apr 26, 2008
Hello.
I have a select that returns some SubCategories.
SELECT SubCategoryID from SubCategories SCAT
INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X
Now i will to retrieve all rows from a Table called Items where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.
What code i need to write to ?
I think .. Select * from Items WHERE Items.Subcategory = ???
I don't know what code i need to type, anyone can help me
Thanks
View 3 Replies
View Related
May 26, 2007
Hi...
Need help with some SQL-code:
Im just interesting in how many rows in my table 'Location' that has 'New York' in the column called 'City'....
So I just want to return the number of rows that is macthing...
How do I write the sql-part for this??
View 5 Replies
View Related
Oct 18, 2005
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Thanks in advance
View 1 Replies
View Related
Jul 20, 2005
Hi all,I have 2 files containing Id numbers and surnames (these filesessentially contain the same data) I want to select distinct() andjoin on id number to return a recordset containing every individuallisted in both the files HOWEVER, in some cases an incomplete IDnumber has been collected into one of the 2 files -is there a way tojoin on partial matches not just identical records in the same way asyou can select where LIKE '%blah, blah%'??Is hash joining an option i should investigate?TIAMark
View 4 Replies
View Related
Aug 3, 2007
Please indulge my ignorance, as I have only been using SSIS for a couple of weeks.
I'm trying to create a data warehouse using two input tables.
A column needs to be added to one table by using a lookup into the second table.
SSIS seems to handle the "no matches" and "single match" cases perfectly.
I can't for the life of me figure out how to properly handle multiple matches.
SSIS defaults to the first match, but I need to compute the "best" match.
Many thanks in advance
Scott!
View 3 Replies
View Related
Jun 13, 2006
Hi group,
This morning we had an issue with a simple join between 2 tables e.g. table1 and table2.
Both tables have 1 column called 'fielda' with datatype varchar.
Let's assume the following values:
Table1:
hello
Hello
Table2:
hello
This statement joins the tables:
select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda
It returns:
hello
Hello
????????????????????????????????????????????????????????
What the $#@#$
Why doesn't it return 1 value:
hello
Any ideas?
The outcome is exactly what we want but I would expected to have to use the following:
select a.fielda
from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)
What if I wanted to return all EXACT matches? Then what? Change collation?
View 5 Replies
View Related
Mar 11, 2013
Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...
something like
SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-
View 7 Replies
View Related
Mar 26, 2014
I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.
The problem is that when I run my compare I get false matches. Example:
CREATE TABLE #ABC (Acct VARCHAR(10), Que INT);
INSERT INTO #ABC VALUES
('2310947',110),
('2310947',245);
[Code] ....
Which gives me two records when I really do not want any as the tables are identical.
View 2 Replies
View Related
Jun 8, 2007
Hello there,
I have the following table:
ROOMTYPE AMENITY
========= =======
R001 1
R001 2
R001 3
R002 1
R002 2
R002 4
R003 1
Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only.
If I do this:
SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4)
I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4
NOTE: The data and list above is an example only as the table contents as well as the list will change over due course. e.g. the list can be (2,6,8,10,20,..100) etc.. So, I would need a query which can cater for any list contents...(x,y,z,...) and the query should return me the RoomTypes which have ALL elements in that particular list. If one of the RoomTypes do not have an element, it should NOT be returned.
Can anyone help me on this?
Kush
View 6 Replies
View Related
Jun 2, 2015
If it possible to have an if statement match multiple results, as to not have to use the OR multiple times.
Example: I want to say, if Description equals red or blue or green or yellow or orange or black or white or pink, without having to use OR and OR and OR. Description can match 10 different values.
View 9 Replies
View Related
May 1, 2007
I've got a package which reads a text file into a table and updates another. I set up configurations so that I could import it into the SSIS store on both my dev and live servers. Now, I'm getting this error. I tried removing the configs and am still getting it.
I've been through each step and everything looks okay. Does anyone have any idea (a) what's wrong, (b) how to localise the error or (c) get any additional information? Or do I just have to recreate the package from scratch?
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at PartnerLinkFlatFileImporter: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error at PartnerLinkFlatFileImporter [Log provider "SSIS log provider for SQL Server"]: The connection manager "" is not found. A component failed to find the connection manager in the Connections collection.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
View 20 Replies
View Related
Nov 20, 2015
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile).
MATCHED: (email, firstname, mobile)
But not MATCH: (lastname, phone)
MATCHED: (email, firstname, lastname)
But not MATCH: (phone, mobile)
View 9 Replies
View Related
Nov 24, 2007
Has anyone find solution for this problem.
i also checked
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=298056&SiteID=1 and
http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx
Suppose have a Dimension table
DimColor
----------------------------
ColorKeyPK(smallint) ColorAlternateKey(nvarchar(30))
-1 UnknownMember
1
2 Blue
3 Red
4 Black
Color with the ID 1 is empty string
FactOrders
---------------------------
OrderID Date Color Quantity
OrderID = 1 Color = 'Black' Quantity = 10
OrderID = 2 Color = 'Red' Quantity = 20
OrderID = 3 Color = '' Quantity = 10
OrderID = 4 Color = 'Blue' Quantity = 5
OrderID = 5 Color = Black Quantity = 10
When i use the Lookup transform it cannot find the ColorKeyPK
The result of the Lookup transform is.
------------------------------
OrderID = 1 Color='Black' ColorKey=4
OrderID = 2 Color='Black' ColorKey=3
OrderID = 3 Color='Black' ColorKey=NULL ----> This is the problem Lookup cannot find empty string. It should be 1.
OrderID = 4 Color='Black' ColorKey=2
OrderID = 5 Color='Black' ColorKey=4
Thanks from now.
View 5 Replies
View Related
Jan 1, 2008
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
SELECT Orders.[Orders ID],
[Orders Subtable].ID,
[Orders Subtable].Quantity,
Receiving.Quantity,
Receiving.[Component #]
FROM (Orders
LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID])
INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID,
[Orders Subtable].Quantity, Receiving.Quantity,
Orders.[Project #], [Orders Subtable].On_Order,
[Orders Subtable].[Component #],
Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND
(([Orders Subtable].On_Order)=True) AND
(([Orders Subtable].[Component #])="R02101A") AND
((Receiving.[Component #])="R02101A"));
View 2 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
Dec 3, 2014
I have two tables:
Club - Stores all clubs (id_club, name)
Match - Store all matches (id_club1, id_club2, dateMatch, result)
The match has club 1 and club 2.
And I Have to select all matches that clubs played with an interval less than three days between games.
This is my code:
SELECT DISTINCT a.*
FROM
Matches a
INNER JOIN
Matches b ON
[Code] ....
I tried too:
select *, DATEDIFF(day, m1.date, j2.data) from matches m1, matches m2
where abs(DATEDIFF(day, m1.dateMatch, m2.dateMatch))<3
and (m1.id_club1=m2.id_club2)
But it doesn't working. Because I have two clubs in a row. It's so difficult.
View 4 Replies
View Related
Aug 22, 2007
Hi,
While I am trying to install SQL server 2005 I get the following message - "Machine does not have a product that matches the installation package". The installation does not occur.
I am using Windows 2000 Professional SP4 and using 32 bit installer - SQLServer2005SP2-KB921896-x86-ENU.exe
Help would be appreciated... thanks.
Regards,
Ravindranath Kini
View 6 Replies
View Related
Nov 19, 2012
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301
302
303
304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code:
Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0"
For i = 1 To classIDs.Count - 1
assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString
Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
View 1 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
Aug 24, 2007
This is what I wan to do: If I, for example, have a table containing articles, and I want to allow users to return any article that matches a filter, which is defined by any number of keywords, located in another table.
My tables:
Article table: [ArticleID, Headline, Content]
Filter table: [FilterID, Name]
Keyword table: [KeywordID, Word]
FiltersKeywords table: [FilterID, KeywordID]
(The FiltersKeywords table serves as a many-to-many relation table between the Keyword table and the Filter table)
In other words, a filter, which is used to selected articles with, can be made up of any number of keywords. The users would thus be able to build on the filters, adding new keywords as time goes, removing old none relevant etc. I suspect some filters could end having anywhere from 100 to 1000 keywords associated with them.
Using SQL 2005, is there anyway to query for articles in an efficient manner? How would the code look?
Thanks, Egil.
View 3 Replies
View Related
Aug 20, 2007
I am trying to clean up an old database someone developed. I took the data and grouped the information that matched
I then imported it into a trips table. The matching data is all in the where statement. It shrunk 16k records for just under 2k records. Now I am trying to right a query to relate the 2 tables and update the tripid# to the triplist of people who actually attended.
Declare @Trip varchar(500)
Declare @Destination Varchar(500)
Declare @LeaveDate varchar(500)
Declare @LeaveTime Varchar(500)
Declare @ReturnDate varchar(500)
Declare @ReturnTime varchar(500)
Declare @Comment varchar(500)
Declare @RescheduleDate Varchar(500)
Declare @Rec int
Select @Rec = Rec# From Trips
Select @Trip = Trip from [Trips] where Trips.rec# = @Rec
Select @Destination = Destination from [Trips] where Trips.rec# = @Rec
Select @LeaveDate = LeaveDate from [Trips] where Trips.rec# = @Rec
Select @LeaveTime = LeaveTime from [Trips] where Trips.rec# = @Rec
Select @ReturnDate = ReturnDate from [Trips] where Trips.rec# = @Rec
Select @ReturnTime = ReturnTime from [Trips] where Trips.rec# = @Rec
Select @Comment = Comment from [Trips] where Trips.rec# = @Rec
Select @RescheduleDate = RescheduleDate from [Trips] where Trips.rec# = @Rec
Update TripList
SET tripid# = @Rec
WHERE (Trip = @Trip) and (Destination = @Destination) and (LeaveDate = @LeaveDate) and (LeaveTime = @LeaveTime) and (ReturnDate = @ReturnDate) and (ReturnTime = @ReturnTime) and (Comment = @Comment) and (RescheduleDate = @RescheduleDate)
View 7 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