Query Assistance Combining Columns Into One
Oct 5, 2006
I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column.
My Query:
Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID
ORDER BY Product_ID DESC
View 10 Replies
ADVERTISEMENT
Mar 3, 2000
I have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.
Do anyone know the syntax??
Thanks, Vic
View 1 Replies
View Related
Jun 19, 2008
Hello everybody,
I have the following problem.
I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.
I would like to make a query that retreives al send emails, its recipients and message_size.
SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles
Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55
But i'd like to get;
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55
Can anyone help me with this query?
Best Regards,
Sidney
View 3 Replies
View Related
Aug 29, 2005
Hi
What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??
Thanks
View 8 Replies
View Related
Jul 18, 2007
I need to combine my first 3 columns into 1.
SELECT [Category]
,[Sub Category - I]
,[Sub Category - II]
,[MSC #]
,[Price]
FROM [Pricing Analysis].[dbo].[Table1]
View 2 Replies
View Related
Mar 21, 2008
Hey everyone,
I have a windows app with a particular report that has 2 date columns in it. I need to combine these two columns based on a choice that the user makes on a previous form. So the user enters two dates (beginning and ending) and then makes a selection. So if my query brings back
Column1 Column2
1/1/2000 1/1/2004
2/2/2002 2/2/2007
3/3/2005 3/3/2008
.... .....
If the user selects option 1 column1 is displayed on the report, option 2 then column2, and if they select option 3 then columns 1 and 2 must be combined together based on other criteria.
I'm passing a parameter with the selection to my report, is there any way to combine these columns strictly in the report, or would it be easiest to do this in my sql query, and if so how? Keep in mind that I must keep each column separate because after the user makes the selection I have to make certain comparisons to each column before combining them. If any more information is needed then just let me know and I will post it as well.
Thanks in advance,
Aaron
View 4 Replies
View Related
Oct 13, 2005
Greetings,
I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:
order# Part# Addon1 Addon2 Addon3
What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:
Old Table: addon1 Addon2 Addon3
New Table:
Addon1
Addon2
Addon3
Of course I would also provide a link between the part and the applicable addons.
Thanks
View 1 Replies
View Related
Oct 18, 2013
I have a query (SELECT * FROM Pricing) that produces the following results
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23
I want to convert this to the following :
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23
We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23
View 1 Replies
View Related
Aug 7, 2014
I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
View 3 Replies
View Related
Feb 23, 2006
Using MySQL 5, MySQLQuery (latest). Complete Newbie,
I have the following query:
SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;
It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.
I think the query above is pretty self explanatory!
Many thanks.
View 4 Replies
View Related
Feb 8, 2007
Basically what I am doing is uploading inventory information from our point of sale/inventory management software into our website. I have a third party app that does most of the work for me but I need a view of our item table in our POS that this 3rd party software will use as the data source for what gets uploaded into the web. I have created the view with the following sql statement:
CREATE VIEW dbo.vwsfwebitemswine
AS
SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName
FROM dbo.Item INNER JOIN
dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID INNER JOIN
dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID
WHERE (dbo.Item.DepartmentID = 7) AND (dbo.Item.WebItem = 1)
As you can see I am pulling from 3 tables. Item, Category and Department so that I can get the actual category and department names not just their numeric ID codes referenced in the standalone item table. The view data looks like this:
UPC ….. CategoryName DepartmentName
12345….. MerlotWine
34556….. ChadonnayWine
Etc…
What I need is an additional column created that combines the DepartmentName column values with the CategoryName column values and includes a ~ in between like this.
UPC ….. CategoryName DepartmentName Combined
12345….. MerlotWine Wine~Merlot
34556….. ChardonnayWineWine~Chardonnay
Etc…
I’m not a SQL expert by any stretch. I’ve gotten this far using this forum but I’m stuck. Any help would be greatly appreciated.
View 1 Replies
View Related
Jul 20, 2005
Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully
View 5 Replies
View Related
Aug 2, 2006
I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?
View 5 Replies
View Related
Oct 23, 2001
How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).
Thank you,
Andrew
View 2 Replies
View Related
Jan 14, 2008
I am trying to build a related article display. I have a SQLDataSource that I want to be able to select information (Category) from the table (Articles) based on a querystring (ID). SELECT [Category] FROM [Articles] WHERE ([ArticleID] = @ID) which for an example, lets say ID = 1, and it results as Category = Health.That is easy enough, but how would I then select all columns from the table WHERE Category = Result of first SELECT? SELECT * FROM [Articles] WHERE ([Category] = ??? Result of prior select) Can this be done in 1 select command, or would a store procedure need to be written? I am a little lost, sincr I am using a SQLDataSource, and it will be displayed with a Repeater. Thanks!
View 2 Replies
View Related
Jul 28, 2005
First off, here is my query:SELECT DeviationDist.DEVDN, DeviationDist.DEVDD, DEVDA, DEVCT, DEVST, DEVBG, DEVDV, DEVPS, DEVAT, DEVCN, DEVCF, DEVCP, DEVCEFROM DeviationDistINNER JOIN DeviationContact ON DeviationContact.DEVDN = DeviationDist.DEVDNWHERE DeviationDist.DEVDN = '200270'ORDER BY Deviationdist.DEVDN DESCI'm joining the deviationcontact table to the deviation dist table by DEVDN. This query works fine except when the DeviationContact table doesnt contain any records for the DEVDN that the DeviationDist table does contain. In my app, Deviationdist will always have an record for each DEVDN, but DeviationContact may not. I would like to still get the results back for what records exist in the DeviationDist table, even if DeviationContact has no associated records, but still show them if it does...
View 1 Replies
View Related
Nov 30, 2005
Hi,
I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:
ClaimNumber LineNumber SequenceNumber
abc123 1 1
abc123 2 2
abc123 3 3
def321 5 1
def321 6 2
ghi456 2 1
jkl789 3 1
jkl789 4 2
So...
SELECT ClaimNumber, LineNumber, <Some Logic> AS SequenceNumber FROM MyTable
Is there any way to do this?
Thanks,
Dennis
View 4 Replies
View Related
Mar 5, 2007
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!
View 10 Replies
View Related
Mar 8, 2006
I am trying to pull some "notes" from a sql database.....the notes thatare put into the database come via the web and the user is entering itfor a certain task. they are stored in their own table and field andget assigned and incremental ID #.I want to be able to pull up the latest entry to the task, not all ofthe notes just the latest one.. The entry does get a timestamp in thefield so I am thinking I might be able to look at that fieldsomehow.... Right now my query shows all notes / entries for the task.I am an intermediate sql query guy so I hopefully expained enough toget assistance.Let me know if you need to know more.
View 2 Replies
View Related
Nov 12, 2007
I am trying to get a running total. I need the query to reset the running total for each year/id. Below is the sample query. Any help would be greatly appreciated.
Code:
CREATE TABLE #valueset (k1 int, date datetime, groupByThis nvarchar(50), c1 int)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13024, '09/14/2007', '2007', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/15/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '10/13/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/14/2007', '2007', 2)
SELECT v.k1, v.date, v.groupByThis, v.c1, RunningTotal=SUM(a.c1)
FROM ( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) v
CROSS JOIN
( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) a
WHERE a.Rank <= v.Rank
AND a.groupByThis = v.groupByThis
GROUP BY v.k1, v.date, v.groupByThis, v.c1
ORDER BY v.groupByThis, v.date
Drop Table #valueset
Expected Results:
k1 date groupbythis c1 RunningTotal
13025 2006-09-15 00:00:00.000 2006 1 1
13025 2006-10-13 00:00:00.000 2006 1 2
13024 2007-09-14 00:00:00.000 2007 1 1
13025 2007-09-14 00:00:00.000 2007 2 2
13025 2007-11-09 00:00:00.000 2007 1 3
I am almost there - any help would be great. I need to reset the running total after every "groupbythis" for every "k1"
Thanks.
View 1 Replies
View Related
Jun 25, 2007
I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.
Source table.
Address
ID
Workstation
Test-a
1
WS1
Test-b
2
WS1
Test-a
5
WS2
Test-d
3
WS2
Test-b
7
WS2
I am trying to write a query that will display this result into Excel.
Address
Duplicate
WS1
WS2
Test-a
Yes
1
5
Test-b
Yes
2
7
Test-d
No
Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.
Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.
I appreciate any help I can get on this.
View 7 Replies
View Related
Mar 10, 2008
Hello,
I have two tables, USER and ROTATION. What I would like to display is the Maximum Start_Date and Maximum End_Date from the ROTATION Table along with the First_Name and Last_Name that is associated with that max entry.
When I just create a select statement that asks for the max value of the Start_Date and End_Date, I get the value I see. However when I try to add the First_Name and Last_Name to the mix, I get the Max Start and End Date Values of all the people in the User Table. I only want one result returned. I'm probably missing something very simple here. Any assistance would be appreciated.
The SQL code I am currently using:
Code SnippetSELECT [USER].FIRST_NAME, [USER].LAST_NAME, MAX(ROTATION.ONCALL_START_DATE) AS Expr1, MAX(ROTATION.ONCALL_END_DATE) AS Expr2
FROM ([USER] INNER JOIN
ROTATION ON [USER].USER_ID = ROTATION.USER_ID)
GROUP BY [USER].FIRST_NAME, [USER].LAST_NAME
Example of my desired result:
First_Name Last_Name OnCall_Start_Date OnCall_End_Date
John Doe 8/10/08 8/17/08
John Doe should be the only result returned because he would have the highest OnCall_Start and OnCall_End in the ROTATION Table.
View 4 Replies
View Related
Feb 27, 2006
I am new to SQL and aftering reading my SQL For Dummies book, I still am unsure how to accomplish this task.
The table I need to query contains resident census information. There are multiple rows for each resident. I need to determine if the resident is still active, so I only need to read the last row for each resident to make this determination.
What method can I use to read only the last row for the resident. Here is what I tried previously, but it returns multiple rows for each resident.
select a.firm_id_code as FacAddOnNum,
f.shortname as FacName,
left(a.resident_code,6) as ResidentCode,
(r.res_first_name + ' ' + r.res_last_name) as ResidentName,
a.LastDate,
a.cens_trans_type
from (select firm_id_code,
resident_code,
max(dt_cens_trans) as LastDate,
cens_trans_type
from arrescensus
where firm_id_code = @FacId
group by firm_id_code, resident_code, dt_cens_trans, cens_trans_type) as a
join corp_info.dbo.facilityinfo as f on (a.firm_id_code = f.addonnum)
join arresidents as r on ((r.firm_id_code = a.firm_id_code) and (r.resident_code = a.resident_code))
Any help with this is greatly appreciated!
View 6 Replies
View Related
Oct 12, 2004
I've got a website with dynamic content, each page (subject) got an ID. On every page there can be a number of links. These are either links to internal other pages on that website or external links.
For the internal links the only thing I need is the ID and Title of that page. Those can be found in the Tbl_subjects. As for external links I need ID, Title and URL which can be found in the Tbl_ext_links.
I've got a table named Tbl_linkboxes with:
- a Subject ID which means that this link belongs on this subject page.
- Link ID which is either an ID from Tbl_subjects or Tbl_ext_links
- External a boolean column to indicate if the Link ID refers to the Subject table or the External links table
There's basically 2 questions:
1) How to make this work? I've got a query below as feeble attempt
2) Should I really really really consider to use 2 columns for IDs and removing the External boolean. And simply setting one of those fields in the columns to >0 while the other is 0.
Okay, here's my attempt
PHP Code:
SELECT s.Sub_id, s.Link_id
(l.external IS FALSE, (SELECT Title FROM Tbl_subjects), (SELECT Title,URL FROM Tbl_ext_links)
FROM Tbl_subjects s
WHERE s.Sub_id = <some id>
Not sure if I should work with IIF here to make it work or something else. I'm almost tempted to kick the boolean column overboard and introduce a JOINT on both columns then, one for external link ids and other for internal page ids.
Amazing how long one can stare at a query and not being able to get it right
View 14 Replies
View Related
Mar 7, 2007
I'm attempting to write and update query. So far I have written the following:
update vwDISTCITY_TAXCODE
set tax_code='04'
where DIST_CITY='04'AND year_id=2007 AND frozen_id=0 AND p_id=93549 AND total_taxes=isnull
The last part of the query "total_taxes=isnull" is where the problem lies. Essentially I want to say if all of the other things are true and there is a null value in the total_taxes column, then I I want to set the tax_code to '04'. However how would I phrase the last part correctly?
Thanks!
-Steve H.
View 4 Replies
View Related
Jul 23, 2005
Good Day;I would appreciate assistance developing a query that I haven't beenable to develop without using a second table. I wish to count thenumber of records that are still open on the first of each month.Each record has an open date and a close date or the close date isnull i.e., the record is not yet closed. I've previously beaten thisby building a table, simply a list of the dates for the first of eachmonth for the next ten years or so, and then selecting values basedupon a date selected from that table. However I'd be happier if Icould do it without the second table. I'd be prepared to accept theMin(Date) for each month as being the first of the month.I've included some DDL statements to build and populate the table ifthat helps. Since the selection is rather small and all the opendates are very close together I think the result will be simply adecreasing count from the month the first record is opened till today.A pseudo code select statement might look likeSelect Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]FROM DetailTWhere DateReceived > DateOfInterest or DateReceived is Null andDateOpened < DateOfInterestGroup by Min(DateOpened)Order by Min(DateOpened)I hope I've explained it sufficiently well.CREATE TABLE [dbo].[DetailT] ([Autonum] [int] IDENTITY (1, 1) NOT NULL ,[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[DateOpened] [smalldatetime] NOT NULL ,[DateReceived] [smalldatetime] NULL ,)Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('C15788', '06/04/2005 9:35', 07/04/2005)Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('B16091', '06/04/2005 9:36', '07/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('B15001', '06/04/2005 9:51', '08/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('M18696', '06/04/2005 9:56', '06/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('C14969', '06/04/2005 10:05', '10/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('O10091', '06/04/2005 10:08', '12/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('D01197', '06/04/2005 10:13')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('H15001', '06/04/2005 10:15', '08/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('J15090', '06/04/2005 10:24', '08/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('J01202', '06/04/2005 10:31')Insert into DetailT (QDNumber, DateOpened)VALUES('G01193', '06/04/2005 10:32')Insert into DetailT (QDNumber, DateOpened)VALUES('K01164', '06/04/2005 10:35')Insert into DetailT (QDNumber, DateOpened)VALUES('K01162', '06/04/2005 10:48')Insert into DetailT (QDNumber, DateOpened)VALUES('F01124', '06/04/2005 10:59')Insert into DetailT (QDNumber, DateOpened)VALUES('H01147', '06/04/2005 11:01')Insert into DetailT (QDNumber, DateOpened)VALUES('S15068', '06/04/2005 11:10')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('E12322', '06/04/2005 11:32', '07/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('A12205', '06/04/2005 11:37', '06/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('D12259', '06/04/2005 11:40', '07/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('C03394', '06/04/2005 11:51')If you made it this far thank you for your patience. Any help would beappreciated.Thank you.Bill
View 5 Replies
View Related
Jun 15, 2006
I am trying to generate some datasets with some queries...With a given series information, it should return PART_NOs that has STD= 1 and a unique price at that particular 'START', and keeping the'TYPE' in consideration...DB examples below:Main DBIDPART_NOSERIESSTD1A-1A12A-2A13A-3A14D-1D15D-2D0Price DBIDPART_IDTYPESTARTPRICE501X100050511X1000040521Y100060531Y1000050542X100050552X1000040562Y100060572Y1000050582X100090etc.main.ID and Price.PART_ID are paired together.So in an example case, lets say I am querying for SERIES A, with TYPEX. A table should be outputted something likePART_NOA-1100050A-11000040A-3100090Note how it skipped printing A2 because the price is the same as A1.I'm really looking for the SQL code here... I can't get it to filter ondistinct price.SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICEFROM MAIN, PRICINGWHERE (MAIN.SERIES LIKE 'A')AND (MAIN.STD = '1')AND (PRICING.PRICE != '')AND (PRICING.TYPE = 'X')AND (MAIN.ID = PRICING.PART_ID)I've been trying to use GROUP BY and HAVING to get what I need but itdoesn't seem to fit the bill. I guess I'm not terribly clear on how Ican use the SQL DISTINCT command...? If I try and use it in my WHEREstatement it gives me syntax errors, from what I understand you canonly have distinct in the select statement? I'm not sure how tointegrate that into the query to suit my needs.Thanks for any help.
View 4 Replies
View Related
Mar 5, 2007
I realize this query is inherently incorrect, but my issue is mainly
syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong.
I want to ensure that a.order_id is not in the settlement table. So I
was thinking something along the lines of "WHEN a.order_id not in
(select order_id from settlement)" which I know will cause a slower
response time, but I'm willing to deal with it. In any case, that
syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!
View 9 Replies
View Related
May 23, 2007
I got some help on here before with building my query. I thought this was working fine but it turns out when there are multiple records for a column type, it only grabs the first one. I need to get all records. Is there an alternative to MAX? I needed to structure it like this because I needed to return each row as a column and this was the way suggessted before.
My query:SELECT TOP (100) PERCENT PRODUCT_NUMBER, PRODUCT_NAME,
MAX(CASE WHEN ColumnName = 'Federal Specification Number' THEN TheValue ELSE NULL END) AS [Federal Specification Number]FROM (SELECT dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID, dbo.SHARED_FEATURE_VALUES.FEATURE_TEXT_VALUE AS TheValue,
dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE AS ColumnName, dbo.PRODUCTS.PRODUCT_NUMBER, dbo.PRODUCTS.PRODUCT_NAME
FROM dbo.PRODUCT_FEATURE_VALUES INNER JOINdbo.SHARED_FEATURE_TYPES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_TYPE_ID = dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE_ID INNER JOINdbo.SHARED_FEATURE_VALUES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = dbo.SHARED_FEATURE_VALUES.FEATURE_VALUE_ID INNER JOINdbo.PRODUCTS ON dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID = dbo.PRODUCTS.PRODUCT_ID
UNIONSELECT dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID, ISNULL(dbo.EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE,
dbo.EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE) AS TheValue, dbo.EXTENDED_ATTRIBUTES.COLUMN_NAME AS ColumnName, PRODUCTS_1.PRODUCT_NUMBER, PRODUCTS_1.PRODUCT_NAME
FROM dbo.EXTENDED_ATTRIBUTE_VALUES INNER JOINdbo.EXTENDED_ATTRIBUTES ON
dbo.EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = dbo.EXTENDED_ATTRIBUTES.EXT_ATT_ID INNER JOIN
dbo.PRODUCTS AS PRODUCTS_1 ON dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID = PRODUCTS_1.PRODUCT_ID) AS t1
WHERE PRODUCT_NUMBER = '02083'
GROUP BY PRODUCT_NUMBER, PRODUCT_NAME
ORDER BY PRODUCT_NUMBER
This returns:
Product_Number Product_Name Federal Specification Number
02083 Di-Electric Grease, 10.5 Wt Oz FDZ-CFR-21-178.3570
There is another record for Federal Specification Number I need to return as well. If I change to MIN, it gets the other record. Anyway I can get both?
View 15 Replies
View Related
Oct 26, 2004
Code:
select *
from linkboxes l
left outer
join subjects s
on l.sub_link_id
= s.sub_id
left outer
join external_links e
on l.ext_link_id
= e.ext_id
where l.sub_id = subid
View 4 Replies
View Related
Jul 20, 2005
I have a couple of tables that look like this (not excactly but closeenough):[Contact]id intfname varchar(50)lname varchar(50)[ContactPhoneNumber]id intnumber varchar(15)ext varchar(6)contact_id intpriority int (indicates primary, secondary... numbers)type int (indicates type of number: fax, cell, land line)I'm looking for a more optimized method of displaying this informationin this format:fname, primary business phoneUsing a derived column like this works, but seems to be slow with manyrecords, despite tuning indexes:SELECT c.fname AS [First Name],( SELECT TOP 1numberFROM ContactPhoneNumber cpnWHERE cpn.type = 1AND cpn.contact_id = c.idORDER BY cpn.priority) AS NumberFROM Contact cI can get the same results using a join, and it's a lot faster. But I'mnot sure how to select only the primary phone number this way...basically the first phone number whose priority is either NULL or 1.Any suggestions?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies
View Related
Sep 15, 2006
my query is below:
"SELECT * FROM " + @[User:: TableName] +
" WHERE OrderDate = " + "'" + @[User::dTrxnDate] + "'"
needless to say, the ole db source editor is giving me syntax errors. the data type of TableName is string. the data type of dTrxnDate is DateTime.
can someone please help me resolve the syntax errors?
thanks in advance.
View 8 Replies
View Related
Sep 28, 2007
I have two tables that are pretty standard I think. Table a has product descriptions and one of those fields is a price. I have a second table that contains fees based on the price. so table B looks like this.
min max fee
0 19.99 2.50
20.00 49.99 3.50
50.00 1000.00 5.50
the max ends up around a million just to be sure we cover all prices. my problem is this I need a very efficient query to
poll all the values from A and the correct value from B. All the attempts I have made are not working. I also have to make sure this query is extremely efficient as it is executed several times a minute. If there is a better way in general to structure this I am all ears. I wanted to avoid placing the fees in the product table as the fees are updated often, but if its the only way to get this to work, then that is where I will go.
Thanks everyone
View 7 Replies
View Related
