Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.
Existing procedure:
ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)
select @Cond=''
if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)
if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;
SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')
In the followinf portion of my SQL Statement, I cannot figure out how to specify ASC or DESC for the OrderBy... If I Put OrderDate ASC after the THEN, I get an error and if I put @SortDir after the END I get an error... Can Anyone help with this? WHERE OrderID IN(SELECT OrderID FROM #rsltTable WHERE ID >= @l_FirstRecord AND ID <= @l_LastRecord)ORDER BYCASE @OrderByWHEN 'OrderDate ASC' THEN OrderDateWHEN 'OrderDate DESC' THEN OrderDateEND Thank You,Jason
I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
I am trying to use dynamic ORDER BY with UNION in a stored procedure but I keep getting this error message:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The ORDER BY works fine without the UNION, and the UNION works fine without the dynamic ORDER BY (just putting an ORDER BY works fine)
Here is the whole query in case this helps, thank you
SELECT leave_id, leave_type, annualLeave.from_date, convert(nvarchar(10), annualLeave.request_date, 103) as dated, convert(nvarchar(10), annualLeave.from_date, 103) as from_date, convert(nvarchar(10), annualLeave.to_date, 103) as to_date, annualLeave.canceled, annualLeave.working_days, staff.staff_name, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem' ELSE '' END AS pendingStyle, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' ELSE '' END AS historyStyle FROM annualLeave INNER JOIN staff ON annualLeave.staff = staff.staff_id WHERE staff.department like @Department and (@staffID is Null or annualLeave.staff = @StaffID) and (@Canceled is Null or annualLeave.canceled = @Canceled) and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null) and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null) and (@StartingDate is Null or annualLeave.from_date > @Startingdate) and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave) and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)
UNION
SELECT leave_id, leave_type, annualLeave.from_date, convert(nvarchar(10), annualLeave.request_date, 103) as dated, convert(nvarchar(10), annualLeave.from_date, 103) as from_date, convert(nvarchar(10), annualLeave.to_date, 103) as to_date, annualLeave.canceled, annualLeave.working_days, staff.staff_name, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem' ELSE '' END AS pendingStyle, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' ELSE '' END AS historyStyle FROM annualLeave INNER JOIN staff ON annualLeave.staff = staff.staff_id WHERE staff.department like @Department and (@staffID is Null or annualLeave.staff = @StaffID) and (@Canceled is Null or annualLeave.canceled = @Canceled) and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null) and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null) and annualLeave.leave_type like 'Leave in lieu' ORDER BY CASE @OrderDir WHEN 'ASC' THENannualLeave.from_date END ASC, CASE @OrderDir WHEN 'DESC' THEN annualLeave.from_date END DESC
I need it to dynamically output based off COL1, the output should look like this. When there are more rows for CAT, it should output more columns. Kind of like merging the columns
Hi, Can anyone help as to how to get this to work? dbo.parseInt is a Function which sorts Alpha Numerically. If I removed the top part of the UNION, the Procedure works fine.
ALTER PROCEDURE dbo.ByJobNoAlphaNumeric as SELECT 0 AS JobID, '<All Jobs>' AS JobNo UNION SELECT JobID, JobNo FROM tbl ORDER BY dbo.parseInt(JobNo)
SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_a UNION SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_b
the problem is the rec_num repeat for each statement like this :
rec_num empname
1 john 2 maggy 1 lee 2 mary 3 louis
How do i make the rec_num continue for the next statement after union.
tblOpenSiteDates Site: int OpenDate: smalldate Comment: VarChar
tblCloseSiteDates Site: int CloseDate: smalldate Comments: VarChar newLocationID: int
I am trying to get a view which would display a site with the open and close dates. Null is ok for a close date for those opens that are still open.. not every office has an open date and close date.. it is possible to have just a close date and not an open date (ie unsure of open date but I know its closing)
so the output would be
viewOpenCloseSites Site: int opendate: smalldate closedate: smalldate
Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark
I have a SP that accepts parameters, but the problem is that it is not ORDERING Data as required, It is returning ORDER BY NULL instead of order by my parameter "NAME "here is the output from SQL 2000 DB
'ELP B4 I jump out of LondonBridge
********************************** SELECT * FROM #TempTable WHERE ID > 0 AND ID < 6 AND EmployerID = 54 AND Job_no = 40
ORDER BY NULL ****************************************
SELECT @MYSTATEMENT = ' SELECT * FROM #TempTable
WHERE ID > '+ convert(varchar(20),@FirstRec) +' AND ID < '+convert(varchar(20),@LastRec) +' AND EmployerID = '+ convert(varchar(20),@EmployerID)+' AND Job_no = '+convert(varchar(20),@Job_no)+'
ORDER BY ' + CASE WHEN @WhichColumn= 'name' AND @sortby= 'asc' THEN 'name' ELSE 'NULL' END + CASE WHEN @WhichColumn='name' AND @sortby='DESC' THEN ' desc' ELSE '' END
Hi ! I have some table with some "name" field in it which looks like "Blah blah blah Part 1" "Blah blah blah Part 2" "Blah blah blah Part 3" when I query it select * from mytable orderby name it doesn't actually work in a "proper" way. I keep on getting something like "Blah blah blah Part 2" "Blah blah blah Part 1" "Blah blah blah Part 3" Do you know what should I do to make it work?
Hi ! I have some table with some "name" field in it which looks like "Blah blah blah Part 1" "Blah blah blah Part 2" "Blah blah blah Part 3" when I query it select * from mytable orderby name it doesn't actually work in a "proper" way. I keep on getting something like "Blah blah blah Part 2" "Blah blah blah Part 1" "Blah blah blah Part 3" Do you know what should I do to make it work?
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
Lets say I have a table PortDates with 3 columns like this:PORT ARRIVALDATE SAILINGDATENewYork 12-15-2004Rio 10-12-2004Hamburg 10-14-2004etc.I want to sort the ports on dates from both date columnsSELECT Port FROM PortDatesORDER BY (ArrivalDate JOIN SailingDate)Now, that doesn't work of course, but what should I write to get it to workso I can order by the dates as if they were one merged column?Join Bytes!
I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.
CREATE FUNCTION dbo.[f_Rec_cnt] (@InTableName NVARCHAR(100), @InDtColName NVARCHAR(50), @InDate NVARCHAR(50) ) RETURNS INT
Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.
IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.
select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;
But now if I want to grab them in alphabetical order from another table I have problems.
this is the code I am trying to use maybe I am just thinking it through wrong. select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;
any help is appreciated. Is there a way to grab each row in alphabetical order?
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.
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)
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
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
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--------------------------------------
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
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,
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:
