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?.
ADVERTISEMENT
Help Me With SP OrderBy
Jan 18, 2005I 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)
OrderBy
Nov 14, 2005Hi !
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?
ORDERBY Name
Nov 14, 2005Hi !
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?
How Do You Do ASC And DESC With Dynamic OrderBy
Mar 21, 2006In 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
Merged Columns In OrderBy
Jul 20, 2005Lets 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 RelatedSort Table Descending Without Orderby
Jun 24, 2008can 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
Simple Select Statement OrderBy
Feb 4, 2008Well 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?
Passing Orderby Clause As Parameter In SP
Jan 24, 2008Hi,
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.
Query With Row Number,Union And Dynamic Orderby.
Dec 19, 2007Hi,
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.
Establishing Precedence In ORDERBY Condition Causing Problems.
Jun 11, 2007Hi.I really need some advice on fine-tuning a stored procedurethat is the meat of the search logic on my site. Customersare allowed to save searches, which dumps the search logicin a table called SavedSearches for later access to the search.My problem started with the ORDERBY condition used for zipcodesearches. The condition did something like:"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHENuserID=102 THEN 3 WHEN userID=81 THEN 4"Of course, this fails when a customer described in the saved searchresults deletes his profile.I have since attempted to brace against this problem by adding aUserPrecendence table with the following columns: email_address,up_order (or, user precedence order), and userID.Since I have made the precedence changes, I have been unsuccessful ingetting any results (data) back from the query. I think it has to dowith the change but am not quite sure what I am doing wrong.I would appreciate it is somebody could take a look at my sproc withparticular attention to how precedence is handled in the ORDERBYcondition. Maybe you can see something I can not?As always, much appreciated.PS - In addition to the UserPrecedence change, I have attempted to addpaging - returning N amount of pages per request based on passed-inparamaters. I'd appreciate it if you could take a quick glance herealso just to make sure my logic is OK.----------------------------------------------------------------------------------------ALTER PROCEDURE [dbo].[sp_PeopleSearch]@pagenum INT = 1,@perpage INT = 10ASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INTSELECT@rows = COUNT(*),@pages = COUNT(*) / @perpageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)IF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum @pages SET @pagenum = @pagesIF @pagenum < 1 SET @pagenum = 1SET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,PageSize = @perpage,TotalPages = @pages,TotalRows = @rows,UpperBoundary = @ubound,LowerBoundary = @lbound-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE@gender VARCHAR(50),@country VARCHAR(50),@orderby INTEGER,@low VARCHAR(50),@high VARCHAR(50),@photo VARCHAR(50),@sort INTEGERSET ROWCOUNT @lboundSELECT@gender = saved_sex,@country = saved_country,@orderby = saved_orderby,@low = saved_fage,@high = saved_tage,@sort = saved_sort,@photo = saved_photo_stringFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)ORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT @perPageSELECT COALESCE(tab1.emailAddress,tab2.user_name,tab3.email_address,tab4.email_address,tab5.email_address,tab6.email_address)id ,tab1.bday_day ,tab1.bday_month ,tab1.bday_year ,tab1.gender ,tab1.zipCode ,tab1.siteId ,tab1.userID ,tab2.photo_location ,tab2.photo_name ,tab2.photo_default ,tab2.no_photo ,tab3.headline ,tab3.about_me ,tab4.login_date ,tab4.login_isonline,tab5.up_order,tab6.saved_orderby,tab6.saved_sort,tab6.saved_fage,tab6.saved_tageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)WHEREtab1.gender = @genderAND tab1.country = @countryAND tab1.bday_year BETWEEN @low AND @highAND tab2.photo_default = 1 + @photo--and not tab2.no_photo = 1--firstName + '~' + lastName-->= @fname + '~' + @lnameORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT 0END
View 7 Replies View RelatedHow To Include Row Totals?
Feb 4, 2002This is probably a simple task but I just don't know how to do it. I need to return a recordset of details with a row of totals for selected columns. Something with a result like this:
Location Attendance Showings
======== ========== ========
JOHNS 210 3
SEREN 116 2
total 326 5
I know I could do this with a stored procedure, but I'd prefer to do it with just one sql statement. I tried compute but because I have a total on more than one column, the returned recordset is actually 3 rows.
Thanks in advance for any suggestions.
SQL Include And Execute
Feb 4, 2004Hi everyone,
Is there a way in SQL to include and execute another file? For example, I have file1 and I would like to execute the contents of file2 from file1. How can this be done?
Thanks!
SQL, How To Include Recs....
May 21, 2004Need to find a way to do this. I have two tables, One with codes i.e. S,R,T and other with transactions looks like this:
Transaction table
Emp Trans Tot...
X55677 S 8
X55677 R 2
C22887 S 4
C22887 T 3
F66889 S 9
F66889 R 4
F66889 T 3
Code table
S
R
T
Not sure if the code table even helps. What I want to do is show all three transactions for each employee even if they don't have any (would be zero) like so...
Emp Trans Tot...
X55677 S 8
X55677 R 2
X55677 T 0
C22887 S 4
C22887 T 3
C22887 R 0
F66889 S 9
F66889 R 4
F66889 T 3
How can I get to this. I'm sure there must be a solution already posted for something like this but I'm not sure what to search for.
Thanks for your thoughts.
How To Include ' In A String ?
Feb 22, 2007select *
from dbo.RPT_ContractDetails
where businessname like '%carl's%'
carl's should be a string.
Thanks
How To Include The Nulls??
Sep 13, 2006Hi, I have the following query stored:
SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)
Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...
As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.
The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.
Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.
I'd get
Prod | Qty | Week
1 23 1
1 12 2
2 10 3
3 22 1
3 15 2
3 12 3
3 4 4
Although this looks fine - what I actually need is:
1 23 1
1 12 2
1 0 3
1 0 3
2 0 1
2 0 2
2 10 3
2 0 4
3 22 1
3 15 2
3 12 3
3 4 4
Does that make sense?
Any ideas on how to do this?
Index With Include.
Nov 27, 2007We today have 3 different indexes on a table that look like this
CREATE INDEX IX_01 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX])
CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
CREATE INDEX IX_03 ON [TORDLOG] ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [RADNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
I want to combine the index to only 1 index; How do can combine all the include columns to one large? Or shall keep all index. Bit bad on how to handle include columns. Or just create new index with regdat and forget all about include?
CREATE INDEX IX_02 ON TORDLOG ([REGDAT])
OR
CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE (ALL COLUMNS)
Include All If Parameter Value Null?
Mar 14, 2007Hi:I have an application in which I am producing a lists of media outlets based on a number of 6 possible parameters including location, medium, format, scope, language and coverage. I have an interface in which users can select some or all of the possible parameters. Where they don't choose a parameter - e.g. location, I would like the SELECT query to include all locations. I am at the point now of writing 6 X 6 = 36 possible queries to handle the different combinations of possible parameters. This seems a bit excessive and clumsy. I am just learning about stored procedures and was wondering if it is possible to create a procedure that would accept the 6 parameters including ones with null or 0 values and generate a query that would ignore those that were null or zero. Any help in this regard greatly appreciated.Roger Swetnam
View 5 Replies View RelatedWhere To Include All Constraint And Relationship
Mar 23, 2007Hi Guys,
Is it better to create all tables (in store procedure) and then another store procedure to create and apply all relationship between the tables??? , OR
is it better to have each table relationship at the end of the store procedure to create that table and have each table relationship at the end of its create statement???
What are the Advantages and disadvantages of each method?
Thanks,Mehdi
SQL Query To Include One Record Before And After
Oct 29, 2007Hi I have a query that return set of records between 2 given timestamps. What I want to do is to include one record before and one record after to the result. For example I have the following dataRow Timestamp1 01/01/20072 15/03/20073 17/04/20074 05/05/20075 10/05/20076 11/06/20077 12/07/2007 My query returns row 3 to 5 for example and I want to include row 2 and 6 to the result as well. How can I do that? Is it possible to do it in 1 query?
View 11 Replies View RelatedHow To Include ASPNET.MDF In My Database?
Jan 25, 2008Hi,
I have a test database and need to to include the aspnet.mdf into my database.
I have generated a script for the aspnet.mdf, but don't know how to run/include this into my own test database.
1. Is there any magical sql-command that do the job in a clean way for me?
2. How can I change the name of aspnet in that auto generated script, if needed at all?
I have: SQL server Express Edition 2005, Visual Web Developer Express Edition 2008
Thank's
/Pepe
Sql Select To Include Date?
Apr 19, 2008I have a table of parts and want to display some statistics from it. The parts table has several fields in it, but the two that I need to use in my sql select queries are "PartUserID" and "DateUseBy", but I don't know how to use the "dateuseby" in the second example.Can someone help me out with #2?1. Display the total number of parts owned by a user:SQL Query: SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId)" OnSelecting="sqlPartCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> </SelectParameters>Code-behind: protected void sqlPartCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; } 2. Display the total number of parts owned by a user that are to be used in the current year. The table has a field "dateuseby" that should be used for the "Where" but I don't know how to get it.SQL Query:How to I include the "usebydate" so that it will use 2008 as the year ? I wrote the following query as an example of what I'm trying to get. SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId, ZCPart.DateUseById = @PartUseById)" onselecting="sqlPartYearCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> <asp:Parameter Name="DateUseByID" /> </SelectParameters>Code-behind: protected void sqlPartYearCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; // ? How do I change the date in the field to be in "2008" format and then put that in the sql query? //e.Command.Parameters["@DateUseByID"].Value = currentYear; //currentYear currentDateTime = DateTime.Now; }
View 7 Replies View RelatedDo Not Include Certain Rows Into Gridview
May 11, 2008I want to retrieve some data in my gridview using a sqldatasource. Here's the idea. My Gridview contains events. A user can subscribe into one of them. When he subscribes, the event must be removed in the gridview. So when there is a subscription from that certain user for that event, it may not appear. Here's my not working code..SELECT Event.EventID,Event.name, Event.LocationID, Event.Date, Event.StatusFROM Shift INNER JOIN Event ON Shift.EventID = Event.EventID INNER JOIN Subscription ON Shift.ShiftID = Subscription.ShiftIDWHERE (Subscription.UserID <> @UserID) Greetz
View 1 Replies View RelatedAfter RESTORE, Must Include DBO In Queries?
Feb 4, 2004I used to be able to write a query like this:
select * from foo;
But after re-creating my database using RESTORE, I now have to do this:
select * from myName.foo;
...where myName is either the name of the database or the name of the database owner. That is, it's the name of the database, but I believe it's also the name of the database owner.
How do I go back to being able to just use the table name?
Thanks!
Pull From Two Tables, But Include Everyone
Mar 16, 2004I want to pull ALL users from Table1 and include information that person may have in Table2. As of now, it is only including users that have something in Table2. How do I include everyone?
Thanks
How To Include Months Not In The Records??
Oct 19, 2005Hi,I have 2 tables created. One is the Sale Persons (PersonID, PersonName) and the other is the Sales Detail (PersonID, Month, Year, TotalSales)PersonID | PersonName PersonID | Month | Year | Total Sales================== ==================================ID1 | Sally ID1 | 1 | 2005 | 1000ID2 | David ID2 | 2 | 2005 | 1500Is it possible to write in a SQL statement to return all the sales person & the total sales for the 12 months for a particular year (even though some month data are not in the table)I would like the result to be like the following:PersonID | Month | Year | Total Sales===================================ID1 | 1 | 2005 | 1000ID1 | 2 | 2005 | 0ID1 | 3 | 2005 | 0(For month 4 - 12) Total Sales will be 0 too as no records exist in the first placeID2 | 1 | 2005 | 0ID2 | 2 | 2005 | 1500..........
View 2 Replies View Related% Wildcard Include Nulls
Feb 28, 2006I have a query with 4 parameters:
Name
Location
Employee Number
Officer Code
There are no null values in Name, location and Employee Number. However, all employees do not have an officer code. So in my query I use a where clause that says WHERE OfficerCode LIKE '%'+@Param4+'%'. I use a % for the default value in my ASP.net data control. The only problem is that the only records returned have non-null values in the OfficerCode field. How can I use a wildcard for a default value and return all records (null and non-null)?
Include / In Select Statement
Mar 21, 2006I wanted to create something like this:Select (FirstItem + "/" + SecondItem) AS Itembut I get error. Is there anything wrong with this code?P.S. I'm using mssql 2000
View 2 Replies View RelatedDistinct But Include All The Columns
Nov 1, 2013I have this but how can I get all the columns of the row? I only want them distinct per these 2 cols.
SELECT DISTINCT OrdHst.ordernbr, OrdNbr.trans
FROM OrdHst JOIN OrdNbr
ON OrdHst.ordernbr = OrdNbr.ordernbr
ORDER BY 1,2
Could return:
12345 001
12345 AAA
12345 BBB
12345 CCC
12345 PWB
How To Include Ascin Dynamic Sql
Mar 1, 2007how to include asc near order by
select @sql= ' select u.userid,u.user_name, u.password, c.code_description as role_code,u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.' + @columnname + ' like ''' + @recordname + '%'' order by u.' + @columnname
Include Data In E-mail
Jul 20, 2005I have about 150 people I would like to send e-mail automatically. Eachperson would get a unique form letter that includes username andpassword I have stored in a SQL table. Is this possible? Helpappreciated. Thanks.Frank*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedInclude Tick (') In Varchar?
Aug 15, 2007Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
SQL 2005 RS - Include Link
May 18, 2007When I include link in e-mail from RS I get
"The report is accessible at the
following address:"
before the link.
Can I change this text?