issues capturing and counting values from a table

Please give Table structure/SQL/Sample Data/Req Output

this link is the best i can provide you right now...i hope it has the right information https://www.experts-exchange.com/questions/23646993/Issues-with-SQL-query-syntax-in-my-reports.html

Thanks Jacco for going through the severity of this question :)

but there us no INPUT_NAME on AA..there is a USERNAME on RESELLER_USER joined to AA through INPUT_BY

Thanks Jacco, okay this gives me INPUT_BY, INPUT_DATE, APPLICATION_NAME..NOW THE ISSUE ARISES AS IF I CATEGORIZE "'LoginFailurePassword','LoginFailurePasswordAccountLocked','LoginFailurePasswordExpired','LoginFailureUserName'"  AS FAILED
"'LoginSuccess', 'LoginSuccessAccountUnlocked'" as SUCEESS
'LoginLockout' AS lOCKOUTS
HOW DO I GET THE ROWS DISTINCTIVELY?

let me check and get back to you..i appreciate

I believe i need to separate INPUT_DATE field for each category..:)..Also is there a possible way that i can separate different methods in different columns..then i can just put in the fields for different methods on my report..

Also for some reason, i see NULLS for Methods which doesn't fall into our specified case. such as LOGOUTS etc..how do i eliminate those ?

Hi Kerau,

The query is getting more complex already :-)

I used a subquery to prevent repeating the categorisation over and over.

The NULLs are eliminated.

And the three DATE fields are added now.

Regards, Jacco

select
    *
  , case METHOD
      when 'FAILED' then A.INPUT_DATE
      else NULL
    end as FAILED_DATE
  , case METHOD
      when 'SUCCESS' then A.INPUT_DATE
      else NULL
    end as SUCCESS_DATE
  , case METHOD
      when 'LOCKOUTS' then A.INPUT_DATE
      else NULL
    end as LOCKOUT_DATE
  , Count(*)
from
    (select
      AA.INPUT_BY,
      AA.INPUT_DATE, 
      case 
        when APPLICATION_METHOD in ('LoginFailurePassword','LoginFailurePasswordAccountLocked','LoginFailurePasswordExpired','LoginFailureUserName') then 'FAILED'
        when APPLICATION_METHOD in ('LoginSuccess', 'LoginSuccessAccountUnlocked') then 'SUCCESS'
        when APPLICATION_METHOD in ('LoginLockout') then 'LOCKOUTS'
      end as METHOD,
      AA.APPLICATION_NAME
    from
      APPLICATION_AUDIT as AA) A
where
    A.METHOD is not NULL
--  and AA.INPUT_DATE >= @StartDate
--  and AA.INPUT_DATE <= @EndDate
group by
    A.INPUT_BY
  , A.INPUT_DATE
  , A.METHOD
  , A.APPLICATION_NAME

Select allOpen in new window

looks like this works :)...I am heading out..i will get back to you after 7:00PM EST..sorry but will definitely come back..thanks a ton!!!

Hi Kerau,

Ok :-) See you later.

Regards, Jacco

Notes:
- the case statement is better solved with a METHOD_CATEGORY table (new) join for extensibility
- the subquery converted to a view so the complete query becomes much simpler and can be reused for other queries/reports

Hi Jacco,

Your query looks good and it does what it says..Now i am trying to incorporate your query on my main query...Basically, its the join of five different views.. If you could help me get your query into my main query i would really appreciate...

SELECT APPLICATION_AUDIT.INPUT_DATE AS InputDate, REFERRAL_MEMBER.REFERRAL_MEMBER_NAME AS ReferralMemName, RESELLER_USER.USERNAME AS ReSellerUserName, RESELLER_USER.EMPLOYEE_ID AS ReSellerEmplD, APPLICATION_NAME.CODE AS ApplicationName, RESELLER_USER_REFERRAL_MEMBER.RESELLER_USER_REF _MEMB_ID AS ReSellerUserRefMemlD, RESELLER.RESELLER_NAME AS ReSellerName, APPLICATION_AUDIT.APPLICATION_METHOD AS ApplicationMethod 
FROM    CDDW_ProdArchive.TSMAN.APPLICATION_AUDIT AS APPLICATION_AUDIT INNER JOIN CDDW_ProdArchive.TSMAN.APPLICATION_NAME AS APPLICATION_NAME ON 
        APPLICATION_AUDIT.APPLICATION_NAME=APPLICATION_NAME.CODE        INNER JOIN CDDW_ProdArchive.TSMAN.RESELLER_USER AS RESELLER_USER ON APPLICATION_AUDIT.INPUT_BY= 
