Hi i am trying to write a statement to get the players in a squad based on the StartDate, EndDate of any season and the DateEntered, DateLeft a squad
the possible situations are
Player joins squad after StartDate and is still there (DateLeft Null)
Player joins squad after StartDate and leaves before EndDate
Player joins before StartDate and is still there (DateLeft Null)
Player joins before StartDate and leaves before EndDate
this is my statement at the moment but i am gettin players that are in the squad this season and last season sp if anyone has any ideas i would really appreciate them
select spm.SquadId,Person.Id as ID, IsNull(firstName+ ' ','') + IsNull(MiddleName+ ' ','') + IsNull(LastName,'') as Name, spm.DateEntered, spm.DateLeft
From Person
LEFT JOIN SquadPlayerMapping spm on spm.PlayerId = Person.Id
Where spm.SquadId = 2927
And ((spm.DateLeft IS NULL) Or (spm.DateEntered >= (Select StartDate From Seasons Where ID = 50)))
And spm.DateEntered <= (Select EndDate From Seasons Where ID = 50)
Order by LastName
Hi This is really bugging me. All I want to do is add a date in an update statement but when I do it, its adding the wrong date e.g. here is my update statement trying to update a date field update tbl_x set [date]=01/02/2006 where id = 1 when i do this the data comes back as 1900-01-01 00:00:00.000, am I doing something wrong, what is the correct way of doing this. Thanks.
right now I am able to retrieve the month and year from a field by month(TS_Date), Year(TS_Date), my question is can I put both results as one so output can say Date 2 2008 instead of two columns with month 2 year 2008. any help would be appreciated
I have following SQL statement which I run on an Access database. ( Program written in VB.NET )
'--------- SELECT c.* FROM Communications AS c WHERE CommDate Between #1/4/2005# And #5/4/2005#; '------------
I've put dummy values in the database for dates between 1st and 8th of april... And running this query suprisingly gives back ALL rows in the table... Also the entries for the 6th, 7th and 8th of april... In fact it just gives back the WHOLE table, no matter which values I add...
If I use '1/4/2005' (not with #) then I get an error back.
Anybody an Idea how to make a BETWEEN statement work in an access database?
SELECT SUM(((CASE WHEN o.date>= a.activity_date, other filter condition, other filter condition THEN (select coalesce(d.balance,d2.balance) from drawtable d where coalesce(d.date, d2.date) < a.activity_date order by d.date desc limit 1) - ( select coalesce(d.balance, d2.balance) from drawtable d where coalesce(d.date, d2.date) = interval 'current date' else end ))
from emailtable a LEFT JOIN opportunity o left join drawtable d left join drawtable d2 etc
The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement?
I have a database with a field which contains a load of dates in this format 01/03/1998
What I want to do is select all the records from the database where the month of the date is what ever, this month or the month the customer selected etc.
I suspected it may be something like the following (which doesnt work)
sql = "SELECT * FROM avail_lowermill WHERE CH_Arrival.month = " & showmonth & " ORDER by CH_Arrival"
I'm using the designer to create/modify strongly typed datasets. I have one select statement that I'm having considerable trouble with. The user selects search parameters from a form. These search parameters are used to filter the data sent to a gridview control. Three of these parameters are almost enough to make me abandon the dataset in this case.The first two are minimum and maximum age. I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated. In ASP I'd do something like this:strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")" I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.The third parameter is astrological sign. Again, I calculate it based on the birth date. I don't have a field for it. I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign. I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify? I need to get the age issue fixed. The sign is a nice to have, since I can do it another way if I have to. BTW: I did decide to abandon the dataset in favor of a SqlDataSource control. This allowed me to build my select string in the code behind the way I would have in ASP. This resulted in paging and sorting not working properly. Sorting would be nice, paging is necessary. I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn). Any comments or feedback on this would be avidly read and appreciated.Diane
I have the following table of data. I need to take a date from a large table and do the following case:CASEWhen date < date(0) Then '0'When date between date(0) and date(1) Then '1'When date between date(1) and date(2) Then '2'When date >= date(3) Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.thanksMilton
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have to transfer some data from my SQL Server 7.0 database on NT Pro to a Unix server as a text file. This has to be scheduled as a weekly job. I can use DTS to transfer data into a text file , but is there a way by which i can export this file to a Unix server also ? Any suggestions would be appreciated.
Every day my database 12;00pm we will take fullbackup 1:00 O clock differntial backup 2:00 O clok again on diifferntial backup Every 15(fifteen)Min we will take Transaction log backups My database is crashed on 2:00 clock we will not take 2:00 clock Tlog backup we have taken only 1:45 Tlog backup.
Here my database is loss 1:45 to 2:00 clock data
we cannot take this 15min Tlog backup
Any body plz give suggestion on how to recover this 15min data plz help me it very urgent
Bonjour,CREATE TABLE [dbo].[MAND]([Mat] [varchar](5)[Dur] [varchar](1)) ON [PRIMARY]Mat Dur16030d16030i31217i10000d12000i10000d31217d35000d36000i35000dJe voudrais avoir le resulat suivant (i need this result) :10000 d35000 dCar ils ont tous les deux "d". J'ai beau faire un regroupement (groupby) par Mat avec un having за ne marche pas.Comment faire ?Merci d'avance
Almost have my custom membershipprovider finsihed, but have ran into a small snag.Scenario:: 2 methods. CreateUser(blah blah) and ValidateUserName(string username). CreateUserWizard control calls CreateUser(blah blah) which in the start of the method calls ValidateUserName(string username). ValidateUserName runs a stored procedure on SQL 2k to check and see if user exists in the db. Returns a bool true or false. If true, then everything works correctly and returns a message displaying that the user exists, please enter a different name.Now the problem. If it returns false, meaning that the username doesnt exist and is available to use, it calls a stored procedure to create the record in the users db. This is where the problem lies. It does NOTHING but sit there waiting. But it never seems to time out. If I comment out the validating. It will add a record successfully.Im wondering if it has something to do with calling 2 stored procedures consecutivly????::CODE TO FOLLOW::
I have a couple of jobs set on 1 server. I want these jobs to be transferred, or rather copied to another server. Is there a method by which I can just point these jobs to another server, without having to manually create them again ??
I am having a problem replicating between 2 servers using 1 distributor. I have server X and server Y. Server X is a publisher, distributor, and supposedly a subscriber, while server Y is a subscriber and supposed publisher (using Server X as a remote distributor). My logreader task (off server X since it is the distributor) gives me the error message "Unable to connect to server Y". I was pretty sure it is possible to publish to the same server that is your distributor am I wrong? If I am not wrong and this scenario is possible any ideas on why it isn`t working and how to get it working?
Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o( what maybe the problem?
I have a table w/ 17K line items. There are only 8.5k I want because each one is duplicated exactly twice. How do I get one of each of these line Items into a new Table?
I tried this:
Select DISTINCT * from DuplicateTable INTO NewTable
And it doesnt work. I am still getting the 17K transported over... any solutions?
I wrote next ActiveX script. But it doesn't work. Can somebody tell me what i'm doing wrong. It should retrieve the mail and Assoc-NT-Account, but so far i only get errors?
Another question: should I explicitly write this to a table? If yes, how?
strServerName = "**********" set oConn = CreateObject("ADODB.Connection") set oCommand = CreateObject("ADODB.Command") set oRS = CreateObject("ADODB.RecordSet")
oConn.Provider("ADsDSOObject") on error resume next oConn.Properties("User ID") = "*********" oConn.Properties("User Name") = "*******" oConn.Properties("Password")="*********" oConn.Properties("Encrypt Password")= True oConn.Open "Ads Provider"
set oCommand.ActiveConnectection = oConn
strQuery =" Select Assoc-NT-Account, mail from LDAP://servername/o=orgName/ou=OrgUnit where objectClass = 'person' order by cn"
i = 0 While not oRS.eof vObjectClass=oRS.Fields("objectClass") bShow = oRS.Fields("mail") > " " if bShow then oRS.Fields("mail") oRS.Fields("Assoc-NT-Account") End If oRS.MoveNext i=i+1 wend End Function
I have a stored proc that creates a view so I can pass parameters. I need to replace the 120 with the variable @MEA. I can't get my quotes right to make this work. ALTER PROCEDURE dbo.spi_CallList (@strAgent nchar(4), @MEA int)
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1') DROP VIEW qs_CallList1
SET @SQL = 'Create view qs_CallList1 as SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim) AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat)) LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''') AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null) AND ((tsd_Claim.clfromdos)<GetDate()-120)) GROUP BY tsd_Claim.clinsnum ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC' EXECUTE(@sql)
Himy prob is like this..-----------------------------create table ax(i int ,j int)create table ay(i int ,j int)insert into ax values(1,100)insert into ax values(1,101)insert into ax values(2,103)insert into ay values(1,200)insert into ay values(1,201)insert into ay values(1,202)insert into ay values(2,203)insert into ay values(2,204)insert into ay values(2,205)select * from axselect * from ay--------------------I want to delete2 records(count of ax.i = 1) from ay.i = 1 and1 record(count of ax.i = 2) from ay.i = 2expected result :select * from ayi , j-----1 , any data2 , any data2 , any datanote: j is the temporary column in both table.thanksdishan
My question is i had a report with drop down list which contains nearly 200 items.when i select all items it's shows the error. I am able to get the result up to 19 items.
even i tried to filter at report leval using IN operator it is not working.can any one give some suggation.
TITLE: Microsoft SQL Server 2005 Setup ------------------------------
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff
1)When i export the report the data i am getting in text format.how could i get tha data in number format by default. 2)my report has a total of 15 columns, so when user scrolls ,the first column allways visible.
