How can I compare years?

[nedrob]

I'm relatively new to Sql and have been working on the following problem for two weeks (I'm frustrated and throwing the towel in. I need to add two fields to the following query. Any help would be greatly appreciated.
1. 'YTD vs. LYTD' = I need this to compare this year to date GMP$ to last year to date GMP$.

2 GMP% YTD VS GMP% LYTD = I need to display the % increase or decrease from this years total GMP$ vs. Last years GMP$

The table:

CREATE TABLE [dbo].[Master_Business] (
[Territory_ID] [varchar] (255) NULL ,
[Branch_ID] [varchar] (255) NULL ,
[Branch] [varchar] (255) NOT NULL ,
[Specialty] [varchar] (255) NULL ,
[Type] [varchar] (255) NULL ,
[Pay_Ending_Date] [datetime] NULL ,
[WorkCompAmt] [float] NULL ,
[Gross Pay ] [float] NULL ,
[Bill Amount] [float] NULL ,
[Pay Hours] [float] NULL ,
[Bill Hours] [float] NULL ,
[Federal] [float] NULL ,
[State] [float] NULL ,
[Burden] [float] NULL ,
[Perm_GM] [float] NULL ,

) ON [PRIMARY]
GO

INSERT Master_Business VALUES ('30100','300','Bolingbrook','Banner OS','I','2000-06-26 00:00:00','10.00','485.00','688.00','40.00','40.00','53.00','25.00','8.00','0')
INSERT Master_Business VALUES ('30100','300','Bolingbrook','Banner OS','I','2000-06-14 00:00:00','10.00','485.00','688.00','40.00','40.00','53.00','25.00','8.00','0')
INSERT Master_Business VALUES ('30100','300','Bolingbrook','Banner OS','I','2000-06-14 00:00:00','0','0','0','0','0','0','0','0','5000')
INSERT Master_Business VALUES ('30100','300','Bolingbrook','Banner OS','I','2001-06-26 00:00:00','10.00','400.00','888.00','43.00','41.00','57.00','24.00','3.00','0')
INSERT Master_Business VALUES ('30100','300','Bolingbrook','Banner OS','I','2001-06-14 00:00:00','10.00','485.00','688.00','40.00','40.00','53.00','25.00','8.00','0')
INSERT Master_Business VALUES ('30100','300','Bolingbrook','Banner OS','I','2001-06-14 00:00:00','0','0','0','0','0','0','0','0','8000')
GO

the query:

SELECT DISTINCT
Specialty, Branch,
'Temp Hrs WTD Last WK' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Hours] ELSE 0 END),
'Temp Sales WTD' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
[Temp Sales MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
[Temp Sales YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
'Temp GM$ Last WK' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
[Temp GM$ MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
[Temp GM$ YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
'Perm Sales WTD' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Perm_GM] ELSE 0 END),
[Perm Sales 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),
[Perm Sales YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) THEN [Perm_GM] ELSE 0 END),
'GMP MTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN ([Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden])) + Perm_GM ELSE 0 END),
'GMP YTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN ([Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden])) + Perm_GM ELSE 0 END),
[Perm Falloffs MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
AND [Type] = 'C' THEN [Perm_GM] ELSE 0 END),
'Perm Falloffs YTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
[Type] = 'C' THEN [Perm_Gm] ELSE 0 END)
FROM Master_Business
GROUP BY Specialty, Branch

 

[Bernadette]

Hi There

Check out the DateDiff Function in books on line. It is used to compare dates.

Hope This Helps

Bernadette

 

[tlbroadbent]

I suggest that you create a view from the current query. Add one more column to the view than you have in the query. The new column is highlighted below. Then use the next query to SELECT from the view adding the additional columns for comparison.

NOTE: I believe your query is overly complex and should be designed differently. If you are interested I can explain how I would have designed the query or queries.

New Query to SELECT from the view:
SELECT *,
[GMP YTD]-[GMP LYTD] As [GMP CHG],
CAST(CASE [GMP LYTD] WHEN 0 THEN 100 ELSE 100.*([GMP YTD]-[GMP LYTD])/[GMP LYTD] END As Decimal(6,2)) As [GMP CHG%]
FROM vMasterBusinessSummary

Create the view:
CREATE view vMasterBusinessSummary As
SELECT
Specialty, Branch,
[Temp Hrs WTD Last WK] = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Hours] ELSE 0 END),
[Temp Sales WTD] = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
[Temp Sales MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
[Temp Sales YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
[Temp GM$ Last WK] = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
[Temp GM$ MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
[Temp GM$ YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
[Perm Sales WTD] = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Perm_GM] ELSE 0 END),
[Perm Sales 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),
[Perm Sales YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) THEN [Perm_GM] ELSE 0 END),
[GMP MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN ([Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden])) + Perm_GM ELSE 0 END),
[GMP YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN ([Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden])) + Perm_GM ELSE 0 END),
[GMP LYTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP) -1
= YEAR(Pay_Ending_Date)
THEN ([Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden])) + Perm_GM ELSE 0 END),
[Perm Falloffs MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
AND [Type] = 'C' THEN [Perm_GM] ELSE 0 END),
[Perm Falloffs YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
[Type] = 'C' THEN [Perm_Gm] ELSE 0 END)
FROM Master_Business
GROUP BY Specialty, Branch
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Guest_imported]

Terry,
Thanks for the help. I'm learning without the guidence of a mentor. Any sugestions on how you would have done this querey would be greatly appreciated.
Thanks,
Ron