RESELLER_USER.USERUSERNAME INNER JOIN CDDW _ProdArchive.TSMAN.RESELLER_ USER_REFERRAL_MEMBER AS RESELLER_USER_REFERRAL_MEMBER ON RESELLER_USER.USERNAME= RESELLER_USER_REFERRAL_MEMBER.USERNAME INNER JOIN 
CDDW_ProdArchive.TSMAN.RESELLER AS RESELLER ON RESELLER_USER.RESELLER_ID = RESELLER.RESELLER_ID INNER JOIN CDDW_ProdArchive.TSMAN.REFERRAL_MEMBER AS REFERRAL_MEMBER ON 
RESELLER_USER_REFERRAL_MEMBER.REFERRAL_MEMBER_ID = REFERRAL_MEMBER.REFERRAL_MEMBER_ID ORDER BY ReSellerUserName

Select allOpen in new window

Please disregard the previous post..This one actually has parameters on it. I am trying to incorporate your query on my main query...Basically, its the join of five different views.. If you could help me get your query into my main query i would really appreciate...

SELECT 
APPLICATION_AUDIT.INPUT_DATE AS InputDate, 
REFERRAL_MEMBER.REFERRAL_MEMBER_NAME AS ReferralMemName, 
RESELLER_USER.USERNAME AS ReSellerUserName, 
RESELLER_USER.EMPLOYEE_ID AS ReSellerEmplD, 
APPLICATION_NAME.CODE AS ApplicationName, 
RESELLER_USER_REFERRAL_MEMBER.RESELLER_USER_REF _MEMB_ID AS ReSellerUserRefMemlD, 
RESELLER.RESELLER_NAME AS ReSellerName, APPLICATION_AUDIT.APPLICATION_METHOD AS ApplicationMethod 
FROM    
CDDW_ProdArchive.TSMAN.APPLICATION_AUDIT AS APPLICATION_AUDIT INNER JOIN 
CDDW_ProdArchive.TSMAN.APPLICATION_NAME AS APPLICATION_NAME ON APPLICATION_AUDIT.APPLICATION_NAME=APPLICATION_NAME.CODE INNER JOIN 
CDDW_ProdArchive.TSMAN.RESELLER_USER AS RESELLER_USER ON APPLICATION_AUDIT.INPUT_BY = RESELLER_USER.USERUSERNAME INNER JOIN 
CDDW _ProdArchive.TSMAN.RESELLER_ USER_REFERRAL_MEMBER AS RESELLER_USER_REFERRAL_MEMBER ON RESELLER_USER.USERNAME= RESELLER_USER_REFERRAL_MEMBER.USERNAME INNER JOIN 
CDDW_ProdArchive.TSMAN.RESELLER AS RESELLER ON RESELLER_USER.RESELLER_ID = RESELLER.RESELLER_ID INNER JOIN 
CDDW_ProdArchive.TSMAN.REFERRAL_MEMBER AS REFERRAL_MEMBER ON RESELLER_USER_REFERRAL_MEMBER.REFERRAL_MEMBER_ID = REFERRAL_MEMBER.REFERRAL_MEMBER_ID 
WHERE 
(APPLICATION_AUDIT.INPUT_DATE >= @STARTDATE) AND 
(APPLICATION_AUDIT.INPUT_DATE <= @ENDDATE)AND 
(REFERRAL_MEMBER.REFERRAL_MEMBER_NAME IN ((@RefMemName)) AND 
(APPLICATION_NAME.CODE IN (@ApplicationName)) AND 
(RESELLER_USER.USERUSERNAMe LIKE '%'+@UserName+ '%')

Select allOpen in new window

Attached is the modified version of your query..but i am trying to figure our how to merge them together into one.. If we can do this..the report will work just fine..And thanks for the notes..It will definitely help..At this juncture..my supervisor doesn't care how long my query becomes as long as it works perfect..  merging both the queries will solve my issue of the previous thread as well so i can award you both the questions..Please suggest :)

select
    *
  , case METHOD
      when 'FAILED' then GROUPED.INPUT_DATE
      else NULL
    end as FAILED_DATE
  , case METHOD
      when 'SUCCESS' then GROUPED.INPUT_DATE
      else NULL
    end as SUCCESS_DATE
  , case METHOD
      when 'LOCKOUTS' then GROUPED.INPUT_DATE
      else NULL
    end as LOCKOUT_DATE
  , Count(*) AS COUNTS
from
    (select
      APPLICATION_AUDIT.INPUT_BY,
      APPLICATION_AUDIT.INPUT_DATE, 
      case 
        when APPLICATION_METHOD in ('LoginFailurePassword','LoginFailurePasswordAccountLocked','LoginFailurePasswordExpired','LoginFailureUserName') then 'FAILED'
        when APPLICATION_METHOD in ('LoginSuccess', 'LoginSuccessAccountUnlocked') then 'SUCCESS'
        when APPLICATION_METHOD in ('LoginLockout') then 'LOCKOUTS'
      end as METHOD,
      APPLICATION_AUDIT.APPLICATION_NAME
    from
      CDDW_ProdArchive.TSMAN.APPLICATION_AUDIT as APPLICATION_AUDIT) GROUPED
where
    GROUPED.METHOD is not NULL
--  and AA.INPUT_DATE >= @StartDate
--  and AA.INPUT_DATE <= @EndDate
group by
    GROUPED.INPUT_BY
  , GROUPED.INPUT_DATE
  , GROUPED.METHOD
  , GROUPED.APPLICATION_NAME

Select allOpen in new window

Now i have started appreciating your intelligence. I just don't know how you guys do this..But i am getting some errors like

Error in SELECT clause: 'expression near ','
Missing FROM Clause
Incorrect syntax near 'GROUPED'..

I am trying to check this error, in the mean time ..just wanted to inform you as well..:)

