Need Help in Date Functions

• kaladharreddy15

  SSC-Addicted

  Points: 476

  More actions

  July 20, 2009 at 7:32 am

  #133097

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  July 20, 2009 at 2:10 pm

  #1027279

  Questions:

  Is the start date you entered considered to be day 1 of week 1? In other words, if you enter a start date which is a Wednesday, will week 1 run until the following Tuesday? Or does your week arbitrarily end on a Saturday or Sunday?

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 20, 2009 at 4:47 pm

  #1027338

  This is a duplicate post. You still haven't answered the request for information here.

  Please do not post further answers here, instead go to the thread linked above.

• Jeff Moden

  SSC Guru

  Points: 1004312

  More actions

  July 20, 2009 at 5:14 pm

  #1027354

  . (7/20/2009)

  ---

  Questions:

  Is the start date you entered considered to be day 1 of week 1? In other words, if you enter a start date which is a Wednesday, will week 1 run until the following Tuesday? Or does your week arbitrarily end on a Saturday or Sunday?

  Bob... did you change your name to "dot"? 😉

  --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)