Sorting Cost In Union Query?

Sep 24, 2014

I have a query i have been optimizing. Now runs in about 15 minutes but was wondering if there is any way tr educe the SORT cost.

Currently the high costs left are the Table insert which is 58% and the Sort cost of 36%

The inner query below is around 400million rows and aggregates to around 15,000,000 rows)

SELECT@1 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -1, GETDATE() -1) as DATE)
SELECT@2 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -2, GETDATE() -1) as DATE)
SELECT@3 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -3, GETDATE() -1) as DATE)
SELECT@4 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -4, GETDATE() -1) as DATE)
SELECT@5 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -5, GETDATE() -1) as DATE)

[code]....

View 9 Replies


ADVERTISEMENT

Sorting In Query With Union

Jul 20, 2005

Hi,I am attempting to write a complex query to group sets of data. I have myquery working correctly, but i ran into a problem with sorting.I want to sort my query by a string field.Is there a way around sorting using a field other than numeric of a querycontaining a union?Thanks,Karen

View 1 Replies View Related

T-SQL (SS2K8) :: Finding Last Cost By Article And Compare With Invoice Line Cost?

May 28, 2015

I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.

This is my script:

With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl

[code]....

the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.

Example:

invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075

That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.

View 4 Replies View Related

SQL 2012 :: Cost Threshold For Parallelism - Subtree Cost

Jul 3, 2014

I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?

View 9 Replies View Related

Cost Of A SQL Query

Aug 8, 2003

Is there a way that we can figure out what the cost of a SQL query is via jdbc ? any command etc?

View 1 Replies View Related

Query Cost

Mar 11, 2008



I have 2 different queries which produce same result. I want to know which querry is better and why?
The query is used to display the employee details who is handling the maximum number of project.

Queries are the following

Query A



Code Snippet

SELECT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
COUNT(LUP_EmpProject.Empid) AS Number_Of_Projects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.Empid=EmployeeDetails.Empid
GROUP BY EmployeeDetails.FirstName+' '+EmployeeDetails.LastName,
LUP_EmpProject.Empid
HAVING COUNT(LUP_EmpProject.Empid)>0
AND COUNT(LUP_EmpProject.Empid)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub)


Query B



Code Snippet

SELECT LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName AS EmpName,
COUNT(*) AS NumberOfProjects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.EmpID = EmployeeDetails.EmpID
GROUP BY LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName
HAVING COUNT(*)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub



Please Help!!!!!!!!!!!!!!!!1

View 2 Replies View Related

Where To Find Query Cost

Nov 13, 2013

If I run sample query below against an adventureworks database

where can I find query cost?

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

Does estimated subtree cost for in the actual execution plan (when right click SELECT operator in the execution plan) is considered query cost?

View 3 Replies View Related

How To Calculate Query Cost

Aug 29, 2007

hello,

Does anybody knows how to calculate cost of query ? Any help link or something.

--kneel

View 6 Replies View Related

Query Governor Cost Limit

Jul 20, 2005

I have enabled the query governor on our SQL2000 SP2 server with athreshold of 3600. Now, some of the maintenance jobs fail due to thelimit being to low (e.g. one of the user databases integrity checkfails nightly).I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' justbefore the line in the step which reads 'EXECUTEmaster.dbo.xp_sqlmaint N'-Plan etc'but it has no effect.Does anyone know how to get around this situation without usingsp_configure to change the query governor settings at a systemwidelevel?GC.

View 2 Replies View Related

Query Cost In Execution Plan

Apr 13, 2008

what does query cost(retrive to the batch) mean in execution plan?
what is the differeence between query cost :100% and 65%?

View 3 Replies View Related

Query Analyzer -&> Subtree Cost Vs. Execution Time

Jul 14, 2004

I am using a stored procedure that is behaving badly - the subtree cost is about 2000 and it takes between 3-4 seconds to run, and sometimes it takes over a minute to run. I have made some optimizations that cause the stored procedure to run in generally under 1 second (at most under 2 seconds), but the subtree cost of it jumps to 4000!! All of this while the server was experiencing similar load (the tests were done within minutes of each other).

