Comments by mousau (Top 41 by date)

Hi Griff
Thanks a lot for the solution.I have to learn a lot;Tactical to Practical:)I am a newbie, Thanks a lot for the help:)
Thank You
Regards

Hi All
Understood the magic...of the function.Thanks a lot
Thank You
Regards

Hi Griff
you mean to say..if the date is 8/22/2016, then ISO_WEEK will return the week number onto which 22nd August falls right??
Thanks
Regards

Hi Maciej
Thanks for the response. I got to google on this more:)
Thank you
Regards

Hi Griff
Got a question:-
Two version of identical queries however results varies due to function changes:-

select CustName,Count(CustName)
from [CustmrSrc]
Group By CustName,DATENAME(dw,PurchaseDate)
having Count(PurchaseDate)>=5

and
the inner query as described by you:-

SELECT CustName FROM [CustmrSrc]
GROUP BY CUSTName, DATEPART(ISO_WEEK,PurchaseDate)
HAVING COUNT(PurchaseDate) >= 5


The second query is returing result , however the first query is not returning anything:O Why?

Regards

Hi Griff
Thanks a lot for the response. The solution is working perfectly fine:):)
Thank You
Regards

Hi Sumon
Thanks a lot for the response.
Regards

Hi Phil
Thanks a lot for the response and providing the clarity.
Gotta a question now! Tried the same query using "IN" clause, but I do need to fetch the details of the those customers who are purchasing on any of the five days regularly, but I am struggling here:(

Thank You
Regards

Hi Suvendu
Thanks a lot for the response.Let me refer and get me back in case of doubts
Regards
Moumita

Hi Daniel
Thanks a lot for the response.The link mentioned by you also describes the identical syntax.If i gotta try multiple tables as the source name.then i got the repeat the entire steps from CDC_Enable_Table!!!

Thank You
Regards

Hi Manish
I guess found the problem, I executed the cdc on table query, when the table was empty,then populated, I just tried disabling everything and populated the table and then executed the commands again! And it worked!!

Hi Manishss
The table has data, but one fact to wonder about is :- cdc.dbo_CustDetails_CT was not created!!!!! A little light on this would be of great relief!!
Regards

Hi Richard
Thanks a lot for the update, may be I am not sure,the need was to work on a kind of transform (part of the ETL tool) which requires the CDC database, and we got to work on multiple databases:- like Sql server, Teradata, MongoDb etc.
I was wondering how to address the problem using the Enterprise manager as was being described in one of posts across a forum!! If this is fact(thanks a lot for the update),then a little help over the procedure of creating a CDC database, after using the command:-
Exec sys.sp_cdc_enable_db
How do I configure the SQL Replication Server for your Microsoft SQL Server database???or we don't need this step of configuration at all!!
or alternate way has evolved..???
Thanks
Regards

Hi Chill60
Seems like got to brush up the basics from tactical to practical.Thanks a lot for the help.
Regards
Moumita

Hi digimanus
Kindly excuse me if i have asked something unusual related to sa. How about granting these user rights to another user or (login created under it)DB_DataReader and Db_DataWriter
Regards

Hi Peter
Yes i am using Sql server management studio vs 2012
Regards

Hi Chill60
Wow, seems so cool, Thanks a lot for the information update.I have been through the solutions provided at the site,gotta implement that.Let me check from my end,or contact the admin team perhaps.But thanks a lot again for sharing the valuable information.

Thanks
Regards

Hi Ryan
Thanks for your time

Hi CHill60
Thanks a lot for the solution.It worked.Thanks again
Thank you

Hi Ryan
For the 1st query the error is :-
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'

2nd query:-

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Fail_Count'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'OverallCount'.

3rd Query:-
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near ','.

Hi Tom
Wow,Thanks a lot,Iam not well versed with the concepts of CTE and Temp tables ,just aware about it.Extremely thankful for the help.It has worked.Feeling relieved.Thanks a lot for the above solution.

Hi Tom
Thanks for the response. I tried using the sql but its displaying syntax errors,wherever the simple braces are there.I dont understand the reason, coz syntax wise it seems ok:-
SELECT RUN_ID, JOB_NAME, START_TIME, END_TIME,
(
SELECT COUNT(*) FROM COMP_HIS_TBL on_start
WHERE on_start.RUN_ID <> failed.RUN_ID
AND failed.START_TIME BETWEEN on_start.START_TIME AND on_start.END_TIME AND
) AS JobsCountatStartTime,
(
SELECT COUNT(*) FROM COMP_HIS_TBL on_end
WHERE on_end.RUN_ID <> failed.RUN_ID
AND failed.END_TIME BETWEEN on_end.START_TIME AND on_end.END_TIME AND
) AS JobsCountatEndTime
FROM COMP_HIS_TBL failed
WHERE failed.STATUS = 'Failure'


Its throwing syntax error:-


"Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.
"

Hi
Thanks a lot.I saw and modified the syntax acccordinlgy.
Thank you

Hi Richard
Thanks a lot for your solution.Really thanks.Its working. After this I tried using the following query but its showing incorrect syntax:(

Select JOB_NAME,(Count(Fail_Count)/Count(OverallCount))*100 as Fail_percent,STATUS from COMP_HIS_TBL
where Fail_Count,OverallCount in (SELECT
JOB_NAME,
SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
COUNT(JOB_NAME) As OverallCount
FROM
COMP_HIS_TBL
GROUP BY
JOB_NAME)

Moumita

Hi Tom
following code of mine displays the counts of the jobs based on start time and end time individually:

<pre lang="SQL">declare @job1 varchar(100) = 'JOB_STP_PIR_Pull_SAP'
select @job1 as InputJob ,count(b.JOB_NAME) as CountStartTime
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on b.START_TIME <= a.START_TIME
and b.JOB_NAME <> a.JOB_NAME
where a.JOB_NAME = @job1

declare @job2 varchar(100) = 'JOB_STP_PIR_Pull_SAP'
select @job2 as InputJob ,count(b.JOB_NAME)as CountEndTime
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on b.END_TIME <= a.END_TIME
and b.JOB_NAME <> a.JOB_NAME
where a.JOB_NAME = @job2

Each of the above query has different output.I got to club them the above conditions to one,but if i make the changes accordingly, like adding CountENdTime to the initial Count_Start_time query and add the END_TIME condition as well to the above query, it displays the same value?Please help out:O:O
Thanks
Moumita

Hi i gotta to display the list of distict count for all the job_names satisfied by the condition, but both the available syntaxes are throwing me the error :-
declare @job1 varchar(100) = 'JOB_BW_varYR_PD7'
select @job1 as InputJob , count(Distinct *) as CountStartTime
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on b.START_TIME <= a.START_TIME
and b.JOB_NAME <> a.JOB_NAME
where a.JOB_NAME = @job1

Even Distinct Count(*) is not working:O:(

Hi Tom
Need the count of the jobs returned via the above script( as in the solution proposed by you)
Thank you

Hi Tom
Your logic is right, well i gotta clarify this with my seniors.Thanks for the help. Thanks a lot:)
Moumita

Hi George
Thanks a lot,Tom's solution worked.But i gotta to display even the count for all those jobs, and i am getting error:(

Hi Tom
Thanks a lot.Lets leave about the time thing, will ask with my lead. But in order to get the count of all these jobs,i tried sql as above,but its throwing error:(

Hi Tom,
Well coz that START_TIME fields consists of date and time both. If i try to get the count of these jobs on the whole:- tried: declare @job varchar(100) ='JOB_BW_varYR_PD7'
select b.JOB_NAME,b.REPOSITORY_NAME,b.START_TIME,b.END_TIME,Count(JOB_NAME)
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on Cast(b.START_TIME as Time) <= Cast(a.END_TIME as Time)
and Cast(b.END_TIME as Time)>= Cast(a.START_TIME as time)
and b.JOB_NAME <> a.JOB_NAME
where a.JOB_NAME = @job


but its throwing "Ambiguous Column name" for JOB_NAME!!!:(:(:(

Hi Tom
I tried using both with cast and without cast:-

declare @job varchar(100) = 'JOB_BW_varYR_PD7'
select b.*
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on Cast(b.START_TIME as Time) <= Cast(a.END_TIME as Time)
and Cast(b.END_TIME as Time)>= Cast(a.START_TIME as time)
and b.JOB_NAME <> a.JOB_NAME
where a.JOB_NAME = @job

declare @job1 varchar(100) = 'JOB_BW_varYR_PD7'
select b.*
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on b.START_TIME <= a.END_TIME
and b.END_TIME >= a.START_TIME
and b.JOB_NAME <> a.JOB_NAME
where a.JOB_NAME = @job1, but they are showing different result sets?

Hi Tom
Thanks a lot.Its good,but one problem the START_TIME is of datatype datetime2, and i need the time value alone for comparision. so how could the CAST operator be useful??

Hi Andrew
I would be thankful if you could tell me the way to attach the screenshots. I have already entered a sample data above for one dataset,like wise there are several data. Please let me know
Thank you
Moumita

Hi Tom
Thanks.Yes you are right.
Thank you

Hi George
Thanks a lot for responding back. The START_TIME and END_TIME are of Datatype:-"datetime2"(using SQL sever 2012). The existing table consists of Data related to the specified fields:-

JOB_NAME | RUN_ID | START_TIME | END_TIME | EXECUTION_TIME|STATUS |

JOB_BW_varYR_PD7 | 1121 | 2016-04-30 13:08:14.0000000| 2016-04-30 13:25:21.0000000 |1027.000000 |SUCCESS|

Like wise there are various jobs. Now there are certain long running jobs and which either ended up with the status : failure or success , across specific days. Now the need is what were the other jobs which were running during that time period when a given job was running.
Hence the query is :"display the list of all those jobs which were running across on a given day(when the given job was running),such that, the START_TIME OF the Jobs should be less than the START_TIME of the current job(chosen) and the END_TIME should be less than the END_TIME of the current Job."
The above query helps us to find out the list of active and inactive jobs when a specific job was running. Alternative approaches will also be welcomed.
Thank you
Moumita