Very big query on hollidays - 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

Very big query on hollidays

Author

Topic

romzilla
Starting Member

6 Posts

Posted - 2007-12-27 : 04:51:18

Hello friends!
Desperately need some help again! I have 4 tables and need to get data from them.

1) DOCSADM.PEOPLE(SYSTEM_ID, FULL_NAME)
2) DOCSADM.GROUPS(SYSTEM_ID, GROUP_NAME)
3) DOCSADM.PEOPLEGROUPS(GROUPS_SYSTEM_ID, PEOPLE_SYSTEM_ID)
4) DOCSADM.PROFILE

This is the 1 query I have at the moment. It works. The problem is that I need to get DOCSADM.GROUPS.GROUP_NAME instead of 'Departaments'. The only way to get to it is through table DOCSADM.PEOPLEGROUPS where all connections between people and groups.
The other thing is that not all connections should be listed, bet only with filter shown in 2 query on table DOCSADM.PEOPLEGROUPS. Also 1 query should be grouped by Departaments and inside the group order by should be put.

1)
SELECT
,'Departaments' as Departaments
,p.DOCNUMBER AS DM#
,p.DOCNAME AS Dokumenta_nosaukums
, case p.REG_ID
when 8 then 'N-Rikojumi par pamatdarbibu'
when 10 then 'N-ZM instrukcijas'
when 11 then 'N-Ieteikumi'
end AS Dokumenta_veids
,convert(varchar(10), p.CREATION_DATE, 103) as Ievietosanas_datums
,convert(varchar(10), p.CREATION_TIME, 108) as Ievietosanas_laiks
,a.FULL_NAME AS Autors
,p.ZM_KEY AS ZM#
FROM DOCSADM.PROFILE p INNER JOIN DOCSADM.PEOPLE a ON p.AUTHOR = a.SYSTEM_ID
WHERE (p.REG_ID = 8) OR (p.REG_ID = 10) OR (p.REG_ID = 11)

ORDER BY p.REG_ID, p.CREATION_DATE

2)
SELECT a.FULL_NAME, pg.GROUPS_SYSTEM_ID
FROM DOCSADM.PEOPLEGROUPS pg INNER JOIN
DOCSADM.PEOPLE a ON pg.PEOPLE_SYSTEM_ID = a.SYSTEM_ID
where pg.GROUPS_SYSTEM_ID = 713558 or
pg.GROUPS_SYSTEM_ID = 713909 or
pg.GROUPS_SYSTEM_ID = 713932 or
pg.GROUPS_SYSTEM_ID = 660184 or
pg.GROUPS_SYSTEM_ID = 733374 or
pg.GROUPS_SYSTEM_ID = 709054 or
pg.GROUPS_SYSTEM_ID = 710392 or
pg.GROUPS_SYSTEM_ID = 710399 or
pg.GROUPS_SYSTEM_ID = 710400 or
pg.GROUPS_SYSTEM_ID = 710466 or
pg.GROUPS_SYSTEM_ID = 710475 or
pg.GROUPS_SYSTEM_ID = 713763 or
pg.GROUPS_SYSTEM_ID = 713862

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 05:20:28

[code]SELECT
,
COALESCE(g.GROUP_NAME,'Departaments') as Departaments
,p.DOCNUMBER AS DM#
,p.DOCNAME AS Dokumenta_nosaukums
, case p.REG_ID
when 8 then 'N-Rikojumi par pamatdarbibu'
when 10 then 'N-ZM instrukcijas'
when 11 then 'N-Ieteikumi'
end AS Dokumenta_veids
,convert(varchar(10), p.CREATION_DATE, 103) as Ievietosanas_datums
,convert(varchar(10), p.CREATION_TIME, 108) as Ievietosanas_laiks
,a.FULL_NAME AS Autors
,p.ZM_KEY AS ZM#
FROM DOCSADM.PROFILE p
INNER JOIN DOCSADM.PEOPLE a
ON p.AUTHOR = a.SYSTEM_ID
LEFT OUTER JOIN DOCSADM.PEOPLEGROUPS pg
ON pg.PEOPLE_SYSTEM_ID=a.SYSTEM_ID
LEFT OUTER JOIN DOCSADM.GROUPS g
ON g.SYSTEM_ID=pg.GROUPS_SYSTEM_ID
WHERE p.REG_ID IN (8,10,11)
AND pg.GROUPS_SYSTEM_ID IN(713558,713909, 713932, 660184, 733374, 709054, 710392, 710399, 710400,710466,710475,713763,713862)
ORDER BY p.REG_ID, p.CREATION_DATE[/code]

if you are sure that there is atleast one group associated with people then you can use INNER JOIN instead of LEFT OUTER JOIN. Also didnt understand the need of , in front(hope there's some field which precedes this else it will error)

romzilla
Starting Member

6 Posts

Posted - 2007-12-27 : 05:31:22

Thank You very much! It works just fine and I can now send it to verify if the data is correct. About , - the original query looks like
SELECT
'<tr><td>'
,'Departaments' as Departaments
,'</td><td>'
,p.DOCNUMBER AS DM#
,'</td><td>'
,p.DOCNAME AS Dokumenta_nosaukums
,'</td><td>'
, case p.REG_ID
when 8 then 'N-Rikojumi par pamatdarbibu'
when 10 then 'N-ZM instrukcijas'
when 11 then 'N-Ieteikumi'
end AS Dokumenta_veids
,'</td><td>'
,convert(varchar(10), p.CREATION_DATE, 103) as Ievietosanas_datums
,'</td><td>'
,convert(varchar(10), p.CREATION_TIME, 108) as Ievietosanas_laiks
,'</td><td>'
,a.FULL_NAME AS Autors
,'</td><td>'
,p.ZM_KEY AS ZM#
,'</td></tr>'
FROM DOCSADM.PROFILE p INNER JOIN DOCSADM.PEOPLE a ON p.AUTHOR = a.SYSTEM_ID
WHERE (p.REG_ID = 8) OR (p.REG_ID = 10) OR (p.REG_ID = 11)
ORDER BY p.REG_ID, p.CREATION_DATE

There are already html tags in it. we put query result to text without column names and then copy the result in html source file. And the report is ready. It is needed only one time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources