Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trying to join tables on different dates Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 2:12 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:44 AM
Points: 701, Visits: 1,731
Hi,

I have a project I'm working on that has two SQL tables. I need to create a query that joins the two tables by org level and by date.

Table 1 has the date columns like this :

January February March - etc

And in each of these month columns is an overtime amount for that month (in dollars).

Table2 has it's date columns that are 'real' date time columns like this ' 2013-03-22 00:00:00.000'. These are stamped every time a payroll is run (every week or so)


What I need to do is write a query that joins table1 to table2 so that I join all the months in table 2 and sum the amount.

In other words January I would need to grab all the 01 records from table 2 and then join to the January column and then compare the amounts. Table 1 has the budgeted amounts for the year, table 2 is all the actual amounts.




Post #1474792
Posted Wednesday, July 17, 2013 2:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
krypto69 (7/17/2013)
Hi,

I have a project I'm working on that has two SQL tables. I need to create a query that joins the two tables by org level and by date.

Table 1 has the date columns like this :

January February March - etc

And in each of these month columns is an overtime amount for that month (in dollars).

Table2 has it's date columns that are 'real' date time columns like this ' 2013-03-22 00:00:00.000'. These are stamped every time a payroll is run (every week or so)


What I need to do is write a query that joins table1 to table2 so that I join all the months in table 2 and sum the amount.

In other words January I would need to grab all the 01 records from table 2 and then join to the January column and then compare the amounts. Table 1 has the budgeted amounts for the year, table 2 is all the actual amounts.



You have been around here long enough....post some ddl and sample data along with your desired output.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1474798
Posted Wednesday, July 17, 2013 2:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
Use DATENAME


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474808
Posted Wednesday, July 17, 2013 3:31 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:44 AM
Points: 701, Visits: 1,731
Okay Sean:

Here is table 1 - 'overtime budget 2013'


CREATE TABLE [dbo].[Overtime Budget 2013](
[Org Level] [nvarchar](50) NULL,
[Overtime Code] [nchar](10) NULL,
[January] [money] NULL,
[February] [money] NULL,
[March] [money] NULL,
[April] [money] NULL,
[May] [money] NULL,
[June] [money] NULL,
[July] [money] NULL,
[August] [money] NULL,
[September] [money] NULL,
[October] [money] NULL,
[Novemeber] [money] NULL,
[December] [money] NULL,
[Total] [money] NULL
) ON [PRIMARY]


Here's some sample date for the above table:

INSERT Into [Overtime Budget 2013]
SELECT 'WSIDOE','5204 ','1579588.23','1578296.23','1964371.53'
SELECT 'WSIOTH','5204 ','10979.65','11212.00','15404.53'
SELECT 'WSIWMA','5204 ','121070.56','121070.05','149569.32'
SELECT 'WSIUNY','5204 ','1287.29','1287.68','1609.60'
SELECT 'WSIZHR','5204 ','553.85','553.85','692.31'
SELECT 'WSIZSS','5204 ','96.15','96.15','120.19'


The second table is actually a query that looks like this:

/* Summary Overtime by Employee */
SELECT
cast(left(pehPErcontrol,8) as datetime) AS [Period Control Date],
--ltrim(cmpCompanyName) AS [Company Name],
rtrim(eepNameLast) +
--isnull(' '+rtrim(CodDesc), '') +
', ' + rtrim(eepNameFirst) +
' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,
eepNameLast AS [Last Name],
IsNull(eepNameSuffix,'') AS [Suffix],
eepNameFirst AS [First Name],
IsNull(eepNameMiddle,'') AS [Middle Name],
--eepSSN,
--pehBatchID AS [Batch ID],
pehCurAmt AS [Current Amount],
pehCurHrs AS [Current Hours],
pehCoID AS [Company ID],
pehEEID AS [EE ID],
pehEmpNo AS [Emp No],
pehLocation AS [Location],
pehJobCode AS [Job Code],
pehOrgLvl1 AS [Org Level 1],
pehOrgLvl2 AS [Org Level 2],
pehOrgLvl3 AS [Org Level 3],
pehOrgLvl4 AS [Org Level 4],
pehPayGroup AS [Pay Group],
pehProject AS [Project],
pehShfShiftAmt AS [Shift Amount],
pehearncode AS [Earn Code],
--new
[overtime code] as [Overtime Code],
[Org Level] as [Org Level],
[January] as [January]
FROM EmpPers JOIN pearhist
ON pehEEID = eepEEID
join GSHQSHAREPOINT.EXECUTIVEDASHBOARD.DBO.[OVERTIME BUDGET 2013] ON [org level] = pehOrgLvl2
where pehearncode = '0002'
AND pehPerControl > '201301011'
--AND pehPerControl > '201301011'
--AND pehOrgLvl2 like '%SRS'
AND pehOrgLvl2 like '%ZSW'

You can see where I'm joining by org level but need to add the months/date to complete.




Post #1474819
Posted Wednesday, July 17, 2013 3:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
You can Unpivot your table to be able to join it.
CREATE TABLE #Overtime_Budget_2013(
[Org_Level] [nvarchar](50) NULL,
[Overtime Code] [nchar](10) NULL,
[January] [money] NULL,
[February] [money] NULL,
[March] [money] NULL
)


INSERT Into #Overtime_Budget_2013(Org_Level, [Overtime Code], January, February, March)
SELECT 'WSIDOE','5204 ','1579588.23','1578296.23','1964371.53' UNION ALL
SELECT 'WSIOTH','5204 ','10979.65','11212.00','15404.53' UNION ALL
SELECT 'WSIWMA','5204 ','121070.56','121070.05','149569.32' UNION ALL
SELECT 'WSIUNY','5204 ','1287.29','1287.68','1609.60' UNION ALL
SELECT 'WSIZHR','5204 ','553.85','553.85','692.31' UNION ALL
SELECT 'WSIZSS','5204 ','96.15','96.15','120.19'

SELECT Org_Level, [Overtime Code], month, value
FROM #Overtime_Budget_2013
--CROSS APPLY( VALUES(January, 1), (February, 2), (March, 3))x(value, month )
UNPIVOT ( value FOR month IN (January, February, March)) unpvt

DROP TABLE #Overtime_Budget_2013

The commented line is a different approach to unpivot, but it's up to you to test which is better in your case.(More info)



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474825
Posted Wednesday, July 17, 2013 6:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721

IF OBJECT_ID('tempdb..#OvertimeBudget2013') IS NOT NULL
DROP TABLE #OvertimeBudget2013

CREATE TABLE #OvertimeBudget2013
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Org Level] [nvarchar](50) NULL
,[Overtime Code] [nchar](10) NULL
,[JanuaryBudget] [money] NULL
,[FebruaryBudget] [money] NULL
,[MarchBudget] [money] NULL
,[AprilBudget] [money] NULL
,[MayBudget] [money] NULL
,[JuneBudget] [money] NULL
,[JulyBudget] [money] NULL
,[AugustBudget] [money] NULL
,[SeptemberBudget] [money] NULL
,[OctoberBudget] [money] NULL
,[NovemeberBudget] [money] NULL
,[DecemberBudget] [money] NULL
,[ActualBudget] [money] NULL
,PRIMARY KEY CLUSTERED (ID)
)

INSERT Into #OvertimeBudget2013
([Org Level],[Overtime Code],[JanuaryBudget],[FebruaryBudget],[MarchBudget])
SELECT 'WSIDOE','5204','1579588.23','1578296.23','1964371.53' UNION ALL
SELECT 'WSIOTH','5204','10979.65','11212.00','15404.53' UNION ALL
SELECT 'WSIWMA','5204','121070.56','121070.05','149569.32' UNION ALL
SELECT 'WSIUNY','5204','1287.29','1287.68','1609.60' UNION ALL
SELECT 'WSIZHR','5204','553.85','553.85','692.31' UNION ALL
SELECT 'WSIZSS','5204','96.15','96.15','120.19'

SELECT * FROM #OvertimeBudget2013


IF OBJECT_ID('tempdb..#EmpPers') IS NOT NULL
DROP TABLE #EmpPers

CREATE TABLE #EmpPers (
[ID] INT IDENTITY(1,1) NOT NULL
,[pehPErcontrol] DATETIME NOT NULL
,[eepNameLast] [nvarchar](50) NULL
,[CodDesc] [nvarchar](50) NULL
,[eepNameFirst] [nvarchar](50) NULL
,[eepNameMiddle] [nvarchar](50) NULL
,[eepNameSuffix] [nvarchar](50) NULL
,[Org Level] [nvarchar](50) NULL
,[Overtime Code] [nchar](10) NULL
,[JanuaryActual] [money] NULL
,[FebruaryActual] [money] NULL
,[MarchActual] [money] NULL
,PRIMARY KEY (ID))

