SQL Statement - Part Dates Selection
Jun 20, 2006
Hi all,
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"
Can anyone suggest how I can do this?
Thanks in advance
View 7 Replies
ADVERTISEMENT
Nov 24, 2006
i am having problems selecting by date. I have made sure that the datetime field only contains dates with the time set to 00:00:00
In this instance both it should return one record dated 24/11/2006 00:00:00 but it returns a record with the date 25/11/2006 00:00:00 can someone expain why thi is.
Thanxs
WHERE(Tbl_01020_Tour_Types.TourTypeId = @TourTypeId) AND
(Tbl_01030_Tour_Schedule.Sch_TourStartDate <= CONVERT(DATETIME, CONVERT(INT,GETDATE()))) AND
(Tbl_01030_Tour_Schedule.Sch_TourEndDate >= CONVERT(DATETIME, CONVERT(INT, GETDATE())))
View 20 Replies
View Related
Apr 28, 2008
Hi... I have a problem getting a select statement to return a boolean value from sql server... the aim is to populate a checkbox list with permissions a group of users has. The statement is an outer join as follows: select SS.PermissionName, BB.GroupId,case when BB.GroupId Is Null then cast(0 as bit) else cast(1 as bit)end IsAddedfrom clPermissions SS left join ( select PermissionName, GroupId from clvGroupPermissions where GroupId = (Select groupId from clGroups where GroupName = 'Admin')) BB on SS.PermissionName = BB.PermissionName The returned values are 0 and 1... but .NET does not seem to recognize these values as true and false...Any one had an issue like this before?
View 3 Replies
View Related
Jan 3, 2008
I have this SELECT statement.
SELECT [issueID], [name] FROM [MyIssue]
What I wanted to do is in addition to the above statement, I want to add two run time fields like this:
99 [issueID],'All Issues' [name]
So let's say the above select statements generates this list:
Summer 2007 Issue
Winter 2007 Issue
The two addition fields will make the result list like this:
01 Summer 2007 Issue
02 Winter 2007 Issue
99 All Issues
How do I accomplish this? Any help is much appreciated.
View 5 Replies
View Related
Nov 7, 2007
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
View 14 Replies
View Related
Dec 29, 2003
I am trying to help an individual with a MS Query.
In Excel 97 MS Query the script was written:
TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS YEAR
In Excel XP I can run an existing Query and it returns the data as expected, but when I view the query it is:
TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'
However if I try to create a new query using XP MS Query then the AS YEAR or AS 'YEAR' will be removed by MS Query and my column header will now be:
TO_CHAR(LABTRANS.STARTDATE, 'YYYY')
Here are my examples:
MS Excel 97 Query
SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC,
TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'
FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE
MS Excel XP Query
SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC,
TO_CHAR(LABTRANS.STARTDATE, 'YYYY')
FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE
In Excel XP, no matter what I put in the AS part of the statement when I execute the script it runs and removes the AS part from the statement. This only happens when I run the script from MS Query, if I stay in Excel and just refresh the data, then it works fine.
I did not have this problem in Excel 97.
Any suggestions?
View 2 Replies
View Related
Jun 16, 2004
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
View 1 Replies
View Related
Mar 9, 2008
hey all,
I have the following query:
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS
BEGIN
DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)
DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)
SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name
HAVING article_id = @article_id
END
GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
Cheers
View 6 Replies
View Related
Aug 22, 2006
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate -
(SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name,
department.department_name, location.location_name
FROM employee INNER JOIN
request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
department ON employee.emp_department = department.department_id INNER JOIN
location ON department.department_location = location.location_id
GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial,
employee.emp_lname, department.department_name, location.location_name
ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information:
SELECT (SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!
TIA
View 3 Replies
View Related
Jul 2, 2014
App developers are complaining that a blocking report I sent them today does not have enough info to troubleshoot and fix the root cause of the blocking.(I used SQL Sentry to get the report and send to the users, but I think my question is more fundamental than just using one 3rd-party tool.)The reason for the complaints is that the top blocker is a SELECT stmt on TableA that is not the Wait Resource of the blocking (TableB).
I explained that this is likely because the "blocking" SELECT stmt on TableA is part of a larger explicit tran that includes TableB, and that they need to look through their code to identify the SQL stmt that is the root cause of the blocking.They would like that root statement identified in the blocking report, but I don't think it is possible to get to that through DMVs.
View 5 Replies
View Related
Aug 31, 2015
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
View 2 Replies
View Related
Jun 29, 2007
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.
View 3 Replies
View Related
Jul 24, 2007
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
thanks in advance
Tim
View 2 Replies
View Related
Feb 6, 2008
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
View 2 Replies
View Related
Jun 3, 2015
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
View 8 Replies
View Related
Apr 8, 2005
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?
Thanks,
Frederik
View 2 Replies
View Related
Jun 29, 2015
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?
View 9 Replies
View Related
Mar 14, 2007
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
View 8 Replies
View Related
Oct 5, 2007
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
Dates Table
Date
4/1/2003
1/1/2006
4/2/2007
Fee Table
Date
Period
Class
Fee
1
Daily
True
329
1
Half Day
True
178
1
OT
True
49
1
Hourly
True
41
1
Daily
False
156
1
Half Day
False
86
1
OT
False
27
1
Hourly
False
19
2
Daily
True
355
2
Half Day
True
192
2
OT
True
50
2
Hourly
True
44
2
Daily
False
171
2
Half Day
False
92
2
OT
False
28
2
Hourly
False
21
3
Daily
True
364
3
Half Day
True
197
3
OT
True
51
3
Hourly
True
45
3
Daily
False
175
3
Half Day
False
94
3
OT
False
29
3
Hourly
False
21
View 3 Replies
View Related
Apr 21, 2015
I am trying to find an easy way to create multiple of just two date in a single sql statement.
E.G.
A statement using the parameters
@StartDate = '2015-01-01'
@EndDate = '2015-01-05'
Ends up with rows:
'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'
What would be the best way to do this ?
View 3 Replies
View Related
Oct 29, 2007
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
Thanks
Shankar
View 1 Replies
View Related
Dec 7, 2007
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
View 3 Replies
View Related
Jan 28, 2015
I have made the following Scalar-valued function:
CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)
[Code] ....
What it does is convert numbers to times
E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)
So far so good (function works perfectly)
My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.
This means that, e.g. with a time like 3.23.40 the last zero must be deleted.
My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,
is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))
There are 5 codes: 0 1 2 3 and 4
View 9 Replies
View Related
Mar 2, 2006
SQL 2005 Dev
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?
View 3 Replies
View Related
Jul 18, 2014
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,
[code]....
View 9 Replies
View Related
Feb 28, 2015
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
View 7 Replies
View Related
Jun 21, 2005
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies
View Related
Apr 12, 2006
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config
The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config?
I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
--------------------------------------Part 1------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> --------------------------------------Part 1------------------------------------------------------------------------
--------------------------------------Part 2------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=CCWWSQLEXPRESS;Initial Catalog=ASPNETDB.MDF;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />--------------------------------------Part 2------------------------------------------------------------------------
View 2 Replies
View Related
Nov 8, 2006
Hi,
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Any advice will be greatly appreciated!
View 1 Replies
View Related
Mar 4, 2008
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
View 13 Replies
View Related
Oct 22, 2003
hello
in the database.....
table:1
DELIVERY_HEADER
INVOICE# PO#
1111 001,0002,0003
2222 005,0006,0007
in one invoice has many po# number i already get the po number individually using explode...
the ouput like this
001
002
003 and so on it is ok for that
but my problem is
i want to the po# should be selected i mean like a menu ..like the user select the which po# should be updated....
but i dont know how..
is there anyone can help me?
to elaborate more...
i can display the po# already without commas like in invoice# 1111
i can already display the po# number like this
001
002
003
after that i want it to be like a menu that user will only select the which po# should be updated..using arrow keys down and up....i mean just like selecting..
but i dont know how..
PLS HELP ME
thank
r3sann
View 1 Replies
View Related
Jan 17, 2006
Hi all, I am stuck on the case of many-to-many selection.
Now, I have 3 tables
Table: candidate_skills
candidate_id (PKey)
skill_id (PKey)
Table: skills
skill_id (PKey)
Table: job_skill_required
job_id(PKey)
skill_id(PKey)
Now, I want to select all cadidates who has the same skills of job_skill_required table.
I really don't know how to select it...
Please help, thank you.
View 5 Replies
View Related
Nov 16, 2007
SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name ,
b.station_nbr, b.beg_eff_date, b.end_eff_date,
Round( e.C02_pct,3)As C02_pct, MAX( e.sample_date)As Highest_Sample_Date, d.station_name
from [HOU-TIES01].TIES_Gathering.dbo.group_detail a
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_group b on a.group_code = b.group_code
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_type c on b.station_nbr = c.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.station d ON c.station_nbr = d.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.gas_analysis e ON d.station_nbr = e.station_nbr
Where (b.station_nbr=e.station_nbr)and (b.group_code = a.group_code) and
(a.group_type = 'NPN')and( b.end_eff_date >=DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0))
and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))and
(e.sample_date Between DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0)
and DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
and e.seq_nbr =
(select max(x.seq_nbr)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
and e.C02_pct =
(Select max(x.C02_pct)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
Group by a.group_code, a.group_name, a.group_note, b.station_nbr, b.beg_eff_date, b.end_eff_date, e.c02_pct, d.station_name
order by a.group_code
ok. Here is how the data looks.
Group
code station_nbr beg_eff_date end_eff_date C02 sample_date
0004 D01G000819 2007-09-01 3000-12-31 0.40 2007-10-02
0004 D01G000819 2007-09-01 3000-12-31 2.49 2007-10-09
What I am trying to do is to select the most current sample date. In this case "2007/10/09" when station_nbr and group_code are the same for both dates.
Thank You for all your help!!!!
From the Jr.in SQL
View 4 Replies
View Related
