Displaying Distinct Results From Union Query
Aug 31, 2000
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows.
thanks for any suggestions...
~
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nowo
where nowo.wasrst <='40'
union
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nopo
where nopo.wasrst <='499'
View 1 Replies
ADVERTISEMENT
Apr 14, 2008
Hye Friends,
I'm using a DMX query to get some predictions out of my MiningModel
my DMX query is as follows :
SELECT
predict([x SalaryPredictor].[Emp Gross],20),predict([x SalaryPredictor].[Emp Basic],20)
From
[x SalaryPredictor]
This query is returning me 2 objects of type expressions in my Dataset.
The problem is when I try to drag these 2 fields in my Table i get a "#Error" value
I executed the query in 'Data' tab and found that data is getting returned is a tree like format where "Expression" is at top & values expected ar its child nodes.
I also found out that the returned objects are actually AdomdDataReader , but i'm not able to write a expression to get its values in my tables....
I want to display the values in the following format....
_____________________________
| Emp gross | Emp basic |
---------------------------------------------------
| predicted val 1 | predicted val 2 |
---------------------------------------------------
| .... | ..... |
-- -------------------------------------------------
can anybody help me out in this ????...
Thanks in advance !!!
View 1 Replies
View Related
Mar 13, 2012
I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
SAMPLE:
CustKeySwatchDateRugDateDateDiff
10903963126678366
10903963126837525
10903963126960648
10913962286550322
1115886193625764
1129666456646711
1146986229625324
1146986229627647
11469862296667438
1146986656666711
1146986624666743
DESIRED RESULTS:
CustKeySwatchDateRugDateDateDiff
10903963126678366
1115886193625764
1129666456646711
1146986656666711
View 7 Replies
View Related
Apr 7, 2008
Hello all,
I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:
SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla
FROM tableX....
WHERE call_status=open.....
I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.
At the moment the SELECT query above gives me the following results:
Call Ref sla
10001 In SLA
10002 Breach
10002 In SLA
10003 In SLA
10004 In SLA
10005 Breach
10005 In SLA
... ...
What it should look like is this....
Call Ref sla
10001 In SLA
10002 Breach
10003 In SLA
10004 In SLA
10005 Breach
... ...
Please let me know if anyone has the answers or any clues to this! thanks.
View 5 Replies
View Related
Nov 20, 2007
Hi,
I have follwing union query. I want to put this all in a temp table.
select Store_Id,batchnumber
From
Adjustments where updatedDt between '10/30/2007' and '11/20/2007' and Store_id in(8637 ,8641)
group by Store_Id, batchnumber
Union
select DestinationId,b.batchNumber
from
batch b
inner join Carton C on C.Carton_Id = b.General_ID
inner join Document d on d.Document_Id = c.Document_Id
where b.BatchType = 'Warehouse' and b.TranTable = 'Carton'
and (d.DestinationId in (8637 ,8641) ) and c.UpdatedDt Between '10/30/2007' and '11/20/2007'
Union
select d.DestinationId,b.Batchnumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferIn' and b.TranTable = 'Document'
and (d.DestinationId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
Union
select d.SourceId,b.batchNumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferOut' and b.TranTable = 'Document'
and (d.SourceId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
order by batchnumber
Kindly advice.
Thanks
Renu
View 2 Replies
View Related
Nov 12, 2015
How can I UNION two MDX query results which are deriving from 2 cubes?MDX queries will return same ROW information and only Measure names will be different.
View 8 Replies
View Related
May 21, 2015
I have a data structure like this
UID , Name, amount, start date End Date
1 A 10 2015-05-01 00:00:00 2015-05-01 23:59:59
2 A 10 2015-05-02 00:00:00 2015-05-02 23:59:59
3 A 10 2015-05-03 00:00:00 2015-05-03 23:59:59
4 A 10 2015-05-04 00:00:00 2015-05-04 23:59:59
5 B 10 2015-05-05 00:00:00 2015-05-05 23:59:59
[code]...
View 5 Replies
View Related
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Jul 23, 2005
I have the following stored procedure in SQL 2000 and would like todiplay the database name where data is drawn from. I'm using 4databases db1, db2, db3, db4 which all have the same table (Table1)with identical column names (Surname, GivenNames).CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)ASSelect a.Surname, a.GivenNamesFrom [db1]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db2]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db3]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db4]..Table1 As aWhere a.Surname LIKE @varSurname + '%'Order By a.Surname,a.GivenNamesGOI tried the followingSelect a.Surname, a.GivenNames, db_name()However it only gave me the name of the database where the storedprocedure is kept (in my case 'Common')I was hoping it would display results something like the followingSurname GivenNames Database------- ---------- --------Fred Smith db1Freddy Smith db2Fred Smith db3Fred Smithe db3Fred Smith db4Fred Smithye db4Instead I receiveSurname GivenNames Database------- ---------- --------Fred Smith commonFreddy Smith commonFred Smith commonFred Smithe commonFred Smith commonFred Smithye commonAny ideas?ThanksRick
View 1 Replies
View Related
Dec 12, 2007
Dear All,
please let me know which combination is better one.
here in our application, there is union as well as distinct.
now i'm replacing the the union and distinct with the suggested replacement by you all.
1) using union and removing distinct.
2) using distinct and union all.
now my doubt is ..is there any other possibility for these?
actually union is also not suggested generally.
please help me in this regard.
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 12 Replies
View Related
Nov 14, 2007
That's about it (subject line), I have used UNION and now I want to select DISCTINCT from that resultset
Code:
SELECT c1 FROM t1
UNION
SELECT c2 FROM t2
That code gives me half of what I want.
I would like a list of the unique results.
I know I could use a TempTable and do the DISTINCT on that, but I'm hoping there is a more elegant way.
EDIT: The following code gives me the result I want:
Code:
CREATE TABLE #TempTable (
TempCol VARCHAR (20) collate database_default,
)
INSERT INTO #TempTable
SELECT c1 FROM t1
UNION
SELECT c2 FROM t2
SELECT * FROM #TempTable
DROP TABLE #TempTable
. . . but, can it be done without a TempTable??
View 2 Replies
View Related
May 16, 2007
I have a stored procedure using UNION joins on three SQL queries.
Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :)
I need to return one result set, sorted by date... one complete result per day. eg: 5/15/2007 | XX | XX | XX | XX | XX | XX |5/16/2007 | XX | XX | XX | XX | XX | XX |5/17/2007 | XX | XX | XX | XX | XX | XX |
Currently, when I run the query, I'm getting three separate date values for each date...
eg:5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |etc
How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong".
Here is the stored procedure if you can help. I'd really really love the help!
C R E A T E P R O C E D U R E sp_ApptActivityDate
(@strWHERE as varchar(500), @strWHERECANCELED as varchar(500))
as
exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_ALL_ApptActivity ' + @strWHERE + '
UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_Active_ApptActivity' + @strWHERE + '
UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + '
ORDER BY dbo.vw_ALL_ApptActivity.[Date] ' )GO
View 12 Replies
View Related
Aug 30, 2007
when i try to run this following query, i get an error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The ntext data type cannot be selected as DISTINCT because it is not comparable.
/resultados_termo.asp, line 176
the query:
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade
like '%2004%' or ano like '%2004%' )
when i use UNION ALL, i get repeated rows. i need the select distinct on it.
please, help.
thanks in advance.
View 4 Replies
View Related
Feb 16, 2008
I am trying to create a user permission system that is stored in a database. What is the best table structure for accomplishing this? How could I display the permissions in a grid? Currently I have a users table and a permissions table. I created a map between the two. However, I don't see how this allows me to display a grid. All my "columns" for permissions are actually rows from the permissions table. So ideally my grid would look something like this. User | P1 | P2 | P3 |A | T | F | T |B | T | T | T |Thanks for any help. I am relatively new to SQL so please explain gently.
View 2 Replies
View Related
Jan 8, 2008
I have two tables joined by a map. For example
Table1
[Ball, Frisbee, Kite]
Table2
[Red, Green, Blue]
MAP
[Ball:Green, Ball:Blue, Kite:Red]
I want to return a table that shows each toy and what colors it is available in.
Column1 - Toys | Column2 - Comma separated list of colors
Ball - Green, Blue
Kite - Red
Frisbee -
I have absolutely no idea how to do this even after googling all morning. Please help if you can. There must be a way to do this!
View 4 Replies
View Related
May 3, 2004
I'm looking to display the results of the following query as currency. Any help with this would be greatly appreciated...
SELECT SUM (sysTotalCharge) - SUM (sysTotalCredit) AS TotalNet FROM Bookings WHERE (sysRecordDate BETWEEN '01/01/::Year::' AND '01/31/::Year::')
View 2 Replies
View Related
Jan 30, 2008
I would like to get the top 5000 records from a union. The oringinal query is
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
I thought that the I could do:
SELECT TOP 5000 * FROM
(
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
}
This gives me a syntax error that I cannot figure out.
Any ideas?
View 3 Replies
View Related
Aug 28, 2006
I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>"
SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)">
<SelectParameters>
<asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" />
</SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page. I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.
View 1 Replies
View Related
Dec 10, 2006
Hey All,
This is really simple basic question, and it's just so I can start to learn about MVS.
I have a sql-database called "Test" with a Table called "Agents" with just three colulms "ID", "FNAME" and "LNAME".
On the Default Page I have three Textboxes and a submit Button,
All I want to do is type "Select" criteria in Textbox1 and have Textbox2 Display the "Fname" and Textbox3 Display the "Lname".
(Using VB in ASPX)
Dim i As New SqlDataSource()
i.ConnectionString = ConfigurationManager.ConnectionStrings("Test").ToString()
i.SelectCommandType = SqlDataSourceCommandType.Text
i.SelectCommand = ((((((Now What???)))))))
End Sub
PLease Help, Thanks in advance
A....
View 2 Replies
View Related
Aug 6, 2014
I am designing a database to store data from a leak tester.
We want to display the results between dates, I mean, the results of the leaking test are going to be stored as well as the datetime in which they have been performed.
ID (int)
RESULT (float)
TS (datetime)
The query will be, of course:
SELECT * FROM TABLE Where TS BETWEEN DT1 and DT2
This table is growing by 10000 rows a day, it is possible that in a year getting the values between two dates became impossible.
Using a index with a datetime field sounds like a crazy idea.
View 2 Replies
View Related
Apr 17, 2008
Hi,
I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format:
Customer Name Address
----------------------------------------------------------
ABC Add1
ABC Add2
XYZ Add1
XYZ Add2
Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report).
Please give me a solution to this.
Shyam
View 4 Replies
View Related
Apr 14, 2008
Im trying to display all twelve results from a dataset in reporting services. On the layout tab I can get the first and last record in my dataset to display by using the commands:
=First(Fields!MonthYear.Value)
=Last(Fields!MonthYear.Value)
But I cant get the ones in between?
View 10 Replies
View Related
Feb 3, 2005
I have a problem with a union all that seems to be changing the results of a column based on using the union, when I run the querys separatly I get the expected results in the field!
Basically 3 queries pulled together for reporting purposes.
Orders (which can include a gift so the gifts are separated in the second query).
OrderGifts (pulled out of the orders).
Gifts (straight gifts with no associated orders).
The problem is showing up in the first ORDERS query. I can pull a specific order and the calculation for the AMOUNT(eg:$15.20) is correct, but when I combine the tables I get a different AMOUNT(eg:$15.199999999999999).
Here are the values for the fields in the amount calc:
totitems=12.9500
totcredits=.0000
discount=.0000
tax=.0000
refund=.0000
convrate=1.0
postage=2.2500
giftamt=15.0000
I tried converting each of the fields to an decimal befre the calc and same results
I tried ROUND and same thing!
I have been trying to chase this down all day, cant figure out what the problem is or how to get around it...whats going on here I am missing?
Here is the query, if you need any other info just let me know, I would GREATLY appreciate ANY direction of figuring this out!
select * from
(
--orders
select
acctnbr as PartnerId,
'O' as TrnType,
ordnbr as TrnId,
0 as FundId,
odate as WorkDate,
adate as PostDate,
sdate as ShipDate,
cast(
(
IsNull(totitems, 0) -
IsNull(totcredits, 0) -
IsNull(discount, 0) -
IsNull(tax, 0) -
IsNull(refund, 0) +
IsNull(postage, 0)
) * IsNull(convrate, 1)
as Decimal(10,2))
as Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(0,motvcode) as ProjMotvName
from kmai.dbo.o01omst
UNION all
--orders gifts
select
acctnbr as PartnerId,
'G' as TrnType,
giftref as TrnId,
giftfundid as FundId,
odate as WorkDate,
null as PostDate,
null as ShipDate,
ROUND((giftamt * IsNull(convrate, 1)),2) as Amount,
batchnbr as BatchId,
giftmotvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(giftfundid,giftmotvcode) as ProjMotvName
from kmai.dbo.o01omst
where adate is null
and giftfundid <> ''
UNION ALL
--gifts
select
acctnbr as PartnerId,
'G' as TrnType,
gh.giftref as TrnId,
convert(int, fundid) as FundId,
gdate as WorkDate,
applydate as PostDate,
null as ShipDate,
ROUND((IsNull(gd.amt, 0) * IsNull(convrate, 1)),2) AS Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(fundid,motvcode) as ProjMotvName
from kmai.dbo.g03ghdr gh
inner join kmai..g04gdtl gd
on gh.giftref = gd.giftref
)
UnionOfGiftsAndOrders
View 4 Replies
View Related
Jul 8, 2015
Code:
SELECT DISTINCT LEFT([REPORTING_MONTH], 4)+'-'+SUBSTRING([REPORTING_MONTH],5,6) as REPORTING_MONTH, t.EMPLOYEE,
'' as COUNT_FTP,
CASE WHEN [MEDIUM_RCVD] = 'EMAIL' THEN COUNT(MEDIUM_RCVD) ELSE '' END AS COUNT_EMAIL
FROM [GPO].[dbo].[DW_SUBMISSION] as s
JOIN #TEMPActivity as t
[Code] ....
I'm trying to get the set to come out all on one line. REPORTING_MONTH, EMPLOYEE, COUNT_FTP, COUNT_EMAIL
But when I try null or '' it creates a second record and doesn't merge the two results.
View 3 Replies
View Related
Apr 3, 2008
This may be a dumb question, but I can't seem to get the syntax right. I have two temp tables that have the same columns, I want to do a union on them and store the results in a temp table. Any ideas?
Ie.
select * from #tmpTable1
union
select * from #tmpTable2
into #tmpTable3
Thanks!!!
View 4 Replies
View Related
Aug 7, 2014
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
View 2 Replies
View Related
Sep 5, 2014
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
View 8 Replies
View Related
Nov 8, 2006
I need help in writing a query.
The query should get top 10 items and their values from current year and the values for the same items from previous year table.
I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.
The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.
I am attaching some images below to give more information.
Image of results from my query.
Image of how the final output should look like.
The Store Procedure code is:
ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'
EXEC sp_executesql @SQuery
END
View 2 Replies
View Related
Jun 19, 2008
Here's one thats had me and a coworker puzzled. Hopefully it's something simple:
TABLE A: (log of messages)
userid, int
date, datetime
message, varchar(50)
Table A Data:
1, 6/18/2008 @ 2:32:41, This is my message
1, 6/18/2008 @ 2:31:02, This is my message
1, 6/17/2008 @ 7:34:26, This is another message
2, 6/18/2008 @ 2:32:41, This is not his message
2, 6/16/2008 @ 11:21:32, This is my message
TABLE B: (List of users)
userid, int
name, varchar(100)
Table B Data:
1, John
2, Mike
I want to extract the most recent message logged per user, i.e.:
name | date | message
--------|---------------------|-------------------------
John | 6/18/2008 @ 2:32:41 | This is my message
Mike | 6/18/2008 @ 2:32:41 | This is not his message.
I have been unable to come up with a query that can return just the one value. I've tried variants of:
SELECT DISTINCT b.name, a.date, a.message
FROM a INNER JOIN
b ON a.userid = b.userid
including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary?
TIA
View 4 Replies
View Related
Mar 5, 2007
Hello, first post since I can usually find answers by reading the forums. I've searched the internet up and down and for some reason I can't get this query to work properly.
I have two tables:
ticket
ticket_id
ticket_to
ticket_from
message
message_id
ticket_id
message
There can be several messages per ticket, connected by the ticket_id. I'd like to only pull only the most recent message, as well as the results from the ticket table. Currently I'm getting all messages per ticket.
Here's what I have:
SELECT distinct ticket.ticket_to, ticket.ticket_from, message.ticket_id, message.message
FROM tickets
INNER JOIN message ON tickets.ticket_id = message.ticket_id
GROUP BY message.ticket_id, message, ticket_to, ticket_from
Any help would be greatly appreciated! Thanks much.
View 3 Replies
View Related
Mar 12, 2007
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View 2 Replies
View Related
Jul 14, 2014
Just wondering if it's possible to select distinct results from the where clause?
View 3 Replies
View Related
Jul 28, 2007
I want to select all usernames from tlbUsers which practice a certain sport.I also have a table UsersAndSports:UserID intSportID intI therefore need an inner join statement:select username from(SELECT ROW_NUMBER() OVER (ORDER BY au.LastActivityDate DESC) as RowNum,ud.username from tblusers usinner join tblUsersAndSports uas on uas.usercode=us.usercode and (uas.sportID=3 or uas.sportID=4)WHERE us.UserName <>'' )as MemberInfo WHERE RowNum between (@startRowIndex+1) AND (@startRowIndex+@maximumRows)The problem lies in the usage of the ROW_NUMBER command. I need that in order to effectively page through my records, but it also makes thatI cant make a distinct selection as each RowNum is unique....:SIn case the user would practice 2 sports, the query would return 2 rows...if I place a distinct in front of the username as such:select distinct username fromThe query would return each user only once...BUTBUTBUT: my startrowindex and maximumrows apply to the results that can be found in the MemberInfo selection..so lets say my startrowindex would be 0 and maximumrow 5if my 1st query (without distinct) was to return:johnjohnjohnjohnmikemikerobmy 2nd query (with distinct) would return:johnmikewhereas I want it to return:johnmikerobWhat can I do?
View 2 Replies
View Related
