Problem in getting output - 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

Problem in getting output

Author

Topic

sinapra
Starting Member

24 Posts

Posted - 2007-04-30 : 10:58:00

Hello All,

I am not a very good SQL Programmer, but I have a question, I have a SQL Script and I want some output, while runnig from SQL Profiler, it keeps running but no output is being displayed........i think its looped wrongly. Can anyone help me on this. The query looks like this :

/*Query for Report(1): Knowledge Detailed*/
SELECT
date_time_dataset.date,
date_time_dataset.time,
date_time_dataset.org_name,
isnull(Cr.[Created],0) as Created,
isnull(Modd.[Modified],0) as Modified,
isnull(expd.[Expired],0) as Expired,
isnull(retd.[Retired],0) as Retired,
isnull(Cr.[Created],0) + isnull(Modd.[Modified],0) + isnull(expd.[Expired],0) + isnull(retd.[Retired],0) as Total
FROM
(
SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], skeletons.CREATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], skeletons.CREATION_DATE, '01/01/1970')) AS [Time]
FROM
O_INDEXES,
SKELETONS
WHERE
O_INDEXES.ID = SKELETONS.KD_PERMISSION_INDEX_ID
UNION ALL
SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], O_EVENTS.EVENT_TIMESTAMP, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], O_EVENTS.EVENT_TIMESTAMP, '01/01/1970')) AS [Time]
FROM
SKELETONS,
O_INDEXES,
O_EVENTS
WHERE
O_EVENTS.ENTITY_ID=SKELETONS.ID
AND SKELETONS.KD_PERMISSION_INDEX_ID = O_INDEXES.ID
UNION ALL
SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')) AS [Time]
FROM
SKELETONS,
O_INDEXES,
O_EVENTS
WHERE
O_EVENTS.ENTITY_ID=SKELETONS.ID
AND SKELETONS.KD_PERMISSION_INDEX_ID = O_INDEXES.ID
AND O_EVENTS.EVENT_NAME='Document Expired.'
UNION ALL
SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')) AS [Time]
FROM
SKELETONS,
O_INDEXES,
O_EVENTS
WHERE
O_EVENTS.ENTITY_ID=SKELETONS.ID
AND SKELETONS.KD_PERMISSION_INDEX_ID = O_INDEXES.ID
AND O_EVENTS.EVENT_NAME<>'Document Expired.'
AND SKELETONS.NOTES LIKE '%Retired%'
) AS date_time_dataset
LEFT OUTER JOIN
(SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], skeletons.CREATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], skeletons.CREATION_DATE, '01/01/1970')) AS [Time],
COUNT (distinct skeletons.id) as 'Created'
FROM
O_INDEXES,
SKELETONS
WHERE
O_INDEXES.ID = SKELETONS.KD_PERMISSION_INDEX_ID
GROUP BY
O_INDEXES.CAPTION,
CAST(SUBSTRING(CAST(DATEADD([second], skeletons.CREATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime),
DATEPART([hour], DATEADD([second], skeletons.CREATION_DATE, '01/01/1970'))
) as cr
ON
date_time_dataset.date=cr.date AND date_time_dataset.time=cr.time AND date_time_dataset.org_name=cr.org_name
LEFT OUTER JOIN
(
SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], O_EVENTS.EVENT_TIMESTAMP, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], O_EVENTS.EVENT_TIMESTAMP, '01/01/1970')) AS [Time],
COUNT (distinct skeletons.id) as 'Modified'
FROM
SKELETONS,
O_INDEXES,
O_EVENTS
WHERE
O_EVENTS.ENTITY_ID=SKELETONS.ID
AND SKELETONS.KD_PERMISSION_INDEX_ID = O_INDEXES.ID
GROUP BY
O_INDEXES.CAPTION,
CAST(SUBSTRING(CAST(DATEADD([second], O_EVENTS.EVENT_TIMESTAMP, '01/01/1970')AS varchar(20)), 0, 12) AS datetime),
DATEPART([hour], DATEADD([second], O_EVENTS.EVENT_TIMESTAMP, '01/01/1970'))
) as modd
ON
date_time_dataset.date=modd.date AND date_time_dataset.time=modd.time AND date_time_dataset.org_name=modd.org_name
LEFT OUTER JOIN
(SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')) AS [Time],
COUNT (distinct skeletons.id) as 'Expired'
FROM
SKELETONS,
O_INDEXES,
O_EVENTS
WHERE
O_EVENTS.ENTITY_ID=SKELETONS.ID
AND SKELETONS.KD_PERMISSION_INDEX_ID = O_INDEXES.ID
AND O_EVENTS.EVENT_NAME='Document Expired.'
GROUP BY
O_INDEXES.CAPTION,
CAST(SUBSTRING(CAST(DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime),
DATEPART([hour], DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970'))
) as expd
ON
date_time_dataset.date=expd.date AND date_time_dataset.time=expd.time AND date_time_dataset.org_name=expd.org_name
LEFT OUTER JOIN
(SELECT
O_INDEXES.CAPTION as org_name,
CAST(SUBSTRING(CAST(DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime) AS 'Date',
DATEPART([hour], DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')) AS [Time],
COUNT (distinct skeletons.id) as 'Retired'
FROM
SKELETONS,
O_INDEXES,
O_EVENTS
WHERE
O_EVENTS.ENTITY_ID=SKELETONS.ID
AND SKELETONS.KD_PERMISSION_INDEX_ID = O_INDEXES.ID
AND O_EVENTS.EVENT_NAME<>'Document Expired.'
AND SKELETONS.NOTES LIKE '%Retired%'
GROUP BY
O_INDEXES.CAPTION,
CAST(SUBSTRING(CAST(DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970')AS varchar(20)), 0, 12) AS datetime),
DATEPART([hour], DATEADD([second], SKELETONS.EXPIRATION_DATE, '01/01/1970'))
) as retd
ON
date_time_dataset.date=retd.date AND date_time_dataset.time=retd.time AND date_time_dataset.org_name=retd.org_name
ORDER BY
date_time_dataset.date, date_time_dataset.time, date_time_dataset.org_name

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-30 : 11:11:06

You don't expect someone who have no idea what you are doing to read your long code and tell you what's wrong with it do you ?

Please explain what are you trying to achieve here. Also there are too long for us too much for us ( for me at least ) to digest. Try to simplified it to something manageable. And please also post the table DDL (simplified version please), sample data and the expected result.

sinapra
Starting Member

24 Posts

Posted - 2007-04-30 : 11:26:24

ok well, let me begin again, see I want a report to be generated here with this already scripted SQL Query.I know its a pain to read this long script. But thought if any expert would grab it quick and give me some idea about how this script can give a report shown below

Client Date Hour Created Modified Expired Retired
Equiva 30 December 2006 2 23 0 3 20
GMLG 30 December 2006 2 0 0 1 0

Let me know if that helps.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources