ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E
Result
ID PID Date
4 1 26/02/07
7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date.
I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.
where, statString is a string variable containing Hebrew characters.
Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database. The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.
Code Snippet String sql = "select String_Id from Static_String1 where String like ('" + statString +"')"; Statement statement=connection.createStatement(); ResultSet rs=statement.executeQuery(sql); rs.next(); int stringId=rs.getInt("String_Id");
I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below
Code Snippetselect String_Id from Static_String1 where String like( ' ×”×–×—' );
But even this is returning null rows, even though the entry is present in the table Please help me out asap.
Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email FROM #temp_pregovb, [Survey Return] INNER JOIN SelectedInvited ON [Survey Return].SelectedID = SelectedInvited.SelectedID --*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact] ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID WHERE [panelist contact].email=#temp_pregovb.email AND SelectedInvited.CellRef IN ( SELECT surveycell FROm [Survey Cells] WHERe SurveyRef='5')
- Since the parameters are optional user can enter either one or can leave both blank. - If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL - If user enters SD, query should use @StartDate as the SD and GetDate() as the ED - If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs The query is as below
AS IF ( @StartDate IS NULL) Select @StartDate = MIN(DateInputted) from Document
Select FName as 'First Name', LName as 'Last Name', ID as 'Student ID', Orphan as 'Orphan', DocumentType as 'Document Type', DocDesc as 'Description of the Document', DateInputted as 'Date Entered', InputtedBy as 'Entered by'
From Document,DocumentTypeCodes Where FName LIKE ISNULL(@FName,'%') AND LName LIKE ISNULL(@LName,'%' + NULL) AND ID LIKE ISNULL(@ID,'%' + NULL) AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL) AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated Thanks in advance :)
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
Each record represent information for each ProductID, per PriceZone, per Date. There's always one record per product; per zone; per Date. Here is the sample of the records (Just for example I am just using one product in one zone)
Here; Date is the maximum date available in the entire [History] table. Meaning the "Date" value will be same for all the records in the output. Price is the Price for that product in that zone on Date Cost is the cost for that product in that zone on Date
About "Last4Dates" & "Last12Dates": "Last4Dates" is the last 4 dates in the entire table from 'Date" value , including "Date" value and prior 3 dats before the "Date" value. In the above data set example it would be from "2008-04-25" to "2008-04-30" "Last12Dates" is the last 12 dates in the entire table from 'Date" value, including "Date" value and prior 11 Dates before the "Date" value. In the above data set example it would be from "2008-04-09" to "2008-04-30"
UnitsSum_ForLast4Dates and UnitsSum_ForLast12Dates is the sum of Units for 4 and 12 weeks respectively; per product, per zone
DollarSalesSum_ForLast4Dates and DollarSalesSum_ForLast12Dates is the sum of DollarSales for 4 and 12 weeks respectively; per product, per zone
AvgMargin_ForLast4Dates and AvgMargin_ForLast12Dates is the Average of DollarSales for 4 and 12 weeks respectively; per product, per zone
ProfitSum_ForLast4Dates and ProfitSum_ForLast12Dates is the sum of Profit for 4 and 12 weeks respectively; per product, per zone
Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:
create table [User_Tbl]( [ID] int IDENTITY(1,1) PRIMARY KEY, [Name] varchar(100), [Password] varchar(100), ) go
create table [Printer_Tbl( [ID] int IDENTITY(1,1) PRIMARY KEY, [Name] varchar(100), [PaperCost] int ) go
I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @MinDate - @MaxDate)
So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.
One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!
I am quite surprise by getting wrong resultset from a simple query like:
select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'
In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null? simply because there is no such date as 2008 yet in the DB. Help from anyone is needed. Thanks in advance.
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
I have a table with 4 relevant fields (blank lines added for clarity). State, City, Name, Primary_Contact IL, Springfield, Bill, n IL, Springfield, Frank, n IL, Springfield, Larry, n
IL, Bloomington, Steve, n IL, Bloomington, Chris, y
IL, Chicago, Betty, n IL, Chicago, Linda, n IL, Chicago, Sue, n
I need a query to return the state and cities that don't have a Primary_Contact='y' So the results would be: IL, Springfield IL, Chicago
I need the results from the following query to be with the results of the second query. Any ideas?
Code:
SELECT PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME, CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4, CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4, Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud
FROM PR LEFT OUTER JOIN Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 CROSS JOIN CFGMain Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'
and
Code:
SELECT * FROM LD WHERE (BilledPeriod = '200408') AND (WBS1 = '001-298')
document_area: doc_area_id(int) and doc_area_name(string). document_area_access: doc_area_id(int) and username(string).
I am trying to do a select statement in an sqldatasource in .net that will select all the document_area.doc_area_name's where the current users username is in the document_area_access using the doc_area_id to link the tables.
I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.
Basically i need to change all fac_id which = 1 and change them to fac_id = 2, this is simple enough:
UPDATE SIC SET SIC_id = 2 WHERE SIC_id = 1
How ever, i need an exception where the 'id' is the same so i don't get duplicate entries.
So from the table above i need to change 2347 fac_id = 1 to fac_id = 2. I would NOT how ever change 1234 fac_id = 1 to 1234 fac_id =2 because there is already an id number with the same fac_id.
SELECT LB.WBS2, LB.WBS3, LedgerAR.WBS2 AS Expr1, LedgerAR.WBS3 AS Expr2, LB.AmtBud AS amtbud, SUM(LedgerAR.Amount * - 1) AS amt FROM LB LEFT OUTER JOIN LedgerAR ON LedgerAR.WBS1 = LB.WBS1 AND LedgerAR.WBS2 = LB.WBS2 AND LedgerAR.WBS3 = LB.WBS3 WHERE (LB.WBS1 = '001-298') GROUP BY LB.WBS2, LB.WBS3, LedgerAR.WBS2, LedgerAR.WBS3, LB.AmtBud
I want to sum the amtbud column like I did for the amt column. and group everything based on WBS2. However, I keep getting an outrageous amount for the amtbud. This is what is seems to be summing up:
SELECT vehicleref, capID, make, model, derivative, COUNT(vehicleref) total, SUM(case when inStock=1 then 1 else 0 end) AS stock, (SELECT dealer FROM tblMatrixDealers WHERE id=dealerid) As Dealer FROM tblMatrixStock WHERE inStock = 1 GROUP BY vehicleref, capID, make, model, derivative, dealerid
I need to get the number in stock, i.e. when instock=1 and the total i.e. when instock=1 or 0
But the problem is i'm only showing records where instock=1 so my SUM(case when inStock=1 then 1 else 0 end) AS stock statement is useless.
I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...
I have a sqlDataSource control that has its select command and parameters set dynamically. One of the parameters is a date, which should be in smalldatetime format (ie, 12/22/2006). Obviously, using SQL Server, the data is stored with both date and time. The problem is, I cannot get any data selected. I initially thought that I needed to tell it to only look at the date and not the time, but from what I have seen this should work. Here is the pertinent code. dim seldate as datetime selDate = Session("ChosenDate") Dim SelCmd As String = "SELECT [PatientName], [AssignedTo], [CPT], [CPT2], [HospitalID], [RoomNbr], [ACType], [TxDx1], [TxDx2], [MRNbr], [Notes], [PatientID], [PCPID], [ResidentID], [Status], [CaseID], [AdmitDate], [crtd_datetime] FROM [DailyBilling] WHERE ([crtd_datetime] = @crtd_datetime) and ([AssignedTo] = @AssignedTo) and ([HospitalID] = @HospitalID) ORDER BY [PatientName]" SqlDataSource1.SelectCommand = SelCmd SqlDataSource1.SelectParameters.Clear()
Dim parFilterProvider As New ControlParameter() parFilterProvider.ControlID = "ddlProvider" parFilterProvider.Name = "AssignedTo" parFilterProvider.Type = TypeCode.String SqlDataSource1.SelectParameters.Add(parFilterProvider)
Dim parFilterHospital As New ControlParameter() parFilterHospital.ControlID = "ddlHospitals" parFilterHospital.Name = "HospitalID" parFilterHospital.Type = TypeCode.String SqlDataSource1.SelectParameters.Add(parFilterHospital)
Dim parFilterStatus As New Parameter() parFilterStatus.Name = "crtd_datetime" parFilterStatus.DefaultValue = selDate.ToShortDateString parFilterStatus.Type = TypeCode.DateTime SqlDataSource1.SelectParameters.Add(parFilterStatus)
I am trying to select all records added between 2 dates that the user inputs into a form and am having problems. I had this working no problems with asp but can't seem to get it working with .net. BTW I am using SQL Server and Visual Studio.
The asp.net code I am trying to use is:
Me.SqlSelectCommand1.CommandText = "SELECT news_title, news_date, news_type, news_link FROM news WHERE (news_type = 'news') AND (news_date BETWEEN CONVERT(DATETIME, '"" & startdate & ""', 102) AND CONVERT(DATETIME, '"" & enddate & ""', 102))"
....
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim startdate As DateTime startdate = Request.Form("date_from")
Dim enddate As DateTime enddate = Request.Form("date_to")
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 a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates?
Hi all,Right now I have this SELECT statement:SELECT MUSIC_PK, MUSIC_TITLE, MUSIC_TITLE2, MUSIC_ORIGINAL_SINGER Now I want to add a second SELECT statement (same as above, but with an additional column) if the record was added within the 10 days (while still select other records, I just need to select an additional column). How would I do that? The additional column that I want to select is called MUSIC_NEW (so I can display if the record is new within 10 days.) I have a column in my table named DATE_ADDED, how do I go to calculate if it's within the 10 days since the day it was added to the current time?Thank you very much,Kenny.