Hi Kerau,

Can't check the query easily.

I see a bracket to many:  and GROUPED.APPLICATION_NAME in (@ApplicationName))  <--

But I guess the parser has not yet come there.

Ah, a comma too many:

 , REFERRAL_MEMBER.REFERRAL_MEMBER_NAME                    as ReferralMemName,         /*4*/

Remove the one in the end.

Hope it works now.

Regards, Jacco

Okay the query looks fine but when i try to pass in the parameter i get this following error

"Column 'CDDW_ProdArchive.TSMAN.RESELLER_USER.EMPLOYEE_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Hi Kerau,

Ah yes. Thats a pitfall. There is an easy workaround though. It is solved by putting Min() around the fields that are not in the group by.

Regards Jacco

  , Min(RESELLER_USER.EMPLOYEE_ID)                               as ReSellerEmplD            /*1*/
  , Min(RESELLER.RESELLER_NAME)                                  as ReSellerName             /*2*/
  , Min(RESELLER_USER_REFERRAL_MEMBER.RESELLER_USER_REF_MEMB_ID) as ReSellerUserRefMemlD     /*3*/
  , Min(REFERRAL_MEMBER.REFERRAL_MEMBER_NAME)                    as ReferralMemName          /*4*/

Select allOpen in new window

Thanks a ton ..above works !..but i think our issue is much bigger now ..as i don't see the query fetching any data..the previous query (without the main query incorporated into it)  had showed me lots of data with same parameters but now i see no data being no matter what parameter i feed in.

Another issue bugging me is do we have to use left outer join ..instead of just an inner join..just a thought though....:)

On my report ..the query tries to execute and tell me the following:

