T-SQL (SS2K8) :: Join 2 Reference Values In One Line
Jun 12, 2015
I have 2 tables ... order table (shipping city and destination city) and a referential city table:
1:New York
2:Chicago
3:Atlanta
etc...
In the order table the shipping address and destination address have the identity's a values, I want to write a join to show the names of the cities instead. I'm doing something like this, is there a way to do it in one select?
;with srcemkt as
(
select ID, OrderNumber, b.MarketCenterCity as 'SourceMKT',
from Orders as a
join MarketCity as b
[Code] .....
View 6 Replies
ADVERTISEMENT
Nov 6, 2014
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
its taking so much of time how t o solve ?
View 1 Replies
View Related
Mar 6, 2014
I'm going around in circles trying to visualise what's happening in this code,
declare @ReturnValue int, @ProcessFlag int
exec @ReturnValue=spGetProcessFlag @ProcessFlag output
select @ReturnValue as ReturnValue, @ProcessFlag as ProcessFlag
I get the feeling that the code runs spGetProcessFlag, and assigns the value to either @ReturnValue or @ProcessFlag.
By the way - next task is to direct SSIS whether to fail package or continue running.
View 1 Replies
View Related
Sep 11, 2014
We have a stored procedure that makes a decision to pull records from one of two servers.
If one of these servers became unavailable but was no longer queried would the stored procedure still work? Doesn't SQL recompile stored procedures periodically?
Something like this:
If @ServerAIsDown=1
begin
select * from ServerB.dbo.MyTable
end
else
begin
select * from ServerA.dbo.MyTable
end
View 2 Replies
View Related
Sep 15, 2014
Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.
View 8 Replies
View Related
Apr 16, 2014
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
View 2 Replies
View Related
May 19, 2014
I am working with a large amount of text data in a single column. I am in the process of pumping that column of data into a delimited text file. The data in the column has new line characters that I need to remove. I need to data to be in a single line in the text file.
So far the things that I have tried are not working. What I try for removing the new line characters?
View 3 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
May 14, 2014
I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.
Currently it is displaying.....
MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real
-===== 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
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL
Output should display as
MeterId Period Reading
4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15
View 4 Replies
View Related
Aug 4, 2015
I have a table which has been import from a TXT file. Most lines can be ignored, but when I get a line like this
'qwwwwwww 399 0 75124M - 2015-02-06 13:07:29'
I need to insert into a table as 6 columns ('-' is a value)
View 7 Replies
View Related
Mar 29, 2007
HI, I keep getting this warning with a lookup (full cache mode) that retreives data form a table that contains the following information:
SRCE_SYS TABLE_NAME FIELD_NAME CODE ENGLISH_DESCRIPTION
STATIC STATIC PM_PC_TX_TYPE_CODE G GROSS
STATIC STATIC PM_PC_TX_TYPE_CODE E EXCESS
STATIC STATIC PM_PC_TX_TYPE_CODE F FACULTATIVE
STATIC STATIC PM_PC_TX_TYPE_CODE S SURPLUS
STATIC STATIC PM_PC_TX_RIDER_CODE BOAT BOAT
STATIC STATIC PM_PC_TX_RIDER_CODE RIDER RIDER
STATIC STATIC PM_PC_TX_RIDER_CODE CONT CONTENTS
The column tab matches SRCR_SYS, TABLE_NAME and FIELD_NAME (using constants defined in a derived column transform) .The code column comes from the source. We want to retreive the english_description colum from SRCR_SYS, TABLE_NAME,FIELD_NAME and CODE in the dataflow.
I would normally ignore the warning but sometimes, it seems that the lookup does not match any values and enabling memory restriction on the advanced tab resolve the issue and suppress the warning.
As I said, I keep getting this warning and I don't know why since there are no duplicates in the table? Am I missing something?
Thank you,
Christian
View 3 Replies
View Related
Apr 14, 2014
How to update column with multiline comments.
eg. I need to update Notification column in my table as below.
Exit Formalities Notification sent to:
Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress
View 4 Replies
View Related
Jun 4, 2007
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
View 5 Replies
View Related
Oct 18, 2015
I have a table with constantly changing data - stock.
I want to start monitoring the value of stock at the end of each month. I can do this with a simple query, export the results to Excel and store on the network.
My question is:
Are there better ways to do this with SQL Server?
I thought of a monthly "job" that does the query and outputs to a file. (Need to be able to look at each month separately though for trend monitoring.)
Then wondered, if I should have an extra table to store the data and write queries on that in the future?
View 1 Replies
View Related
May 9, 2006
Hi,
We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).
When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.
We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.
Please help me to solve this problem.
Regards,
Gopi
View 3 Replies
View Related
Jan 27, 2007
am using a line chart to display series of results over time - data is got from and AnalysisServices cube.
problem is that sometimes there is no value for the result - and the chart doesnt display that as zero
e.g. with the following data
week 1 10 items
week 2 12 items
week 3 14 items
week 4 8 items
week 6 12 items
the chart will join the week 4 result of 8 items directly to the week 6 result of 12 items
is there anyway of changing this so the line goes from 8 items on week 4 to ZERO items on week 5 and then 12 items on week 6 ???
View 1 Replies
View Related
Sep 22, 2015
I have a requirement for the following SSRS expression ( I am using Report Builder 3.0). The end user will input a selection via a mutli select parameter (financial month/s), Â if all month are inputted then sum(Field1.Month1.value...Field12.Month12.value )/12 * 100 else if parameter.value =1 then sum(field/s.values for selected month)/1Â *100 else if month.values = 1 AND 2 then sum(field/s.values for selected month)/2 *100 and so on for any combination of the selected inputs.
Put in simpler terms I would like to calculate a percentage from a field/s containing relevent values, based on month/s the user inputs.
View 2 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Jun 17, 2008
Hi everyone!
How can i change one of the values in an existing line (id = 10)
thanks a lot to this forum
landau66
View 1 Replies
View Related
May 16, 2008
I have a report I'm writing and have got a problem that has stumped me.
The data the report is based in is in this format:
Date Type
1/1/08 A
1/3/08 B
3/1/08 C
3/5/08 B
3/10/08 A
3/12/08 C
3/20/08 A
Management wants a report showing a line chart that summarizes the data by Month and by Type. So, there are 2 series depending on the Type and data values are based on the count of each Type. So, in the example above the x-axis group would be month, the values would be count(Type) and there would be 2 series €“ Series 1. A, B and Series 2. C
I€™ve defined the x-axis to be Month(Fields!DateOccured.Value). However, they want the x-axis to show every month for the entire year not just what is in the database. So, I defined the x-axis to have a scale of 1 to 12 and the major interval is set to 1. This displays 1 through 12 on the x-axis.
The problem is when there is no data (null) for a particular month. Instead of showing 0, the line chart does not plot anything and the line is drawn to the next displayed point. So, for example on the data above the line chart would plot:
January
Series 1 €“ Qty. 2 Series 2 €“ Qty. 0
February
Null
March
Series 1 €“ Qty. 3 Series 2 €“ Qty. 2
The line chart would draw a line from January to March skipping February. I need to display the null values as 0 and not null indicating no occurrences for the month rather than no data present.
Thanks!
View 11 Replies
View Related
Dec 13, 2007
I can't figure out how to get my line chart to break when there isn't a value. For example, I have a trend line over 4 time periods. The 3rd time period is missing a value. Instead of the line ending at the 2nd period and picking up again at the 4th time period, it's connecting the line 2nd to the 4th period. I'd like it to break and for there to be no line appearing in the 3rd period. I bet that's as clear as mud, but let me know if you have any questions.
Thanks!
sash
View 1 Replies
View Related
Aug 6, 2014
I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating
View 1 Replies
View Related
Dec 1, 2015
I have this query:
SELECT
         ID1,
         ID2,
          type,
         (case when type = '1' then sum(value) else '0' end) as Value1,
         (case when type = '3' then sum(value) else '0' end) as Value2,
          (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHEREÂ ID1 = 'x' and ID2= 'y'Â
GROUP BY ID1, ID2, Type
Which returns:
ID1    ID2       Type    Value1    Value2    Value3
005Â Â Â 11547Â Â Â 0Â Â Â Â Â Â Â Â Â 0.00Â Â Â Â Â Â Â Â 0.00Â Â Â Â Â Â Â 279.23
005Â Â Â 11547Â Â Â 1Â Â Â Â Â Â Â Â Â 15.23Â Â Â Â Â Â 0.00Â Â Â Â Â Â Â 0.00
005Â Â Â 11547Â Â Â 3Â Â Â Â Â Â Â Â Â 0.00Â Â Â Â Â Â Â Â 245.50Â Â Â Â 0.00
And I want to obtain this result:
ID1Â Â Â Â ID2Â Â Â Â Â Â Â Value1 Â Â Â Value2 Â Â Â Value3
005Â Â Â 11547Â Â Â Â 15.23Â Â Â Â Â Â 245.50Â Â Â Â 279.23
View 5 Replies
View Related
Aug 20, 2013
I am facing issues with a LEFT JOIN in my query. It takes 45 secs to process on the production server due to huge number of records.building a query to avoid the LEFT JOIN. I am Trying to use UNION ALL and it works much faster except that I am stuck in the last bit.
scripts (sample):
CREATE TABLE [dbo].[tbl_PersonDetails](
[PersonID] [int] NOT NULL,
[LeaveTimeId] [int] NOT NULL
) ON [PRIMARY]
[code]...
Need Rows from tbl_PersonDetails macthing (all 3 below) following criteria :
1. tbl_PersonDetails.PersonID is present in tbl_PersonLeaveDetails
2.tbl_PersonDetails.TimeID does not fall between any of the aligned (matching personid) FromTimeID and ToTimeID in tbl_PersonLeaveDetails.
3. not using LEFT join
View 9 Replies
View Related
Apr 25, 2014
I've come across a piece of code which i have never seen before.
ON T.CT_YEAR in
(
case
.[DBO].[FN_GET_YEAR]
(
CAST
(
C.[YEAR] AS VARCHAR(4)
[Code] ....
The CT_Year column is simply C for current year L for last year, O for Other, N for Next.
The Function simply returns the year value.
View 4 Replies
View Related
Aug 12, 2014
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
[Code] .....
View 2 Replies
View Related
Jan 29, 2015
I am trying to use this logic into a query:
Select P.S,E.S,E.R
from Pack P(nolock)
join Exp E on P.Id=E.O
on E.R is null
case when E.R is not null then ''
else ''
end
where P.s='PLT000044'
I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.
View 2 Replies
View Related
Feb 28, 2008
Hello,
I need to know what means are available to change the Minimum and Maximum Y-Axis scale values rather than leave them simply as they are in the properties.
Right now whatever min/max values I enter into the scale for the Y-Axis are unchangeable.
I need to be able to change the min/max scale values based on the value of the data at run time.
Any help is appreciated.
Thanks.
View 6 Replies
View Related
Jun 5, 2014
In t-sql 2008 r2, I have 2 select statements that I would like to join by calendarID . I would like to obtain the results in the same query but I keep getting syntax errors.
The first select is the following:
SELECT Section.number, Section.homeroomSection,
Course.number, Course.name, Course.homeroom, Calendar.calendarID
FROM Section
INNER JOIN Course
ON Course.number = Section.number
INNER JOIN Calendar
ON Course.calendarID = Calendar.calendarID
The second select is the following:
SELECT Person.studentNumber, Enrollment.grade, Calendar.calendarID, Calendar.name, Calendar.endYear
FROM Enrollment
INNER JOIN Person
ON Enrollment.personID = Person.personID
INNER Calendar
ON Enrollment.calendarID = Calendar.calendarID
I would like the following columns to display:
Section.number, Section.homeroomSection, Course.number, Course.name, Course.homeroom, Calendar.calendarID
Person.studentNumber, Enrollment.grade, Calendar.name, Calendar.endYear
Thus can you show me how to change the sqls listed above to obtain the results I am looking for?
If possible I would like the sql to look something like the following:
select Section.number, Section.homeroomSection, Course.number, Course.name, Course.homeroom, Calendar.calendarID
Person.studentNumber, Enrollment.grade, Calendar.name, Calendar.endYear
from
(SELECT Section.number, Section.homeroomSection,
[Code] ....
View 6 Replies
View Related
Jul 16, 2014
I have 2 tables:
Table Transaction
-----------------
EmpID TransDate
00001 1/1/2014
00001 1/2/2014
00001 1/3/2104
00001 1/4/2014
00001 1/5/2014
00001 1/6/2014
00001 1/15/2014
00001 2/1/2014
00001 2/2/2014
00001 2/20/2004 ....
Table Master
---------------------------
EmpID EffectiveDateFr Group
00001 1/1/2014 A
00001 1/5/2014 B
00001 1/9/2014 C
00001 2/1/2014 B
00001 2/20/2014 A ....
I want to create query the output should be:
EmpID TransDate Group
00001 1/1/2014 A
00001 1/2/2014 A
00001 1/3/2104 A
00001 1/4/2014 A
00001 1/5/2014 B
00001 1/6/2014 B
00001 1/15/2014 C
00001 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 A
View 4 Replies
View Related
Aug 12, 2014
join three tables and wont be duplicate records.
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
[Code]....
View 4 Replies
View Related
Mar 9, 2015
I have this statement:
SELECT top 100 P.LastName ,
P.FirstName,
P.MiddleName,
P.PractitionerTypeID,
P.SuffixID,
A.Linenumber1,
A.LineNumber2,
Z.City,
[code]....
Seems like no matter which join type I choose i still get duplicates.
View 5 Replies
View Related
May 28, 2015
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
View 4 Replies
View Related
