SHOW Query For MSSQL

Sep 18, 2004

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

View 8 Replies


ADVERTISEMENT

SQL Script To Show The Table Definition In MSSQL

Dec 7, 2005

Just like "describe select(*) from table_name" in DB2?

Thanks!

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

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

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

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

Analysis :: Show Dimension Names In MDX Query Result

Oct 28, 2015

I am facing an issue in MDX Query. I have a custom MDX query, When I run the query I am getting results too. Now my requirement is to show the dimension names in the query result, So that I can get those header names in the cell set itself. Please see the below image.

In this image I need to show 'Fiscal Year' and 'Fiscal Quarter' in that highlighted area. Is there any custom query for this?

View 3 Replies View Related

MSSQL Query Help

Dec 9, 2004

Having problem completing this query. I have a list of items. Some items need to be grouped by a list, some by a range. I was thinking of useing two tables, one for the items, and one for the groups. The groups would have something like groupid, title, listnumbers, rangelow, and rangehigh. The tables are in sql database. For example:
The list of items numbers are 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20. In the group table, it would be listed like this:
G1, Group1, 1,,
G1, Group1, 6,,
G2, Group2, ,2,5
G3, Group3,6,18,20
G3, Group3,7,15,17
G3, Group3,8,14,16
G3, Group3,9,11,13

In this example, Group1 is a list, group2 is a range, and group3 is a list of ranges. I can make a query that pulls all the items just in the groups:
SELECT ECC_ITEMS.NBR, Group.Group_Name, Group.Title, ECC_ITEMS.DESCR, ECC_ITEMS.REG_PRC
FROM Group, ECC_ITEMS
WHERE ECC_ITEMS.NBR Between Group.RangeLow And Group.RangeHigh Or ECC_ITEMS.NBR=Group.GroupItems
Now, I am not sure how to put the rest of the items (the ones that aren't in a group) in that query. I was thinking on making a union and the second query being a unmatched query. Not sure how to make it were that query is "unmatched" with a table in the same query. And ideas on how to make the second part of the union query?

View 3 Replies View Related

T-SQL (SS2K8) :: How To Show Multiple Values In Column Of Join Query

Nov 6, 2014

in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').

i tried in my join query

these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')

its taking so much of time how t o solve ?

View 1 Replies View Related

SQL Server 2008 :: Query To Show XML Output For Hierarchical Data?

Mar 10, 2015

selecting table data in hierarchical XML .

Here is the sample table DDL and data

Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int

[code]....

View 8 Replies View Related

SQL Server 2008 :: How To Change Query So Apple Does Not Show Up In List

Apr 2, 2015

I am trying to print Companies with less than 100 employees for all dates.Here's my table structure

Create table CompanyEmployeeArchive(
Company varchar(100) not null, Employees int, Dateinserted date)

Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01')
Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01')
Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01')
Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01')
Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01')
Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')

I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.Select Company, dateinserted, employees from CompanyEmployeeArchive group by company,dateinserted,employees having employees < 100 order by company, dateinserted this query lists Apple too. How can I change the query so Apple does not show up in the list.

View 4 Replies View Related

Query To Show Today And Upcoming Event From Current Month

Aug 27, 2015

Here is my query

SELECT id, (CONVERT(datetime, event_date)) AS event_date, xmlfilename, event_active FROM test WHERE (YEAR(event_date) >= YEAR(GETDATE())) AND (MONTH(event_date) >= MONTH(GETDATE())) AND (DAY(event_date) >= DAY(GETDATE())) ORDER BY event_date ASC

The above query shows me event only upcoming event from the current month.

I want to list all the events happening today and future. I do not want to show past event. How to do this?

View 4 Replies View Related







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