Inflated Number Of Records Returned When Adding Bit Field Criteria
Jul 20, 2005
When querying a bit field, I am encountering a problem with MS SQL
Server returning a larger number of records for a table than the
actual number of records that exist within that table.
For example, my customer table has 1 million unique records, so the
results of the following query are as such:
select count(customer_nbr) from customer = 1,000,000
There is bit field in the customer table that denotes whether a
customer has placed an order with us called. That flag is called
order_flag
If I run the following query:
select count(customer_nbr) from customer where order_flag = 1
The result is 3,000,000 records.
There is no logical way that this is possible, as my table only
contains 1,000,000 unique records and the number of customers with an
order should be a subset of this.
If a run the above query with a distinct before customer number, I get
the results I want:
select count(distinct customer_nbr) from customer where order_flag = 1
600,000 records.
So while I can get to the answer I want, I have no idea why I am
returning incorrect values if I don't select distinct.
Can anyone help? I checked microsoft support and message boards but
haven't seen anything.
I should note that the bit field is indexed.I am not sure if that is
the problem or not.
In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks. Query SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.
-------------------------------------- UPDATE dbo.Asgnmnt SET CustomerPending = 'Y' FROM dbo.Asgnmnt WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')
ALTER Table Asgnmnt ALTER column CustomerPending varchar(1)
UPDATE dbo.Asgnmnt SET CustomerPending = 'N' FROM dbo.Asgnmnt WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')
ALTER Table Asgnmnt ALTER column CustomerPending varchar(1) ---------------------------------------
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = @VENDOR and Sitecode = @SITECODE and PackageType = @PACKAGETYPE )HB on HA.VendorPackageId = HB.VendorPackageId and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 )
Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in. Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_2 and ValidItemType = @VALIDITEMTYPE_2 and ItemValue = @ITEMVALUE_2 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_3 and ValidItemType = @VALIDITEMTYPE_3 and ItemValue = @ITEMVALUE_3 )
Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''
I have two tables a and b, where I want to add columns from b to a with a criteria. the columns will be added by month criteria. I want to keep all the records in a, and join columns from b. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a:
/****** Script for SelectTopNRows command from SSMS ******/ SELECT a.[naics] ,a.[ust_code] ,a.[port] ,a.[all_qty_1_yr] ,a.[all_qty_2_yr] ,a.[all_val_yr]
I have a data output with many rows. In order to group things with flags, I do this in excel using 2 formulas which *** a flag of 0 or 1 in 2 new columns.
This takes a long long time as I have hundreds of thousands of rows and wondered of I could do it in sql?
Its transact SQL and the formulas I use in excel are:
The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.
What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.
I worked out a method of doing this going by the number of entries each item had in the table:
select count(distinct valueId) as valueId, adWeeks, clientId, adID, AdBookingID into #worktable from #tmp group by adWeeks, clientId, adID, AdBookingID having count(distinct valueid) > 1
Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.
Can anyone suggest a way I can get just the data I need? Cheers, Matt
I have regular work that requires me to extract a bunch of customer records from our database, and then remove duplicate address destinations (so we dont mail the same address more than once).
I can currently achieve this using a combination of my poor SQL skills and Excel, but it's really not working out for me, so looking for SQL wizardry necessary to do it just in SQL.
Relevant fields: Member.AddressBarcode (This is a unique barcode (Text representation of a base-3 number) based on the customer address. So if there's more than one record in the pulled records with the same barcode, we then look at Member.MemberTypeID to determine whether to include this record in the results or discard it as a duplicate. Note that AddressBarcode may be blank if the mailing address couldn't be validated, if it is blank we don't discard it since there is no easy way to detect duplicate addresses without the barcode)
Member.MemberTypeID (This is the type of member account. We have 3 types - Single, Joint Primary, Joint Secondary, represented in this field by the numbers 1/2/3. This is also the order of preference of who to mail. So if there is a Joint Primary and Joint Secondary with the same mailing barcode, we want to discard the Joint Secondary from the results, so that the Joint Primary is the record we include in the results of who to mail.)
Member.ID (Unique numeric ID for each customer. Kind of irrelevant here, but it's a key)
So some pseudo code for what I'm trying to achieve is:
(Member.MemberTypeID = 1) OR (Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1) OR (Member.MemberTypeID = 3 AND Member.AddressBarcode not in results of Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)
I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:
CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int) AS
SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item] FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#] WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM" GO
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric or non-printable characters. For instance, if I have a table with a 20 character name column with names with beginning ranges from A-Z, why doesn't the following return any rows: select * from table where name < CHAR(126). In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does not include characters outside the alphanumeric range. Any ideas ?
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:- 1. Status is 1 @ max (trans_id), paychnl = CC 2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:- table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:- ==> 1 means the current paychnl method ==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2, 2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B 3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
update wce_contact set blank = 'missing' where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')
I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?
I guess it would be a case of adding another bracket somewhere but im unsure?
when I execute the line: reader = comm.ExecuteReader(); Is there a way to get a count of the number of records returned (the query is a SELECT with no count in it)? I want to vary the display of the results set based on the number of records returned. For example if no records are returned I want it to display nothing, if one, I want the header to be in the singular, but if more than one record is returned, I want it to display the header in plural form. Here is my code snippet with further explanation of what I am trying to do:int Inumber = 0;foreach (string item in menuHeaders) {string title = menuHeaders[Inumber]; sp.Value = menuHeaders[Inumber]; Inumber++; conn.Open();reader = comm.ExecuteReader(CommandBehavior.CloseConnection); //Get the culture property of the thread.CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture; //Create TextInfo object.TextInfo textInfo = cultureInfo.TextInfo; // WHAT I AM TRYING TO DO....... Here I would like to wrap this with an if statement, if Records returned by the reader are 0, skip while loop and header display // If one, then display in singular and if 2 add an s to the title. Convert to title case and display.content.Text += "<H3>" + textInfo.ToTitleCase(title) + "</H3>";while (reader.Read()) { content.Text += "<a href='" + reader["website"] + "'>" + reader["f_name"] + reader["l_name"] + "</a>"+ ", " +reader["organization"]+"<br />"; } //Close the connection. reader.Close(); conn.Close(); }
I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?
I have a report that calls a stored procedure. I want to display the number of records that the stored procedure returned. I can't seem to find an expression that will do this. Is there an expression that will display the record count for a dataset?
I know only enough SQL to be dangerous, but I'm stumped by this general task.
I need to know the method for selecting info from several fields of the records that satisfy a group function criteria on one of the fields. Example: Table contains Employees, products, amounts. If I group on Employee, and product, I can find the total amount of each product's sales for that employee.
create view prod_sales_by_emplyoee select employee product SUM(amount) as empl_prod_total from sales group by employee, product
But if I want to know which product made each employee the most money, I don't know how to do that, because the MAX function works on each field individually, not by row. From the above view, (or a table created from it) I want to know how to identify, for each employee, the product they made the most money selling, and what proportion of their total sales that product accounted for. Then, I'd like to use those results to eliminate the top tier, and find their second place product, etc.
I can do this in Access by sorting the table produced from the above view by empl_prod_total in decending order, joining that to a view that groups by employee only and provides a total, then using a grouped query on the joined data, I can use the "FIRST" function to find the values in each field of the record with the highest sales for that employee. However SQL doesn't have the FIRST function in its aggregate functions.
I suspect there's a standard way SQL does this, but it wasn't in my class. HELP!
Hi All,Sorry if the subject line is too obscure -- I couldn't think of a wayof describing this request.I have a table that contains approximately 1 million records.I want to be able to be able to select the top x records out of thistable matching variable criteria.Pseudo table records:custid, category, segment1,1,12,1,13,1,14,1,15,1,26,1,27,1,28,1,29,2,110,2,111,2,112,2,113,2,214,2,215,2,216,2,217,2,318,2,319,2,320,2,3So, what I'm trying to do is return a recordset, for example, thatcontains the top 2 of each variation of category and segment.ie:1,1,12,1,15,1,26,1,29,2,110,2,113,2,214,2,217,2,318,2,3The only way I can think to achieve this is in a while statement,performing individual selects against each combination, feeding thewhere criteria by variables that I automatically increment.I can't help thinking there's a much more graceful way of achievingthis?If anyone can give me any insight into this I'd be incrediblyappreciative!Many thanks in advance!Much warmth,Murray
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
hello, i have a stored procedure SELECT CommentID, UserName, CommentingDate FROM Comm WHERE PictureID = @PictureID ORDER BY CommentingDate DESC witch shows me the users who commented a Picture with PictureID = x I need to add two rows at that stored procedure, one to show the number of total comments at that picutre (like counting the number of rows returned) and the second to show count the DISTINCT users who commented that picture I tryied with COUNT but i have to use GROUP BY and i don't think this is good... I hope you understand... please help me, thanks
I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?
In Access, I use [Enter Date] in the Criteria field of the Query. I tried the same thing in SQL Server in the Criteria field of the View and it does not recognize this. Is there a comparable command in SQL to get user input into the Criteria field of a view?
Is there a way to extend the number of rows that will be returned so that Reporting services doesn't display such a large number of pages for the users to page through? It would be easier for them to "wheel mouse" through a long page on the screen.
Anyone out there have any thoughts on how this might be accomplished?
The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?
---------------------------------------------------------------------------------------------------- CREATE PROCEDURE spUnitsbyUnitID @unitid int AS
INNER JOIN tbl_radios as B ON A.unitid = B.unitid INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid INNER JOIN tbl_contacts as D on A.unitid = D.unitid INNER JOIN tbl_cameras as E on A.unitid = E.unitid
WHERE A.UnitID = @unitID GO -----------------------------------------------------------------------------------------------------
I have a simple report with a group header, details and footer. How do I display a message to inform users when no data is returned when they enter a parameter which returns no records - at the moment my report returns a blank screen, it would be more useful if I could output the following
This is my 1st thread. Hopefully somebody can help me with the full-text serach.
I enabled full text serach on sql server 2k and created a catalog for a table I tried using the following sql statement but it doesn't seem to return any records although I used a like statement and got some records. Can anybody answer why this is so? Thanks!
Select * from table1 where contains (field1, '"ESTATE PLAN"')
