Please point me in the direction of a tutorial that will do help me do the following:
The database has been previously created and the users first & last names are in the respective columns. There is also a column that should contain the full name of the user but as such it only contains <NULL>. Is there a way to pull the first name and last name from the db then write both names to column3?
Thanks - Sharon
Help!!, Does anyone know of a way that I can merge three seperate fields into one field in my sql statement.
EG.
SELECT catnumber, catnumber2,catnumber3
FROM Categories
WHERE Customer = 'xxxx' and itemnumber = 1 or 2 or 4
I want the catnumber, catnumber2, catnumber3 all in one field so I can sort by it.
If anybody could help me it would be greatly appreciated.
Thanks
Mike
i have two columns in sql which is datetime datatype
fromdate & todate, now i want to display it like fromdate-todate in my front end
select fromdate +'-'+ todate as date from table.
when i use this query , the error is
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Thanks for any help
I have an MS Access database that has 140 fields.
Every 4 fields are the same repeating pattern of types Memo, Number, Number, Currency. (e.g. Q1 - Memo, Q2 - Number, Q3 - Number, Q4 - Currency, Q5 - Memo ,etc. . . )
I need to query this and combine them into 4 columns. Do a group by on the 2nd field and calculate average on the fourth.
Is there any way to do this with queries? The fields were named in sequential order from Q1 - Q140.
This database isn't my fault, but unfortunately, I need to work with it.
I'm using a view where I do a few things, including merge two short text fields into one. Just wanted to know if there was an alternative way of doing this:
Example:
SELECT first_name + ' ' + last_name AS full_name
FROM names
It works - but it seems rather blah. Is there something that'd be more "proper"?
hi all,
can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.
1.number of imported imported animal type:
select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p
where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'
2. number of imported animal type that are licensed:
select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id
3.percentage: 2/1*100
select percentage=
((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id)
(select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100
4.local animal type:total pet records - 1
select number=
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)
5.local animal type that are licenced:total pet records-2
select number1=
(select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)
6.percentage: 5/4*100
select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100
thx,
vani
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
I have two tables. Status and Fourhistory tables.
Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.
Below i have given some possible sample scenarios which i face and the third table contains the expected output.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
)
[Code] .....
I am still fairly new to SQL, having been tasked with creating a csv file from data now someone else has left.
I can do the csv export using sqlcmd and I have the query sorted and am pulling out the right data, but it generates two rows, as one of the tables has multiple records per cardholder. See the query below, I know there is a way of doing it with XML PATH, I think, but it has got me slightly confused.
set nocount on
selectdbo.card.EncodedNumber,
dbo.card.IsEnabled,
dbo.Cardholder.FirstName,
dbo.cardholder.LastName,
dbo.card.ExpiryTime,
dbo.PersonalDataString.Value
[Code] .....
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Hi,
We are trying to consolidate sales order data from different sales locations where in which we have to merge multiple [more than 25] MSDE 2000 databases into a Single DB. What is the best way to do this?
At the end of the day i should have one DB which contains sales order data of all the sales locations.
Thanks,
SakthiVenkatesh.
Greetings... I'm working at consolidating several Reporting Services installations into one so we can decommission some old servers. We have two Report Servers that are in SQL Server 2000 and three that are in SQL Server 2005 (none are in a web farm configuration). I can upgrade the 2000 databases to 2005 format just fine, but now how do I merge these five ReportServer/ReportServerTempDB databases into one? I'm reluctant to redeploy reports to the "official" Report Server because we lose security, subscription, and scheduling information by doing that. So redeploy would cost us time and data loss over hundreds of reports, thus my hope to merge the databases at a data level. Is there a tool to automate this process, or has anyone tried this before? Thanks in advance.
Austin
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
[code]...
I've done some simple sql's for searching a field using Like,But this one is different.
I am adding a param named @searchText
I would like to bring back all records in all the fields listedbelow that has that string in the field...
WHERE a.manufacturer = b.manufacturerIDAND a.location = c.locationIDAND a.Status = d.statusIDAND a.EquipmentType = e.IDAND a.calLab = f.ID AND a.testTechnology = g.id AND (c.locationID = @location OR @location = 0)
So, each line/field above I want to search for the string and includein the dataset.
Anyone can point me in the right direction?
Thanks,
Zath
How can I get two fields to appear side by side in the DropDownList? Ex. FName and LName
Thanks
I have a table that tracks some dates, and I am looking for an SQL statemet that will check all of the fields to see if a prespecified data range is true. For exampe you enter a start and end date and then then the SQL statement would check about 12 different fields to see if any of the dates are within that range. I am trying to use an or statement, but to no avail. So if you have any help it would be greatly appreciated.
View 1 Replies View RelatedI have two issues I'm trying to deal with in my code.
1. I'm trying to group by first and last name, which are in two different columns
2. I'm trying to take an average of pay per miles.
Neither of these is working well. Actually, neither is working at all.
This is the code!
SELECT
OD.DriverID,
(W.FirstName + W.LastName AS 'Driver'),
DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0) AS Date,
DATENAME(dw,O.ReadyTimeFrom) AS DayOfTheWeek,
Count(OD.OrderID) AS 'Total Orders',
SUM(O.Distance) AS OrderMiles,
[Code] ....
Hi all,I'm running into a road block, and I know I've done this before. I'mgetting fields from two tables, and I need to do a count of similaritems with it showing some extra info.Here's my fields:Log.LogId - IntLog.LogDispatcherID - IntOfficer.OfficerID - IntOfficer.OfficerFirstName - VarcharOfficer.OfficerLastName - VarcharI can get the info I need without a count with this:select a.LogID,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDBut when I try to add a count and group-by it errors out:select Count(a.LogID) as LogCount,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDGroup By a.LogIDI've done this before, but this isn't working. It's giving the error"it is not contained in either an aggregate function or the GROUP BYclause" for each field other then LogID.How can I do this? I want output similar to this:LogCountLogDispatchIDOfficerName334Tom Jones422John Smith.... EtcThanks for any suggestions or ideas...Sam Alex
View 2 Replies View RelatedHow can I update each record in a table, based on a value in another tablewith a single SQL statement?For example, suppose I have the following two tables:Table1Name Something Color-----------------------------------------John GHAS BlueJohn DDSS BlueJohn EESS BluePaul xxxx RedRingo HJKS RedRingo FFFS RedSara hjkd PurpleSara TTHE PurpleJimi sdkjls GreenTable2Name Color------------------------John ?Paul ?Ringo ?Sara ?Jimi ?How can I update the color field in table 2 to correspond with the colorfield in table1 (so I can normalize the db and delete the color field fromtable1)?I know I could open table2 and loop through within my app; just wonderingabout a single SQL statement that would do it. I need a similar technique inother places as part of my app.Thanks,Calan
View 5 Replies View RelatedHi All,
I am trying to do something like this
Select * from Table 1 where (field1, field2, field3) IN (select field1, field2, field3 from table2 where fieldx = 'rt')
This can be done in oracle. Is there any equivalent in SQL Server ?
Can anybody please help !
Thanks.
PN
Hi All,
I am trying to do something like this
Select * from Table 1 where (field1, field2, field3) IN (select field1, field2, field3 from table2 where fieldx = 'rt')
This can be done in oracle. Is there any equivalent in SQL Server ?
Can anybody please help !
Thanks.
PN
I have multiple textboxes in a page. How do i make them insert their values to multiple fields on multiple tables using a button.
View 1 Replies View RelatedI have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)
Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.
I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?
Code:
CREATE TABLE #myTable (
YrStart INT,
MthStart INT,
YrEnd INT,
MthEnd INT);
[code]....
I'm having trouble using Aggregate functions with multiple fields.
Here is what I'm trying to do:
SELECT REPLACE(Cust_Key, sum(PRODUCT_CHARGE), count(Tracking_Number), Tracking_Number FROM Shipments = '2008-05-05'
Group By Cust_Key, Tracking_Number
I can get this to work when I use Group By for a single field like Cust_Key but I don't know how to make it work for multiple fields. What is the best way to write something that contains a few aggregate functions on top of multiple fields?
Thanks
Please see below post
View 2 Replies View Related
I have an exisitng report that lists unit name, provider, runday, shift, patient. The report groups by unit name and there is a page break after each unit name. So in the current environment the report prints one page per unit that contains all of the providers, rundays, shifts, and patients for that unit name its grouping on. So the report would like like this when it prints:
Unit A
Provider 1 Runday 1 Shift 1
patient 1
patient 2
Provider 1 Runday 1 Shift 2
patient 1
patient 2
Provider 1 Runday 2 Shift 1
patient 1
patient 2
Provider 1 Runday 2 Shift 2
patient 1
patient 2
Provider 2 Runday 1 Shift 1
patient 1
patient 2
Provider 2 Runday 1 Shift 2
patient 1
patient 2
Provider 2 Runday 2 Shift 1
patient 1
patient 2
Provider 2 Runday 2 Shift 2
patient 1
patient 2
PAGE BREAK
Unit B............(repeat data from page 1)
PAGE BREAK
Unit C............(repeat data from page 1
The end user would like the ability to keep the report as is but would like to also be able to print the report as one page per each unit name, provider, runday and shift. so it would look like this
Unit A
Provider 1 Runday 1 Shift 1
patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 1 Shift 2
patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 2 Shift 1
patient 1
patient 2
So I created a boolean parameter with a prompt of Page break by Unit, Provider, Runday & Shift? My thought is if the users sets this to False, the report will group on just Unit Name (the first example). If the user sets this to True, the report will group on Unit Name, Provider, Runday & Shift.
I set the grouping expression for this data table as:
=iif(Parameters!Grouping.Value = "False", Fields!unit_Name.Value,(Fields!unit_Name.Value,Fields!Lname.Value,Fields!Rundays.ValueFields!Shift.Value))
Within the expression editor window it displays a syntax error and the report will not run.
Any help would be greatly appreciated!!!!!!!!!
I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What isare the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.
TIA,
Barkingdog
I have the folloiwng fields:
ProbationLengthYears AS Decimal
ProbationLengthMonths AS Decimal
ProbationLengthDays AS Decimal
Sample Data:
ProbationLengthYears = 2.00
ProbationLengthMonths 0.00
ProbationLengthDays 0.00
I have successfully concatenated and converted all three fields into one field as Follow:
SELECT
CONVERT(varchar(10),CAST(cc2.ProbationLengthYears AS INT)) + ' Year(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthMonths AS INT)) + ' Month(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthDays AS INT)) + ' Day(s)'
The result is:
2 Year(s) 0 Month(s) 0 Day(s)
Ideally I would like the result to be as follow because the probation months and days are 0:
2 Year(s).
I have a table where I need to concatenate all values into one field separated by a comma. If the field is null display a blank value. This is my table structure and example output
Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)
[code]...
I have a query that produces seperate rows for people, but I want to combine them into one place. I tried doing this in SQL but apparently it's not very easy in SQL Server. You need to loop through a table using cursors. I'm not quite that advanced with SQL Server and was wondering if there might be an easier way just using SSRS.
In other words I have a table as such:
1 John Smith
2 Jane Doe
3 Matthew Jones
And I'd like to create one textbox that contains the following:
"John Smith, Jane Doe, Matthew Jones"
I've been drawing a blank. Anyone have any ideas?
Levi
I have a very complex (for me, anyway) data transformation problem.
I've been given a flat-file of physician data from another system which must be automated for entry into the SQL server on a regular basis.
This was no problem until we discovered that several fields (all of which we wanted to use) had multiple pieces of information in them, separated by semicolons.
Well, this didn't seem to be too big of a problem, so I wrote a DTS activeX script to handle it. This is what I originally wrote:
'*******************
Function Transform()
'Declare variables
Dim strOffice
Dim strOfficeNew
Dim cChar
Dim x
Dim y
Dim z
'Scrub values into new rows
strOffice = DTSSource("Col050")
x = 1
y = len(strOffice)
z = 1
While x <= y
cChar = Mid(strOffice, x 1)
If cChar <> ";" Then
strOfficeNew = strOfficeNew & cChar
Else
DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z
DTSDestination("Addr_Office") = strOfficeNew
strOfficeNew = ""
z = z + 1
End If
x = x + 1
Wend
'Insert final record after last semicolon
If strOffice <> "" Then
DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z
DTSDestination("Addr_Office") = strOfficeNew
End If
Transform = DTSTransformStat_OK
End Function
'*********************
This, of course, didn't work. WHat I got was the last part of the parsed data, which for the first record, was the second Address in the field.
I searched around, and found the following script that is supposed to allow multiple rows off of a single row, but I can't seem to merge the two and still get the data out clean.
'**********
Dim nCounter
nCounter = 4
Function Main()
if nCounter > 0 then
Main = DTSTransformStat_SkipFetch
DTSDestination("PatientNumber") = DTSSource("PatientNumber") Select Case nCounter
Case 1
DTSDestination("PhysicianType") = "Admitting" DTSDestination("PhysicianId") = DTSSource
("AdmittingPhysician")
Case 2
DTSDestination("PhysicianType") = "Attending" DTSDestination("PhysicianId") = DTSSource
("AttendingPhysician")
Case 3
DTSDestination("PhysicianType") = "Referring" DTSDestination("PhysicianId") = DTSSource
("ReferringPhysician")
Case 4
DTSDestination("PhysicianType") = "Consulting" DTSDestination("PhysicianId") = DTSSource
("ConsultingPhysician")
End Select
nCounter = nCounter - 1
else
nCounter = 4
Main = DTSTransformStat_SkipInsert
end if
End Function
'**************
I'm not a VB Script expert, so there's probably something very simple that I'm missing here... if someone could point it out, I'd be greatly appreciative.
Jaysen
I have two tables - products and productpropertyvalue
I need to select multiple fields from the productpropertyvalue as it corresponds to the product id. The script I am using is
select a.id, a.productname, a.siteprice,
b.propertyvalue
from product a, productpropertyvalue b
where a.id = b.productid and propertyid=590
This allows me to extract only 1 propertyid. I need to make it add 3 other columns for propertyid=589, 617, 615
Any help solving this problem would be appreciated - thanx!
Ron
What I need to do is re-populate a unique number into multiple fields,
Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.
Please help!!!!! Below are how the tables are set up and an example of the result.
TABLE 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST_TBL]
GO
CREATE TABLE [dbo].[TEST_TBL] (
[Empl_Num] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mid_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hours_Day] [numeric](18, 2) NULL ,
[Hours_Wk] [numeric](18, 2) NULL ,
[KR_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[Date_Term] [datetime] NULL ,
[Empl_Type] [int] NULL ,
[Cost_Centre] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Empl_Status] [int] NULL
) ON [PRIMARY]
GO
TABLE 2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST2_TBL]
GO
CREATE TABLE [dbo].[TEST2_TBL] (
[EmpNumber] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[KR_ID] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[EmpStatus] [int] NULL ,
[EmpType] [int] NULL ,
[CommonName] [char] (32) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Query goes as follows for table 1:
SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber
Query goes as follows for table 2:
SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber
From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.
Example:Table 1
000001,Jackson,James,Sam,7.6,38,12345,475431212
Example:Table 2
000001,Jackson,James,Sam,7.6,38,12345,475431212
001000,Jackson,James,Sam,7.6,38,,475431212
I hope this helps
Thanks in advance