An error has occurred during report processing.
Query execution failed for data set 'test'.--(where our query lies)
Must declare the scalar variable "@StartDate".

But this is not the case when i run query this on Management studio. I just don't see any values there but column headers appear fine. May be its the parameter :)..I have declared something like below in Management Studio

Declare @StartDate int, @EndDate int, @ApplicationName char, @UserName char, @RefMemName char
 
Set @StartDate = 01012007
Set @EndDate = 01012008
Set @ApplicationName = '50502'
Set @UserName = 'B'
Set @RefMemName = 'ResellerPortal'
 
Is there something wrong on my part :( Please suggest

Select allOpen in new window

Hi Kerau,

Try enabeling parameter by parameter.

The left joins are to ensure rows will be in the output also if the is no matching record in the "right" table. It will put NULLs for all rows then.

Possibly one of the tables is not filled with the corresponding records. I would suspect REFERRAL_MEMBER not containing records needed and since there is a where supplied for this field the query would not return results...

You could test this with the code in the snippet.

Regards Jacco

  and (   REFERRAL_MEMBER.REFERRAL_MEMBER_NAME in (@RefMemName) 
       or REFERRAL_MEMBER.REFERRAL_MEMBER_NAME is NULL)

Select allOpen in new window

For some reason..the query seems to be working now ...:) ..I don't know why

I got this error and how this got solved

 An error has occurred during report processing.
Query execution failed for data set 'test'.--(where our query lies)
Must declare the scalar variable "@StartDate".

The report is being generated. I just cut and pasted your code on the query... I know its been a hardball for you..But on the management studio i pass in the same parameter and don't get any output..Could you plz tell me as....am i declaring the dates and other parameters in a right way..or am making any kind of mistake...let me know

I think its on date param

when i set the date like @StartDate = 20070101
@EndDate = 20080101

i get the following error " Airthematic overflow error converting expression to data type datetime."

if i declare my date as
@StartDate = 01012007
@EndDate = 01012008

The query executes but i get just the headers...Please let me know

Hi Kerau,

Glad it is almost working.

What is the type of the INPUT_DATE field?

If it is datetime or smalldatetime you can use the following conversion:

INPUT_DATE >= convert(datetime, @StartDate, 112)

declare it as varchar(20) then and set is to a string YYYYMMDD

Regards, Jacco

Sorry for the late reply...i will check this and get back to you tomorrow...you have a nice weekend!!enjoy

Okay so here is how i am converting my date value. I have two data sets


DS_DateParams
SELECT     CASE WHEN dbo.DateToInt(@RunForDate) > dbo.DateToInt(GETDATE()) THEN dbo.inttodate(dbo.DateToInt(DATEADD(DAY, - 1, GETDATE())))
                      WHEN dbo.DateToInt(@RunForDate) = dbo.DateToInt(GETDATE()) THEN dbo.inttodate(dbo.DateToInt(DATEADD(DAY, - 1, @RunForDate)))
                      ELSE dbo.inttodate(dbo.DateToInt(@RunForDate)) END AS EndDate, DATEADD(MONTH, - 1, CASE WHEN dbo.DateToInt(@RunForDate)
                      > dbo.DateToInt(GETDATE()) THEN dbo.inttodate(dbo.DateToInt(DATEADD(DAY, - 1, GETDATE()))) WHEN dbo.DateToInt(@RunForDate)
                      = dbo.DateToInt(GETDATE()) THEN dbo.inttodate(dbo.DateToInt(DATEADD(DAY, - 1, @RunForDate))) ELSE dbo.inttodate(dbo.DateToInt(@RunForDate))
                      END) AS StartDate

DS_DateValues
SELECT     dbo.DateToInt(@StartDate) AS StartDateKey, dbo.DateToInt(@EndDate) AS EndDateKey.

