Hi all,
I'm using SQL Server 2005 along side Visual Studio 2005, using VB to create a web application at work.
Now I'd like to bunch together some child fields in a gridview when displayed. Have a look at the attached pic to show what I would like.
Is this possible via a query or would I need to set something up on the gridview to do this?
Cheers,
Paul.
I have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
I am working on a query to generate parent child hierarchy from a table.
Table has below records.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
[Code] ...
how to achieve this.l tried with temp tables it doesn't work.
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
I found in error log of my server is full of message like this
The query notification dialog on conversation handle '{9AD14600-28AD-DC11-9B36-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-174a4df0-bac8-4a51-8564-28d750d7c11a'.</Description></Error>'.
any idea what could cause that?
thanks!
I have tableX with columns colA, colB, colC, colD and there are 2256 rows in the tableX.
I would like to find out the percentages of colA, colB, colC, colD that hold data (where it is not an empty string or NULL value).
So out of 2256 rows in the table, the user has stored data in colA 1987 times, colB 2250 times, colC 2256 times and colD 17 times.
So the report would say:
colA: 88.07%
colB: 99.73%
colC: 100%
colD: 0.01%
We have an application that has a bunch of fields that we believe are not being used and would like to remove them, but we need to prove this by looking at the data.
I know I could run a query, one at a time and change the column name, but this would take a long time as there are a lot of columns in this table. I am hoping there is some way to do this in one query.
Currently I'm using a UDF and a physical temp table to accomplish thisand I want to know if there's any way of doing it in a query orsubquery instead...In TableA there are a bunch of rows:InvitationID (PK) PartyID Partygoer1 1 Jim2 20 Bob3 2 Frank4 1 Robert5 1 PeteIn TableB are rows:PartyID (PK) PartyDate PartyName1 1/1/2000 WildParty2 1/1/2000 BoringPartyWhen a user runs a query to search for all parties on 1/1/2000 I wantthe result to look like:PartyID PartyDate PartyName Partygoer1 1/1/2000 WildParty Jim, Robert, Pete2 1/1/2000 BoringParty FrankI'm hoping there's a solution to this.Thanks,lq
View 9 Replies View RelatedI need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
hi all, can anyone help me?I am a relative newbie to sql server and I am more familiar withEnterprise Manager than QA. I have made many many access databasesthough. I am making an asp.net application where by there are a setnumber of users, about 80, each one logs in and manages informationwithin their department.To get them started a manager has written 10 different hazards thatwill apply to all of the departments, and he has written consequencesand controls for the hazards. Each department must have thisinformation as each will manage and deal with them differentlyThe hazard information is stored in a main 'hazards' table, and theconsequences and controls are stored in related tables linked by the'hazardID' from the main table to a foreign key 'hazardID' in therelated tablesWhat i want to know is if there is a relatively simple way of using aquery to populate the 10 hazards to each department, and to alsoinclude the related table links, i dont mind renaming the departmentsnames to match each hazard, but i do not want to have to relink therelated tables manuallyIf anyone can give me any advice to get me started i will be incrediblygratefulthank youTable information is belowHazards------------HazardID - identity key fieldHazard - varcharDepartment - varcharConsequences----------------------ConsequenceID - identity key fieldHazardID - FKConsequence - varcharControls------------ControlID - identity key fieldHazardID - FKControl - varchardwight
View 3 Replies View RelatedHow can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
Hello all,
How can i write a sql statement that can give me the name of the fileds in a specified table.
Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.
any help.
I apologize for the newbie sort of question, but I could not find an answer in an SQL book nor via Google.
I wish to search for a text string in ALL fields of a table. This will be used to provide a simple search box in a web application.
So far, the only method I've found to accomplish this is follows:
SELECT *
FROM Inventory
WHERE SerialNumber LIKE '%searchstring%' OR UserName LIKE '%searchstring%' OR Location LIKE '%searchstring%' ... etc
My goal is to accomplish something like the following. This, of course, does not execute properly since * can only be used following SELECT, but you can get an idea of the target behavior:
SELECT *
FROM Inventory
WHERE * LIKE '%searchstring%'
I'm using MSDE with Visual Basic .Net. Any suggestions on how to accomplish this?
Thanks for all help,
Kieran
I am trying to write a simple search page that will search all the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:
SELECT Table.*
FROM Table
WHERE * = @USERINPUT
But thats not working. Can someone help.? Thanks..
I have following input:
CREATE TABLE #tree
(
Childid varchar(20),
Parentid varchar(20)
)
INSERT INTO #tree
(Childid,ParentId)
SELECT '123' , null UNION ALL
SELECT '456' , '123' UNION ALL
SELECT '789' , '456' UNION ALL
SELECT '870' , '456' UNION ALL
SELECT '985' , '870';
Input:
Child IDParent ID
123 NULL
456 123
789 456
870 456
985 870
I am trying to populate lowest level child with path and depth...Output should be:
Child IDParent IDLast ChildPath Depth
123 NULL 789/123 1
456 123 789/123/456 2
789 456 789/123/456/789 3
123 NULL 985 /123 1
456 123 985/123/456 2
870 456 985/123/456/870 3
985 870 985/123/456/870/9854
I have the following insert query which works great. The purpose of this query was to flatten out the Diagnosis codes (ex: SecDx1, SecDx2, etc.) [DX_Code field] in a table.
Code Snippet
INSERT INTO reports.Cardiology_Age55_Gender_ACUTEMI_ICD9
SELECT
Episode_Key,
SecDX1 = [1],
SecDX2 = [2],
SecDX3 = [3],
SecDX4 = [4],
SecDX5 = [5],
SecDX6 = [6],
SecDX7 = [7],
SecDX8 = [8],
SecDX9 = [9],
SecDX10 = [10],
SecDX11 = [11],
SecDX12 = [12],
SecDX13 = [13],
SecDX14 = [14],
SecDX15 = [15]
FROM (SELECT
Episode_Key, DX_Key,
ROW_NUMBER() OVER ( PARTITION BY Episode_Key ORDER BY DX_Key ) AS 'RowNumber', DX_Code
FROM srm.cdmab_dx_other
WHERE Episode_key is not null
) data
PIVOT
( max( DX_Code ) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13], [14], [15] )) pvt
ORDER BY Episode_Key
The query below also works fine by itself. You may notice that the Episode_Key field appears in both the query above and below therefore providing a primary key / foreign key relationship. The srm.cdmab_dx_other table also appears in both queries. I would like to add the fields in the select statement below to the select statement above. Using the relationships in my FROM statements, can anyone help me figure this one out?
Code Snippet
SELECT
e.episode_key,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(p.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(p.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(p.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(p.patient_sname)), '') AS PatientName,
CONVERT(CHAR(50), e.admission_date, 112) as Admit_Date,
CONVERT(CHAR(50), e.episode_date, 112) as Disch_Date,
e.episode_type as VisitTypeCode,
d.VisitTypeName,
convert(int, pm.PatientAge) as PatientAge,
pm.PatientAgeGroup,
pm.patientsex,
p.race
FROM srm.episodes e inner join
srm.cdmab_dx_other dxo on dxo.episode_key=e.episode_key inner join
srm.cdmab_base_info cbi on cbi.episode_key=e.episode_key inner join
srm.item_header ih on ih.item_key = e.episode_key inner join
srm.patients p on p.patient_key = ih.logical_parent_key inner join
ampfm.dct_VisitType d on d.VisitTypeCode=e.episode_type inner join
dbo.PtMstr pm on pm.AccountNumber = e.Account_Number
I have the following tables in my DB
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary
Here is a piece of code to generate sample data sets
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')
[Code] ....
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
[Code] ....
I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.
ResumeSkill Skill
Id SkillId
SkillId Name
I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.
how do you draw an E-R diagram from a bunch of functional dependencies?
I mean, where do you start? Can I just pick, say, E to be the starting point and have branches coming out to all the other attributes?
Hi All,
Not sure if this is exactly the place to post this, but here it goes anyways.
I am writing a ASP.Net/C# program and I am interacting with a MS Access database in order to derive data on user login and logout times. Basically, I am trying to create a line graph that will display the number of users over the course of a user specified timespan. Currently, I am doing this by look at the number of users that were logged on during each minute of the timespan.
My database table setup consists of a EmployeeID column (Text), Logon Date (Date/Time), and Logoff Date (Date/Time). I have also created an index on the Logon Date and Logoff Date columns.
In order to view the number of users during a minute of the timespan I use a Jet SQL query of the following format
Code Snippet
SELECT DISTINCT Count (EmployeeID) AS [User Count]
FROM ProgramName
WHERE ([Logon Date] < #August 27, 2007 11:45:00# OR [Logon Date] Is Null) AND ([Logoff Date] > #August 27, 2007 11:45:00# OR [Logoff Date] Is Null)
The problem is that using this method I have to execute 1,440 queries for each day in the timespan. Currently this takes about 25 seconds to execute if the timespan is a full workweek (7,200 queries).
Now the question. Is it possible to create a SELECT statement that will return user counts for multiple minutes? Like maybe a SELECT statement that returns a column of counts for every minute in an hour? If it is possible, does anyone have any examples? I am hoping by lowering the number of queries my program has to execute I will also cut down the time required for the code to run.
I am pretty new to SQL, so any guidance or advice is very appreciated.
Thanks!
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies View RelatedI have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View 1 Replies View RelatedIn Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
See pic
Syntax on using BCP.
Here is my Requirements: I have a file that has a bunch of INSERT STATEMENTS. So the stuff inside the file looks like the following:
File has about 5000 rows.
INSERT INTO abc ( name ) VALUES ( 'Peter' );
INSERT INTO abc ( name ) VALUES ( 'Bob' );
For example in a table with this fields "field1, L1,L3,L100" field2 the count is 3
it would be better to match a number into the like but i thinks it cannot be done in the like so i've to add another condition to ensure all the text after L is a number.
is this the best way to do it?
Select count(*) from Information_Schema.Columns Where Table_Name = @Table
AND column_name like 'L%' and ISNUMERIC(SUBSTRING(column_name,2, len(column_name)-1))=1
I am developing a asp.net app and I have a long, very long list of data fields.
The values of the data fields have to be saved to a SQL database. The list is so long that it a pain in the ass to have to write all the code even to declare an object and all its properties by hand. So...
I made a list of all the field names and types and load it all up in an array. Then I wrote a bunch of macros to write all the properties one by one with the corredt type for the object I needed to create instead of doing it all one by one by hand.
I want to do the same thing with the Database. I dont want to have to hand define all the fiedls 1 by 1 by hand in the IDE. I want to write a simple macro, a loop theat loops thru my array and creates a field with the field name and type that it says in the array.
Simple enough.. Now I have the table defined already since that was simple enough. It is the 200 ++ fields that I dont wanna do.
So, please what is the code I need (in VB) to create 1 simple field in an existing table. Say the table is called "Table1" in the database "Database1" and the field I want is to be called "Field1" and to be 250 chr string..... ???
Thank you Marc
Hi,
I have a query that uses the following fields from MSmerge_history:
MSmerge_history.start_time, MSmerge_history.runstatus and MSmerge_history.duration
Below is the query that I am using:
SELECT
MSmerge_agents.subscriber_name AS SubscriberName,
MSmerge_history.start_time AS SyncTime,
MSmerge_history.runstatus AS SyncStatusID,
MSmerge_history.comments AS Comments,
MSmerge_history.duration AS Duration
FROM distribution.dbo.MSmerge_agents MSmerge_agents INNER JOIN distribution.dbo.MSmerge_history MSmerge_history
ON MSmerge_agents.id = MSmerge_history.agent_id
WHERE MSmerge_history.runstatus IN (2, 6) AND publisher_db = DB_NAME()
AND MSmerge_agents.subscriber_name + CONVERT(nvarchar, MSmerge_history.start_time) NOT IN
(SELECT SubscriberName + CONVERT(nvarchar, SyncTime) FROM SyncActivities)
My query runs fine under SQL Server 2000 but when I run it in SQL Server 2005, it doesn't work any more. Looking at MSmerge_history table under SQL Server 2005, this fields have been removed. Does anyone know where I can access those fields? Is it in another table?
thanks
Romeo
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:
SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;
This is a snap shot of the output:
ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS
[Code] ....
What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?
We have a proc that adds some fields to a few tables of ours and normally there are no issues. For one of our client databases this process is taking anywhere from 5-10 minutes to add the fields. This causes an issue where the app will timeout waiting. After plugging around and looking at the proc and trying different items i found it to only be for this one database and ONLY when there is data in the table. If i truncate the table and run the same procedure everything is fine. Tables all have same index on 4 columns and the columns being added are not indexed because of the stupid hoops we have to jump thru to pre-pivot data for our reporting package.
View 4 Replies View RelatedI have 6 SSRS reports for account receivable module. All of these 6 reports are for one specific business requirement. The difference between these reports are layout, number of columns and styles. I want user to choose one from these 6 reports based on their requirement.
When user clicks the menu in their application to initiate the report, Is there any way that the SSRS show list of these reports in a window so that user can choose one of them?
As I am working on a 3rd party ERP application, I cannot provide a form or window in that ERP to choose one report out of these 6 reports. I believe that there is a way to do this in Crystal report, but do not know how to do this in SSRS.
I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.Does anyone know the easy way to do this? / Syntax for the trigger?
View 3 Replies View RelatedI am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:
(A = Active, R = Repeat, L = liquidation......)
Here is a sample of the data I am trying to report off of:
CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL
) ON [PRIMARY]
[code]...
I have tried join to the same table - but I am still unable to get it to work.