Help with code - 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

General SQL Server Forums

New to SQL Server Programming

Help with code

Author

Topic

diane.lonergan@gmail.com
Starting Member

6 Posts

Posted - 2008-11-27 : 08:54:45

I am new to T-SQL. I have created two selects, as below. My problem is that the first table gives me a row for every member of staff, my second select gives me a row for any staff with holiday still to be agreed. I need to join these tables to produce one report which lists every member of staff, with details of holidays still to be agreed, if applicable

I would apprieciate any help. I ise sql 2000

Many Thanks

SELECT 1 - This selects gets a row (from the personnel records table)for every current member of staff and returns how many days holiday they have remaining in this holiday year
select Surname
, pr.Initials
, pr.department
, cast(pr.Holiday_Entitlement as int) as 'Entitlement'
, cast(pr.Holiday_balance as int) as 'balance'
--, ht.start_date
--, ht.end_date
, pr.id as 'prid'

from Personnel_Records pr

where
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()

SELECT 2 - This select joins the personnel table with the holiday tracking table to produce a row for any member of staff who has holidays still to be agreed.

select pr.department 'pr dept'
, surname
,Forenames
,Holiday_Balance 'Remaining Holiday'
,Lieu_Days_Left 'Remaining Lieu'
,sum(ht.duration) as 'Still to be agreed'
,pr.id

--, ht.id, ht.id_1, pr.id
from dbo.Personnel_Records PR
join holiday_tracking HT
on pr.id = ht.id_1

where datepart(yy,ht.start_date) = '2008'
and
(
((ht.agreed = 'submitted') and
(ht.Request_For_cancellation = 0 or ht.Cancelled = 'no'))
or
(ht.agreed = 'pending')
or
(ht.Request_For_cancellation = 1 and ht.agreed = 'yes')
)
and
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)

group by surname, forenames, holiday_balance, Lieu_Days_Left, pr.department, pr.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 08:58:28

[code]
SELECT *
FROM (Query1)q1
LEFT JOIN (Query2)q2
ON q2.prid=q1.prid
[/code]

diane.lonergan@gmail.com
Starting Member

6 Posts

Posted - 2008-11-27 : 11:49:44

Thanks for advice. It still isn't working but I suspect I'm still being stupid. Here is my revised code and error

SELECT *
FROM (select Surname
, pr.Initials
, pr.department
, cast(pr.Holiday_Entitlement as int) as 'Entitlement'
, cast(pr.Holiday_balance as int) as 'balance'
--, ht.start_date
--, ht.end_date
, pr.id as 'prid'

from Personnel_Records pr

where
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)q1

LEFT outer JOIN (select pr.department 'pr dept'
, surname
,Forenames
,Holiday_Balance 'Remaining Holiday'
,Lieu_Days_Left 'Remaining Lieu'
,sum(ht.duration) as 'Still to be agreed'
,pr.id

--, ht.id, ht.id_1, pr.id
from dbo.Personnel_Records PR
join holiday_tracking HT
on pr.id = ht.id_1

where datepart(yy,ht.start_date) = '2008'
and
(
((ht.agreed = 'submitted') and
(ht.Request_For_cancellation = 0 or ht.Cancelled = 'no'))
or
(ht.agreed = 'pending')
or
(ht.Request_For_cancellation = 1 and ht.agreed = 'yes')
)
and
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)

group by surname, forenames, holiday_balance, Lieu_Days_Left, pr.department, pr.id

)q2

ON q2.prid=q1.prid

Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'q1'.
Server: Msg 170, Level 15, State 1, Line 53
Line 53: Incorrect syntax near 'q2'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 11:55:30

[code]SELECT *
FROM (select Surname
, pr.Initials
, pr.department
, cast(pr.Holiday_Entitlement as int) as 'Entitlement'
, cast(pr.Holiday_balance as int) as 'balance'
--, ht.start_date
--, ht.end_date
, pr.id as 'prid'

from Personnel_Records pr

where pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)q1
LEFT outer JOIN (select pr.department AS [pr dept]
, surname
,Forenames
,Holiday_Balance as [Remaining Holiday]
,Lieu_Days_Left [Remaining Lieu]
,sum(ht.duration) as [Still to be agreed']
,pr.id

--, ht.id, ht.id_1, pr.id
from dbo.Personnel_Records PR
join holiday_tracking HT
on pr.id = ht.id_1

where datepart(yy,ht.start_date) = '2008'
and(((ht.agreed = 'submitted') and
(ht.Request_For_cancellation = 0 or ht.Cancelled = 'no'))
or
(ht.agreed = 'pending')
or
(ht.Request_For_cancellation = 1 and ht.agreed = 'yes')
)
and
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)

group by surname, forenames, holiday_balance, Lieu_Days_Left, pr.department, pr.id

)q2
ON q2.prid=q1.prid[/code]
Try like above

