Dynamic SQL - Compage Dates
Feb 18, 2007
Hi, I have a stored procedure that is all dynamic SQL... here is the portion that I have a question about:
1 -- Check for date
2 IF @DateSearch > 0
3 BEGIN
4 IF @whereFlag = 'F'
5 BEGIN
6 SET @strSQL = @strSQL + ' WHERE '
7 SET @whereFlag = 'T'
8 END
9 ELSE
10 BEGIN
11 SET @strSQL = @strSQL + ' AND '
12 END
13
14 SET @strSQL = @strSQL + ' (BL.ApprovedDate >= ' + @From + ') AND (BL.ApprovedDate <= ' + @To + ')'
15 END
BL.ApprovedDate is a 'DateTime' format.
@From and @To are also datetime
I'm getting the following error:
"Syntax error converting datetime from character string."
Any solutions? Thanks in advance.
View 1 Replies
ADVERTISEMENT
Feb 23, 2005
Hello,
To determine if the current date is between Monday 6AM and Friday 5PM you may want to consider the following T-SQL code.
DECLARE @FromDay int, @ToDay int
DECLARE @FromTime varchar(10), @ToTime varchar(10)
DECLARE @Day int
DECLARE @RangeFrom datetime, @RangeTo datetime
SET @Day = DatePart (dw, getDate())
SET @FromDay = 2
SET @ToDay = 6
SET @FromTime = '06:00'
SET @ToTime = '17:00'
IF (@Day >= @FromDay) AND (@Day <= @ToDay) BEGIN
PRINT 'Day ' + CAST(@Day AS varchar)
PRINT 'FromDay ' + CAST(@FromDay as varchar)
PRINT 'ToDay ' + cast(@ToDay as varchar)
SET @RangeFrom = CONVERT(varchar, GetDate() - (@Day - @FromDay), 110) + ' ' + @FromTime
SET @RangeTo = CONVERT(varchar, GetDate() + (@ToDay - @Day), 110) + ' ' + @ToTime
PRINT @RangeFrom
PRINT GetDate()
PRINT @RangeTo
IF (GetDate() >= @RangeFrom AND GetDate() <= @RangeTo) BEGIN
PRINT 'Between Dates'
END
END
View 3 Replies
View Related
May 25, 2007
I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.
For example a report that would run on today's MM, yy (5/2007) would look something like this:
MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08
(Col 1 - Col 12 separated by commas)
How do I do this in a matrix report column?
View 1 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
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
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 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
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
Feb 28, 2008
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
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.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
View 13 Replies
View Related
Oct 24, 2004
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.
View 2 Replies
View Related
Apr 15, 2008
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
View 14 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Mar 9, 2007
I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.
Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.
I have come to understand that a dynamic anything is a moving target!
Please advise.
View 3 Replies
View Related
Mar 30, 2007
Hello All,
I am trying to Add certain number of days to a particular date.
and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver.
Please can anyone help me in achieving it.
thanks
Shiva Kumar
View 2 Replies
View Related
Jan 10, 2002
Hi can anyone help me , or am I on the wrong track.
Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters.
I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'
Thanks
View 1 Replies
View Related
Apr 19, 2004
OK, so I can return (& therefore use) the last day of last month using
select (GETDATE()- DATEPART ( dd , GETDATE()))
Which returns 31-03-2004 (dd-mm-yyyy)
Has anyone a good way of determining a way of deriving say a date such as 01-01-2004 (1st Jan)....
I'd like to get some data where my dmd.sdate falls always in the previous quarter.. but would like it to be not hard coded...
Currently I have
SELECT cust.no,
cust.surname,
dmd.csan,
dmd.sdate
FROM pdunity.dbo.cust cust,
pdunity.dbo.dmd dmd
WHERE cust.rowno = dmd.rowno_custdmd_cust
AND(dmd.sdate
BETWEEN '01/01/2004'
AND(GETDATE()- DATEPART ( dd , GETDATE())))
ORDER by dmd.sdate asc
Is there some form of function I can use to return 01/01/2004 automatically ?
Will
View 6 Replies
View Related
Nov 21, 2005
why does this sql return the dates in different formats? Is it a default setting on the server or do i just handle it on SQL?
use northwind
select top 20 orderdate from orders
select top 20 left(orderdate,10) from orders
thanks
fatherjack
View 1 Replies
View Related
Feb 11, 1999
We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?
View 2 Replies
View Related
Aug 16, 2002
Hi All
I have a table which contains apart from other fields, 2 dates. i.e. startdate and enddate.
The startdate is always less than / equal to enddate.
I want to write a stored proc where I will give a date as a parameter and it should give me all the records there my date is between the startdate and the enddate.
Also I want to create a temp table in the procedure and the table gets populated with all the dates that comes between startdate and enddate, both dates inclusive.
I know this might be a tall order, but I am new to writing stored procedure, so I will be obliged if somebody can help me out
Yusuf
View 1 Replies
View Related
Apr 11, 2008
How would I pull just a list of dates for say the next year or two
View 1 Replies
View Related
Apr 18, 2008
I have an assignment using SQL this is the first time i have used it but i can understand the basics.
It is for an instrument service company and i need to find what instruments have not been serviced for a year.
i already know i have to:
SELECT instrument number, date of last service
FROM Instrument table
but after this i am unsure what code i can use in order to find all instruments that have not been servided in over 365 days what code can is best to use?
View 4 Replies
View Related
Apr 26, 2008
Hi friends,
I need to display the dates in between two dates without using tables. two dates are today date and 10th date from today date.
View 17 Replies
View Related
Apr 30, 2008
I have a columns with storeID's and dates i need to be able to find the difference of date for a certain store for its Min(Date) to the End of that Month
example
Date StoreID Difference of Date to end of the
Month
2008-01-02 00:00:00.000 163
2008-01-03 00:00:00.000 165
2008-01-01 00:00:00.000 167
2008-01-02 00:00:00.000 180
View 2 Replies
View Related
May 7, 2008
Hi,
I am trying to calculate our job value and product costs over a given time period. But if I run my query, the numbers just dont look right at all. Please can you have a look at this query and see if anything stands out?
-- FINISHED PRODUCTS PRODUCTION REPORTS DATA
-- gets current WIP Job information.
-- requires item details from this.
USE Staging
-- declare start and end variables
DECLARE @Start DATETIME
DECLARE @End DATETIME
SET DATEFORMAT DMY
SET @Start = '01/01/2008'
SET @End = '01/02/2008'
SELECT
SUM(jbs.Value) AS ProducionValue,
SUM(prd.Cost) AS Cost,
CONVERT(CHAR(10), @Start, 103) AS [From],
CONVERT(CHAR(10), @End, 103) AS [End]
FROM
(
SELECT
Job.[Job No],
Job.[Cust Name],
Job.[Req'd Date],
Job.ProdStartActual,
Job.Dept,
[Job].[Value],
Job.Loan
FROM
Job
WHERE
Job.[ProdStartActual] BETWEEN @start AND @End AND
Job.Cancel = 0 AND
Job.Sales = 1 AND
Job.Invoiced = 1 OR
Job.[ProdStartActual] BETWEEN @start AND @End AND
Job.Cancel = 0 AND
Job.Sales = 1 AND
Job.Invoiced = 2
) AS jbs INNER JOIN
(
SELECT
Item.[Job No],
Item.[Code]
FROM
Item
) AS itm ON jbs.[Job No] = itm.[Job No] INNER JOIN
(
SELECT
Product.[Product Code],
Product.[Cost]
FROM
Product
) AS prd ON itm.Code = prd.[Product Code]
"Impossible is Nothing"
View 5 Replies
View Related
Jun 9, 2008
If I give the from date and to date i should get all dates the between from and to.
eg:
from:'01-June-2008'
To:'03-june-2008'
Result:
Date
'01-June-2008'
'02-June-2008'
'03-June-2008'
Thanks in advance..,
cool...,
View 9 Replies
View Related
Jul 23, 2005
I've got a table (TYPE, DAYMTH) where it is a compound key and theDAYMTH is a datetime but all years are 1900 ONLY THE day and month aresignificant.What I want to do is for each type, find the next n dates - so forexample:TYPE DAYMTH1 1900-02-201 1900-05-051 1900-09-141 1900-11-112 1900-03-032 1900-07-07if I want to get the next 3 dates after 22 June 2005 it should give me:1 2005-09-141 2005-11-111 2006-02-202 2005-07-072 2006-03-032 2006-07-07so, anyone got any bright ideas?I've started down this route:create table #year (yr int)insert into #year values(2005)insert into #year values(2006)insert into #year values(2007)insert into #year values(2008)select *,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)from DAYMTHtable,#yearwhere convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20) > getdate()order by type,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)and although this seems to be a good start I wonder if there are bettersolutions. Also any quick ideas on how to finish if I decide to followmy existing thought process?thanksPhil
View 3 Replies
View Related
