I need to use a self join on a table to get it to return one line item ofr all of the line items within the table. The subscriptions table has many line items containing product code. I need to eb able to return one line item containing each of the different product codes.
Table Subscriptions
ID, Product_code
101 ChaptA
101 ChaptB
101 ChaptC
Need to return
ID, Product_codes
101 ChaptA ChaptB ChaptC
Hi All
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
I need to figure out hours based on StartTime and EndTime (year does not matter) from the first table and totaled for the entire week based on the effective date in the second table.
I need it to return the follwing:
WorkHourGroup TotalWorkHours
TJOHNSO 24 Hours
Table 1 -'workhour'
WorkHourGroup DayIDStatrtTimeEndTime
TJOHNSO0NULLNULL
TJOHNSO11899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO2NULLNULL
TJOHNSO31899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO4NULLNULL
TJOHNSO51899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO6NULLNULL
Table 2 -'workhourgroup'
WorkHourGroupWorkHourDescEffective Date
SMBSMB Work Week2007-09-11 00:00:00.000
SMITHBSTANDARD2008-01-12 00:00:00.000
TJOHNSOJohnson12008-01-11 00:00:00.000
Any ideas on how to accomplish this?
Thanks,
DZ
How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.
I am checking for all parishes with no associated drivers. Currently I only have 2 of such.
The regular left join
select parish.name, driver.fname from parish left join driver on driver.parish=parish.name
Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.
I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level.
Here is the code:
SELECT
fs.TransactionId
,fs.DateKey
,dc.FirstName
,dc.LastName
,dc.CompanyÂ
[code]....
Due to case by statement and having multiple category I have to use multiple group by.
select TransactionId
,DateKey
,FirstName
,LastName
,Company
,City
,Sum(CategoryAPurchase) CategoryAPurchase
[code]....
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies View RelatedHi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies View Relatedhi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies View RelatedWe have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
View 1 Replies View RelatedI have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
kam
HEllo can anybody tell me how to monitor a long store procedure
line by line. Also how to put progress bar in it to tell user how
much is done.
Sabih.
Hi,
When creating a line chart, I would like to be able to show Markers for the data points, but no line between these points (as you can do in excel).
I have set the line setting to none (for the lines of interest), however the lines still show.
Is this a bug, or am i missing something obvious settings-wise?
Cheers,
M
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:
SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?
HiIt's my stored procedure 1 CREATE PROCEDURE singleSearch2
2 @SQ nvarchar(30),
3 @pType nvarchar(11),
4 @pCol nvarchar(11)
5 AS
6 BEGIN
7 DECLARE @SQL NVarChar(1000)
8 SELECT @SQL='SELECT *,'
9 SELECT @SQL=@SQL+' CASE RTRIM(LTRIM(op))'
10 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e'+CHAR(39)+' THEN '+CHAR(39)+'اجاره'+ CHAR(39)
11 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r'+CHAR(39)+' THEN '+CHAR(39)+'رهن'+CHAR(39)
12 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'f'+ CHAR(39)+' THEN '+ CHAR(39) +' Ù?روش '+CHAR(39)
13 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r'+CHAR(39)+' THEN '+CHAR(39)+ 'اجاره - رهن '+CHAR(39)
14 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - Ù?روش '+CHAR(39)
15 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r f'+CHAR(39)+' THEN '+CHAR(39)+' رهن - Ù?روش '+CHAR(39)
16 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - رهن - Ù?روش'+CHAR(39)
17 SELECT @SQL=@SQL+' ELSE op END AS xop, CASE LTRIM(RTRIM(type)) '
18 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'z -'+CHAR(39)+' THEN '+CHAR(39)+'زمین'+CHAR(39)
19 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'m -'+CHAR(39)+' THEN '+CHAR(39)+'مسکونی'+CHAR(39)
20 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'t -'+CHAR(39)+' THEN '+CHAR(39)+'تجاری'+CHAR(39)
21 SELECT @SQL=@SQL+' ELSE [type] END AS [xtype] FROM [data] '
22 SELECT @SQL=@SQL+' WHERE ([type] LIKE %'+CHAR(39)+@pType+CHAR(39)+'%) AND ('+@pCol+' LIKE %'+CHAR(39)+@SQ+CHAR(39)+'%)'
23 Exec (@SQL)
24 END
25 GO
and i face this error: Line 1: Incorrect syntax near '-'.
Hi
Anyone have any idea how to make a line style dashed or dotted in a line chart please?
If I change the series style to dashed or dotted it still appears as a solid line, yet the legend displays a dashed or dotted line....
Thanks
My question is about coding style for specifying constraints when creating tables.
Two styles for defining constraints:
In-line:
CREATE TABLE Fruit
(
FruitID INT IDENTITY(1,1)
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED,
FruitName NVARCHAR(50),
FruitTypeID INT
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY
REFERENCES FruitTypes (FruitTypeID) ON UPDATE CASCADE,
DateCreated DATETIME DEFAULT GETDATE()
)
Out-of-line:
CREATE TABLE Fruit
(
FruitID INT,
FruitName NVARCHAR(50),
FruitTypeID INT,
DateCreated DATETIME
)
ALTER TABLE Fruit ALTER COLUMN FruitID INT NOT NULL
ALTER TABLE Fruit ADD
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED (FruitID),
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY (FruitTypeID)
REFERENCES FruitTypes (FruitTypeID),
CONSTRAINT DF_fruit_date_created DEFAULT
GETDATE() FOR DateCreated
Which style do you prefer and why?
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
Hi
If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
Then i get Images(6)Images(1)Images(1)
But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this
Images(1)Images(1)
But i cant get it to work.
I want read
dr[2] = SqlDataReader dr1.item("lastmane");
from database
but I have error expected
I don't while
SqlDataReader dr1 = cmd.ExecuteReader();
dr1.Read();
dr[2] = SqlDataReader dr1.item("lastmane");
select i.a,
w.c,
w.e
from table1 i,table2 w where
i.a=w.a (Here c is the primary key for each a)-iam getting o/p like this
a c e
aaa x 2
aaa y 10
bbb y 5
ccc x 3
ccc y 4
i need o/p in this way (ie sum of e for each a)
a e
aaa 12
bbb 5
ccc 7
Is it possible
Thanx
I´m trying to get a query where I´ll get an answer like this:
+--------------+----------------+----------------+------------------+-----------------+
| champion_id | champion_name | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+----------------+------------------+-----------------+
| 1 | Legolas | boots | ... | sword |
+--------------+----------------+----------------+------------------+-----------------+
This is the scenario how I set up my tables (let me know if I should change it):
table champions
+--------------+----------------+
|champion_id | champion_name |
+--------------+----------------+
| 1 | Legolas |
+--------------+----------------+
table equipment
+--------------+----------------+------------------+-----------------+
| champion_id | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+------------------+-----------------+
| 1 | 5 | ... | 22 |
+--------------+----------------+------------------+-----------------+
table items
+--------------+----------------+
| item_id | item_name |
+--------------+----------------+
| 5 | boots |
+--------------+----------------+
| 22 | sword |
+--------------+----------------+
But I still don´t know how to call the equipment table and get champion name and item names for the numbers. What did I do wrong and how can I fix it?
The foreign keys are in place.
Okay I've posted something like this in another area but it doesn't seem to be focused towards the right people. I need ya'll SQL experts!
I have a database table that has 5 columns: Time Status Message Source IP
The Status column has only two choices "up" or "down"
I'm looking for some kind of advanced query that will do the following:
I need the query in my application to pull the table and then match up IP, Status, and Message columns. It should only find One match and then once it has found a match it needs to recognize that one is an "up" status and one is a "down" status and then remove them from the database.
Does that make sense? Is it impossible?
Hi,
This is a where clause I am using in a search.
WHERE (ADDRESS_STREET LIKE '%' + @Search + '%' )
I am trying to do a search which returns the most matching record. For example if I have a record with Denver as text . If I search for Denvr the spell error is intended , I will not get the result. How can I create a stored procedure to counter probable spelling errors and return matching results in a ranked order.
Thanks
HI,
say I have a list from an sql statement (results list)
this list contains 10 items
In another table, in one particular column - there is a match for one of these items from the initial list.
SO... this may be the list
_____________________
itemnumber
1
2
3
4
5
6
7
8
9
10
------------------------------
in the other table there is a match...
but just for one item on that list.
____________________
othertablefield
11
13
14
3 <------ match
99
78
----------------------------
How do I find that match with my sql statement?
Dear all,
I have two table, both table have a col which stored the data in CSV format, ie "ab , bc, de", etc
I would like to select the item where "any one item" in first table is same as "any one item" on second table.
I have alread have the private CSV function which can convert the string and return as table. However, I find i cannot do it like this select ..... where dbo.CSVfunction(tableA.colA)
Thx//CSV functiondeclare @separator char(1) set @separator = ','
declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50)))
select @array = stuff(@array, 1, @separator_position, '') endre @separator char(1) set @separator = ','
declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50)))
select @array = stuff(@array, 1, @separator_position, '') end
My company is going to start a Cancel web site so customers can cancel their future orders by simply filling out a form. I was wondering what would be the best way to compare the cancel db to db that is used to store customer information and order information.
The cancel website will only hold the name, address, and Credit Card Number used. There will not be a customer/order number. The simple Credit Card to Credit Card search will be easy but I was more worried about comparing the rest of the information in case the Credit Card search fail. I was told the "standard" look up was to take the first 3 letters of the first name and the last 3 letters of the last name. If that comes up with more then one record, take that set and see if the zip code matches up and/or part of the street address matches up (counting we do not have very reliable people inputting the information into the system is not going to help this search).
Anyway, any suggestions on comparing the data would be great.
Thanks ahead of time