I genereally take @StartDateKey and @EndDateKey on the query. I have tried both this and still am getting "airthematic overflow error converting expression to datatype datetime" :(

Now what should i do to get this work with our query..FYI tried your above option as well

Hi Jacco,

Good news! I somehow made this work.. while going through our previous post i found that you have written  "I didn't join on the APPLICATION_NAME table since you use no other field then the key. And I used fields from GROUPED whereever possible", which is kinda bugging me.

As one of my dropdown parameter takes value from Application_Name...

SELECT DISTINCT CODE AS ApplicationName
FROM         CDDW_ProdArchive.TSMAN.APPLICATION_NAME
ORDER BY ApplicationName

so if we don't use this in our main query. Will the parameter be passed accordingly. I really appreciate your patience but since i am new to all this ..please suggest in towards the right direction

Hi Kerau,

So you don't need help with the DateToInt stuff?

In the select appears

APPLICATION_NAME.CODE AS ApplicationName,

In the join specification

APPLICATION_AUDIT.APPLICATION_NAME=APPLICATION_NAME.CODE

In the where clause

APPLICATION_NAME.CODE IN (user selected APPLICATION_NAME.CODE values)

So essentially simplified you say:

select  A where A = B and B = C

Now I just said that you will get the same results if you do

select B where B = C

Since "A = B"

The way I constructed the query prevents an extra join, joins cost performance. And since it can not make any difference in which records you select and what you display, I left it out. (It can only make a difference if the table APPLICATION_NAME does not contain all values of APPLICATION_AUDIT..APPLICATION_NAME. But there should be a referential constraint in place that prevents this friom happening in the first place.

You can add extra the join if you want to prepare the report for displaying other fields from APPLICATION_NAME.

You could also change

SELECT DISTINCT CODE AS ApplicationName
FROM         CDDW_ProdArchive.TSMAN.APPLICATION_NAME
ORDER BY ApplicationName

to

select distinct APPLICATION_NAME from APPLICATION_AUDIT

If it bugs you that the paramter values come from a differet table. But this could be very slow, since you try to select these few records from far more records.

Regards Jacco

"It can only make a difference if the table APPLICATION_NAME does not contain all values of APPLICATION_AUDIT..APPLICATION_NAME..." Your this statement is completly true. I have just checked this now and see that if i use

SELECT DISTINCT CODE AS ApplicationName
FROM         CDDW_ProdArchive.TSMAN.APPLICATION_NAME
ORDER BY ApplicationName gets me

1. Batch
2.BetterWay
3. verisign

where as if i use this

select distinct APPLICATION_NAME from APPLICATION_AUDIT, i get

1. Verisign
2. Provide.

This is OK, it just means the application BetterWay has not been used yet.

If it was the other way around it would have been a problem.

Regards, Jacco

Hi Jacco,
This is approved with changes in Date Params..Thanks for elaborating on why you took the specific approach
I am totally blown away by your intellect. You dont only have programming skills but a very comprehensive analytical skill, which I see lacking with most of the Genius and GurusJ. I posted the same question different ways as this is being VERY HIGH priority to me, but your analysis was great and thoughtful. Now, I believe this is much more than just 500 points, you can comment anything on these links  https://www.experts-exchange.com/questions/23658122/Joining-Main-Query-with-Subquery.html , https://www.experts-exchange.com/questions/23650293/merge-subquery-into-main-query.html so that i can award you all the points.
I would love to get your opinion on some of my other reports as well, if you have any secondary email ID where I can just send you the link after I post them in EE, would help me a lot to get my work rolling as suddenly I am in a situation where I am the only person responsible for frontend as well as backend.

Thanks, for the compliments and points Kerua.

I will post a comment in the other questions with a link here.

About giving you my email for helping you on further problems: I have a 40 hour job as a programmer and do not always have this much time on my hands. Helping you out with this one has cost a lot of time. Am happy to help but can't promise anything.

You can of coarse post further question here, but as I said, can't promise anything. If you want promises you need to hire me ;-)

Regards, Jacco

Oh yeah, I think that if you leave a comment in here I should get a notice?

This might just interest you :)

https://www.experts-exchange.com/questions/23758728/issues-with-required-field-validator-control.html

Need assistance on this

https://www.experts-exchange.com/questions/23764710/Open-in-a-new-window.html