PLEASE HELP ME WITH MY QUERY - 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)

PLEASE HELP ME WITH MY QUERY

Author

Topic

dockies
Starting Member

25 Posts

Posted - 2007-11-30 : 21:39:47

Hi can anyone here help me with my query...
I need to combine this 2 query result into 1 dataset.

heres my original 2 query:

SELECT
WGTYPE.WAGE_CODE AS WAGE,
TRFIX.DESCRIPTION AS WAGE_DESC,
TRFIX.QUANTITY AS UNITS,TRFIX.RATE,
TRFIX.AMOUNT,
TRFIX.AMOUNT * TBL.MONTHS_ON_BOARD AS YEAR_TO_DATE
FROM PAYROLL.EMPLOYEES EMP
INNER JOIN PAYROLL.TRANSACTIONSFIXED TRFIX ON TRFIX.EMPLOYEE_UID = EMP.EMPLOYEE_UID
INNER JOIN PAYROLL.WAGETYPES WGTYPE ON WGTYPE.WAGETYPE_UID = TRFIX.WAGETYPE_UID
INNER JOIN
(SELECT SV.EMPLOYEE_UID,DATEDIFF(month, CONVERT(VARCHAR,SV.SIGN_ON_DATE,101) + ' 00:00:00', CONVERT(VARCHAR,SV.SIGN_OFF_DATE,101) + ' 23:58:59') AS MONTHS_ON_BOARD
FROM PAYROLL.EMPLOYEES EMP INNER JOIN PAYROLL.SERVICES SV ON SV.SERVICE_UID = EMP.SERVICE_UID
WHERE EMP.EMPLOYEE_UID = 9) TBL ON TBL.EMPLOYEE_UID = 9

WHERE
EMP.EMPLOYEE_UID = 9 AND
TRFIX.CLOSE_PERIOD BETWEEN '2007-OCTOBER-01' AND
(SELECT CONVERT(VARCHAR,dateadd(mm,1,CONVERT(DATETIME,('2007-OCTOBER-01')) - day(CONVERT(DATETIME,'2007-OCTOBER-01'))+1)-1,101) + ' 23:59:59')
AND TRFIX.MARK = 1

SELECT
WGTYPE.WAGE_CODE AS WAGE,
TRVAR.DESCRIPTION AS WAGE_DESC,
TRVAR.QUANTITY AS UNITS,TRVAR.RATE,
TRVAR.AMOUNT,
TRVAR.AMOUNT * TBL.MONTHS_ON_BOARD AS YEAR_TO_DATE
FROM PAYROLL.EMPLOYEES EMP
INNER JOIN PAYROLL.TRANSACTIONSVARIABLES TRVAR ON TRVAR.EMPLOYEE_UID = EMP.EMPLOYEE_UID
INNER JOIN PAYROLL.WAGETYPES WGTYPE ON WGTYPE.WAGETYPE_UID = TRVAR.WAGETYPE_UID
INNER JOIN
(SELECT SV.EMPLOYEE_UID,DATEDIFF(month, CONVERT(VARCHAR,SV.SIGN_ON_DATE,101) + ' 00:00:00', CONVERT(VARCHAR,SV.SIGN_OFF_DATE,101) + ' 23:58:59') AS MONTHS_ON_BOARD
FROM PAYROLL.EMPLOYEES EMP INNER JOIN PAYROLL.SERVICES SV ON SV.SERVICE_UID = EMP.SERVICE_UID
WHERE EMP.EMPLOYEE_UID = 9) TBL ON TBL.EMPLOYEE_UID = 9

WHERE
EMP.EMPLOYEE_UID = 9 AND
TRVAR.CLOSE_PERIOD BETWEEN '2007-OCTOBER-01' AND
(SELECT CONVERT(VARCHAR,dateadd(mm,1,CONVERT(DATETIME,('2007-OCTOBER-01')) - day(CONVERT(DATETIME,'2007-OCTOBER-01'))+1)-1,101) + ' 23:59:59')
AND TRVAR.MARK = 1

--------------------------------------------------------------
So far this is what i have:

select tbl.WAGE,tbl2.WAGE
from payroll.employees EMPS
INNER JOIN
(
SELECT
EMP.EMPLOYEE_UID,WGTYPE.WAGE_CODE AS WAGE,
TRFIX.DESCRIPTION AS WAGE_DESC,
TRFIX.QUANTITY AS UNITS,TRFIX.RATE,
TRFIX.AMOUNT,
TRFIX.AMOUNT * TBL.MONTHS_ON_BOARD AS YEAR_TO_DATE
FROM PAYROLL.EMPLOYEES EMP
INNER JOIN PAYROLL.TRANSACTIONSFIXED TRFIX ON TRFIX.EMPLOYEE_UID = EMP.EMPLOYEE_UID
INNER JOIN PAYROLL.WAGETYPES WGTYPE ON WGTYPE.WAGETYPE_UID = TRFIX.WAGETYPE_UID
INNER JOIN
(SELECT SV.EMPLOYEE_UID,DATEDIFF(month, CONVERT(VARCHAR,SV.SIGN_ON_DATE,101) + ' 00:00:00', CONVERT(VARCHAR,SV.SIGN_OFF_DATE,101) + ' 23:58:59') AS MONTHS_ON_BOARD
FROM PAYROLL.EMPLOYEES EMP INNER JOIN PAYROLL.SERVICES SV ON SV.SERVICE_UID = EMP.SERVICE_UID
WHERE EMP.EMPLOYEE_UID = 9) TBL ON TBL.EMPLOYEE_UID = 9
WHERE
EMP.EMPLOYEE_UID = 9 AND
TRFIX.CLOSE_PERIOD BETWEEN '2007-OCTOBER-01' AND
(SELECT CONVERT(VARCHAR,dateadd(mm,1,CONVERT(DATETIME,('2007-OCTOBER-01')) - day(CONVERT(DATETIME,'2007-OCTOBER-01'))+1)-1,101) + ' 23:59:59')
AND TRFIX.MARK = 1
) TBL on TBL.EMPLOYEE_UID = EMPS.EMPLOYEE_UID

INNER JOIN
(
SELECT
EMP.EMPLOYEE_UID,
WGTYPE.WAGE_CODE AS WAGE,
TRVAR.DESCRIPTION AS WAGE_DESC,
TRVAR.QUANTITY AS UNITS,TRVAR.RATE,
TRVAR.AMOUNT,
TRVAR.AMOUNT * TBL.MONTHS_ON_BOARD AS YEAR_TO_DATE

FROM PAYROLL.EMPLOYEES EMP
INNER JOIN PAYROLL.TRANSACTIONSVARIABLES TRVAR ON TRVAR.EMPLOYEE_UID = EMP.EMPLOYEE_UID
INNER JOIN PAYROLL.WAGETYPES WGTYPE ON WGTYPE.WAGETYPE_UID = TRVAR.WAGETYPE_UID
INNER JOIN
(SELECT SV.EMPLOYEE_UID,DATEDIFF(month, CONVERT(VARCHAR,SV.SIGN_ON_DATE,101) + ' 00:00:00', CONVERT(VARCHAR,SV.SIGN_OFF_DATE,101) + ' 23:58:59') AS MONTHS_ON_BOARD
FROM PAYROLL.EMPLOYEES EMP INNER JOIN PAYROLL.SERVICES SV ON SV.SERVICE_UID = EMP.SERVICE_UID
WHERE EMP.EMPLOYEE_UID = 9) TBL ON TBL.EMPLOYEE_UID = 9
WHERE
EMP.EMPLOYEE_UID = 9 AND
TRVAR.CLOSE_PERIOD BETWEEN '2007-OCTOBER-01' AND
(SELECT CONVERT(VARCHAR,dateadd(mm,1,CONVERT(DATETIME,('2007-OCTOBER-01')) - day(CONVERT(DATETIME,'2007-OCTOBER-01'))+1)-1,101) + ' 23:59:59')
AND TRVAR.MARK = 1) TBL2 on TBL2.EMPLOYEE_UID = EMPS.EMPLOYEE_UID

BUT THE PROBLEMS IS MY QUERY RESULT LOOKS LIKE THIS

WAGE,WAGE
1 1
2 2
3 3
4 4
5 5
1 1
2 2
3 3
4 4
5 5

THE QUERY SHOULD HAVE THIS RESULT
WAGE WAGE
1 1
2 2
3 3
4 4
5 5

PLEASE SOMEONE HELP :(

ranganath
Posting Yak Master

209 Posts

Posted - 2007-12-01 : 01:21:19

HI,

use distinct

dockies
Starting Member

25 Posts

Posted - 2007-12-01 : 02:09:26

Nope not working :( i tried it before...

ranganath
Posting Yak Master

209 Posts

Posted - 2007-12-01 : 02:42:27

Try this May be this is usefull

SELECT tbl.WAGE,tbl2.WAGE
FROM payroll.employees EMPS
INNER JOIN
(
SELECT EMP.EMPLOYEE_UID,WGTYPE.WAGE_CODE AS WAGE,
TRFIX.DESCRIPTION AS WAGE_DESC, TRFIX.QUANTITY AS UNITS,TRFIX.RATE,
TRFIX.AMOUNT,TRFIX.AMOUNT * TBL.MONTHS_ON_BOARD AS YEAR_TO_DATE
FROM
PAYROLL.EMPLOYEES EMP
INNER JOIN
PAYROLL.TRANSACTIONSFIXED TRFIX ON TRFIX.EMPLOYEE_UID = EMP.EMPLOYEE_UID
INNER JOIN
PAYROLL.WAGETYPES WGTYPE ON WGTYPE.WAGETYPE_UID = TRFIX.WAGETYPE_UID
INNER JOIN
( SELECT DISTINCT
SV.EMPLOYEE_UID,DATEDIFF(month, CONVERT(VARCHAR,SV.SIGN_ON_DATE,101) + ' 00:00:00', CONVERT(VARCHAR,SV.SIGN_OFF_DATE,101) + ' 23:58:59') AS MONTHS_ON_BOARD
FROM
PAYROLL.EMPLOYEES EMP
INNER JOIN
PAYROLL.SERVICES SV ON SV.SERVICE_UID = EMP.SERVICE_UID
WHERE EMP.EMPLOYEE_UID = 9) TBL ON TBL.EMPLOYEE_UID = 9
WHERE
EMP.EMPLOYEE_UID = 9 AND
TRFIX.CLOSE_PERIOD BETWEEN '2007-OCTOBER-01' AND
(SELECT CONVERT(VARCHAR,dateadd(mm,1,CONVERT(DATETIME,('2007-OCTOBER-01')) - day(CONVERT(DATETIME,'2007-OCTOBER-01'))+1)-1,101) + ' 23:59:59')
AND TRFIX.MARK = 1
) TBL on TBL.EMPLOYEE_UID = EMPS.EMPLOYEE_UID

INNER JOIN
(
SELECT EMP.EMPLOYEE_UID, WGTYPE.WAGE_CODE AS WAGE, TRVAR.DESCRIPTION AS WAGE_DESC,
TRVAR.QUANTITY AS UNITS,TRVAR.RATE, TRVAR.AMOUNT, TRVAR.AMOUNT * TBL.MONTHS_ON_BOARD AS YEAR_TO_DATE
FROM
PAYROLL.EMPLOYEES EMP
INNER JOIN
PAYROLL.TRANSACTIONSVARIABLES TRVAR ON TRVAR.EMPLOYEE_UID = EMP.EMPLOYEE_UID
INNER JOIN
PAYROLL.WAGETYPES WGTYPE ON WGTYPE.WAGETYPE_UID = TRVAR.WAGETYPE_UID
INNER JOIN
(
SELECT
SV.EMPLOYEE_UID,DATEDIFF(month, CONVERT(VARCHAR,SV.SIGN_ON_DATE,101) + ' 00:00:00', CONVERT(VARCHAR,SV.SIGN_OFF_DATE,101) + ' 23:58:59') AS MONTHS_ON_BOARD
FROM
PAYROLL.EMPLOYEES EMP
INNER JOIN
PAYROLL.SERVICES SV ON SV.SERVICE_UID = EMP.SERVICE_UID
WHERE
EMP.EMPLOYEE_UID = 9) TBL ON TBL.EMPLOYEE_UID = 9
WHERE
EMP.EMPLOYEE_UID = 9 AND
TRVAR.CLOSE_PERIOD BETWEEN '2007-OCTOBER-01' AND
(SELECT CONVERT(VARCHAR,dateadd(mm,1,CONVERT(DATETIME,('2007-OCTOBER-01')) - day(CONVERT(DATETIME,'2007-OCTOBER-01'))+1)-1,101) + ' 23:59:59')
AND TRVAR.MARK = 1) TBL2 ON TBL2.EMPLOYEE_UID = EMPS.EMPLOYEE_UID AND TBL1.Employee UID = TBL2.EMPLOYEE_UID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources