Need Help in Date Functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help in Date Functions

Author

Topic

Kaladhar
Starting Member

3 Posts

Posted - 2009-07-20 : 09:42:18

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 11:43:42

[code]DECLARE @People TABLE
(
LoginID INT,
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

INSERT @People
SELECT 15, 'Sarah', 'Jones' UNION ALL
SELECT 12, 'John', 'Smith' UNION ALL
SELECT 10, 'CUNY', 'RECED' UNION ALL
SELECT 7, 'Sue', 'Lee'

DECLARE @Sample TABLE
(
ID INT,
Date DATETIME,
byID INT
)

INSERT @Sample
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

;WITH Yak (byID, startWeek, endWeek, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
AS (
SELECT p.byID,
p.startWeek,
p.endWeek,
p.[0] AS Monday,
p.[1] AS Tuesday,
p.[2] AS Wednesday,
p.[3] AS Thursday,
p.[4] AS Friday,
p.[5] AS Saturday,
p.[6] AS Sunday
FROM (
SELECT byID,
Date,
DATEADD(DAY, DATEDIFF(DAY, 0, Date) / 7 * 7, 0) AS startWeek,
DATEADD(DAY, DATEDIFF(DAY, 0, Date) / 7 * 7, 6) AS endWeek,
DATEDIFF(DAY, 0, Date) % 7 AS dayWeek
FROM @Sample
) AS s
PIVOT (
COUNT(Date)
FOR dayWeek IN ([0], [1], [2], [3], [4], [5], [6])
) AS p
)

SELECT p.FirstName + ' ' + p.LastName AS Validator,
ROW_NUMBER() OVER (PARTITION BY y.byID ORDER BY y.startWeek) AS [Week],
y.startWeek AS [Start Date],
y.endWeek AS [End Date],
y.Monday,
y.Tuesday,
y.Wednesday,
y.Thursday,
y.Friday,
y.Saturday,
y.Sunday
FROM @People AS p
INNER JOIN Yak AS y ON y.byID = p.LoginID[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources