Hai friends i have table
s_team
id name game
1 raja cricket
2 ravi football
3 muthu cricket
4 pravin cricket
5 hurry football
I have to diplay the above table in the following ...pl give me sql query
game totalplayers
cricket 3
football 2
Hai friends
i have table student with 2 fields s_id,s_name
s_id s_name1 raja2 ramu3 vinoth4 muthu5 arun
i have another table test with s_id who wrote test...
s_id 25
now i have to display like below....give me sql query
s_id s_name2 ramu5 arun
Thanks in Advance...pl Help me......
Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks
These are my table structures . Could you please suggest me analternative:tblArticles: ArticleID,Title,Summary,ContextSubTypeID,PostDatetblArticlePages: ArticlePageID,ArticleID,PageNum,ContenttblAuthors:AuthorID, NametblArticleAuthors:ArticleID,AuthorIDtblContextSubtype:ContextSubTypeID,NameI need to get Author,PostDate,Title,tblContextSubType.Name,PageN um,Content,- these have to grouped accordingly by the respective fields. Contentneed not be grouped by.
View 2 Replies View RelatedI am running some query with the following where clause
where DateCreated BETWEEN '06/01/2004' AND '06/30/2004'
It gives the records where DateCreated is of 2004-07-01 21:48:02.377
the default language on the server is British English.
Could anybody please tell me the reason why records from july are also coming
I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.
"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"
ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.
Thanks,
Malar
I am not able to use WHERE Clause in my query. What am I doing wrong?
Here my query that will generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT WHERE trn_dt > '2007-03-08' '
I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.
Here is my query which doesn't generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT'
Using SQL Server 2000
DSN to a CACHE database on local network
Thanks in advance,
Sam
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors.
Bag counts are logged every 15 minutes. There is a count for each conveyor.
Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)
Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date
End Date
Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)
My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies View RelatedWe created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
View 9 Replies View RelatedSELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
I have tried and tried to get this to work and I'm doing something dumb.I'm going to include sample data and my format file. Can someone helpme figure out my dumb mistake on a bulk insert using a format file?Don't worry about data types, I'll handle that later. Thanks a lot!!!CSV File:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPETITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContract,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch,forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,NetworkComparedTo1,name1,Family Practice General Practice,FP,PCP,address1,DeerfieldBeach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4,Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp12,name2,Family Practice GeneralPractice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,1000067851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp23,name3,General Practice,GP,PCP,address3,DeerfieldBeach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp34,name4,ReproductiveEndocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,1000083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4Format File:8.0311 SQLCHAR 0 1 "
" 0 quote Latin1_General_CI_AS2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_ASHere's a script for the table!if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[Provider_Results]GOCREATE TABLE [dbo].[Provider_Results] ([Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[NetworkComparedTo] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[forceMatchUserId] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[paymentPlatforms] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[matchStatusColor] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks again. I am dying on this one. With everything includedhopefully someone (smarter than me) can figure it out.Thanks,Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
Jag har installerat sql server 2008 (I have installed sql server)
I cannot find enterprise manager - I think that the sql server is useless unless there is a manager.
Been trying to get this one but need a little input.
SELECT a.Id,g.LabName......
FROM someTbl a,table g
WHERE a.id = @idAND (b.calLab = g.ID OR g.LabName = ???)
With the above, if b.calLab = 0 then there is no entry in table g.(I didn't develop the table or would have included NONE)
So, how to tell g.LabName to come back as 'None' if b.calLab = 0?
Thanks,
Zath
Hello I have a problem with a query..
What I want:
I have a query that selects from different tables:
Code:
SELECT * FROM table1 as t1 ,table2 as t2 WHERE t1.t2_Id = t2.Id
But now I want all results from table1 even if it's not linked with table2, if that's the case i want to add something like:
Code:
CASE
WHEN 'notlinked' THEN 1
ELSE 0
END HighLight
that way I can show the rows that have to be manually linked
hope you understand my problem...
thanks!
Hi Everybody,
In a table there´s a field for store real values,
so...in the database, using the SQL Server Enterprise Manager
and execute query as 'select * from' and it return the value
'15,35' just like stored before, why using Delphi via an ADO conection
in my result set there is a '15,3500003814697' value if in the record time
the inputed value was '15,35'?
What I have doing wrong?
hi,everyone
i use sybase before,and i want to learn ms sqlsr2000,can anybodu give me some advice,or give me some matrial
thx in advance
batch processing means
View 3 Replies View Relatedhi,
i want to get day if we give date.ie if i give 03-01-2006 it has to give day as wednesday.so what ever date if i give it has to give the day of the date.can any one please give me query for this
Hi
I am new to Sql Server
I have to select all fields from a table based on min(date) . i have duplicate accountnumbers in the query i need to eliminate that based on min(dateTime).
when i am writing a query
Select a,b,c,min(d) from table , it is not working?
Below is the example
A B C D
222 1 11 2007-02-12 14:09:16.000
222 1 8 2007-11-06 13:57:18.480
Any suggestions would be very helpful
I have started working with MS SQL Server 2000 recently. I have a scenario in which I require to know within the given period of time ( say 5 mins) , which all tables from a particular database got modified. I do not want to write a trigger for each and every table for all the 13 databases , my application deals with. I have even tried the following query:
declare @curdate datetime
select @curdate=getdate()
select name, refdate from sysobjects
where xtype = 'U' and refdate =@curdate
But nope it does not help me, since refdate is something else. Can anybody tell me how can you figure out from sysobjects when was a particular object last accessed , even this would serve my purpose.
I have trying to get this done via profiler. My applications api's connect to the database under some credentials which i do not know since I do not have access to source code ( i am doing black box testing). So I can't even put a trace on one particular user account. What I am doing currently is trapping all store procedure events..., but then its too much of work...
Hence I wanted to know , is there any way out for the situation where given a database name and a time span we can find out the tables modified/accessed within that time span from that database ???
CAn you please give me a copy of this script...
THANK YOU...
How can i give a table wise exclusive lock in MSSQL Server ?
I got the description but, How can I apply this ?
The sql :
LOCK TABLE <tablename> IN EXCLUSIVE MODE
is not working.
Is there any query/method to do this ?
Please help ...
thanks
About Exclusive locks
--------------------
Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.
Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.
--------------------
Can someonte tell me that if i
bcp bda..mytable out c:discounts.xls -c -p , how can I put the first row as my column names since I get only data
(Please Rush)
Hi all,
I am using SQL SERVER 2000 now, and my SERVER is still have about 27 GB but in my database is saying only 3.4 MB left, I was trying to shrink my database but it is still did not give me more space.
Do you know any way could help me to extend more spaces for my database?
I also included the picture so could help you more
Thank you very much
vdang (vdang2003@sbcglobal.net)
SELECT post.C_ID, post.Comment, post.Title, post.Date, authors.U_Name
FROM authors INNER JOIN
post ON authors.A_ID = post.A_ID
ORDER BY C_ID DESC
That's the query I'm using, and I was hoping there is a way to use the WHERE clause to have only the 5 highest #'s from my primary key, which is A_ID IDENTITY(1,1).
What would be the best way for me to do this? I also have date as a datetime, but then I could be getting more or less than 5 results in my query.
Thanks
I am using iif which works just fine in Access to prevent divide by zero errors but this:
=iif(Fields!oh_ret.Value<>0,(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value,"")
Fails
As Does:
=iif(Fields!oh_ret.Value=0,"",(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value)
Why? How do I get around this?
Hi
Im trying to load a report using
Reportexecutionservice
like this
Reportexecutionservice.ExecutionInfo exinfo=res.LoadReport(reportname, null);
rptname consists of report which is present @ my server.How to give reportname inorder to load a report .im using reportingservice2005
plz do help me.
error im getting Unable to indentify the report.Plz tell what is path i should given in order to acces th report..
Table:Questionforum_id quest_id question description quest_raised_by raised_date alert_me approval no_of_hits16 1 What is diff between asp and asp.net? <Binary data> JOHNYJP 2/27/2008 5:32:38 PM True True NULL4 1 test question? <Binary data> SAMUEL 2/25/2008 12:25:05 PM False True 317 1 diff between asp.net1.0 and 2.0? <Binary data> MICHEL 2/27/2008 6:07:56 PM False True 315 2 What is diff vb6&vb.net? <Binary data> MICHEL 2/27/2008 6:32:52 PM True True 514 1 what are features available? <Binary data> RAGAVAN 2/26/2008 12:11:06 PM True True 915 1 VB6 tutorial.. <Binary data> RAGAVAN 2/26/2008 12:12:29 PM True True NULL14 2 Tell about access? <Binary data> RAGAVAN 2/26/2008 12:14:09 PM False True 514 3 how to create grath in excel? <Binary data> SAMUEL 2/26/2008 12:23:31 PM True True 714 4 test1 <Binary data> MICHEL 2/27/2008 6:34:08 PM True True 24 2 new quest? <Binary data> SAMUEL 2/27/2008 6:38:20 PM True True 18 1 test quest for html? <Binary data> GANESH 2/27/2008 6:41:07 PM True True NULL4 3 test html tag2 <Binary data> GANESH 2/27/2008 6:44:24 PM False True NULL8 2 hello its for test? <Binary data> MICHEL 2/27/2008 6:46:13 PM False True NULL21 1 What is the new feature ? <Binary data> SAMUEL 2/28/2008 9:30:43 AM False True NULL22 1 What is the new feature ? <Binary data> SAMUEL 2/28/2008 9:32:14 AM True True NULL23 1 Is orcas released? <Binary data> SAMUEL 2/28/2008 9:33:18 AM True True NULL16 2 Test page for html code!!!!!!!.... <Binary data> SAMUEL 2/28/2008 9:36:50 AM True True 417 2 explain with details? <Binary data> SAMUEL 2/28/2008 4:46:08 PM True True NULLTable:Replyforum_id quest_id ans_id answer reply_by reply_on alert_me14 3 12 <Binary data> MICHEL 2/28/2008 3:57:46 PM True4 1 8 <Binary data> GANESH 2/27/2008 4:46:52 PM NULL15 2 13 <Binary data> SAMUEL 2/28/2008 4:20:36 PM False4 2 14 <Binary data> SAMUEL 2/28/2008 4:46:39 PM False14 3 15 <Binary data> GANESH 2/28/2008 4:49:12 PM True14 2 16 <Binary data> GANESH 2/28/2008 4:50:01 PM False16 2 17 <Binary data> GANESH 2/28/2008 4:51:02 PM True17 1 9 <Binary data> MICHEL 2/27/2008 6:08:32 PM NULL17 1 10 <Binary data> SAMUEL 2/27/2008 6:25:40 PM NULL16 2 11 <Binary data> SAMUEL 2/28/2008 9:40:41 AM NULLThis is my table structure.when i pass value only to 'quest_raised_by' field in question table.but I want following result,suppose i pass quest_raised_by='SAMUEL'forum_id quest_id quest_raised_by ans_id no_of_reply answer reply_by reply_on alert_me 4 1 SAMUEL 8 1 <Binary data> GANESH 2/27/2008 4:46:52 PM NULL14 3 SAMUEL 15 2 <Binary data> GANESH 2/28/2008 4:49:12 PM True4 2 SAMUEL 14 1 <Binary data> SAMUEL 2/28/2008 4:46:39 PM False21 1 SAMUEL - 0 - - - -22 1 SAMUEL - 0 - - - -23 1 SAMUEL - 0 - - - -16 2 SAMUEL 17 2 <Binary data> GANESH 2/28/2008 4:51:02 PM True17 2 SAMUEL - 0 - - - -Any one give storedprocedure as soon as posible.Thanks,Durai
View 4 Replies View Relatedusername work_date no_of_working_hours(nvarchar)a 02/17/2008 0:50 a 02/18/2008 0:50 a 02/23/2008 0:20b 02/18/2008 0:30 b 02/23/2008 0:30 b 02/24/2008 0:30 suppose i pass work_date from 02/18/2008 to 02/23/2008.i want following result username total_no_of_working_hours(nvarchar)a 1:10b 1:00 How to write stored procedure?Thanks,Durai
View 7 Replies View RelatedI am trying to select records based upon last name
WHERE (Last_Name BETWEEN 'A%' AND 'C%')
When I run this I get only last names starting with A and B--no C. ?!?
This is confusing to me....I ran it with lastname >= 'A%' and lastname <='C%'and it returned only names starting with B. Why does SQL ignore the "="
I hope this isnt obvious :/
Can you give me a free version of "Log Explorer"
View 2 Replies View Related