I know that the subtree cost is a way to gauge the performance of a query against other queries, but I have typically seen the cost go in the same direction as the execution time (they both go up or the both go down).

How does SQL Server determine the cost (I know that is based on statistics, but I was wondering if anyone had more details)? Is it more important to have a lower subtree cost, or a lower execution time? Am I going to get into trouble later with this high subtree cost?

I would appreciate any help on this matter.

View 3 Replies View Related

Turn On The Query Governor Cost Limit Option For 20 Minutes?

Sep 18, 2014

I want to turn on the query governor cost limit option for 20 minutes so that queries do not run longer than 20 minutes but I could not find any info as if this option would also not allow database backup job or other maintenance jobs to run more than 20 minutes. We have backups (Via RedGate) run over 3.5 hours and others like rebuild indexes and integrity checks even more than 3.5 hours....

SQL Server 2012 SP2-CU1
Windows 2008 R2

View 1 Replies View Related

DB Engine :: Cost Vs Time When Including Actual Query Plan In SSMS

Nov 11, 2015

I have two queries yielding the same result that I wanted to compare for performance. I did enter both queries in one Mangement Studio query window and execute them as one batch with the actual query plan included.Query 1 took 8.2 seconds to complete and the query plan said that the cost was 21% of the batchQuery 2 took 2.3 seconds to complete and the query plan said that the cost was 79% of the batch.The queries were run on my local development machine. I was the only user. No other programs were running at the time of this test. The results are repeatable.I understand that the query with the lowest cost is not necessarily the fastest query. On the other hand, the difference is quite big. The query that has approx. 80% of the cost takes 20% of the time and the other way around. I have two questions:

Is such a discrepancy normal?Can conclusions be drawn from the cost distribution? For instance, does the query that takes 8.2 seconds but only costs 21% scale better?

View 9 Replies View Related

How To Create A Make-Table Query With A Union Query

Oct 30, 2006

I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?

View 2 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.



Any suggestions would help.


Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int.  When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end.  The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order.  The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

Help With A Query And Sorting

Feb 8, 2006

I have three tables: one that holds users, on that holds products, and one that holds votes on products (fields are userid, productid, and the vote they made (1-5)). I am trying to come up with a query that orders the product result set by number of votes both descending and ascending for a particular user. If a user has not voted, then there will not be anything in the vote table for that user. I am not even sure where to begin on this as it is way out of my SQL experience.

This is a normal query I use to just pull them out ordered by name.

SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.P_NAME, USERS.U_USERNAME
FROM [PRODUCTS]
INNER JOIN [USERS] ON USERS.USER_ID = PRODUCTS.USER_ID
WHERE STATUS_ID = 1
ORDER BY PRODUCTS.P_NAME DESC

Can anyone help or point me in the right direction?

Thanks.

View 5 Replies View Related

How To Wrap A UNION Query In A Totals Query?

Jul 20, 2005

I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell

View 9 Replies View Related

Sorting Issue In A Query

Oct 11, 2007

I have a SQL script which runs perfectly on my test database, but fails to run on the (identical) production server. After some research, I found out that the field "Remark" (nvartext type of 800 chars) is the cause of the problem. Error message: "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Taken this error message into account in the script, I changed it to the following: "CASE WHEN dbo.User_Updates.Remark IS NULL THEN '<no comment>' ELSE (dbo.User_Updates.Remark) END AS Remarks"

This also works on the test server, but still refuses to run on the production server. Does anyone know what I can do to solve this?

thank you in advance

View 3 Replies View Related

Help Sorting A Recursive Query

Jul 27, 2007

Guys, I need help sorting a recursive query. This is my table

CTLG Table
txtID(PK)
txtParentID(FK)
numSortID
txtKeywords
txtTitle
memContent

I'm using txtParentID as the FK, which matches with txtID, to create the recursion.


This is my current Query

This is my current Query:




Code Snippet
WITH tree (data, id, level, pathstr, numSortID, memContent)
AS (SELECT txtTitle, txtid, 0,
CAST('' AS NVARCHAR(MAX)), numSortID, memContent
FROM CTLG
WHERE txtParentID IS NULL
UNION ALL
SELECT txtTitle, V.txtid, t.level + 1, t.pathstr + '>' + V.txtTitle, V.numSortID, v.memContent
FROM CTLG V
INNER JOIN tree t
ON t.id = V.txtParentID)
SELECT SPACE(level) + data as data, id, level, pathstr, numSortID, memContent
FROM tree



