Is this a job for datepart?

[nedrob]

The query I am working on needs a column that calculates week to date hours
called: "WTD Hours" off of a field called "Bill_Hours". Sunday is always the
week ending date. I am new to this and the date calculations mess me up.
Thanks in advance for any insight.

Ron

CREATE TABLE [dbo].[GM_Master] (
[Branch_ID] [varchar] (255) NULL ,
[Pay_Ending_Date] [datetime] NULL ,
[Bill Amount] [float] NULL ,
[Pay Amount] [float] NULL ,
[Perm_GM] [float] NULL ,

[Bill_Hours] [float] NULL ,
) ON [PRIMARY]
GO

INSERT GM_Master VALUES ('100', '05/06/2001', '5000', '2500', '0', '40')
INSERT GM_Master VALUES ('100', '05/06/2001', '10000', '0', '10000', '0')
INSERT GM_Master VALUES ('100', '05/13/2001', '4000', '1300', '0', '30')
INSERT GM_Master VALUES ('215', '05/13/2001', '4700', '800', '0', '5')
INSERT GM_Master VALUES ('215', '05/27/2001', '1300', '250', '0', '0')
INSERT GM_Master VALUES ('215', '05/27/2001', '8000', '0', '8000', '15')
GO



The Query is:



SELECT Branch_ID,

MTD = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date) AND

MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)

THEN [Bill Amount] - [Pay Amount] ELSE 0 END),

YTD = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date)

THEN [Bill Amount] - [Pay Amount] ELSE 0 END),

'GM MTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date) AND

MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)

THEN Perm_GM ELSE 0 END),

'GM YTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date) THEN Perm_GM ELSE 0 END)

FROM GM_Master

GROUP BY Branch_ID

 

[redlam]

Hi Ron.
If I understand your question correctly, you want to calculate the number of hours that have been accrued in the current week. If this is the case, you can use datepart. I added this calculation to your current query logic below:

SELECT Branch_ID,

MTD = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date) AND

MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)

THEN [Bill Amount] - [Pay Amount] ELSE 0 END),

YTD = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date)

THEN [Bill Amount] - [Pay Amount] ELSE 0 END),

'GM MTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date) AND

MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)

THEN Perm_GM ELSE 0 END),

'GM YTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)

= YEAR(Pay_Ending_Date) THEN Perm_GM ELSE 0 END),

'WTD HRS'= SUM(CASE WHEN DATEPART(WW,CURRENT_TIMESTAMP)

= DATEPART(WW,Pay_Ending_Date) THEN Bill_Hours ELSE 0 END)

FROM GM_Master

GROUP BY Branch_ID

Hope this is helpful.

 

[nedrob]

thanks, this worked and was rather painless. I am in your debt.
Ron