Query With Row Number,Union And Dynamic Orderby.

Dec 19, 2007

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


exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int


select @reccount=count(*) from (SELECT tbItemMaster.id
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 + '
Union
SELECT tbItemMaster.id
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 + ') tbl


set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (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' + @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
')


RETURN


can anybody help me pls. ITs very urgent.

Thanks in advance.

View 1 Replies


ADVERTISEMENT

How Do You Do ASC And DESC With Dynamic OrderBy

Mar 21, 2006

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

View 9 Replies View Related

Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

Hi Craig/Kamal,

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

Thanks



Craig Guyer
SQL Server Reporting Services

View 12 Replies View Related

Dynamic ORDER BY And UNION

May 13, 2008

Hello,

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

View 3 Replies View Related

Dynamic Union All - Output More Columns

Apr 29, 2014

I am having a bit of trouble writing a query that dynamically outputs what I need.

I have a table that looks like this:

COL1 AAA BBB CCC
CAT 1 3 3
CAT 1 2 2
CAT 1 4 4
DOG 2 3 3
DOG 5 5 5
MICE 1 1 1

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

COL1 AAA BBB CCC AAA BBB CCC AAA BBB CCC
CAT 1 3 3 1 2 2 1 4 4
DOG 2 3 3 5 5 5 NULL NULL NULL
MICE 1 1 1 NULL NULL NULL NULL NULL NULL

View 3 Replies View Related

UNION With Dynamic Order By Failing

Nov 30, 2007

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)

Regards
Andrew

View 2 Replies View Related

How Do I Put Records Number In Union

Dec 21, 2007



Hi i have sql statement like this :

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.

View 1 Replies View Related

Using Top To Get Set Number Of Results From A Union

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

Union Of 2 Tables With Differnt Number Of Rows

May 24, 2006

I have 2 tables

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

PLEASE HELP i just can not figure it out

thanks...

View 1 Replies View Related

Count The Number Of Rows In A UNION ALL Statement

Jan 8, 2007

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

View 1 Replies View Related

Error Using Union All With Row Number Transformation From SQLIS(.com)

May 14, 2008

Has anyone else experienced similar problems? I've been trying to use "Row number" from sqlis.com site with "Union All" but no lucks.

[DTS.Pipeline] Error: component "Union All" (1840) failed the pre-execute phase and returned error code 0x80070057.


thanks

View 6 Replies View Related

Dynamic Number Of Columns

Jun 2, 2005

when using sorred procedure to create a temporary table is it possable to base the number of columns in that table on another variable?

View 5 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

Help Me With SP OrderBy

Jan 18, 2005

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

EXEC (@myStatement)

View 5 Replies View Related

OrderBy

Nov 14, 2005

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?

View 5 Replies View Related

ORDERBY Name

Nov 14, 2005

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?

View 3 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

Merged Columns In OrderBy

Jul 20, 2005

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!

View 7 Replies View Related

Can We Include Orderby In View?.

May 5, 2008



when i tried to put an orderby in view...i am getting an error...

we can't have a orderby in view?.

View 7 Replies View Related

Dynamic Function To Return Number Of Records In Table

Aug 5, 2014

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

[Code] .....

View 1 Replies View Related

Sort Table Descending Without Orderby

Jun 24, 2008

can any one help me to sort a table on the basis of one colomn without using ORDER BY Desc.

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 6 Replies View Related

Simple Select Statement OrderBy

Feb 4, 2008

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?

View 3 Replies View Related

Passing Orderby Clause As Parameter In SP

Jan 24, 2008

Hi,

I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.

Below is my procedure..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),

as

BEGIN

SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END

The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.

I am not able to figure out why.

Any help in this will be appriciated.

Regards,
Salim.

View 16 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







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