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

General SQL Server Forums

New to SQL Server Programming

Help with the query

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-25 : 10:45:28

The DDL of my table is :

Finish_time : datetime
job_exit_status : varchar
job_exit_code : int
Job_id : int

I need to display , on each day in the month of feb 2010, total no Jobs , Total no of Jobs where job_exit_status is 'exit',
Percent of total jobs which would be 100% and percentage of exited jobs, grouping by finish_day(its a datetime field in my table)

and conditions are given in the below where statement...
with like operators and clustercode = 1

I tried with the below query but it does not work ...

Please Help ...

select finish_time job_exit_status, job_exit_code, count(job_id)
from dw_t_jobdata
where finish_time >= '1-feb-2010'and finish_time < '1-mar-2010'
and cluster_code = 1
and (job_cmd like '%icfb%' or job_cmd like '%layout%')
group by trunc(finish_time,'month'), job_exit_status, job_exit_code

msuneerbabu
Starting Member

6 Posts

Posted - 2010-03-25 : 11:14:33

try this...

select trunc(finish_time,'month'), job_exit_status, job_exit_code, count(job_id)
from dw_t_jobdata
where finish_time >= '1-feb-2010'and finish_time < '1-mar-2010'
and cluster_code = 1
and (job_cmd like '%icfb%' or job_cmd like '%layout%')
group by trunc(finish_time,'month'), job_exit_status, job_exit_code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:17:08

will dw_t_jobdata have data for all days of a month? also do you need to show the days having no data also in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-25 : 11:28:44

yes

i want to show total no of jobs each day and total no of exited job on that day..

quote:
Originally posted by visakh16

will dw_t_jobdata have data for all days of a month? also do you need to show the days having no data also in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:32:20

then you need to have a calendar table which needs to be left joined to your table. do you have such a table? if not,use function below to generate one

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-25 : 11:46:53

i think we can do that by using dateadd funtion right
i think i can use something like dateadd(d, datediff(d, 0, FINISH_TIME), 0) right to get date and bu grouping it by dateadd(d, datediff(d, 0, FINISH_TIME), 0) for each day right ?

here ius the qeury i tried

select
finish_day ,
job_exit_status,
job_exit_code,
jobs,
exit_jobs,
jobs/exit_jobs as percent
from
(select
finish_day,
job_exit_status,
job_exit_code,
jobs,
sum(jobs) over (partition by job_exit_status)exit_jobs
from
(select

dateadd(d, datediff(d, 0, FINISH_TIME), 0) finish_day,
a.job_exit_status,
a.job_exit_code,
count(a.job_id)jobs

from dw_t_jobdata a where a.job_exit_status = 'exit'
and dateadd(d, datediff(d, 0, FINISH_TIME), 0) >= '01-feb-2010'and dateadd(d, datediff(d, 0, FINISH_TIME), 0) < '01-mar-2010'
group by dateadd(d, datediff(d, 0, FINISH_TIME), 0) job_exit_status,job_exit_code)
)

there are some syntaxes error in the query could you please correct them , basically i will cut copy this query in my congos report backend sql to get the report...

Please Help ..

Visakh it was you who actually showed be similar kind of syntax before for one of my question...

quote:
Originally posted by visakh16

then you need to have a calendar table which needs to be left joined to your table. do you have such a table? if not,use function below to generate one

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:49:12

syntax error in which query? your posted one or that in function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:51:03

you're missing aliases


select 
finish_day ,
job_exit_status,
job_exit_code,
jobs,
exit_jobs,
jobs/exit_jobs as percent
from
(select 
finish_day,
job_exit_status,
job_exit_code,
jobs,
sum(jobs) over (partition by job_exit_status)exit_jobs
from
(select

dateadd(d, datediff(d, 0, FINISH_TIME), 0) finish_day,
a.job_exit_status,
a.job_exit_code,
count(a.job_id)jobs

from dw_t_jobdata a where a.job_exit_status = 'exit'
and dateadd(d, datediff(d, 0, FINISH_TIME), 0) >= '01-feb-2010'and dateadd(d, datediff(d, 0, FINISH_TIME), 0) < '01-mar-2010'
group by dateadd(d, datediff(d, 0, FINISH_TIME), 0) job_exit_status,job_exit_code
)t
)r

also learn to use as before aliases to avoid confusion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources