Stored Proc Date Range Nor Producing RS

Date Range In Stored Procedure
I have a stored procedure (below) to which I pass several input parameters, 2 of which are dates (@in_ToDate and @in_FromDate) to
allow me to calculate a date range.

When I use the date range in the query I don't get a record set back. The data within the specified date range exists. I know that because the same query works fine and returns the right results when run by it self in the query analyser. It also works ok if I hard code the dates in the stored procedure. I can not work out what I am doing wrong.

I would greatly appreciate any help on this

The code in question is identified by the /* FIXME */ comment below.

Thank you


/************************************************** **************
* Procedure: SP_ReportStoreBenchmark
*
* Author: Jerry Vanicky
*
* Date: 20/11/2000
*
* Description:
*
*
* Table:
*
*
************************************************** **************/

PRINT 'CREATE PROCEDURE SP_ReportStoreBenchmark'

-- section for deleting all version of existing procedure
if exists (select * from sysobjects where id = object_id('SP_ReportStoreBenchmark') and sysstat & 0xf = 4)
drop procedure SP_ReportStoreBenchmark
PRINT 'Existing Procedure SP_ReportStoreBenchmark Dropped!'
GO

-- create the procedure
CREATE PROCEDURE SP_ReportStoreBenchmark
(
@in_ObjectIDint,
@in_ToDatedatetime,
@in_FromDatedatetime,
@in_Industryint,
@in_MediaFormatTypes int,
@in_Genresint,

@out_error_codeintOUT,-- error code, 0 if no error found
@out_error_messagenvarchar(200)OUT-- error description relating to above error code
)

AS

DECLARE @l_proc_namenvarchar(100)
DECLARE @l_yourStorereal
DECLARE @l_yourStoreTempreal
DECLARE @l_yourStatereal
DECLARE @l_nationalreal
DECLARE @l_thisStateIDint

DECLARE @l_stateAverageSales int
DECLARE @l_nationalAverageSales int

DECLARE @l_statePercentreal
DECLARE @l_nationalPercentreal

DECLARE @l_stateStoreCountint
DECLARE @l_nationalStoreCountint

DECLARE @l_ToDatedatetime
DECLARE @l_FromDatedatetime

-- person and store details that are requesting this report
DECLARE @l_lastnamenvarchar(50)
DECLARE @l_firstnamenvarchar(50)

-- the details posted into the report
DECLARE @l_industrynvarchar(50)
DECLARE @l_genrenvarchar(50)
DECLARE @l_categorynvarchar(50)
DECLARE @l_totalnvarchar(50)

BEGIN

-- initialise status parameters
SET@out_error_code = 0
SET@out_error_message = 'OK'
SET@l_proc_name = 'SP_ReportStoreBenchmark'

PRINT '--- SP_ReportStoreBenchmark starts here --- '

PRINT 'Input Parameters Passed Into ' + @l_proc_name + ' Are:'
PRINT '@in_ObjectID :' + CONVERT(nvarchar(10), @in_ObjectID)
PRINT '@in_ToDate :' + CONVERT(nvarchar(12), @in_ToDate)
PRINT '@in_FromDate :' + CONVERT(nvarchar(12), @in_FromDate)
PRINT '@in_Industry : ' + CONVERT(nvarchar(10), @in_Industry)
PRINT '@in_MediaFormatTypes : ' + CONVERT(nvarchar(10), @in_MediaFormatTypes)
PRINT '@in_Genres : ' + CONVERT(nvarchar(10), @in_Genres)

-- set the dates to the dd mon yyyy format eg.(1 Jan 2000)
SET @l_ToDate = CONVERT(datetime, @in_ToDate, 113)
SET @l_FromDate = CONVERT(datetime, @in_FromDate, 113)


-- get the person and store details requesting this report
PRINT 'Getting the person and store details that are requesting this report.'

SELECT @l_firstname = tblPerson.FirstName, @l_lastname = tblPerson.LastName
FROM tblObject INNER JOIN
tblPerson ON
tblObject.PersonID = tblPerson.PersonID
WHERE (tblObject.ObjectID = @in_ObjectID)


-- get the parameter names used by this report to filter on
PRINT 'Get the parameter names used by this report to filter on.'

-- the industry name
SELECT @l_industry = Name
FROM tblLookup
WHERE Type = 20 AND id = @in_Industry

-- the genre name
SELECT @l_genre = Name
FROM tblLookup
WHERE type = 133 AND id = @in_Genres

-- the category name
SELECT @l_category = Name
FROM tblLookup
WHERE type = 25 AND id = @in_MediaFormatTypes


-- Task 1
PRINT '<task 1 - get the stateID of this state>'
-- retrieve this state id
SELECT @l_thisStateID = tblAddress.StateID
FROM tblObject INNER JOIN
tblContactAddress ON
tblObject.ContactAddressID = tblContactAddress.ContactAddressID
INNER JOIN
tblAddress ON
tblContactAddress.AddressBillingID = tblAddress.AddressID
WHERE (tblObject.ObjectID = @in_ObjectID)

PRINT '@l_thisStateID : ' + CONVERT(nvarchar(10), @l_thisStateID)

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to retrieve the storeID for storeID (' + @in_ObjectID + ').'
PRINT 'Unable to retrieve the stateID for storeID (' + @in_ObjectID + ').'
GOTO error_lbl
END


PRINT '<task 2 - sum the sales for your store>'

PRINT '@in_FromDate : ' + CONVERT(nvarchar(20), @in_FromDate)
PRINT '@in_ToDate : ' + CONVERT(nvarchar(20),@in_ToDate )
PRINT '@in_Industry : ' + CONVERT(nvarchar(20),@in_Industry )
PRINT '@in_MediaFormatTypes : ' + CONVERT(nvarchar(20),@in_MediaFormatTypes)
PRINT '@in_Genres : ' + CONVERT(nvarchar(20),@in_Genres )
PRINT '@in_ObjectID ' + CONVERT(nvarchar(20),@in_ObjectID)

/* FIXME */
SELECT @l_yourStore = SUM(SaleAmt)
FROM tblReportStoreBenchmark
WHERE
Industry = @in_Industry AND
FormatType = @in_MediaFormatTypes AND
Genre = @in_Genres AND
StoreID = @in_ObjectID AND
PurchaseDate BETWEEN convert(datetime, @in_FromDate, 113) AND convert(datetime, @in_ToDate, 113)

PRINT 'The following line should display the store count of the sales.'

PRINT '@l_yourStore : ' + CONVERT(nvarchar(10), @l_yourStore)

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum store sales for storeID (' + @in_ObjectID + ').'
PRINT 'Unable to sum store sales for storeID (' + @in_ObjectID + ').'
GOTO error_lbl
END


PRINT '<task 3 sum the sales for your state>'
-- get the data for the state
SELECT @l_yourState = SUM(SaleAmt)
FROM tblReportStoreBenchmark
WHERE
(PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND
Industry = @in_Industry AND
FormatType = @in_MediaFormatTypes AND
Genre = @in_Genres AND
State = @l_thisStateID

PRINT '@l_yourState : ' + CONVERT(nvarchar(10), @l_yourState)

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum state sales for stateID (' + @l_thisStateID + ').'
PRINT 'Unable to sum state sales for stateID (' + @l_thisStateID + ').'
GOTO error_lbl
END


PRINT '<task 4 sum the sales for the nation>'
-- get the national data
SELECT @l_national = SUM(SaleAmt)
FROM tblReportStoreBenchmark
WHERE
(PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND
Industry = @in_Industry AND
FormatType = @in_MediaFormatTypes AND
Genre = @in_Genres

PRINT '@l_national : ' + CONVERT(nvarchar(10), @l_national)

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum national sales.'
PRINT 'Unable to sum national sales.'
GOTO error_lbl
END


PRINT '<task 5 get the count of the stores for the state>'
-- get the state wide count of all stores
SELECT @l_stateStoreCount = COUNT(DISTINCT StoreID)
FROM tblReportStoreBenchmark
WHERE
(PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND
Industry = @in_Industry AND
FormatType = @in_MediaFormatTypes AND
Genre = @in_Genres AND
State = @l_thisStateID

PRINT '@l_stateStoreCount : ' + CONVERT(nvarchar(10), @l_stateStoreCount)

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to count stores for the stateID (' + @l_thisStateID + ').'
PRINT 'Unable to count stores for the stateID (' + @l_thisStateID + ').'
GOTO error_lbl
END


PRINT '<task 6 get the count of the stores for the nation>'
-- get the national count for all the stores
SELECT @l_nationalStoreCount = COUNT(DISTINCT StoreID)
FROM tblReportStoreBenchmark
WHERE
(PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND
Industry = @in_Industry AND
FormatType = @in_MediaFormatTypes AND
Genre = @in_Genres

PRINT '@l_nationalStoreCount : ' + CONVERT(nvarchar(10), @l_nationalStoreCount)

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to count stores nationaly.'
PRINT 'Unable to count stores nationaly.'
GOTO error_lbl
END

PRINT 'All varaibles have been set, their values are:'
PRINT '@l_thisStateID : ' + CONVERT(nvarchar(10), @l_thisStateID)
PRINT '@l_yourStore : ' + CONVERT(nvarchar(10), @l_yourStore)
PRINT '@l_yourState : ' + CONVERT(nvarchar(10), @l_yourState)
PRINT '@l_national : ' + CONVERT(nvarchar(10), @l_national)
PRINT '@l_stateStoreCount : ' + CONVERT(nvarchar(10), @l_stateStoreCount)
PRINT '@l_nationalStoreCount : ' + CONVERT(nvarchar(10), @l_nationalStoreCount)


PRINT '<task 7>'
BEGIN
PRINT '<task 7a>'
-- get the state average sales
SET @l_stateAverageSales = @l_yourState/@l_stateStoreCount
PRINT '@l_stateAverageSales: ' + CONVERT(nvarchar(10), @l_stateAverageSales)

-- get the national average sales
SET @l_nationalAverageSales = @l_national/@l_nationalStoreCount
PRINT '@l_nationalAverageSales: ' + CONVERT(nvarchar(10), @l_nationalAverageSales)

-- set the statepercentage figure
SET @l_statePercent = (@l_yourStore/@l_stateAverageSales)*100
PRINT '@l_statePercent contains: ' + CONVERT(nvarchar(10), @l_statePercent)

-- set the national percentage figure
SET @l_nationalPercent = (@l_yourStore/@l_nationalAverageSales)*100
PRINT '@l_nationalPercent contains: ' + CONVERT(nvarchar(10), @l_nationalPercent)


SELECT @l_yourStore AS storeTtl, @l_stateAverageSales AS stateAverage, @l_nationalAverageSales AS nationalAverage, @l_statePercent AS statePercentage, @l_nationalPercent AS nationalPercentage, @l_FirstName AS FirstName, @l_LastName AS LastName, @l_industry AS industry, @l_genre AS genre, @l_category AS category, @l_total AS total, @in_ObjectID AS objectID, @in_ToDate AS toDate, @in_FromDate AS fromDate

--SELECT @l_statePercent AS StatePercent, @l_nationalPercent AS NationalPercent, @l_yourStore AS thisStore, @l_yourState AS thisState, @l_national AS thisNational,

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Error in calculating the total results.'
PRINT 'Error in calculating the total results.'
GOTO error_lbl
END

END

IF (@@ERROR <> 0)
BEGIN
SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to retrieve the Benchmark report for the ID of (' + @in_ObjectID + ').'
PRINT 'Unable to retrieve the Benchmark report for the ID of (' + @in_ObjectID + ').'
GOTO error_lbl
END

PRINT '--- SP_ReportStoreBenchmark ends here ---'

-- return successfully
RETURN(0)

error_lbl:
IF (@out_error_code = 0)
SELECT @out_error_code = 1, @out_error_message= 'Unable to retrieve the Benchmark report.'

-- return with errors
RETURN(1)

END
GO

PRINT 'Created Procedure SP_ReportStoreBenchmark!'
PRINT 'Done...'

View Replies !   View Related

---

Stored Procedure To Check Date Range
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries:  1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance

View Replies !   View Related

---

Stored Procedure Giving Error When Searching On Date Range
I've  a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
 
@ClientCode VARCHAR(7) = '',@UserID INT = 0
,@OrderID INT =0
,@StartDate datetime,@EndDate datetime
 
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name  FROM dbo.customer c     WHERE   c.Customer = @ClientCode)     ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1)     FROM dbo.master m     WHERE phase=1     AND m.Customer = @ClientCode    AND M.Status <> 'PIE')
 ,@AverageAge = ISNULL((select avg(age) from    (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age    from dbo. master M    WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END

View Replies !   View Related

---

Query Info Between Time Range & Date Range
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View Replies !   View Related

---

Last Date Stored Proc Updated???
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!

View Replies !   View Related

---

Max Date Via Select Not Stored Proc
I have the following sample data.

User Date
3 05/01/2000
3 06/03/2000
2 04/08/2000
1 05/01/2000
4 07/15/2000
2 03/02/2000

I want a select that comes back with the following with a view or select, not a stored proc and without adding fields to my table. Can anyone help with this?

DaUser DaDate DaMaxUserDate
3 05/01/2000 06/03/2000
3 06/03/2000 06/03/2000
2 04/08/2000 04/08/2000
1 05/01/2000 05/01/2000
4 07/15/2000 07/15/2000
2 03/02/2000 04/08/2000

Note the last field for all DaUser rows showes the max DaDate from within the table.

I am using SQL 7 and 2000 so ideally the code should work for both if possible.

Thanks in advance.

View Replies !   View Related

---

How To Tell The Last Change Date Of A Stored Proc?
Is there someplace in the database I could check to see when the last time a stored proc was altered?

Thanks in advance.

View Replies !   View Related

---

Return Formatted Date From Stored Proc?
What is the recommended method of returning a formatted date from a stored procedure?


The date is held in a date time field. I wish to return the date formatted as:

dd/mm/yyyy hh:mm

for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:

dd/mm/yyyy hh:mm:ss

I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.

I was looking at:

select jobHeaders.DateTimeJobTaken AS [Job Taken],
CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))

from jobHeaders

but this gives :
8 /3 /2004 with spaces.

Before looking further I thought one of you guys may have the answer.

Thanks in advance

View Replies !   View Related

---

SELECT And Stored Procedure Producing Different Results?!?!?
I ran my SP in QA and it brought back some data from my live database but I had my test DB selected from the DDL at the top of the screen.  So i ran a Select statement on the whole table in a different query window (with the same parameters)and it gave me the correct info.  Any idea y?  They both should be bringing back information from the same table not one from my live DB and one from my TEST DB.  Please help me--I'm so lost!min max rate <----This is what my stored procedure brought back(data from my live DB) ---it has parameters 90 100 .4080 89 .3070 79 .2559 69 .1549 58 .10The stored procedure is as follows:  CREATE PROCEDURE dbo.sp_WageMatrix_GetRate( @CompanyID nvarchar(2), @FacilityID nvarchar(2))AS SET NOCOUNT ON;SELECT [Min], [Max], Rate FROM Performance.dbo.WageMatrix WHERE (CompanyID = @CompanyID) AND (FacilityID = @FacilityID) AND PeriodID = dbo.fn_GetCurrentPeriod(CompanyID, FacilityID)ORDER BY RangeIDmy select statement brought back the following:min max rate 90 100 .4080 89 .3070 79 .2560 69 .15and I ran the following select statement:  SELECT [Min], [Max], [Rate] FROM [PerformanceDEV].[dbo].[WageMatrix] where companyid = '21' and facilityid = '01' and periodid = 2order by rangeid

View Replies !   View Related

---

Stored Procedure Producing Duplicate Records
Hi,

I have written the following stored procedure:

alter proc GetProducts

@prodcatint=null
as

select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name]
,p.designer,p.weight,p.price
from productdescription pd inner join products p on pd.productcategory=p.productcategory
where @prodcat=p.productcategory
order by p.productid

return

My Results are:

ProductCategory ProductID (Rest of the columns)
22 47
22 47
22 58
22 58


In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.

Does anybody have any suggestions.

Thank you in advance,

poc1010

View Replies !   View Related

---

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View Replies !   View Related

---

Query Help - Giving A Date Range Given The Start Date, Thanks!
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 Replies !   View Related

---

Date Picker Controls - Anyway To Limit Date Range
Have seen other questions here about modifying date pickers supplied by reports created in BIDS.  The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.
Any ideas?

View Replies !   View Related

---

{RESOLVED} Date Logic - Calculating A Date Range
I have a report that I need to run on 2 different date ranges.

Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE

The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)

So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007

My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007

Any help would be greatly appriciated!

View Replies !   View Related

---

Date Format In A Date Range Parameter
I´ve made a report with a date range parameter as described at http://msdn2.microsoft.com/en-us/library/aa337401.aspx

The language setting is Dutch. The date in de parameter is dd-M-yyyy. Is it posible to change this to dd-MM-yyyy.

View Replies !   View Related

---

Finding Where My Date Falls In Date Range
Hi;

We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.


I have a CommisionAudit table that tracks changes in commission rate with the following values.

ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------


with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.

View Replies !   View Related

---

Stored Proc - Calling A Remote Stored Proc
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
        Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
        No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View Replies !   View Related

---

Stored Proc Question : Why If Exisits...Drop...Create Proc?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK

View Replies !   View Related

---

Range In Date
 
I have a order table which has a orderdate and despatchdate
i want to write a query in such a way that i want to get all the details from the table for a range specified date as the oderdate and despatchdate are user
interactable. I search the google to solve this problem but could not find a answer
 

View Replies !   View Related

---

Date Range
What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @Begin Date AND @EndDate. I want to include all of the @EndDate.

View Replies !   View Related

---

Sum By Date Range
I am working on a report for staff productivity, and have to get a summary figure for how much productivity was expected for a date range. The problem is that the amount expected from an employee can change if they move from full time to part time etc.

So I have a view that has the begin date, end date, expected daily production # by employee, and have to figure out how to get the multiplication to work correctly.

Example:

Employee 1 had a daily production # of 10 from 1/1/07-3/31/07 and daily production of 5 from 4/1/07 - now

If I run the production report for 1/1/07 - 6/30/07 what I want is one summary figure of for the entire range which would be 10*Datediff(d,1/1/07,3/31/07)+5*Datediff(4/1/07,6/30/07) or 890+450=1340.

Of course the actual date range for the report will be a variable, and the dates for the begin and end of a production date range will be all over the place.

Any quick and easy way to do this?

View Replies !   View Related

---

Date Out Of Range?!
Greetings!

I have a data source that gets generated based on a variable with the following SQL :

"select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @[User::Last_Run_Date] + "'"

But when I run my package I get an error message saying:

The conversion of CHAR to DATETIME resulted in a DATETIME value out of range

The Last_Run_Date variable is set to '2006-06-25 14:35:05.450'

When I run the code in a QA session it works, but in the package it complains! What am I doing wrong?

 

Thanks for your help in advance.

 

View Replies !   View Related

---

Get Date Range
I have a function that takes in a week number and returns data based on that week number, for one client we have a table that tells what the weeks are since they are not on a normal calendar, but their fiscal calendar.   We now have another client that want the same function, but for it to return the data based on the normal calendar week.   So, how can I determine the date range of a week, based on the week number?

View Replies !   View Related

---

Date Range With MDX
 

I want to return data from a date range using MDX.  The user will pick a month and a number for the number of previous months to display.  So far, using SSRS to write my MDX code I have:
 

SELECT NON EMPTY { [Measures].[Steel Margin], [Measures].[Coil Weight], [Measures].[Amount], [Measures].[Gross Margin], [Measures].[Gross Profit] } ON COLUMNS, NON EMPTY { ([Date Shipped].[Month].[Month].ALLMEMBERS * [Out Rep].[Rep].[Rep].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DateShippedFiscalYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DateShippedMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DateShippedYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@OutRepRep, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW]))))) WHERE ( IIF( STRTOSET(@DimSalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@DimSalesTypeDescription, CONSTRAINED), [Dim Sales Type].[Description].currentmember ), IIF( STRTOSET(@DateShippedYear, CONSTRAINED).Count = 1, STRTOSET(@DateShippedYear, CONSTRAINED), [Date Shipped].[Year].currentmember ), IIF( STRTOSET(@DateShippedFiscalYear, CONSTRAINED).Count = 1, STRTOSET(@DateShippedFiscalYear, CONSTRAINED), [Date Shipped].[Fiscal Year].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 
 
I have two other parameters: StartMonth and RollBackNumber.  RollBackNumber will be the number of months and StartMonth will be my computed start parameter.  I can write some code if necessary to compute the StartMonth (something like [Date Shipped].[Month].&[2007]&[3]&) if I have to.

View Replies !   View Related

---

Date Range
Hey guys..
pls help

what query should I do? Obtaining certain date only using SQL server2000?

Thnx...

View Replies !   View Related

---

Date Range
How to display start date and finish date together in the same column with a '-' in between?

2/24/2008 - 3/1/2008

View Replies !   View Related

---

Date Range
Hi, 'am fairly very new to SQL SERVER 2000.

I have this particular problem....I need to develop a stored proc
where in a parameter checks the dates between JAN 1st and DEC 31st of a particular year. The parameter is declared for year..

I'll mention a small example below...


USE PUBS
declare @year as varchar
set @year = 1993

select * from employee
where hire_date >= '@year-01-01'
and hire_date<='@year-12-31'

This is just an example to show wat i want...
the year is prompted to the user...he/she can select any year

I get the following error message....

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Can anyone help me on this......

Thanks in advance

View Replies !   View Related

---

Date Range
Hi All,

I am very new to SQL and have a question, hopefully someone can answer.

I have a table of data, one of the fields is a date.

What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".

Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506
dates between 01/06/06 and 31/05/07 is fiscal year 0607

Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???

Any help is much appreciated.

Cheers

Rudi

View Replies !   View Related

---

ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.

View Replies !   View Related

---

How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
 

Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment.

Thanks in advance.

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [SampleStoredProc]
@Var1  varchar(20),
@Var2  varchar(3),
@Var3       varchar(2) = 'Dummy'           
As

declare @selectlist   varchar(5000)
declare @tableBuild   varchar(1000)
declare @FieldName   varchar(50)
declare @FieldSelect   varchar(500)
declare @FieldTitle   varchar(50)
declare @TableName   varchar(50)
declare @holdTable   varchar(50)
declare @title    varchar(50)
declare @holdTitle   varchar(50)
declare @PageName   varchar(50)
declare @sequence   varchar(100)
declare @extraCriteria   varchar(200)
declare @holdCriteria   varchar(200)
declare @insertSQL   varchar(5000)
declare @ConvertRoutine  varchar(500)
declare @loopCtrl1   bit
declare @loopCtrl2   bit
declare @ConvertSQL   varchar(5000)
declare @PrevValue   varchar(50)
declare @NewValue   varchar(50)
declare @ActionTxt  varchar(1)
declare @Description  varchar(20)
declare @effDate  varchar(10)
declare @transEffDate  varchar(10)
declare @expDate  varchar(10)
declare @lastTransDate  varchar(10)
declare @policyStatus  varchar(2)
declare @reasAmendDesc varchar(50)
declare @policyNumber  varchar(20)
declare @riskState  varchar(20)
declare @PriorPrem  money  
declare @AmendPrem  money  
declare @PremDiff  money  
declare mtcursor cursor for
 select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1
   where Column1 = @Var2
  order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName

 open mtcursor

 fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine

 set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
 set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
 set @loopCtrl1 = 0
 set @loopCtrl2 = 0

 WHILE (@loopCtrl1 = 0)
 begin
  set @holdTable = @TableName
  set @holdCriteria = @extraCriteria
  set @holdTitle = @title

  if @FieldSelect = ''
      set @selectlist = @selectlist + ',' + @FieldName
  else
      set @selectlist = @selectlist + ',' + @FieldSelect

  set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)'

  fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
  if @@fetch_status <> 0
      set @loopCtrl2 = 1

  if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1)
   begin

    set @tableBuild = @tableBuild + ')'
    set @insertSQL = '
 declare mtcursor2 cursor for
  select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1
  where TableName = ''' + @holdTable + '''
      and ExtraCriteriaTxt = ''' + @holdCriteria + '''
      and PageTitleTxt = ''' + @holdTitle + '''
      and Column1 = ''' + @Var2 + '''
      order by FieldDisplaySequenceNbr

 declare @FieldName varchar(50)
 declare @FieldTitle varchar(50)
 declare @ExtraUpdateMatch varchar(500)
 declare @PullUpdate bit
 declare @PullAdd bit
 declare @PullDelete bit
 declare @PullAnyUpdate bit

  open mtcursor2
  fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate

  WHILE (@@fetch_status = 0)
  begin

    if substring(@FieldTitle,1,1) = ''#''
        set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1)
                else
                    set @FieldTitle = '''''''' + @FieldTitle + ''''''''

   if @PullAnyUpdate = 1
                  begin
            exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
      from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
        where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''')
                   end
   else
      begin
  if @PullUpdate = 1
             exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
       from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
         where A.Val1 = ''''O''''  and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null)
          or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '')
                  end

   if @PullAdd = 1
  exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A''''       from #tempTable A where Val1 = ''''A'''''')
   if @PullDelete = 1
    exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D''''
      from #tempTable A where Val1 = ''''D'''''')
   fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
  end

  close mtcursor2
  deallocate mtcursor2'

    exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL)

    set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
    set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
   end

    if @loopCtrl2 = 1
       set @loopCtrl1 = 1
  end

  close mtcursor
  deallocate mtcursor

  Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 
  declare deletecursor cursor for
 select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U'

  open deletecursor

  fetch next from deletecursor into @PageName

  while @@fetch_status = 0
     begin
  if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0
       DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U'
  fetch next from deletecursor into @PageName
     end

  close deletecursor
  deallocate deletecursor

  declare convertcursor cursor for
 select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a
 inner join MyTable1 b
  on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> ''
 where a.Id = @Var1

  open convertcursor

  fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine

  while @@fetch_status = 0
     begin
 set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)'
 set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) '
 -- start 33385
 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
 set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''
 --end 33385
 if @ActionTxt = 'A'
  set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + ''''
 else
   begin
  set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + ''''
  set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' 
   end
 if @ActionTxt = 'D'
  set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + ''''
 else
   begin
  set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + ''''
  set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')'
   end

 set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted
  where EntrySequenceNbr = ''' + @Sequence + ''''

 exec (@ConvertSQL)

 fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
    end

  close convertcursor
  deallocate convertcursor

/* LOB-specific data conversions */
  if @Var2 = 'PA '
  --exec PAConfirmCovConversions @Var1 = @Var1
 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3
 -- End issue 33385
 
  Create table #pageSeqTable (PageTitle varchar(50), PageSeq int)
  insert into #pageSeqTable
 select distinct PageTitleTxt, PageDisplaySequenceNbr
 from MyTable1
 where Column1 = @Var2

  select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt
  from    MyTable2, #pageSeqTable b
  where Id = @Var1 and PageNm = b.PageTitle
  order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr

  select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101),
 @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes,
 @policyNumber = PolicyNumber,
 @riskState = StateName,
 @AmendPrem = convert(money,PremiumAmount)   /* Case 33385 */
  from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C
  where Id = @Var1
       AND Val2 = (select max(Val2)
      from SHPlanInfo
      where Id = @Var1)
       AND B.ReasonAmendedCd = A.ReasonAmendedCd
       AND C.StateCode = A.RiskState
Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' 
Set @PremDiff = @AmendPrem - @PriorPrem            


  select EffectiveDate = @effDate

  select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate

  select AmendXPolStat = @policyStatus

  select ReasonAmendedDes = @reasAmendDesc   

  select PolicyNumber = @policyNumber     

  select RiskState = @riskState      

  select PriorPremium =  @PriorPrem             
  select AmendPremium = @AmendPrem             
  select PremiumDifference = @PremDiff            
Select ClientNumber from SHClient with (nolock) where Id=@Var1 and  ApplicantRecordInd = 1     
delete from MyTable2 where Id = @Var1

return

 

View Replies !   View Related

---

Sql Server Date Range
 I have a table namely "Information"  , it has one field namely " Lastupdate" when i insert or update data , lastupdate column takes takes current datetime value.  and this data i am displaying in another  page . Here i want to display the information upto 21 days from last updated dates. means if i save data today (12/04/2008) then the "lastupdate"  value will be (12/04/2008) and when i display this data should display upto 21 days means upto  (23/04/2008) when date 24/04/2008 will come, this data should not display.  and i want this from sql server query , if anybody have idea please write query for that , its very urgent ..............

View Replies !   View Related

---

Date Range Problem
Hello all,I am trying to write a query that compares a member's enrollment periodwith the products that their group has had during that period (allmembers belong to a group and the products that the member has arebased on that group). I need to get the date range for all productsthat the member had during their enrollment.Here are a few rules:- In the source table there are some group products that have tworanges that are really contiguous. This is because another column thatwe don't care about may have changed between those two periods. If theend_date = DATEADD(dy, 1, start_date) then the two periods are actuallycontiguous. These should only appear as one row in the output.- If the gap is greater than one day then two rows should result- If the product changes, of course it should be two rows in the output- If a group has a product from before the start of the member'senrollment then the start_date for the row should be the member'sstart_date. If the product extends past the member leaving the groupthen the end_date should be that of the member.- In my sample data below I only have as many as two rows back to backfor the same product that are contiguous. In reality there could beeven more than that.I have SQL that will join the two tables based on either the start orthe end date of the group product falling in the member's enrollmentperiod, but I'm not sure of the best way to merge the contiguous dateranges into single rows. Any suggestions?Erland, despite it being late on a Friday afternoon, the SQL and sampleoutput are below. ;-)Thanks,-Tom.CREATE TABLE Members (group_id INT NOT NULL,member_id INT NOT NULL,start_date DATETIME NOT NULL,end_date DATETIME NOT NULL )GOALTER TABLE Membership ADD CONSTRAINT PRIMARY KEY PK_Members PRIMARYKEY (group_id, member_id)GOCREATE TABLE Group_Products (group_id INT NOT NULL,product_id INT NOT NULL,start_date DATETIME NOT NULL,end_date DATETIME NOT NULL )GOALTER TABLE Group_Products ADD CONSTRAINT PRIMARY KEY PK_Group_ProductsPRIMARY KEY (group_id, product_id, start_date)GOINSERT INTO Members VALUES (1, 1, '2002-01-01', '9999-12-31')INSERT INTO Members VALUES (1, 2, '2004-11-01', '9999-12-31')INSERT INTO Members VALUES (1, 3, '2000-10-01', '2004-12-31')INSERT INTO Members VALUES (2, 4, '2002-01-01', '2005-01-15')INSERT INTO Members VALUES (2, 5, '2004-10-01', '9999-12-31')GOINSERT INTO Group_Products VALUES (1, 1, '2001-01-01', '2003-12-31')INSERT INTO Group_Products VALUES (1, 1, '2004-01-01', '2004-11-15')INSERT INTO Group_Products VALUES (1, 2, '2004-11-16', '9999-12-31')INSERT INTO Group_Products VALUES (2, 1, '2002-01-01', '2004-11-01')INSERT INTO Group_Products VALUES (2, 1, '2004-11-15', '9999-12-31')GOExpected Output:group_id member_id product_id start_date end_date-------- --------- ---------- ---------- ----------1 1 1 2002-01-01 2004-11-151 1 2 2004-11-16 9999-12-311 2 1 2004-11-01 2004-11-151 2 2 2004-11-16 9999-12-311 3 1 2001-01-01 2004-11-151 3 2 2004-11-16 2004-12-312 4 1 2002-01-01 2004-11-012 4 1 2004-11-15 9999-12-312 5 1 2004-10-01 2004-11-012 5 1 2004-11-15 9999-12-31

View Replies !   View Related

---

Dates In A Date Range
Is there a way that I can get a resultset that contains unique dates ina given date range without the need to have a temporary table and acursor?perhaps something like:declare @start_date as datetimedeclare @end_date as datetimeset @start_date as '1/1/2005'set @end_date as '1/1/2006'select fn_getuniquedate(@start_date, @end_date)1/1/20051/2/20051/3/2005...12/31/2005

View Replies !   View Related

---

Problem With Using BETWEEN For Date Range...
Hi all -This problem has been driving me crazy, and I'm hoping the answer issomething stupid I am neglecting to see....The procedure posted below is part of an Access/SQL database I havedeveloped. Basically, the user would input a beginning and ending date,and the query goes and pulls records that meet the following criteria:1. TradeDate is between beginning date and ending date2. TradeTime is between beginning date and ending date3. Trade's Match ID is equal to match IDs returned by the second selectstatement - this is part of a ticket processing system, and tickets aregrouped using their match id. So, if one ticket has been updated andnow meets criteria #1 or #2 above, this is supposed to also return anyof the other tickets with the same match ID - so if one ticket in agroup changes, our acct. dept can look at the whole group on theirreports.Anyway, the query below seems to work, but I am not happy with it. Theproblem was I was using the BETWEEN function, and not converting allthe dates to varchar. This worked fine, unless the beginning date andending date were the same. For example, if I had a ticket with atradedate of 5/3/06 and I ran a beginning date of 5/3/06 and an endingdate of 5/3/06, the ticket should be returned. However, with theBETWEEN statement, it would return no rows.I changed the BETWEEN statements to statements like:tradedate >= beginning date and tradedate <= ending datebut this also returned no rows.It was only upon converting all the dates to varchar and using the <=and >= operators that I started getting the results I need.Can someone tell me why the heck BETWEEN wouldn't work? Tradedate andTradetime are both datetime values, and I was bringing in the beginningand ending date variables in datetime form...is there a problem usingBETWEEN when the first and second variables used are the same?Any light you can shed on this would be great, because having all theseconvert statements and such makes me nervous...I'd rather get betweento work, but I have not been able to in my testing...Thanks! -JimCREATE PROCEDURE dbo.spAcctExport(@begindate datetime,@enddate datetime)AS SELECT TOP 100 PERCENT dbo.tblTradeAccount.AccountingCode ASTradeAccount, dbo.tblOrders.TicketNum, dbo.tblOrders.TradeDate,dbo.tblOrders.SettleDate, NULL ASProductionMonth, dbo.tblOrders.RepID, dbo.tblOrders.AcctNum,dbo.tblAccounts.Shortname, dbo.tblOrders.Quantity,dbo.TBLCUSIP.Factor, dbo.tblOrders.BuySell,dbo.tblOrders.CUSIP, dbo.TBLCUSIP.Issuer, dbo.TBLCUSIP.PoolNum,dbo.TBLCUSIP.Coupon,dbo.tblOrders.FixAdj, dbo.tblOrders.Price,dbo.tblOrders.RepSC, '=(H:H*I:I*Q:Q)/100' AS Markup, 'PTMSA' ASProdType, dbo.tblOrders.DeskSC,dbo.tblOrders.RepCarry, '=(H:H*I:I*T:T)/100' ASDeskMarkup, dbo.tblOrders.MatchID,'=IF(K:K="B",((I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' ASTotalPrincipal, dbo.tblOrders.CancelCorrect,dbo.tblOrders.OriginalTrade,dbo.tblOrders.TradeTime, dbo.tblOrders.Rep2ID,dbo.tblOrders.Rep2SCFROM dbo.tblOrders INNER JOINdbo.TBLCUSIP ON dbo.tblOrders.CUSIP =dbo.TBLCUSIP.CUSIP INNER JOINdbo.tblAccounts ON dbo.tblOrders.AcctNum =dbo.tblAccounts.AcctNum INNER JOINdbo.tblTradeAccount ON dbo.tblOrders.TradeAccount= dbo.tblTradeAccount.TradeAccountWHERE (((dbo.tblOrders.MatchID IS NOT NULL) AND (dbo.tblOrders.MatchID IN(SELECT MatchID FROM dbo.tblOrders WHERE (CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=CONVERT(VARCHAR(10), @begindate,101) ANDCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=CONVERT(VARCHAR(10), @enddate,101))OR (CONVERT(VARCHAR(10),dbo.tblOrders.TradeTime,101) >=CONVERT(VARCHAR(10), @begindate,101) ANDCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=CONVERT(VARCHAR(10), @enddate,101)))))OR ((CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=CONVERT(VARCHAR(10), @begindate,101) andCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=CONVERT(VARCHAR(10), @enddate,101))OR (CONVERT(VARCHAR(10),DBO.TBLORDERS.TradeTime,101) >=CONVERT(VARCHAR(10), @begindate,101) ANDCONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=CONVERT(VARCHAR(10), @enddate,101))))ORDER BY dbo.tblOrders.CancelCorrect,dbo.tblTradeAccount.AccountingCode, dbo.tblOrders.MatchID,dbo.tblOrders.BuySell, dbo.tblOrders.TicketNumGO

View Replies !   View Related

---

Run Same Query For Each Day Across A Date Range?
Hi,I have a query which works for one day:SELECT SOME_COL AS something, SOME_COL2 AS something2 FROM myTableWHERE DATE = '2007-05-11' AND SOME_STAT 1Returnssomething something 21 23 4How do I get this to work for a date range (e.g. DATE '2007-05-09')where I get:date something something22007-05-09 1 22007-05-09 3 42007-05-10 1 22007-05-10 3 42007-05-11 1 22007-05-11 3 4Thanks in advance!

View Replies !   View Related

---

Select From A Date Range
Hi guys,whilst working on a project which I thought was nearly complete I havecome across a problem which was some how over seen, which I am hopingone of you guys know how to resovle.Firstly here is the script to create the table and insert some sampledata:CREATE TABLE [dbo].[tbltemp999] ([Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EventDate] [datetime] NOT NULL ,[EventID] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6005)The EventID 6006 represents a system shut down, and the EventID 6005represents a sytem start. Therefore from the above data you cans seethat the machine is not rebooted very often.My problem occurs when you select a date BETWEEN something where thereis no data. ie BETWEEN 2004-10-01 AND 2004-10-15 my desired resultfrom this should return 15 days of uptime but as there is no data itis returning nothing:(Can anyone work this one out?ThanksSunny

View Replies !   View Related

---

SQL Date Range Query
Hi Folks

Need a little help with a Date Range query;

Im using SQL 2000 and the field is set to datetime

I need to query from now to 30 days ago, its very basic I know...

The field is called - ArtDate

Any help appreciated !

Thanks

View Replies !   View Related

---

Date Range Logic
Good morning all,

I've got a little headscratcher for you involving date ranges.

We have a table for recording absences:
Absence(unique_identifier, parent_identifier, date_from, date_to ... )
And an employee table
Employees(unique_identifier, Surname, Firstname, birth_date ...)

Where the relationship between the two is:
Employees.unique_identifier = Absence.parent_identifier

The problem lies when wanting to know whether an employee was off within a specified date range.

Absence:

u_idp_iddate_fromdate_to
112007-02-012007-02-06
222007-01-292007-02-06
322007-03-252007-03-25
432007-06-062007-06-08
542007-02-052007-02-06

Given the above sample results, how can I identify which employees were off during the first week of February (2007-02-01 to 2007-02-07)?

Expected Results:

u_idp_iddate_fromdate_to
112007-02-012007-02-06
222007-01-292007-02-06
542007-02-052007-02-06

Any advice you can give to help me get the answer I need is much appreciated :)

View Replies !   View Related

---

Absences Between A Date Range...
Ok guys and gals I have a logic-scratcher for you!

Employees(employee_number, surname, forename, etc)
Absences(employee_number, date_from, date_to, etc)

I know, not the best design but it's what I've got to work with!

So here comes the tricky part; given a user-defined date range I need to return who has been off sick!

Here's some sample data

+-----------------+-----------+
| employee_number | forename |
+-----------------+-----------+
| 1 | Aaron |
| 2 | Ben |
| 3 | Charlotte |
| 4 | Dawn |
| 5 | Edward |
| 6 | Frank |
+-----------------+-----------+

+-----------------+-----------+----------+
| employee_number | date_from | date_to |
+-----------------+-----------+----------+
| 1 | 01/01/08 | 03/01/08 |
| 2 | 02/01/08 | NULL |
| 3 | 01/01/08 | 15/01/08 |
| 4 | 08/01/08 | 10/01/08 |
| 6 | 05/01/08 | 07/01/08 |
+-----------------+-----------+----------+

So, who was off sick between 04/01/2008 and 09/01/2008?
red indicates the people we want to return in our resultset


Aaron was not off sick during this date range.
Ben is still of sick and his sickness started in this date range.
Charlotte was off sick during the entirety of this date range.
Dawn's sickness started between these dates.
Edwards hasn't been off sick at all
and finally; Frank was off sick between these two dates.

Unfortunately I can't get my head round the logic needed here... Any suggestions? I hope my noddy example gives you enough insight to help, if not; let me know and I'll try answer any questions you fire at me!

-George

View Replies !   View Related

---

Date Range Problem
Hello!
I'm quite new to Analysis Services.
I have a problem i can't solve.

Scenario:
A car rental company registers the history of each car. for each record there are two date fields, StartDate and ReturnDate.
A record in the historical table is:

Car Mercedes C200
Customer Company1
StartDate 12/12/2003
ReturnDate 01/12/2004
Quantity 3

This means that the customer rented 3 mercedes c200 from 12/12/2003 to 01/12/2004

Then, If I want to know at the end of Q4-2003 how many cars where rented in T-SQL I should write

SELECT….. FROM ……
WHERE startdate<=12/31/2003 and (returndate >=31/12/2003 or returndate is null)

Now I built a cube based on that table with the following dimensions and measures:

Dimension:
CarModel
Customer
StartDate
ReturnDate

Measures
Quantity

The question is: if I use as point of my analysis Q4-2003 and I want to see at that time how many car I rented, how can I filter or select the subset of the cars rented before Q4-2003 and returned after Q4-2003 (or not returned yet)?


Carmodel quantity start return
Mer c200 2 may-2003 feb-2004
Mer c200 6 dec-2003 mar-2004


If in the cube browser I try to filter the startime selecting q4-2003 my result will be just 6 but I had other to cars out rented before (then it should be 8).

I should be able to have the possibility to dynamically select the analysis point because the cube is browsed by crystal reports, so I have to insert something in the cube structure, like….calculated member?

Thanks in advance.

View Replies !   View Related

---

Dynamic Date Range MDX
Hi,

I currently have a cube with a Dynamic Date Dimension that contains calculated members such as YTD, LYTD, current month etc.

I need to add a new members that displays the last 12,18,24 months.

I have this "Current Year" code but cannot work out what it is doing or how to modify it. It successfully displays the months for the current year.

iif(
IsAncestor([Period].CurrentMember,strtomember("Period].[Year].&[" + CStr(Year(Now())) + "]").FirstChild),
(strtomember("[Period].[Year].&[" + CStr(Year(Now())) + "]"),[Dynamic Dates].[All Dates]),
iif(
IsAncestor(strtomember("[Period].[Year].&[" + CStr(Year(Now())) + "]"), [Period].CurrentMember),
[Dynamic Dates].[All Dates],
null
))

Can you help please.

View Replies !   View Related

---

Compare Date Range
I want to write a query to retrieve all the items that are
order between 2 dates range, 07/30/2000 to 08/02/2000, any suggestions are
greatly appreciated,

View Replies !   View Related

---

Please Help Me Very Urgent To Get A Date Range
 

Dear All expertise please help me becuase I am very beginner
 
I have a Holiday Table  in which contain each employee's holiday from date and todate . I want to get an sql to get the record having holiday from the given date range
 
example
 
Employee A  having holiday from 01-01-2008 to 06-01-2008
Employee B  having holiday from 05-01-2008 to 10-01-2008
Employee C  having holiday from 20-01-2008 to 30-01-2008
 
 
 
I given the criteria  as follows to get list of employeed having holiday in given date range
 
Holiday from 01-01-2008 to 06-01-2008 . So Employee A  and Employee B should come in the list. Please give me an SQL

View Replies !   View Related

---

Date Range Issue
I've been trying to find a good way to do this and it seems every idea I come up with or find only accomplishes the desired result in one scenario.  Here is my issue.

I'm designing reports with SQL Reporting Services 2000 and many of these reports need to show values for a dynamic date range such as the last full month, last full week, etc. similar to the built-in functions in Crystal Reports.  To accomplish this, my first effort was to filter my results by looking only at results where a specific date parameter matched the current week or month -1 or -2 or whatever was needed.  The following example would look at records for the previous month only:

SELECT * FROM tbExample
WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE())

I understand this may not be the most efficient way of performing this operation, but this seemed to work at the time.  If I were looking at the previous week, I would simply replace MONTH([EntryDate]) with DATEPART(week, [Entry Date]) and get the same result.  The issue that I've run into is that at the very beginning of the year (first week and month particularly), this code fails.  Since the functions I've used above result in an integer, then statically subtract from it, at the beginning of the year, I potentially end up with zero or negative numbers which causes unpredictable results or errors.

To address my issue, I thought I would write an IF statement which would look at the result and if it were zero or a negative number, compensate accordingly.  Following is an example for the previous month:

IF (MONTH(GETDATE())-1 <= 0)
BEGIN
  SELECT * FROM tbExample
  WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1+12 AND YEAR([Entry Date]) = YEAR(GETDATE())-1
END
ELSE
BEGIN
  SELECT * FROM tbExample
  WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE())
END
RETURN

The above example simply compensates by adding 12 months back to the result and subtracting 1 year instead.  This is obviously limited, but seemed alright for my application.  This example does not however work for the weekly ranges.  The problem with using this on a weekly basis is that there are technically 53 weeks in a year, though the last week only has a few days.  I am unsure if there is a way and if so, how to calculate this to add back 52 or 53 weeks respectively depending on where the current date falls.

In researching another issue, I received a tip of a different way to pull date ranges.  Although this addresses the issue of rolling back into the previous year, it would only work for the monthly reports, not the weekly.  Here is the example:

SELECT * FROM tbExample
WHERE [Entry Date] >= DATEADD(month, -1, DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0)) AND [Entry Date] < DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0)

As stated previously, this works perfectly by focusing on anything between the first day of the previous month and the first day of the current month.  The problem is that the 1-day function does not allow for a weekly focus.

I believe I have explained my situation to the best of my ability.  I am completely open to suggestion, whether it be along the line of things I have already tried or fresh, new ideas.

Thank You

View Replies !   View Related

---

Date Range Dilema
 

Hi,
          I have  a report in which I need to filter data for a range from beginning of current month  to end of current month , ie a data table field "date_occured" should be between the range from beginning of current month  to end of current month  this is a default
Also this should be a parameter that the users can change
 
ie
 two text box parameters on the top of report set to default of beginning of current month and  end of current month
which can be later varied by the users
 
In my sql code I have set two parameters
@begindate  datetime
@endDate    datetime
 
in where clause
 
where date_occured between @begindate and @endDate
 
 
but in the report rdl , I am unable to set the two text boxes to default to the dates 11/1/2007  and 11/30/2007 ---example for this month
 
Please help
 
Thank you
 
 

View Replies !   View Related

---

Date Range Criteria
Ehhm,great!!
Could you tell me what is going wrong with the stored procedure below? I can't get any results!
(the 'ap_dateIn' field in the sql table 'tbl_1' has "dd/mm/yy hh:mms" smalldatetime format)

 
(

@date_min smalldatetime='1/1/1910',

@date_max smalldatetime='1/1/2010'

)

AS

SET NOCOUNT ON;

SELECT tbl_1.*

FROM tbl_1

WHERE (ap_dateIn BETWEEN @date_min AND @date_max)

 
 
thakns for your time,fellaz!
 

View Replies !   View Related

---

Query For Date Range
I use oledb (ACCESS database) in my application. i want to build a query to retrieve the number of Bookings from my Booking table where the appointment_date_time.timeOfADay is in range of 9am-12pm or 14pm- 7pm, that is (>= 9 and <12) or (>= 14 and < 17). Please help to build the query,

I found some query sample like:

select * from tblstudents where classID='1' and studentstartdate between ('2004-12-03') and ('2004-12-12')

or

WHERE DateField BETWEEN @StartDate AND @EndDate

But I dont want to search year and month and day, i just want to search the actual hour of a day. i am stuck with the syntax, please help

View Replies !   View Related

---

Searching Date Range
i have a date field with which i'd like to search several date ranges.  the date field is populated as month, day, year (2/23/2007).

I'd like to select date using the following criteria:

< 30 days (i.e. select * from date where today's date < 30)

30 - 60 days (i.e. select * from date where today's date >= 30 and <= 60)

> 90 days (i.e. select * from date where today's date < 90)

Is there a simple way of acheiving this?

Thanks!

View Replies !   View Related

---