September 10, 2015 at 1:05 am
Hi All!
I have a query, which delivers all my headlines:
Select D.name as institution,RTRIM(B.per_fornavn)+' '+RTRIM(B.per_efternavn) from dbo.employment A
Join dbo.personal B on B.per_personal_id = a.EmployeeID
JOIN dbo.EmpToInst C ON C.employeeid = A.EmployeeID
JOIN dbo.Institutions D ON D.id = C.institutionsid
JOIN DBO.agreement_Set_To_Personal E ON E.per_personal_id = A.EmployeeID
where A.POSITIONTYPECHOICE = 2 and E.Agreement_sets_Id = 5
Group By D.name,B.per_fornavn,B.per_efternavn
Order by D.name,B.per_fornavn
In common english, i get employee's in institutions based on some rules.
Looks like this:
institution employee
Adm - somewhere Lotte Gry Hansen
B&U - somewhereelse Afrah Al-hansen
B&U - somewhereelse AyseHansen
B&U - somewhereelse Bernard Hansen
B&U - somewhereelse Birgitta Hansen
The base table is:
CREATE TABLE [dbo].[employment](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GROUPMAPPINGID] [int] NULL,
[DATEFROM] [int] NULL,
[DATETO] [int] NULL,
[PER_TIMER] [decimal](7, 2) NULL,
[Agreement_sets_Id] [int] NULL,
[POSITIONTYPECHOICE] [tinyint] NULL,
[ADDITIONALTEXT] [char](100) NULL,
[EmployeeID] [int] NULL,
[is_on_flex_agreement] [tinyint] NULL,
[Flex_Hours] [decimal](7, 2) NULL,
[vacation_model] [tinyint] NULL,
CONSTRAINT [PK_EmploymentID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Each header is based on 2 to 20 employment records
So, i want to have lines with corrsponding DATEFROM,DATETO,ADDITIONALTEXT under each header
something like for the first two records:
institution employee
Adm - somewhere Lotte Gry Hansen
20150101 20150131 first job
20150201 -- second job
B&U - somewhereelse Afrah Al-hansen
20120601 job as it consultan
Possible?
How to?
Best regards
Edvard Korsbæk
September 10, 2015 at 2:14 am
Yes it's possible to do, but it's a little like digging a big hole underneath your car so you can get a flat tyre off and put a fresh tyre back on - instead of using a jack to raise the car.
Working with header and detail rows (Orders/order lines, invoices/invoice lines etc), typically you generate an output set containing detail rows. All of the detail rows also have the columns holding their header data. The presentation layer - Reporting Services, Excel, Crystal whatever - is used to format this output. All standard stuff, well understood and well documented on the net.
Are you going to dig the hole, or use the jack?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2015 at 2:20 am
It will take me app. 15 minutes to create a program, that makes the report - OK
So, I use the Jack as you descibe it.
I am in the 'Newbies' section - And trying to understand what i can get from SQL 'Native'
This was one, which I understand is a 'not'.
So, thanks!
Best regards
Edvard Korsbæk
September 10, 2015 at 2:41 am
Edvard Korsbæk (9/10/2015)
It will take me app. 15 minutes to create a program, that makes the report - OKSo, I use the Jack as you descibe it.
I am in the 'Newbies' section - And trying to understand what i can get from SQL 'Native'
This was one, which I understand is a 'not'.
So, thanks!
Best regards
Edvard Korsbæk
You're welcome, and thanks for the feedback. Post back if you have any uncertainty about how to proceed with this.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply