SQL Query - Working With Date
Jul 2, 2006
Hi;
I'm here for many hours trying to do this but i couldn't find a way.
I have a table whith a field called [DOB], where i have people's date of birth.
Now, i need a SQL query to get people who's birthdays are in between two dates "BUT", what about the year on the date?
I use to do this on Access:
SELECT * FROM Members WHERE DATESERIAL(YEAR(NOW()), MONTH(DOB), DAY(DOB)) BETWEEN @startDate AND @endDate
In the query above the year is not a problem because the DateSerial() function add the current year for all birthdates making it easyer to user parameters like: 06/01/2006 to 06/30/2006
Unfortunately, SQL Server does not support DateSerial() function.
I appreciate any help on this.
Thanks a lot.
View 5 Replies
ADVERTISEMENT
Apr 16, 2006
Hi,
The following INSERT query works in all aspects apart from the date value:
String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);
The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900").
In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT.
What am I doing wrong? Thanks very much.
Regards
Gary
View 3 Replies
View Related
Dec 11, 2007
I'm a SQL newbie, and I'm trying to write a report that returns records based on a beginning and end date that the user supplies. But when I run the query and supply the dates (begin 11/29/2007 / end 11/30/2007, for example), I'm only getting back one record, when there should be at least 3. It appears that my query is ignoring the =, and only returning those records that have a date > or <, not <= or >=.
I was told that it's possible that the time needs to be converted to UTC as well, but I'm not sure how to do that ( I tried, but I really don't know what I'm doing so I commented it out)... Can someone offer some guidance on how to get all the records to show up? I've pasted my query below, thanks in advance!
SELECT FilteredContact.fullname, FilteredContact.new_memberleadsourcename, FilteredContact.new_prospectforclubname, FilteredContact.owneridname,
FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime
FROM FilteredContact JOIN filteredOpportunity on
FilteredOpportunity.contactid = Filteredcontact.contactid
where filteredopportunity.new_opportunitytype = 1 and (FilteredContact.createdon >= '11/29/2007') and (FilteredContact.createdon <= '11/30/2007')
View 3 Replies
View Related
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Apr 17, 2007
Hello Everyone and thanks fo ryour help in advance. I am working on a blog site that stores each blog entry in a SQL Server table. Each entry is date stamped with the time it is created in a datetime field called "TimeCreated". I want to be able to create some type of ability to sort the older blogs as an archive and display links on a pages such as "Archive - January 2007, Archive - December 2006" etc. I'm not exactly sure on how to go about doing this. any help on this topic would be greatly appreciated.
View 8 Replies
View Related
Jul 20, 2004
Hi,
I have a problem with a SQL that check if a date column IS NULL.
On one server the check work ok but on another (the same data is on both - restored copy) the check does not find any NULL values.
If check where datecolumn =convert(datetime,'9999-12-31 23:59:59.000',121) I get the same result as when checking for NULL in the other.
Is there any parameter set somewhere that tell the server to return a value even if NULL is stored in the database?
thank you for reading,
YakoBay
View 3 Replies
View Related
May 23, 2008
Hey Guys, I can’t get this date conversion stuff to work. I have a variable that holds the current date -1 day (@Yesterday). The date that is on the table (DTE) is in the format yyyymmdd (i.e. 20021213), but I cannot get the date to convert.
I have tried:
BEGIN
DECLARE @Yesterday as nvarchar (8);
set @Yesterday = (replace(CONVERT(VarChar(10), DATEADD(d, -1, getdate()), 101), '/', ''))
PRINT @Yesterday
select * from tblxxx
where Replace(Convert(varchar (10), DTE, 101), '/', '') = @Yesterday
END
@Yesterday = 05222008
DTE = 20080522 (after conversion)
Why can’t I get DTE to convert to 05222008
View 6 Replies
View Related
Jan 29, 2015
I need to calculate a date.example it needs to be 20 working days ago compared to today so that means it needs to not include any Saturday or Sunday in between
declare @start_date datetime
declare @end_date datetime
declare @working_days int
set @working_days = 20
[code]...
So I need to calculate @start_date but it needs to exclude any weekend days.@working_days is the number of working day I'm interested in.
View 1 Replies
View Related
Jul 23, 2005
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, but I'm having trouble putting those rules into efficient SQL.The table that I'm dealing with has just under 9M rows and I may needto use similar logic on an even larger table, so I'd like somethingthat can be made efficient to some degree using indexes if necessary.Here is some sample (simplified) code:CREATE TABLE Coverage_Rates (rate_id INT IDENTITY NOT NULL,coverage_id INT NOT NULL,start_date SMALLDATETIME NOT NULL,end_date SMALLDATETIME NOT NULL,rate MONEY NOT NULL )GOINSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',40.00)INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',30.00)INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',90.00)INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',30.00)GOThe rule is basically this... for any given period of time, for aparticular coverage, always use the coverage with the highest rate. So,given the rows above, I would want the results to be:coverage_id start_dt end_dt rate----------- ---------- ---------- --------1 2004-01-01 2004-06-01 40.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies
View Related
Oct 23, 2007
Hello,
My SQL Server 2005 database table data definition is as follows:
ID int
LST_DATE DateTime
SUBJECT varchar(100)
An example of a record in the database table has a date format like this: 23/10/2007 16:56:00. My VB code as below:
Dim Command As New SqlClient.SqlCommand("SELECT ID,LST_DATE,SUBJECT FROM dbo.LISTS WHERE LST_TIME = GETDATE(), 100)))", conn)
Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
While dataReader.Read()
Dim NWAlert As New Alert(dataReader("SUBJECT").ToString(),DateTime.Parse(dataReader("LST_DATE").ToString())))
:
LST_TIME should be equals the CURRENT DATE & TIME in the format above to activate.
I have tried using the following:
WHERE LST_DATE = CONVERT(GETDATE(), 103) + ' ' + CONVERT(GETDATE(), 108)", conn)
WHERE LST_DATE = CONVERT(GETDATE(), 107) + ' ' + CONVERT(GETDATE(), 108)", conn)
But for some reason it is not working. Please help!
View 22 Replies
View Related
Oct 3, 2006
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2
oct 3
sep 13
sep 21
sep 22
sep 30
aug 3
aug 5
aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
View 7 Replies
View Related
May 20, 2008
I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.
So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.
Table two has personID, Service Date, and lots of other fields.
So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?
View 6 Replies
View Related
Dec 5, 2007
Hi,
We are using [DATE] [TIME] functions in SQL Server 2000 agent jobs and SQL Server use to translate it to current data and time functions but in
SS2005 it is not replacing the functions and we are getting filename as "test_DATE_TIME" whereas we expect "test_20071204_130000"
Do we have any new functions as replacement?
Thanks
--rubs
Following is the code we are using:
declare @name nvarchar(100)
declare @name1 nvarchar(100)
set @name1 = 'test_[DATE]_[TIME]'
set @name = 'c:ackup' + @name1 + '.bak'
backup database test to disk = @name
View 8 Replies
View Related
Jun 2, 2007
Hi all,
I'm looking to return certain rows from our db into an SSRS based on a user selected date range using the parameters calendar.
My query/analyzer returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select date ranges .So far the reports pull in all rows from my query.
Thanks,
Scott
View 1 Replies
View Related
Mar 4, 2004
Hi everyone.
I know, I know, it should have been a datetime from the start...but here's the problem.
I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:
ORDER BY [Date]
Are there any work arounds for this? Is there some way of doing:
ORDER BY covert(datetime, [Date], 103) or something?
Cheers
Andrew
View 3 Replies
View Related
Apr 30, 2014
I have a table with 5000 rows history about working and retired time.
It is the several rows from table(User, StartDate, EndDate):
User1 2011-05-09 00:00 2014-01-17 00:00
User2 2012-07-01 00:00 2012-08-20 00:00
User2 2013-08-26 00:00 2013-09-02 00:00
User2 2013-10-07 00:00 NULL
User3 2013-09-01 00:00 2014-04-07 00:00
User3 2014-04-08 00:00 NULL
How many users have worked and have retired by years and months,
Example:
2011-01 working 2000
2011-02 retired -50
2011-02 working 1950
2011-02 retired -27
and etc,,
Does it need to join to a time dimension table?
View 7 Replies
View Related
Sep 4, 2014
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
View 1 Replies
View Related
Aug 22, 2007
Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?
Thanks...
View 2 Replies
View Related
May 6, 2008
Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)
I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse
declare @WorkingDate as datetime
SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)
-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)
Hope someone can help
Thanks
View 3 Replies
View Related
Oct 22, 2007
Hi, I've got this ssrs 2005 report that works great passing a few
security related parameters from asp.net codebehind. However, there
are two date related parameters that won't be coming from my web form,
but rather from the report form itself. When I test the report's date
parameters from visual studio it work fine, but when I attempt the
same report from a reportviewer no matter what input I place on the
report's date fields or even if I select the date picker, the report
simply resets to default and reloads. And actually the date picker
from the reportviewer does not not even pop up.
Here's my aspx code:
<%@ Page Language="VB" AutoEventWireup="false"
Inherits="_ReportViewer" MasterPageFile="~/Main/MasterPage.master"
CodeFile="~/Reports/CashSales.aspx.vb" Title="Cash Sales" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms,
Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Content1"
runat="Server">
<rsweb:ReportViewer BackColor="Transparent" ZoomMode="PageWidth"
Width="100%" ProcessingMode="Remote" ID="ReportViewer1"
runat="server">
<ServerReport ReportPath="/Retailer/CashSales"
ReportServerUrl="http://myserver/reportserver" />
</rsweb:ReportViewer>
</asp:Content>
Note: I'm only passing the parameters that are not coming from the
reportviewer form.
the code behind:
Imports system.web.security.membership
Imports system.web.security.Roles
Imports Microsoft.Reporting.Webforms
Partial Class _ReportViewer
Inherits System.Web.UI.Page
Private Users As New Retailer.Core()
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
Dim Roles() As String =
GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser",
"mypassword", "mydomain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user",
Page.User.Identity.Name.ToString)
Dim param2 As New ReportParameter("r_role", Roles(0))
Dim p() As ReportParameter = {param, param2}
ReportViewer1.ServerReport.SetParameters(p)
ReportViewer1.ServerReport.Refresh()
End Sub
End Class
on my reportviewer form, at the top I have parameters for startdate
and end date, they are not set to internal or hidden.
If I select the calendar icon, i get a javascript error
Line: 606
Object Required
When I attempt to debug I get a Just-In-time failed : Unspecified
error. Check the documentation index for 'Just-in-time debugging,
errors' for more information. So pretty much I can't see the error or javascript in question.
If i enter anything in the date fields, it disregards them setting them to the defaults.
Again, if i run the report from my vs.net client (not using reportviewer), i can select the calendar and enter dates and it respects them.
Any chance I need to patch sql server or reporting services? maybe ie. I'm on IE 7.0.5730.11
Could my problem be that selecting items on the report itself fails to send my credential information from my codebehind?
Thanks for any help or information!
View 26 Replies
View Related
Aug 24, 2015
SSRS Date Picker is not working in Google Chrome, it works in both IE and Firefox.
View 3 Replies
View Related
May 6, 2014
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
View 0 Replies
View Related
Sep 29, 2015
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
View 3 Replies
View Related
Jul 23, 2005
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
View 2 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Jul 15, 2006
Hello All, I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. The searchString, Type and the Location (where the user who is searching belongs to). When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.For example: Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c. So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters. Any help would be greatfull. Query:
SELECT Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @SR + '%' OR [Client.SSN] LIKE '%' + @SR + '%' OR [Client.LastName] LIKE '%' + @SR + '%' OR [Client.FirstName] LIKE '%' + @SR + '%' OR [Client.MiddleName] LIKE '%' + @SR + '%' )
View 5 Replies
View Related
Jul 31, 2006
I've gotten spoiled with all these query builders. Now in SQL server management studio express its gone. I don't understand whats wrong with their query....any help would be appreciated.
SELECT [Products].myID, [ProductDetails].ShortName
FROM [Products]
INNER JOIN [ProductDetails]
ON [Products].DetailID = [ProductDetails].myID
Parse comes back asCommand(s) completed successfully.
Execute comes back asMsg 208, Level 16, State 1, Line 1Invalid object name 'Products'.
View 2 Replies
View Related
May 9, 2000
Hi,
I'm using SQL SERVER 7.0. I'm trying to run the following query:
-------------------------------------------------------------------------
DECLARE @DATABASENAME VARCHAR(255),
@TABLENAME VARCHAR(255)
Declare @RUN_ID VARCHAR(8000)
SELECT @DATABASENAME = "SID_TEST",
@TABLENAME = "T01_BRANCH_RPR"
EXEC("SELECT @RUN_ID = (select run_id
from " + @DATABASENAME + ".." + "t905_run_statistics
where TABLE_NAME_NM = '" + @TABLENAME + "'
and DATE_TABLE_LOAD_END_DTE is null)")
select @run_id, @dataBasename, @taBlename
---------------------------------------------------------------------------
But I'm getting this error when I run it:
Server: Msg 137, Level 15, State 1, Line 0
Must declare the variable '@RUN_ID'.
I've been over this about 1,000,000 times but I can't figure out what I'm doing wrong. Can anyone help me out?
Thanks in advance,
Darrin
View 1 Replies
View Related
Dec 31, 2004
Hello All,
I am fairly new and am having a small problem that hopefully someone can shed some light on.
I have two tables with a one to many relationship, each user can have more than image.
[tbluser]
UserID
LoginName
BirthDate
[tblImage]
ImageID
UserID
ImageName
ProfileImage
Approved
My problem is that I am not able to select a distinct set of results of all users and the ImageName if that user has an image which is approved and ProfileImage is 'yes' or '1'. Can anyone help me with writing the correct SQL for this with some basic explanation. Thanks!!
Code:
SELECT tblUser.LoginName
, tblUser.BirthDate
, tblImage.ImageName
, tblImage.ProfileImage
, tblImage.Approved
FROM tblUser
INNER
JOIN tblImage ON tblUser.UserID = tblImage.UserID
WHERE (((tblImage.ProfileImage)=1) AND ((tblImage.Approved)=1));
I am using Access 2000 and using the Access Query builder.
View 1 Replies
View Related
Sep 14, 2006
I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_idI'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.SELECT DISTINCT times.employee_id, employee.employee_nameFROM employee INNER JOIN times ON employee.employee_id = times.employee_id WHERE (times.job_id <> @job_id) Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!
View 3 Replies
View Related
Sep 24, 2007
Hi,I have three tables
Time_Sheet
Pin_Code
P_Date
Day_Status
Primary Key
Primary Key
Leave
P_Number
Leave_Code
Start_Date
End_Date
Primary Key
Employees
P_Number
Pin_Code
More>>
Primary Key
Primary Key
I want to update Day_Status in Time_Sheet from Leave_Code (Leave) when P_Date in Time_Sheet between start date and End Date in Leave I am getting Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Please help me.Thanks,Janaka
View 2 Replies
View Related
Jan 9, 2004
Hello,
I m stuck here with a very nasty problem. I am selecting data from different tables based on the search criteria of the user. But when i run the following quesry it does
not give any results in my script.
select s.* from my_profile m,signup s where s.gender='male' and s.age between '18' and '26' and eye_color in (7) and bodytype in (6) and languages in ('11, 15') and ethnicity in ('1, 3, 5') and religion in (12) and occupation in (4) and education in (7) and income_level in (4) and drinking in (4) and smoking in (1) and relationship in (5) and want_children in (2) and m.distance='10' and zip='9988' and s.sid=m.sid
The "languages in ('11, 15') and ethnicity in ('1, 3, 5') " part fails the query in the script. If I remove this part it works fine in both SQL and Asp script.
Please help me out!! This problem is pretty weird and cant make out why does this happens..
Any help will be greatly appreciated!
Thanks
may
View 11 Replies
View Related
Feb 4, 2000
I have this query:
SELECT Tabelle1.Feld1 AS a1, [Feld2]+1 AS a2, [a1]+[a2] AS Ausdr1
FROM Tabelle1;
It runs perfectly on a Access database but not on a SQL 7.0 server. Can anyone give me a idea on how to convert this?
Thomas Schoch
View 1 Replies
View Related