The output is this:








Data
ID
Level
pathstr
NumSortID

Undergraduate Catalog
1
0

1

History
12
1
>History
7

Academic Calendar
14
1
>Academic Calendar
8

Preface
2
1
>Preface
2

NonDiscrimination Statement
3
1
>NonDiscrimination Statement
3

Accreditation
4
1
>Acreditation
4

Memberships
5
1
>Memberships
5

Mission Statement
8
1
>Mission Statement
6

Fall Calendar
20
2
>Academic Calendar>Fall Calendar
1

Winter Calendar
21
2
>Academic Calendar>Winter Calendar
2

Summer Calendar
22
2
>Academic Calendar>Summer Calendar


3



I need my out put to look like the following:

Undergraduate Catalog Level (0) NumSortID (1)
Preface (1) (2)
NonDiscrimination Statement (1) (3)
Accreditation (1) (4)
memberships (1) (5)
Mission Statement (1) (6)
History (1) (7)
Academic Calendar (1) (8)
Fall Calendar (2) (1)
Summer Calendar(2) (2)
Winter Calendar (2) (3)

The Order that I would like to have is based on NumSortID and by Levels. Like the above example.

View 7 Replies View Related

Interactive Sorting/Execution Of Query

Aug 13, 2007

Does clicking interactive sort button in a column reporting services 2005 result re-execution of the query.
Or will it just re-print the rendered data in the layout and so perform better in comparison to the implementation which can be done using drill down to same report with the help of some extra parameters

Priyank

View 5 Replies View Related

Union Query Help

Mar 27, 2006

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.

View 4 Replies View Related

Union Query

Jul 10, 2004

I want to perform the task which is querying the table using the select statemtent and then inserting some values using the insert/update statement.

Can i perform the both (select and insert) using the union statement.

View 1 Replies View Related

UNION Query

Aug 9, 2004

Hi gusy, this is the first time I am trying to use "Union" query. I am trying to create a view(linking and taking data from 3 tables) so I can create a crosstab report out of it.

Basically one table contains about 12 fields and I am trying to grab data from all of them is they are not null.So this is my query,but when it executes it only dispalys result from the first query,what am I doing wrong.

SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
dbo.RelocateeRemovalistAmounts.CarInsurance
FROM dbo.RelocateeRemovalist INNER JOIN
dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
WHERE (dbo.RelocateeRemovalistAmounts.CarInsurance IS NOT NULL)
UNION
SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
dbo.RelocateeRemovalistAmounts.CarTransport
FROM dbo.RelocateeRemovalist INNER JOIN
dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
WHERE (dbo.RelocateeRemovalistAmounts.CarTransport IS NOT NULL)

Thanks

View 1 Replies View Related

A WHERE In A Union Query

Apr 22, 2004

Hi, i have a union query that lists all the years from a date field and add the currentyer if its not already listed:

SELECT DISTINCT Cas Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
ORDER BY DatePart(yyyy,[PressreleaseDate])

what i need to do is filter it with something along the lines of:

WHERE Yearlist LIKE myvariable

although i know i cant simply use:

WHERE Yearlist

it would have to be something like:

WHERE DatePart(yyyy,[PressreleaseDate]) UNION datepart(yyyy, getdate()) LIKE myvariable


Does anyone know how to write this correctly?

View 8 Replies View Related

Union With Sub Query

Jul 23, 2005

I've been trying to do a union with a subquery - I've made a differentexample which follows the same principles as follows:First bit brings back accounts which are in the top 10 to 15 by accountname.Second bit brings back accounts which are in the bottom 10 to 15 byaccount name.I want to union the two result sets together. These selects work asthey are, but don't when i take the comment away from the unionoperator.select top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName asc)order by c1.accountName asc--union allselect top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName desc)order by c1.accountName descSo my problem is really about how to have an order by in a sub querywhich is then used in a main query which is then unioned with anotherquery - SQL Server doesn't seem to like that combination of things. Anyclues anyone?Cheers,NAJH