;WITH cteEmpData
([pehPErcontrol],[eepNameLast],[CodDesc],[eepNameFirst],[eepNameMiddle],[eepNameSuffix],[Org Level],[Overtime Code],[JanuaryActual],[FebruaryActual],[MarchActual])
AS
(
SELECT GETDATE(),'Bush','1','George','W','Jr.','5204','WSIDOE','157588.23','158296.23','196471.53' UNION ALL
SELECT GETDATE(),'Jones','2','George','T','Sr.','5204','WSIUNY','15758.23','18296.23','19641.53' UNION ALL
SELECT GETDATE(),'Smith','3','April','Mae','','5204','WSIZSS','1578.23','1582.23','19641.53'
)
INSERT INTO #EmpPers
([pehPErcontrol],[eepNameLast],[CodDesc],[eepNameFirst],[eepNameMiddle],[eepNameSuffix],[Org Level],[Overtime Code],[JanuaryActual],[FebruaryActual],[MarchActual])
SELECT
CAST(LEFT(pehPErcontrol,8) AS DATETIME) AS [Period Control Date]
,RTRIM(eepNameLast) + --isnull(' '+rtrim(CodDesc), '') +
', ' + RTRIM(eepNameFirst) + ' ' + COALESCE(SUBSTRING(eepNameMiddle,1,1) + '.','') AS Name
,eepNameLast AS [Last Name]
,ISNULL(eepNameSuffix,'') AS [Suffix]
,eepNameFirst AS [First Name]
,ISNULL(eepNameMiddle,'') AS [Middle Name]
--new
,[overtime code] AS [Overtime Code]
,[Org Level] AS [Org Level]
,[JanuaryActual]
,[FebruaryActual]
,[MarchActual]
FROM
cteEmpData

SELECT * FROM #EmpPers AS ep


SELECT
r.[Org Level]
,ISNULL(r.JanuaryBudget,0) AS JanuaryBudget
,ISNULL(r.JanuaryActual,0) AS JanuaryActual
,ISNULL(SUM(r.JanuaryActual-r.JanuaryBudget) OVER (PARTITION BY r.[Org Level]),0) AS [+/-]
,ISNULL(r.FebruaryBudget,0) AS FebruaryBudget
,ISNULL(r.FebruaryActual,0) AS FebruaryActual
,ISNULL(SUM(r.FebruaryActual-r.FebruaryBudget) OVER (PARTITION BY r.[Org Level]),0) AS [+/-]
,ISNULL(r.MarchBudget,0) AS MarchBudget
,ISNULL(r.MarchActual,0) AS MarchActual
,ISNULL(SUM(r.MarchActual-r.MarchBudget) OVER (PARTITION BY r.[Org Level]),0) AS [+/-]
FROM
(

SELECT
bud.[Org Level]
,SUM(CAST(bud.[JanuaryBudget] AS DECIMAL(9,2))) OVER (PARTITION BY bud.[Org Level]) AS [JanuaryBudget]
,SUM(CAST(bud.[FebruaryBudget] AS DECIMAL(9,2))) OVER (PARTITION BY bud.[Org Level]) AS [FebruaryBudget]
,SUM(CAST(bud.[MarchBudget] AS DECIMAL(9,2))) OVER (PARTITION BY bud.[Org Level]) AS [MarchBudget]
,SUM(CAST(ep.[JanuaryActual] AS DECIMAL(9,2))) OVER (PARTITION BY ep.[Org Level]) AS [JanuaryActual]
,SUM(CAST(ep.[FebruaryActual] AS DECIMAL(9,2))) OVER (PARTITION BY ep.[Org Level]) AS [FebruaryActual]
,SUM(CAST(ep.[MarchActual] AS DECIMAL(9,2))) OVER (PARTITION BY ep.[Org Level]) AS [MarchActual]
FROM
#OvertimeBudget2013 AS bud
LEFT OUTER JOIN
#EmpPers AS ep
ON ep.[Org Level] = bud.[Org Level]
AND ep.[Overtime Code] = bud.[Overtime Code]
) r


 
Post #1474845
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse