June 28, 2012 at 1:57 pm
here in this table which has lot of data for example i have written 3 value in
the table and i want to write a store proc with two parameter
@startdate and @enddate
CREATE TABLE [dbo].[tblProductInfo](
[ProducttId] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[SaleMemberId] [int] NULL,
[SaleClosedPrice] [decimal](18, 4) NULL,
[SaleDate] [datetime] NULL
)
INSERT INTO [tblProductInfo] ([ProducttId],[ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate])
value(1,1,1,1000,'1-04-2012')
(2,1,2,500,'04-06-2012')
(3,1,4,500,'6-05-2012')
(4,1,5,5000,'6-08-2012')
i have to compare the saledate with @startdate and @enddate
and i have return function for that
create function dbo.generateDates(@startdate date, @enddate date)
returns @dates table(dates date)
as
begin
while @startdate<@enddate
begin
insert into @dates values(@startdate)
set @startdate = dateadd(d, 1, @startdate)
end
return
end
plz tell me how to implement this function in proc and i just tried like this
;with cte as (
select
ProducttId,
ProjectId,
SaleMemberId,
SaleClosedPrice,
SaleDate
from
tblProductInfo
where SaleDate=cast(@startdate as DATE) between
SaleDate< cast(@enddate as DATE)
)
June 28, 2012 at 2:06 pm
i dont think you need your function. i think you just need the create proceture like follows:
CREATE PROCEDURE MyProceture
@StartDate DATE,
@EndDate DATE
AS
SELECT ProducttId,
ProjectId,
SaleMemberId,
SaleClosedPrice,
SaleDate
FROM tblProductInfo
WHERE SaleDate BETWEEN @StartDate AND @EndDate
Unless there is some sort of breakdown and im not understanding the requirements
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
June 28, 2012 at 2:07 pm
I think we are suffering from a language issue, english is obivously not your first language. You have post some good information but it doesn't convey what it is you are trying to accomplish very well.
What would help is some additional sample data for your table and more importantly would be expected results based on a specific set of criteria. The best way to tshow that is to create a table, perhaps called dbo.ExpectedResults. The populate that table using insert statements with the values you wuld expect to be returned by the query based on the sample data and the querys parameters (dates, etc).
June 29, 2012 at 4:34 am
for example
INSERT INTO [tblProductInfo] ([ProducttId],[ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate])
value(1,1,1,1000,'1-04-2012')
(2,1,2,500,'04-06-2012')
(3,1,4,500,'6-05-2012')
(4,1,5,5000,'6-08-2012')
(5,1,3,1000,'1-0 9-2012')
(6,1,2,500,'06-09-2012')
(7,1,4,500,'6-07-2012')
(8,1,5,5000,'6-09-2012')
here i would pass two parameter in my select store proc for example
@fromdate='6-08-2012'
@end date='6-07-2012'
it should bring the datas with allcolumn [ProducttId],[ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate]
present in the data
(4,1,5,5000,'6-08-2012')
(5,1,3,1000,'1-0 9-2012')
(6,1,2,500,'06-09-2012')
(7,1,4,500,'6-07-2012')
(8,1,5,5000,'6-09-2012')
June 29, 2012 at 4:37 am
what is your dateformat MDY or DMY, also your @fromdate and @enddate look like they are the wrong way around
June 29, 2012 at 4:44 am
I'm still having a problem understanding what you are trying to accomplish. It would help if you would provide the expected results in an unabiguous manner, meaning as a table.
Also, to prevent confusion regarding dates it would also be highly beneficial if you provided those in the YYYYMMDD (for example '20120629') as this is unabiguous. The example '20120629' will always be interpreted as June 29, 2012 regardless of the dateformat setting.
June 29, 2012 at 4:46 am
dmy format
June 29, 2012 at 4:50 am
DECLARE @tblProductInfo TABLE (ProductID INT, ProjectID INT, SaleMemberID INT, SaleClosedPrice INT, SaleDate DATE)
INSERT INTO @tblProductInfo VALUES
(1,1,1,1000,'2012-04-01'),
(2,1,2,500,'2012-06-04'),
(3,1,4,500,'2012-05-06'),
(4,1,5,5000,'2012-08-06'),
(5,1,3,1000,'2012-09-01'),
(6,1,2,500,'2012-09-06'),
(7,1,4,500,'2012-07-06'),
(8,1,5,5000,'2012-09-06')
SELECT * FROM @tblProductInfo
DECLARE @FromDate DATE = '2012-06-07', @EndDate DATE = '2012-06-08'
SELECT
*
FROM
@tblProductInfo
WHERE
SaleDate BETWEEN @FromDate AND @EndDate
--OR
SELECT
*
FROM
@tblProductInfo
WHERE
SaleDate >= @FromDate
AND
SaleDate <= @EndDate
June 29, 2012 at 4:56 am
for example in web application if the sure want to view details or records from the table for a particular period of time the user will give @fromdate and @enddate in the proc then the user will see only the details only for that particular period of time he mentioned fromdate and enddate from the table
what are produt soled out who are the members saled the product
June 29, 2012 at 5:06 am
hi lynn pettis i think now u understand my concept
June 29, 2012 at 5:14 am
thanks
lynn pettis,
anthony.green,
cap.hector
for your help
if u have time means
plz have look on this question
http://www.sqlservercentral.com/Forums/Topic1322880-391-1.aspx
and give some idea related to this
June 29, 2012 at 5:16 am
sivag (6/29/2012)
thankslynn pettis,
anthony.green,
cap.hector
for your help
if u have time means
plz have look on this question
http://www.sqlservercentral.com/Forums/Topic1322880-391-1.aspx
and give some idea related to this
http://www.sqlservercentral.com/Forums/Topic1322880-391-1.aspx
June 29, 2012 at 5:17 am
looks like you have already posted the same question a number of times
http://www.sqlservercentral.com/Forums/Topic1320505-391-1.aspx
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply