Of the two queries below, the first returns the desired result, the union of the select-except statments. Why doesn't the second query return the same result as the first? Is this a mistake/bug on Microsofts side?
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
I just found out that I can do an ORDER BY clause on entire records set retrieve from a query that combines several sub queries with UNION from different tables with the same structure... so this is great to know, BTW, is this a new feature of MSSQL 2K ? I don't recall being able to do this in MSSQL 7 or 6.5.
Anyway, the main question is, can I use the TOP command in a query that has UNION in it?? Meaning, there are two queries (or more) from two tables (or more) and I need to fetch the top 10 records by an ORDER BY clause from the combined results, when I try to add each sub query TOP 10 the results are not correct at all, when I try to add TOP 10 only to the first query hoping that the analyzer will refer to the whole query, it's selecting TOP 10 from the first query and combines it with all the records from the others...
So, can anyone help? I hope the problem is understood.
I am trying to join to different queries into one table ( I accomplished this)
Next I need to ADD or SUM the results of 2 rows to form a single row.
As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.
Any help is greatly appreciated!
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', Count(ProdID0) as '# copies installed', '' as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', '' as '# copies installed', Count(ProdID0) as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60) AND DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
Why is it assumed that one would want the duplicates removed by default? Isn't that what SELECT DISINCT is for?
I was wondering if there is a way I could write this query as one query using union instead of 3 different queries?
SELECT columna as column,count(ID) as Applications FROM tablea GROUP columna
SELECT columna as column2, count(ID) as Approved FROM tablesa where substring(APP_DATE,1,6) >'200304' and in 'Approved') GROUP BY columna
SELECT columna as column3, count(ID) as Booked, sum(AMT) as amt, sum(AMT)/count(ID) as lavg from tablea where substring(APP_DATE,1,6) >'200304' and STATUS in('book') group by columna
I am doing a UNION of 8 views(with 3 million rows in each view approx). I am sure that there is NO DUPLICATE data between the views. SO specifying a UNION ALL would be better than specifying a UNION in this case?
Thanks!
Prakash.P The secret to creativity is knowing how to hide your sources!
Hi All I want to do the following report and I want the 2nd select statement to appear at the bottom to display the totals
SELECT Item,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan FROM fncWineSales(2007) AS A UNION SELECT 'Total',SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan) FROM dbo.fncWineSales (2007) AS B
Hello,Bear with me (not had much sleep last night), pls see following ddl,dml and comments for what is desired, I don't have a problem gettingdesired result(s), however, I'm wondering if there's another (better)solution than UNION operator in this case. TIA.-- DDLCREATE TABLE #TMP (col varchar(10));-- DMLinsert into #TMPvalues('A124');insert into #TMPvalues('A127');insert into #TMPvalues('A12728');insert into #TMPvalues('A17282');insert into #TMPvalues('BCD');insert into #TMPvalues('BCD');insert into #TMPvalues('CDSS');insert into #TMPvalues('DS');insert into #TMPvalues('YUUEI');-- goal: get one row with col data starting with 'A' and distict rowsfor the restselect top 1 colfrom #TMPwhere col LIKE 'A%'UNIONselect distinct colfrom #TMPwhere col NOT LIKE 'A%'
Hi,I can return results of a union easily enough, but I wish to sum the recordsfirst. Is this possible in a single SQL statement? Or do I have to useseparate ones to sum up after the union?Cheers,Chris
When any changes have been made to the underlying table structure, the Union all reports error and does not automatically correct the error. I then have to delete it and recreate it. Is this a bug?
These are the errors that I am getting. The 2nd error only comes up when I try to put in "UNION". Any help would be very much appreciated. Thank you!
Msg 156, Level 15, State 1, Procedure searchUser, Line 79 Incorrect syntax near the keyword 'UNION'.
Msg 102, Level 15, State 1, Procedure searchUser, Line 100 Incorrect syntax near 'END'.
Code Snippet
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[searchUser] -- Add the parameters for the stored procedure here
@Username VARCHAR(25), @AppID INT, @ResID INT, @Access VARCHAR(5), @Region INT AS BEGIN
IF @Username IS NULL BEGIN
IF @Access IS NULL BEGIN PRINT 1 SELECT a.XID, a.AppID, a.ResID, b.AppDescription, c.ResDescription, d.Username, d.Custom, d.Region
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
WHERE a.AppID = @AppID AND a.ResID = @ResID AND d.Custom = @Access END
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
WHERE d.Username = @Username AND a.AppID = @AppID AND a.ResID = @ResID AND d.Custom = @Access
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
WHERE d.Username = @Username AND a.AppID = @AppID AND a.ResID = @ResID AND d.Custom = @Access AND d.Region = @Region
I want to know the top 5 patterns in sales, the report will like this:
Month Top1 Top2 Top3 Top4 Top5
2005-06 A1 A2 A3 A4 A5
2005-07 B1 B2 B3 B4 B5
2005-08 C1 C2 C3 C4 C5
--- --- ---
What I did is:
declare @StartDate and @EndDate, set @StartDate and @EndDate
Delete AAAA (AAAA is a table)
Begin
While (@EndDate<somedate)
Insert into AAAA(sales, Month, Pattern)
Select TOP 5 sales, Month,pattern from sometables order by sales DESC
increase @StartDate and @EndDate by a month
End
Select * from AAAA
It works fine. My question is: Can I get rid of table AAAA? Is there a better way that just use Top 5 combine with something say Union? ( I tried Union and failed )
The first comment, by 'zpeceno', exactly describes a problem that I have hit upon. However despite looking thro the list of issues resolved in SP1, I've found no mention of it. Any chance I could get some offical comment re. the bug described, when it was(/has it been?) officially acknowledged, and what service pack (if any) resolves it?
hi! What do you think is the error in this sql squery? What I'm trying to do is to get all the records having dates from 2007/01/21 to 2007/02/20 where YearFr =2007 MonthFr =01 DayFrom =21 YearTo =2007 MonthTo =02 DayTo =20 By the way, this is part of my code: '||||| Open connection MyConn.Open()
'||||| Create Command Object for filtering time ins and time outs base from Year and Month Dim odbcCommand_date As OdbcCommand = New OdbcCommand("Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day > ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day UNION Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day < ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day", MyConn)
I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns. Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank. I'm not sure if this is possible. So far, I have: 1 SELECT RANK, field_1, field_2, ..., field_m FROM 2 ( 3 SELECT RANK, field_1, field_2, ..., field_m FROM T_1, 4 CONTAINSTABLE(T_1, field_i,@searchText) searchTable 5 WHERE KEY = T_1.field_i 6 UNION 7 SELECT RANK, field_1, field_2, ..., field_m FROM T_2, 8 CONTAINSTABLE(T_2, field_i,@searchText) searchTable 9 WHERE KEY = T_2.field_i 10 UNION 11 . 12 . 13 . 14 UNION 15 SELECT RANK, field_1, field_2, ... field_m FROM T_n, 16 CONTAINSTABLE(T_n, field_i,@searchText) searchTable 17 WHERE KEY = T_n.field_i 18 ) 19 ORDER BY RANK DESC
I haven't tried it yet, but it seems wrong. How do we actually do this?
hi,i have a union statement that works like a charm:SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel
UNION
SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours] WHERE AccessLevel <= @AuthenticationLevel
ORDER BY [SortDate] this statement selects all the records i want from both tables, and orders the entire new table by sort date.what i want to do is to add a limitation on the number of records. i have tried using both LIMIT and TOP commands, but i can't get it working. both of the below statements give me a syntax error saying "Incorrect syntax near the keyword 'ORDER'"first try:SELECT * FROM (<same_statement_as_above>) ORDER BY [SortDate] LIMIT 2second try:SELECT TOP 2 * FROM (<same_statement_as_above>) ORDER BY [SortDate]what am i doing wrong, and how do i achieve what i want?i am working with mssql server 2005 (express locally, and standard on my hosting service). thanks for your help!
Hi all I'm new to SQL Server 2005 and its queries. I've created a DB that stores bonusses. For example, a person goes out with usually 4 other people, and does their work. The first person is the engineer, the second is the team leader, and the third and forth is the helpers. According to the number of notes they capture for that day, they get a bonus. if 3 notes has been done for the day, the enigeer gets 100 bucks per note, aka 300 bucks - 3 * 100 = 300. the team leader gets half of it, 150, and the helpers get each half of that, 75 and 75. in a select query i did the match/calculation now i want to add everything up to get a total, 300+150+75+75 - how does one do it, and write it to a table? Note: The amount of people can change, the role of the person can change, so a helper can be a team leader or engineer as well, and the amount also changes depending on performance.DECLARE @BonusID int; SET @BonusID = '1';
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, b.Notes * a.Amount AS Total, bd.DateModified FROM tblBonusDetails AS bd INNER JOIN tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN tblUsers AS u ON bd.UserID = u.UserID INNER JOIN tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN tblRoles AS r ON bd.RoleID = r.RoleID WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Surveyor')
UNION
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, (b.Notes * a.Amount)/2 AS Total, bd.DateModified FROM tblBonusDetails AS bd INNER JOIN tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN tblUsers AS u ON bd.UserID = u.UserID INNER JOIN tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN tblRoles AS r ON bd.RoleID = r.RoleID WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Team Leader')
UNION
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, ((b.Notes * a.Amount)/2)/2 AS Total, bd.DateModified FROM tblBonusDetails AS bd INNER JOIN tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN tblUsers AS u ON bd.UserID = u.UserID INNER JOIN tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN tblRoles AS r ON bd.RoleID = r.RoleID WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Helper')
ORDER BY Total DESC
BonusDetailID BonusID Name Surname Role Total DateModified
Hi All I'm Try to get some data from some table but i wont get in 1 dataset how can i do CREATE PROCEDURE sp_r_getSalesBySalesSite (@dtStart datetime,@dtEnd datetime,@SiteID varchar(15) = '')ASBEGINSELECT distinct tblInvoiceLine.SubTotal,tblInvoiceLine.Isdeleted, tblSite.SiteID, tblSite.SiteName, tblInvoice.InvoiceDate,tblInvoice.InvoiceID,tblproduct.productCostPrice as Cost,tblInvoiceLine.SubTotal-tblproduct.productCostPrice as Profit,(dbo.fn_getPayment(PaymentId, tblPayment.InvoiceId, PaymentDate,'CC')) as CC , (dbo.fn_getPayment(PaymentId, tblPayment.InvoiceId, PaymentDate,'CQ') ) as CQ, (dbo.fn_getPayment(PaymentId, tblPayment.InvoiceId, PaymentDate,'Cash')) as CASHFROM tblInvoice INNER JOIN tblDiscount ON tblInvoice.InvoiceID = tblDiscount.InvoiceID INNER JOIN tblSite ON tblInvoice.SiteID = tblSite.SiteIDinner JOIN tblInvoiceLine on tblInvoiceLine.InvoiceID = tblInvoice.InvoiceIDINNER JOIN tblProduct on tblInvoiceLine.ItemCode = tblProduct.ProductCodeINNER JOIN tblPayment on tblInvoice.InvoiceID = tblPayment.InvoiceIDWHERE (tblInvoice.IsInvoiceDeleted = 0)ANDtblInvoice.InvoiceDate between @dtStart + '00:00:00.000' and @dtEnd + '23:59:59.999'and tblSite.SiteID like '%' + @SiteID + '%'Order by tblSite.SiteID, tblSite.SiteName, tblInvoice.InvoiceDateSELECT tblInvoice.InvoiceID,tblRefund.RefundAmount,tblRefund.RefundDate,RefundMethod,tblSite.SiteIDFROM tblInvoice INNER JOIN tblRefund ON tblInvoice.InvoiceID = tblRefund.InvoiceIDINNER JOIN tblSite ON tblInvoice.SiteID = tblSite.SiteIDWHERE (tblInvoice.IsInvoiceDeleted = 1)ANDtblRefund.RefundDate between @dtStart and @dtEndand tblSite.SiteID like '%' + @SiteID + '%'Order by tblInvoice.InvoiceDate,tblinvoice.invoiceidEND This is the PROCEDURE im using Any one Can Help Me Please
Hello,Im wondering if someone can offer some advice, Im trying to get a union in the statement below to it will return all the data as rows in one table..... DECLARE @counter INT SET @counter = 0
WHILE @counter < 24 BEGIN
SELECT "Hour"=(SELECT @counter), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
SET @counter = @counter + 1
ENDIf i write each statment out with the values as apose to @counter and union under each i get the result i am looking for... SELECT "Hour"=(SELECT 0), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) UNION SELECT "Hour"=(SELECT 1), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) UNION
etc. etc. Does doing a while loop have any performace advantages to my other example? Thanks Bart
I need help with a union query. My table structure is as follows: OffierID (key field) CaseFileID CurrentOffer PrevOffer
There table can have multiple entries per CaseFileID. I need a query that will tell the highest value in Current Offer or PrevOffer for each CaseFileID. I have a union query that combines CurrentOffer and PrevOffer and then selects the top value for a specific CaseFileID; however, I want to have a complete list of CaseFileIDs with one value for PrevOffer. My current query is as follows:
SELECT TOP 1 Offer FROM ( select CurrentOffer as PrevOffer FROM tblOffers WHERE tblOffers.CaseFileID = @CaseFileID AND CurrRank <> 1 UNION select PrevOffer as PrevOffer FROM tblOffers WHERE tblOffers.CaseFileID = @CaseFileID ) tmp ORDER BY 1 desc
How can I get this to work for all CaseFiles? Thanks for your help.
When you perform a union between table A and table B which table is the duplicate eliminated from? BOL says they are removed but it does not specify how it chooses which to delete.
