how 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
This 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.
Hi 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!
Need 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.
select *
from dbo.RPT_ContractDetails
where businessname like '%carl's%'
carl's should be a string.
Thanks
Hi, 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?
We 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)
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
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
Hi: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 RelatedHi 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
Hi 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 RelatedHi,
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
I 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 RelatedI 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 RelatedI 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!
I 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
Hi,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 RelatedI 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)?
I 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 RelatedI 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
I 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 RelatedHi 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.
when i tried to put an orderby in view...i am getting an error...
we can't have a orderby in view?.
When 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?
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName CostApple 0.63Pear 0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID FruitName Cost2 Apple 0.634 Pear 0.89
Thanks.
error:
Another SqlParameterCollection include ParameterName“@Id� SqlParameter?
if I remove one of " RunProc("test",param);" ,there will be right
private void Page_Load(object sender, System.EventArgs e)
{
SqlParameter param = new SqlParameter("@Id", SqlDbType.Int);
param.Value = 1;
RunProc("test",param);
RunProc("test",param); //if i remove this line ,it's OK
}
public void RunProc(string procName, SqlParameter pram)
{
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
con.Open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(pram);
cmd.ExecuteNonQuery();
con.Close();
}
Does an Differential Backup contain all the changes since the last full backup, including Inserts, Updates, and Deletions. Our DB has "Truncate on Checkpoint" = True, so Log backups are non-sensical. I want to apply the Differentials to an archive, offline DB.
"one pebble does not fill the void, but it is a start."
"Dedicated to only creating original mistakes."
Hi there:
Using Oracle SQL*Plus, I can have a script to execute other scripts written before such as this:
@script1
@script2
..
@scriptn
Using ISQL or ISQL/w, how can I do this?
Thanks in advance
I have a insert statement but one of the strings contains a apostrophe. If I leave the apostrophe in an error occurs becuase it thinks that it is the end of the string. What is the proper syntax for including apostrophes in a string?
Thanks.
I have some lengthy code that creates a #Temp file. I would like to access this code from different stored procedures. Is there such a thing as an INCLUDE statement for stored procedures, or is this what the EXEC statement accomplishes?
View 12 Replies View Related