diane.lonergan@gmail.com
Starting Member

6 Posts

Posted - 2008-11-27 : 12:29:40

Pasted code from above,

just one error this time
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'prid'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 12:34:05

quote:
Originally posted by diane.lonergan@gmail.com

Pasted code from above,

just one error this time
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'prid'.


SELECT *
FROM (select Surname
, pr.Initials
, pr.department
, cast(pr.Holiday_Entitlement as int) as 'Entitlement'
, cast(pr.Holiday_balance as int) as 'balance'
--, ht.start_date
--, ht.end_date
, pr.id as 'prid'

from Personnel_Records pr

where pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)q1
LEFT outer JOIN (select pr.department AS [pr dept]
, surname
,Forenames
,Holiday_Balance as [Remaining Holiday]
,Lieu_Days_Left [Remaining Lieu]
,sum(ht.duration) as [Still to be agreed']
,pr.id AS [prid]

--, ht.id, ht.id_1, pr.id
from dbo.Personnel_Records PR
join holiday_tracking HT
on pr.id = ht.id_1

where datepart(yy,ht.start_date) = '2008'
and(((ht.agreed = 'submitted') and
(ht.Request_For_cancellation = 0 or ht.Cancelled = 'no'))
or
(ht.agreed = 'pending')
or
(ht.Request_For_cancellation = 1 and ht.agreed = 'yes')
)
and
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)

group by surname, forenames, holiday_balance, Lieu_Days_Left, pr.department, pr.id

)q2
ON q2.prid=q1.prid

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 12:36:33

quote:

Originally posted by visakh16

SELECT *
FROM (select Surname
, pr.Initials
, pr.department
, cast(pr.Holiday_Entitlement as int) as 'Entitlement'
, cast(pr.Holiday_balance as int) as 'balance'
--, ht.start_date
--, ht.end_date
, pr.id as [prid]

from Personnel_Records pr

where pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)q1
LEFT outer JOIN (select pr.department AS [pr dept]
, surname
,Forenames
,Holiday_Balance as [Remaining Holiday]
,Lieu_Days_Left [Remaining Lieu]
,sum(ht.duration) as [Still to be agreed']
,pr.id as [prid]

--, ht.id, ht.id_1, pr.id
from dbo.Personnel_Records PR
join holiday_tracking HT
on pr.id = ht.id_1

where datepart(yy,ht.start_date) = '2008'
and(((ht.agreed = 'submitted') and
(ht.Request_For_cancellation = 0 or ht.Cancelled = 'no'))
or
(ht.agreed = 'pending')
or
(ht.Request_For_cancellation = 1 and ht.agreed = 'yes')
)
and
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)

group by surname, forenames, holiday_balance, Lieu_Days_Left, pr.department, pr.id

)q2
ON q2.prid=q1.prid

Try like above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 12:38:00

quote:

Originally posted by sodeep

quote:

Originally posted by visakh16

SELECT *
FROM (select Surname
, pr.Initials
, pr.department
, cast(pr.Holiday_Entitlement as int) as 'Entitlement'
, cast(pr.Holiday_balance as int) as 'balance'
--, ht.start_date
--, ht.end_date
, pr.id as [prid]

from Personnel_Records pr

where pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)q1
LEFT outer JOIN (select pr.department AS [pr dept]
, surname
,Forenames
,Holiday_Balance as [Remaining Holiday]
,Lieu_Days_Left [Remaining Lieu]
,sum(ht.duration) as [Still to be agreed']
,pr.id as [prid]

--, ht.id, ht.id_1, pr.id
from dbo.Personnel_Records PR
join holiday_tracking HT
on pr.id = ht.id_1

where datepart(yy,ht.start_date) = '2008'
and(((ht.agreed = 'submitted') and
(ht.Request_For_cancellation = 0 or ht.Cancelled = 'no'))
or
(ht.agreed = 'pending')
or
(ht.Request_For_cancellation = 1 and ht.agreed = 'yes')
)
and
(
pr.Start_Date <= getdate()
and pr.leaving_date is null
or pr.leaving_date > getdate()
)

group by surname, forenames, holiday_balance, Lieu_Days_Left, pr.department, pr.id

)q2
ON q2.prid=q1.prid