carving results in 4 weeks chunk

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)

carving results in 4 weeks chunk

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-13 : 12:12:29

Given the following table . I am trying to bring back result set of a month, carved up by 4 weeks, week 1, week2, week 3, week4, even if week has blank values for DefinitionValue. Starting week being Sunday. I plan to use START_OF_WEEK_DATE function kindly provided by you folks (thanks Mike V)

CREATE TABLE [dbo].[CommodityDefinitions](
[DefinitionID] [int] IDENTITY(1,1) NOT NULL,
[CommodityID] [int] NOT NULL,
[DefinitionDescr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DefinitionValue] [float] NOT NULL,
[DefinitionDate] [datetime] NOT NULL CONSTRAINT [DF_CommodityDefinitions_CapacityDate] DEFAULT (getdate()),
[BranchID] [int] NOT NULL,
[UserDate] [datetime] NOT NULL CONSTRAINT [DF_CommodityDefinitions_UserDate] DEFAULT (getdate()),
[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_CommodityDefinitions] PRIMARY KEY CLUSTERED
(
[DefinitionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 12:44:09

isnt it enough to group on DATEPART(wk,[DefinitionDate]) for range of month ?

something like


SELECT fields,
SUM(CASE WHEN Seq=1 THEN quantity else 0 end) as week1,
SUM(CASE WHEN Seq=2 THEN quantity else 0 end) as week2,
SUM(CASE WHEN Seq=3 THEN quantity else 0 end) as week3,
SUM(CASE WHEN Seq=4 THEN quantity else 0 end) as week4
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY DATEPART(wk,[DefinitionDate])) AS Seq,
DATEPART(wk,[DefinitionDate]) AS WeekNo
...
 FROM [dbo].[CommodityDefinitions]
WHERE [DefinitionDate] BETWEEN DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) AND DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)
)t
GROUP BY fields...

replace fields by actual grouping fiuelds and quantity by summating measure

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-13 : 13:15:12

Visahk

you are one bad boy! except I am looking at having the result set coming back with the values 'week1' 'week2' etc as actual field values since I am passing this on to a Graph that seperates the data based on that key value. I will twek it a bit and see what I can bring up.

Awesome!

Thanks very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:28:25

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources