Show SQL Search Query In Pages

Jul 12, 2007

How to show a SQL search query in pages? for example 10 records in each page, like search engines.
I'm using .NET Framework 2, SQL Server 2000 and Adobe Dreamweaver CS3.
Thanx

View 1 Replies


ADVERTISEMENT

How To Integrate Full Text Search With Asp.net Pages

Jun 10, 2006

Hi,
I just designed a website using asp.net 2.0, now my customer hopes I can add Search website feature, he expected that it can search both static text in aspx and dynamic content in SQL Server. The problem is : I don't know how to compose the result URL to the user. Suppose I have a table called "Table1", webform1.aspx and webform2.aspx will retrieve data from this table, and the query string of these two pages are different. After searching the table, I expected that there will be two matching records, the first one is linked to "webpage1.aspx?ID1=xxx" and the second one is linked to "webpage2.aspx?ID2=xxx". Must I hard code the result page for each searchable table? Any other solutions?
Thanks,
Ziff

View 1 Replies View Related

Search HTML Pages And Populate Links

Apr 17, 2008

Hi All,

I have a table with several columns including 'Path' column where I saved HTML file name and saved all the html pages to root folder of the website. Now I want to make search for text inside the html pages and populate the html page links, so user can click and open the relevant html files.

Can someone give me an idea that how this all process works or refer me to any useful link.

Thanks

View 1 Replies View Related

Why Clean Data Pages Written To L2 Cache To Make Space For Other Not Modified Pages

Oct 2, 2014

in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014: only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??

why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...

View 2 Replies View Related

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

Mar 29, 2008

 Hi -  I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids:  dbo.Search_Articles        @searchText varchar(150)        AS    SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText);        RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter 

View 3 Replies View Related

Pages Results On Full-text Query - Sql Help Please

Mar 30, 2008

 Hi -  I am trying to add paging to my stored procedure. The stored procedure successfully executes a full-text search. Unfortunately, the paging routine below the full-text search operates on the articles table after the search has been conducted. This means that it utilizes the row numbers from the entire table rather than the row numbers from the result set. I somehow need to the paging routine starting at "WITH tempArticles AS" to operate on the search results rather than the articles table. I am too new to SQL and can't figure out how to populate a temporary table storing text search results to use in this paging routine. Can anyone help me with this? Thanks! Peter   dbo.Search_Articles        @searchText varchar(150),    @PageSize int,    @PageNumber int        AS         Declare @RowStart int     Declare @RowEnd int         if @PageNumber > 0     Begin         SET @PageNumber = @PageNumber -1;     SET @RowStart = @PageSize * @PageNumber + 1;     SET @RowEnd = @RowStart + @PageSize - 1 ;     CREATE TABLE #results (ArticleID int)    INSERT INTO #results        SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText)    /*this returns all matching records from the text search*/    SELECT *    FROM articles, #results    WHERE #results.ArticleID=articles.ArticleID;                     WITH tempArticles AS    ( SELECT Title,      PostDate,      UserID,      City,      Country,      Tags,      StoryID,      Approved,        ROW_NUMBER() OVER (order by PostDate) as RowNumber        FROM articles WHERE Approved = 1)           SELECT *    FROM tempArticles     WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd;              END

View 4 Replies View Related

Spreading SELECT Query Results Over Multiple Pages

Dec 20, 2006

Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages.
for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on...
is there any way to achieve it using an SQL query?
the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway.
any suggestions?
Thanks.

View 5 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

Query To Show ID That Is Not In DB?

Oct 24, 2013

I have a list of OrderIDs and I'd like SQL to show me the ones that do not appear in the DB.

Lets say that my table looks like this:
idorderIdUserItem
122joepotatoes
223SteveApples
324SteveBananas
438DaveCarrots

And the query looks like this:
SELECT orderID FROM sales WHERE orderID IN ('22','51','38')

You'll notice that orderID 51 is not in the table. How do I make it tell me the orderIDs that do not appear?

View 1 Replies View Related

How To Show Boolian Value In Sql Query?

Apr 1, 2008

i have one table completed tabletask_id   task_due_datetime   task_completed_on  1                    03/21/2008        03/25/20082                    03/12/2008        03/10/20083                    03/10/2008        03/18/2008 i want the output:task_id    on time        late 1                  0             1 2                  0             0 3                  0             1  how i get this output in sql query without the use of cursor...  

View 5 Replies View Related

Query To Show Duplicates

Aug 16, 2005

mytable      fld1 int     primkey                  fld2 varchar(20),                  fld3 varchar(20)From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2.  I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates.     select fld1, fld2, fld3 from mytable                

View 1 Replies View Related

SHOW Query For MSSQL

Sep 18, 2004

What would the MySQL equivalent for "SHOW TABLES" and "SHOW FIELDS" be in MSSQL?

View 8 Replies View Related

Query To Show All Data According To ID

Feb 1, 2015

I have a table i need to show data according to id. distinct id and all name should be comma separated against each id.

DECLARE @TBL TABLE (ID NUMERIC(10), NAME VARCHAR(10))

INSERT INTO @TBL (ID,NAME)
VALUES(1, 'A'), (1,'B') ,(1,'C') ,(2,'E') ,(2,'B') , (3,'F') , (3,'G')

output :

id name

1 a,b,c
2 e,b
3 f,g

View 2 Replies View Related

Show Me Example Of Dynamic SQL Query

Mar 5, 2008

Show me example of dynamic SQL query if possible

thank you

View 5 Replies View Related

SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase

Aug 9, 2006

I'm just wonder if this is a bug in MS Search or am I doing something wrong.

I have a query below

declare @search_clause varchar(255)

set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'

select * from results

where contains(finding,@search_clause)

I don't get the correct result at all.

If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"

then i get the correct result.

It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?

Anyone know?

Thanks

View 3 Replies View Related

Show The Decimal Part In Query

Jun 19, 2008

Hi,In the following query the calculator gives value 3.3  but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks 

View 3 Replies View Related

T-SQL (SS2K8) :: How To Show ED / CESS In Query

Nov 5, 2014

Detail:

create table detail
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)

insert into detail values

('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
values ('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
values ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')

i wanna display ED ,CESS ,HCESS,VAT in separate columns in output How to that?

View 4 Replies View Related

How To Query And Show The Result In A Form

May 12, 2006

I am an beginner in VC#2005,

my question is:

I connect a table and transtorm to WM5.0 (PDAphone),

I don't know how to show the result of my query,

EX:

table1,(two column: name , phoneNo)

private void button1_Click(object sender, EventArgs e)
{
SqlCeConnection cn = null;
try
{
cn = new SqlCeConnection("Data source=\Programs files\sqltest1\sqlPDA.sdf");


SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Table1 where phoneNo='0922123456'";


SqlCeDataAdapter da = new SqlCeDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;

}
finally
{
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}

}
}

there is nothing happen, please help me.

thanks

View 3 Replies View Related

Transact SQL :: Query To Show Totals

Aug 28, 2015

This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008

Create Table SanitationGarbage
(
saleid int
,projectname varchar(200)
,typeofsale varchar(200)

[code]....

Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to.  How does this need to be written so the data is displayed in the proper formatting?

Select
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname
order by projectname asc

[code]....

View 4 Replies View Related

SQL.. Studio Query Will Not Show More Than 500 Charcters

Mar 29, 2007

I have a column that is a varchar(1000). When I select that column the whole column doesn't show in the grid. To see the what's in there I right click the cell and "Copy". I then paste the results into notepad.



My problem is that I'm only getting 500 characters. I have several questions:



1. How do I verify that there are more than 500 characters in the column?

2. How do I select all characters in the column?

3. Is there anyway to see them in the Management Studio tool or do I still need to past them into notepad.

View 3 Replies View Related

Query To Show Life Cycle Revenue?

Jan 2, 2007

I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table  and a sales table joined by custno.
SELECT     Customer.LastName, Sales.InvDate, Sales.AmtChargeFROM         Customer INNER JOIN                      Sales ON Customer.CustNo = Sales.CustNo
 The output I'd like is
CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount
Getting the first inv date seems straight forward
SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo
However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head.
 Can anyone point me in the right direction?
 

View 2 Replies View Related

Query Results To Only Show ONE Result Per Locale

Feb 5, 2015

My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?

Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]

[Code] ....

View 1 Replies View Related

Query To Show Time Monday To Sunday?

Jul 17, 2014

I would like to show employee work hours daily from Monday to Sunday. I have managed to write a query to get the total hours.

This is the query giving total hours from and to date.

(select sum(t.timespent)/60 from timeitems t
where t.employee = e.code
and t.project = p.code
and t.ndate >= '2014-07-15'
AND t.ndate <= '2014-07-15') as Hours
from projemplink pl

View 1 Replies View Related

Query To Show Only Users Who Logged On In Past 6 M

Feb 20, 2006

Hi All

I have built this query :

Select position, SubsidiaryName
from position
Where SubsidiaryName in ('country1','country2')
and position not like('testuser_%')
and position not like ('Inactive_%')
and position not like('olduser_%');

However when I run it, the result more accounts in country 1 than expected. I think it could be inactive users who have not logged into this system for 6 months or more. How can I build a query that incorporates my original query + shows only users who logged into the past 6 months ?

View 2 Replies View Related

Write Query To Show Results In DB View -Advanced-

Sep 10, 2007

I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1           1        Book1  10 2           1        Boo21  12 3           2        Book3  6 4           1        Book4  13 5           1        Book5  2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
 
 

View 2 Replies View Related

Index Distribution Statistics And Show Query Plan

Jan 15, 1999

I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.

*************
select orderID, custId, intertcsi from tblorders
where intertcsi = '2815'

STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Index : indxInterTCSI
orderID custId intertcsi
----------- ----------- ---------
1015245 1011313 2815
2556392 2556392 2815
....


Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0
***************
Then I use the same select statement to force a table scan:

select orderID, custId, intertcsi from tblorders (index=0)
where intertcsi = '2815'

STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Table Scan
orderID custId intertcsi
----------- ----------- ---------
60472 61084 2815
102184 102333 2815
...
Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980

When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run.
Any idea or comment would be very appreciated.

View 4 Replies View Related

Looping Query Results - Show All Duplicate Records

Feb 4, 2015

Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..

SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR

View 5 Replies View Related

T-SQL (SS2K8) :: Query To Show Table Products With Its Modes

Nov 28, 2014

Query that show me a Products Tables and its models, 1 product could have 2 or more models

Example Table Products

id Name
1 Product 1
2 Product 2
3 Product 3

Example Table Models

idmodel idproduct model
1 1 Model 1
2 1 Model 2
3 1 Model 3
4 2 Model 1
5 2 Model 2
6 3 Model 1

And I want to show:

Product Model
Product 1 Model 1
Model 2
Model 3
Product 2 Model 1
Model 2
Product 3 Model 1

How can I query to show that?

View 5 Replies View Related

T-SQL (SS2K8) :: Query To Show Database Objects Last Change

Mar 17, 2015

If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?

View 7 Replies View Related

Query To Show Total By Shop On Time Range

Aug 28, 2014

How to edit this query to show the total by shop on time range ?

current result.(Time range from 9:00am-23:00pm)
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
BBB 11:59 $100 $50 +150 (+)
BBB 12:59 $100 $50 +50 (+)
------------------------------------------------------------------

Desired Result .
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
Total $300 $150 +$200 (+)

BBB 11:59 $10 $50 -40 (-)
BBB 12:59 $10 $50 -40 (-)
Total $20 $100 -80 (-)
-----------------------------------------------------------

select shop
,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span
,sum(case
when datediff(dd,yourdatetimefield,getdate())=0

[Code] .....

View 1 Replies View Related

Show Execution Plan And Misleading Query Costs...

Jul 20, 2005

Hi All,I'm a relative newbie to SQL Server, so please forgive me if this is adaft question...When I set "Show Execution Plan" on in Query Analyzer, and execute a(fairly complex) sproc, I note that a particular query is reported ashaving a query cost of "71% relative to the batch" - however, this isnowhere near the slowest executing query in the batch - other querieswhich take over twice as long are reported as having costs in theorder of a few percent each.Am I misreading the execution plan? Note that I'm looking at thegraphical plan, and am not reading the 'estimated' plan - I'm usingthe one generated from executing the sproc. My expectation was thatthis would be based on the execution times of the queries within thesproc, however, this does not appear to be the case. (Note - Idetermined execution times from PRINT statements, using GETDATE() todetermine the current time, down to milliseconds).Any feedback would be of great assistance... I may well have tochange the way I approach optimizing queries based on these findings.Thanks,LemonSmasher.

View 3 Replies View Related

URGENT:How To Show A Appended String To The Query Results?

Oct 31, 2007

Hello Frds,

This is my query


Select Assessment_Id,Question_Id,Question_short_description,

(isnull(Answer_description,' ') + ' ' + Answer_text) as Answer

from dbo.viewX where assessment_id ='xxxx'


Pleae Note: This query is assigned to a string and passed to the database layer. So, i need the modifications with in this query.
Answer_description and Answer_text are the two different fields,
i need to embed a ":" (colon) between the two data if Answer_text is having any data
else display only answer_description.

Thanks in advance..
Phani.

View 17 Replies View Related

Show Zero Values For Missing Data In Query Resultset

Jan 7, 2008

Hello,

I have the following query which grabs monthly usage data which is logged to a database table from a web page:


SELECT Button = CASE ButtonClicked

WHEN 1 THEN '1st Button'

WHEN 2 THEN '2nd Button'

WHEN 3 THEN '3rd Button'

WHEN 4 THEN '4th Button'

WHEN 5 THEN '5th Button'

WHEN 6 THEN '6th Button'

WHEN 7 THEN '7th Button'

WHEN 8 THEN '8th Button'

WHEN 9 THEN '9th Button'

ELSE 'TOTAL'

END,

COUNT(*) AS [Times Clicked]

FROM WebPageUsageLog (NOLOCK)

WHERE DateClicked BETWEEN @firstOfMonth AND @lastOfMonth

GROUP BY ButtonClicked WITH ROLLUP

ORDER BY ButtonClicked

The results look like this:

TOTAL 303
1st Button 53
2nd Button 177
3rd Button 10
4th Button 4
6th Button 18
7th Button 19
8th Button 21
9th Button 1

If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.

-Dave

View 3 Replies View Related







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