Post reply

Datefunctions

  • July 17, 2009 at 12:21 pm

    #136706

    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

    #1026385

    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.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 17, 2009 at 12:48 pm

    #1026387

    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

    #1026434

    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

    #1026436

    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

    #1026437

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 20, 2009 at 4:49 pm

    #1027339

    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

    #1027341

    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

    #1027349

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 20, 2009 at 5:12 pm

    #1027352

    Ah... Jedi mind games... I see now... "other thread". 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 20, 2009 at 5:36 pm

    #1027363

    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

    #1027380

    Heh... no... I'm saying you're too late... no mind left. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 20, 2009 at 8:25 pm

    #1027389

    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

    #1027390

    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