T-SQL (SS2K8) :: Better Method To Detect Record With Multiple / All 0s?
Feb 17, 2015
Better method to detect records with one or more 0's, other than hard coding all of the potential values. For example, I need to detect account duplicates where the duplicate contains multiple/all 0's in the "MasterAccount" column:
DECLARE @Account TABLE(AccountNo varchar(3), MasterAccount varchar(10) )
INSERT INTO @Account
SELECT 'ABC','0000000ABC' UNION
SELECT 'DEF','0000000DEF' UNION
SELECT 'ABC','0000000000' UNION ---- unwanted duplicate
SELECT 'DEF','00000' UNION ---- unwanted duplicate
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18 2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.
For example this query returns readable text from this data.
0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column
SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB
However this column has the following query results.
0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column
--this query returns empty result
SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG
--this query returns no change???
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG 0001000000FFFFFFFF01000000000000000C....etc
Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?
Something like:
1. Try to cast as varchar to see if it is text. 2. Turn into a byte array and see if it is a jpg 3. Turn into a byte array and see if it is a pdf 4. Convert it to hex and then cast as varchar 5. etc....
I have built my own functions in the past that involve a timestamp and record lock column in each table (to support some other DB).
I am currently using SQL 2000 and was wondering if there is a better (OK, easier) way to lock records and even prevent edits from taking place when a record is open.
Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com
,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.
Customer_ID, Order_Type, Product_Code 54, a, 00345 54, b, 00356
Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
How can we insert multiple records in a OLEDB destination table for each entry from the source table. To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record. how is this achieved.
I have a database in XML format , with 100+ tables, that I want to import into SQL server but the problem is that I don't have any of the original primary key information. I know that some of the tables had multiple primary keys.
Is there a method or a tool that is capable of determining the primary keys for me??
I don't want to have to figure it out by hand, especially if I have more of these databases in the future.
Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.
We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...
I need to write SP which will call from front End to populate dropdown, But in dropdown uppermost value should be frequently used.
For example, My SP Select CountryID,Country Columns where i am displaying Country in dropdown. Now first time it will sort by ascending, next time if user selects US then US should be top and rest things after that.
Now If user selects Canada 3 times then result should sort as Canada,US, and rest countries.
if again US Selects 7 times then result should display as US,Canada,........
In short need to sort depending used previously. i.e. most popular first and so on......
How can we write a query to achieve this while keeping in mind:
- We do not want to do unnecessary record lookups and Updates - We only update records that corresponds to new entries. For example, we should not touch the record for InvoiceID = 2 in the above example
I am having table which is having 5 columns say A,B,C,D and E. There are chances to change in C,D,E columns. I want to identify if any change happened in the above column and show to user prev value + new value.
i.e. i want to prepare query by using calculated extra 6 column where i want to put change happned in C,D,E columns.
The situation is that we have resources (trucks) that perform shifts. Shifts consists of actions. A resource can perform multiple shifts.
For every resource we want to find the record that:
- Is 'younger' than the last realized action.
- Has actionkind pickup, deliver or clean
I have constructed a solution with CTE and row_number but I was curious if there would be other alternatives. The fact that I'm joining a CTE onto itself and subject the outcome to a partition makes me think there are sharper ways.
Note that the action id in the data below is also sorted but in practice this need not be the case. The sorting key is prevalent.
I have a client with a fairly simple table as illustrated in my sample code. Their interesting requirement is that the records would be listed horizontally, end-to-end as I hope you can visualize based on what I'm providing here. Two other characteristics/specs of note, 1) there could be as few as two records end-to-end in a row or as many as 100 and 2) of course the column headings would have to be unique.
create table #tmpEndToEnd ( ID int identity, ClientID char(10), Agency varchar(20), Clinician varchar(20), Goal varchar(50)
I am having tables with 12 columns as jan,feb,.......dec.
Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.
For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.
I am simply inserting records in a table, But if record already present then i dont want add that record and display resultset as -1.If record is not present then i want to add that record and commit transaction. I am doing like below : below is sample, I am considering only two columns.
CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details] -- Add the parameters for the stored procedure here @FacilitiDetailID int ,@SpecialityType varchar(50) AS BEGIN
[code]....
above code is working fine for new recordset but if record exists then it is giving two resultsets -1 and 1, but i want to display only -1.
1) TableA : Which contains 5 columns(Column1,..........Column5) 2)TableB : Which contains 10 columns(Column1,..........Column10)
TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:
select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )
But query is taking almost 5 minutes. Is there is another approach?
Let's say you had a User table and one of the fields was called Deceased. It's a simple closed-ended question, so a bit value could be used to satisfy the field, if the person is dead or alive. Let's say another field is called EyeColor. A person can have only one eye color and thus one answer should be stored in this value, so this is easy as well. Now, let's say I want to store all the languages that a specific user can speak. This isn't as easy as the previous examples since it's not a yes or no or a single-value answer. I haven't had much experience with working with databases so I've come up with two possible ways with my crude knowledge hehe. In terms of inputting the multi-answer values, I suppose I could use a multiple-selection listbox, cascading dropdowns, etc. Now, here are the 2 solutions that came to mind..... 1) Make a field called LanguagesSpoken in the User table. When I process the selections the user makes on the languages he knows, I can then insert into the LanguagesSpoken field a string "English, Spanish, Czech" or IDs corresponding to the languages like "1, 5, 12" (these IDs would be referenced from a separate table I guess). I would use commas so that later on, when I need to display a user's profile and show the user's languages, I can retrieve that long string from the LanguagesSpoken field, and parse the languages with the commas I've used. Using commas would just be a convention I use so I would know how to parse (I could have used "." or "|" or anything else I guess) the data. 2) Forget about the LanguagesSpoken field in the User table altogether, and just make a LanguagesSpoken table. A simple implementation would have 3 fields (primary key, userId, languageId). A row would associate a user with a language. So I would issue a query like "SELECT * FROM LanguagesSpoken WHERE userId=5" (where userId=5 is some user). Using this method would free me from having to store a string with delimited values into the User table and then to parse data when I need them. However, I'm not sure how efficient this method would be if the LanguagesSpoken table grows really large since the userIds would NOT be contiguous, the search might take a long time. I guess I would index the userId field in the LanguagesSpoken table for quicker access? OR, I may be going about this the wrong way and I'm way out on left field with these 2 solutions. Is there a better way other than those 2 methods? I haven't work extensively with databases and I'm just familiar with the basics. I'm just trying to find out the best-practice implementation for this type of situation. I'm sure in the real world, situations like this is very common and I wonder how the professionals code this. Thanks in advance.
I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:
-- create table if object_id('tempdb..#tmpdelnotes') is not null drop table #tmpdelnotes
create table #tmpdelnotes( DelNote int identity (1,1) , DelDate date not null, Item int not null, Customer int not null)
[code]...
What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc) select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110' union select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110' union select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110' union
The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.
I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem ( FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY ,ParentFSIDINTEGER NULL ,NameVARCHAR(100) ,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
With mstrTable(ItemNo, Sales) as ( Query1 Union All Query2 ) Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales From mstrTable order by ItemNo
The results from Query1 and Query2 overlap sometimes.
I only want the first occurrence of each item. The desired result set is:
1 Item1 10000 1 Item2 20000 1 Item3 30000
I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".How would I obtain a record set that returns just the first occurrence of the ItemNo field?
I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.
The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.