View 5 Replies View Related

Union Query

Jul 23, 2005

hi,Can you union 2 queries with an IF statement between them?e.g.select a, bfrom mtTablewhere a = cunionif ab = xbeginselect a, bfrom mtTablewhere a = cendCheers,Jack

View 4 Replies View Related

Query: Union On Self

Jul 10, 2006

Hello every body.I have a small issue.Problem: I have a table with 4 descriptor columns (type). I need toformulate a query to retrieve a count for each type so I can groupby...etc. The view I have works, but doesn't work when I supplement thequery with some functions... they just don't like the UNION. The realproblem is I can't change any of the udf's or queries, just the view.The view is inner joined back on to the primary table 'qt_ins' againand a heap of other tables. But for this post and to not complicate ittoo much I've just included the primary table and the view...Also my querys work if I don't put a where clause on for the VIEW. eg:.... and cv.type = 'Environmental'.... for some reason with a clause itgets stuck in an *infinite loop.Conditions: The table structure cannot be changed in anyway. Theview/query must return 2 columns qi_id & type.I considered creating a function to return the Types but then I figuredI would ask you folks for a better way.Any help with the view appreciated.Thank you.The below will create the table, with sample data and the view.---------------------------StartQuery--------------------------------------------CREATE TABLE [dbo].[qt_ins] ([qi_id] [int] NOT NULL ,[qi_injury] [bit] NULL ,[qi_environmental] [bit] NULL ,[qi_equipment_damage] [bit] NULL ,[qi_vehicle] [bit] NULL) ON [PRIMARY]GOINSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (20,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (21,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (23,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (24,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (25,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (26,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (27,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (28,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (29,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (30,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (31,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (32,1,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (33,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (34,1,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (35,1,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (36,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (37,0,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (38,0,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (39,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (40,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (41,0,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (42,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (43,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (44,0,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (45,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (46,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (47,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (48,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (49,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (50,1,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (51,0,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (52,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (53,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (54,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (55,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (56,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (57,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (58,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (59,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (60,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (61,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (62,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (63,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (64,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (65,1,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (66,1,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (67,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (68,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (69,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (70,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (71,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (72,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (73,0,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (81,1,0,0,0)GOCREATE VIEW dbo.v_qt_in_typeASSELECT qi_id, 'Injury' AS type FROM qt_ins WHERE qi_injury = 1UNION allSELECT qi_id, 'Environmental' AS type FROM qt_ins WHEREqi_environmental = 1UNION allSELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHEREqi_equipment_damage = 1UNION allSELECT qi_id, 'Vehicle' AS type FROM qt_ins WHERE qi_vehicle = 1GOselect count(*),type from v_qt_in_type group by type---------------------------ENDQUERY--------------------------------------

View 7 Replies View Related

UNION ALL Query - HELP!

Jul 20, 2005

I am having conceptual trouble with the following query:select r.ServiceID,r.ContractID,sum(Total)from (selectcsc.ServiceID,c.ContractID, sum(csc.ContainerMovement) as Totalfromiwms_tbl_CustomerSiteContainers csc,iwms_tbl_ContractLines cl,iwms_tbl_Contracts c,iwms_tbl_ContractLinePricing clpwhereclp.ContractLineID = cl.ContractLineID andclp.ContractPriceLineDescription = 'Rental' andclp.ContractPriceLineActive=1 andclp.ContractPriceLineExpiry > getdate() andcsc.ServiceID > 1 andc.ContractID = cl.ContractID andc.ContractStatusCode = 5 andcl.ServiceID = csc.ServiceIDgroup byc.ContractID,csc.ServiceIDunion allselectsi.ServiceID,c.ContractID,sum(j.QuantityCollected) -sum(j.QuantityDelivered)as Totalfromiwms_tbl_Jobs j,iwms_tbl_ServiceInstances si,iwms_tbl_ContractLines cl,iwms_tbl_Contracts c,iwms_tbl_ContractLinePricing clpwhereclp.ContractLineID = cl.ContractLineID andclp.ContractPriceLineDescription = 'Rental' andclp.ContractPriceLineActive=1 andclp.ContractPriceLineExpiry > getdate() andc.ContractID = cl.ContractID andc.ContractStatusCode = 5 andcl.ServiceID = si.ServiceID andj.JobStatusCode <> 80 andj.ServiceInstanceID = si.ServiceInstanceID andsi.ServiceID > 1group byc.ContractID,si.ServiceID) as rgroup byr.ContractID,r.ServiceIDhavingsum(Total) <> 0order by r.ContractIDIt returns 140 rows. However, if I comment out the first selectstatement inside the brackets (select csc.ServiceID,c.ContractID....union all) and run it, it returns 4,785 rows. If I comment out thesecond select statement (union all ...group byc.ContractID,si.ServiceID) it returns 4,786 rows. So why doesn't the*whole* thing return 9,571 rows? That's what I thought a UNION did -append the results of one select to the bottom of the second select.I will supply table defs if it will help, but there's a lot of stuffhere and I think it isn't a data question, but anI-don't-understand-the-SQL question!TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk

View 3 Replies View Related

SQL Query Help. UNION ALL

Jan 30, 2008

Hi, I have a question,

Im trying to use the UNION ALL statement to join my results.
I have multiple talbes, each table has the table name as a date: 28_1_2008, 29_1_2008, etc..
Some dates dont have tables.

What I want to do is write an sql query that gets all the results from a rage of dates..

ie. range : 27_1_2008 to 29_1_2008

What im doing :

sql1: Select * from 27_1_2008 UNION ALL select * from 28_1_2008 UNION ALL select * from 29_1_2008.

It works fine if all tables exist, but in my data base every date DOESNT NECESSARLY have a table.



ie. say I have tables 27_1_2008 , 28_1_2008, 30_1_2008

Now if I do

sql2: Select * from 27_1_2008 UNION ALL Select * from 28_1_2008
This works.

But,
sql3: Select * from 28_1_2008 UNION ALL Select * from 29_1_2008 UNION ALL Select * from 30_1_2008

Causes an error, and DOESNT return any records..




I need a way so that even if one particular table doesnt exist, the others should be returned.

i.e in sql3, it should skip the non existing "29_1_2008" table, and return the rest. As of now, it doesnt return anything,

Anyone with some suggestions?

View 10 Replies View Related

Union Query?

Feb 8, 2008



I have the following query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'LI_759') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

which produces the following:






LI_759
-0.001
13:28:20

The second query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'LI_759') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value) DESC, CONVERT(varchar(15), DateTime, 108) DESC

Produces the following:





LI_759
0.3661
06:09:30


I have tried the previous answers to this post and none of them worked, i get various errors saying sql is unable to parse. what I would like to have is one query that produces the following:

min:



LI_759
-0.001
13:28:20

max:




LI_759
0.3661
06:09:30

Thank You.

View 3 Replies View Related

UNION Query..need Help!!!!!

Apr 17, 2008



currently I am investing some time in learning SQL, I have been working with a sample db, and have come across UNION.
I see many examples showing the same thing, 2 tables with identical datatype and either with a alias or same name. My question is, can a UNION be used for more than 2 tables?
I currently have 3 tables: orders(customerNum,orderNum,orderDate), orderline(orderNum,partNum),
part(partNum,description)
I want to display orderdate and ordernum, only if partNum= 1234 or customerNum= 123
everything is char datatype

I am able to innerjoin, however I am somewhat confused attempting a UNION query. is this possible?
so far


select orders.orderNum,orderDate

from orders,orderline

where orders.orderNum=orderline.orderNum and customerNum='xyz'

union

select orderline.orderNum,part.partNum

from orderline,part

where part.partNum=orderline.partNum and orderline.partNum='abcd'

order by 1



as you can see i am somewhat lost. At the moment the orderDate column is showing partnum and dates. But if someone can show how to make this possible I would greatly appreciate it.

View 6 Replies View Related

Query Questions, Filtering Or Sorting Of Columns

Oct 26, 2004

I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information.
I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns
I will use Honda Accord as an example.
Honda has made the Accord since probably the 80's
I know that if I
SELECT yearnum
FROM YearNum
ORDER BY yearnum

I am using C++ Builder too..
it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord.
Is there anyway to filter out multiple years, so I could just have the regular order of years?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved