July 17, 2009 at 12:21 pm
Hi Friends,
I need your's help on
This is my requirement there are two date parameters (@Startdate and @Enddate), when we select start date and end date
No. of Claims should display like below Template, I need help for week, start date and end date
week means entered date Which no.of the week from @startDate to @Enddate and In the below template table startdate and dates are that weeks startdate(Monday) and enddate(friday)
Output shown should be organized by week (please see below) based upon the date range
Validator Week Start Date End Date Monday Tuesday Wednesday Thursday Friday Total
Name1 1 6/29/2009 7/3/2009 5 7 4 3 4 23
Name2 2 7/6/2009 7/10/2009 4 3 3 3 2 15
Thanks in Advance,
Kala
July 17, 2009 at 12:44 pm
Please provide table definition as well as sample data and expected result.
You also should include what you've tried so far and what you're struggling with.
For details on how to provide sample data please see the link in my signature.
July 17, 2009 at 12:48 pm
So, how about DDL for the table(s), sample data, expected results based on the sample data, and most importantly what you have done so far to solve your problem.
Need help with this request? Read the first article referenced below in my signature block.
July 17, 2009 at 2:10 pm
Hi Friends,
I need your's help on
This is my requirement there are two date parameters (@Startdate and @Enddate), when we select start date and end date
Tables Definations:
#TableA:
CREATE TABLE [#TABLEA](
[loginid] [int] IDENTITY(1,1) NOT NULL,
[firstname] [nvarchar](50) NULL,
[lastname] [nvarchar](50) NULL,
)
#TABLEB:
CREATE TABLE [TableB](
[TPPayRequestsId] [int] IDENTITY(1,1) NOT NULL,
[Reviewed_By] [nvarchar](50) NULL,
[RDateEntered] [datetime] NULL
[ValidatedBy] [int] NULL
)
Here I am providing my procedure:
CREATE PROCEDURE [SAMPLE]
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SELECT A.firstname+' '+A.lastname as Validator, convert(varchar(10),B.rdateentered,101) as Date,
DATEPART(WEEKDAY,B.rdateentered) as Days ,count(*) as Claims
FROM #TABLEA A INNER JOIN #TableB B ON A.loginid=B.validatedby
WHERE B.rdateentered BETWEEN @StartDate AND @EndDate
GROUP BY A.firstname+' '+A.lastname,B.rdateentered
END
Output shown should be organized by week (please see below) based upon the date range
Expected Output
Validator Week Start Date End Date Monday Tuesday Wednesday Thursday Friday Total
Name1 1 6/29/2009 7/3/2009 5 7 4 3 4 23
Name2 2 7/6/2009 7/10/2009 4 3 3 3 2 15
No. of Claims should display like above Template(Which displayed below the days), I need help for week, start date and end date
week means "rdateentered" date comes Which no.of the week from @startDate to @Enddate and
for that week startdate and enddate like startdate(Monday) and enddate(friday) like in above table startdate and enddate
can you help me out
Thanks in Advance,
Kala
July 17, 2009 at 2:11 pm
Hi Friends,
I need your's help on
This is my requirement there are two date parameters (@Startdate and @Enddate), when we select start date and end date
Tables Definations:
#TableA:
CREATE TABLE [#TABLEA](
[loginid] [int] IDENTITY(1,1) NOT NULL,
[firstname] [nvarchar](50) NULL,
[lastname] [nvarchar](50) NULL,
)
#TABLEB:
CREATE TABLE [TableB](
[TPPayRequestsId] [int] IDENTITY(1,1) NOT NULL,
[Reviewed_By] [nvarchar](50) NULL,
[RDateEntered] [datetime] NULL
[ValidatedBy] [int] NULL
)
Here I am providing my procedure:
CREATE PROCEDURE [SAMPLE]
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SELECT A.firstname+' '+A.lastname as Validator, convert(varchar(10),B.rdateentered,101) as Date,
DATEPART(WEEKDAY,B.rdateentered) as Days ,count(*) as Claims
FROM #TABLEA A INNER JOIN #TableB B ON A.loginid=B.validatedby
WHERE B.rdateentered BETWEEN @StartDate AND @EndDate
GROUP BY A.firstname+' '+A.lastname,B.rdateentered
END
Output shown should be organized by week (please see below) based upon the date range
Expected Output
Validator Week Start Date End Date Monday Tuesday Wednesday Thursday Friday Total
Name1 1 6/29/2009 7/3/2009 5 7 4 3 4 23
Name2 2 7/6/2009 7/10/2009 4 3 3 3 2 15
No. of Claims should display like above Template(Which displayed below the days), I need help for week, start date and end date
week means "rdateentered" date comes Which no.of the week from @startDate to @Enddate and
for that week startdate and enddate like startdate(Monday) and enddate(friday) like in above table startdate and enddate
can you help me out
Thanks in Advance,
Kala
July 17, 2009 at 2:12 pm
kaladharreddy15 (7/17/2009)
Hi Friends,I need your's help on
This is my requirement there are two date parameters (@Startdate and @Enddate), when we select start date and end date
No. of Claims should display like below Template, I need help for week, start date and end date
week means entered date Which no.of the week from @startDate to @Enddate and In the below template table startdate and dates are that weeks startdate(Monday) and enddate(friday)
Output shown should be organized by week (please see below) based upon the date range
Validator Week Start Date End Date Monday Tuesday Wednesday Thursday Friday Total
Name1 1 6/29/2009 7/3/2009 5 7 4 3 4 23
Name2 2 7/6/2009 7/10/2009 4 3 3 3 2 15
Thanks in Advance,
Kala
Kala... You're kind of new on the forums here so let me explain why it looks like folks are kind of jumping on you. Most folks on these forums prefer to provide good solid answers in the form of tested code. The problem is that it does take a bit of time to setup data for such tests and with so few helping so many, it would really help if you provided the data and a table creation statement both in a readily consumable format. The first article in Lynn's signature line tells you exactly how to do all of that in a pretty easy manner. You'll be amazed at how quick fully tested answers come up when you make it easy on folks.
Otherwise, you might get answers like "you shouldn't be doing this in SQL" or "all you need to do is an EAV conversion using a Cross-tab or Pivot" or someone will post a cursor, etc, etc. 😉
--Jeff Moden
July 20, 2009 at 4:49 pm
From the OP's other thread:
kaladharreddy15 (7/20/2009)
Hi Friends,I need your help to display week number n that weeks startdate and enddate
This is my requirement there are two date parameters, when we select start date and end date
No. of Claims should display like below Template, "RDateEntered" date comes under witch week and that weeks startdate and enddate should be display like below template, This "RDateEntered" comes from "#MYTABLEB " Table.
Output shown should be organized by week (please see below) based upon the date range
Validator Week Start Date End Date Monday Tuesday Wednesday Thursday Friday Total
Cheryl Lawson 1 6/29/2009 7/3/2009 5 7 4 3 4 23
Cheryl Lawson 2 7/6/2009 7/10/2009 4 3 3 3 2 15
These are the tables and data, even I mentioning my procedure
CREATE TABLE [#MYTABLEA](
[loginid] [int] IDENTITY(1,1) NOT NULL,
[firstname] [nvarchar](50) NULL,
[lastname] [nvarchar](50) NULL,
)
INSERT INTO (loginid, firstname, lastname)
SELECT 15 , ‘Sarah’ , ‘Jones’ UNION ALL
SELECT 12 , ‘John’ , ‘Smith’ UNION ALL
SELECT 10 , ‘CUNY’ , ‘RECED’ UNION ALL
SELECT 7 , ‘Sue’ , ‘Lee’
CREATE TABLE [#MYTABLEB](
[TPPayRequestsId] [int] IDENTITY(1,1) NOT NULL,
[RDateEntered] [datetime] NULL
[ValidatedBy] [int] NULL
)
INSERT INTO (TPPayRequestsId, RDateEntered, ValidatedBy)
SELECT 1 , 2008-12-19 , 15 UNION ALL
SELECT 2 , 2008-10-31 , 12 UNION ALL
SELECT 3 , 2008-12-10 , 10 UNION ALL
SELECT 4 , 2009-01-13 , 7
Stored Procedure:
CREATE PROCEDURE [SAMPLE]
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SELECT A.firstname+' '+A.lastname as Validator, convert(varchar(10),B.rdateentered,101) as Date,
DATEPART(WEEKDAY,B.rdateentered) as Days ,count(*) as Claims
FROM # MYTABLEA as A
INNER JOIN # MYTABLEB as B ON A.loginid=B.validatedby
WHERE B.rdateentered BETWEEN @StartDate AND @EndDate
GROUP BY A.firstname+' '+A.lastname,B.rdateentered
END
Thanks in Advance
Mallav
July 20, 2009 at 4:53 pm
Looking it over, there still appears to be some information missing to help answer the question fully. I'll have to look at in more depth when I get home.
July 20, 2009 at 5:07 pm
Looks like the OP posted as I was writing my reply. And, is my mind playing tricks on me or are the INSERTs that you have in the quote gone from the posts?
--Jeff Moden
July 20, 2009 at 5:12 pm
Ah... Jedi mind games... I see now... "other thread". 😉
--Jeff Moden
July 20, 2009 at 5:36 pm
Jeff Moden (7/20/2009)
Ah... Jedi mind games... I see now... "other thread". 😉
Are you saying that I shouldn't play with what's left of an old mans mind?? Trust me, I'm not that far behind. 😉
July 20, 2009 at 6:48 pm
Heh... no... I'm saying you're too late... no mind left. 😛
--Jeff Moden
July 20, 2009 at 8:25 pm
kaladharreddy15, when you post code for people to use/test, particularly DDL and load procedures, please be sure you have tested it and it works. I started looking at your code and have had to fix it to make it work.
July 20, 2009 at 8:31 pm
Also, please be sure that your sample data and expected results match. In the code provided, they aren't even close.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply