locked
display null records RRS feed

  • Question

  • User1897897189 posted

    dear all,

    i have follwoing table

    REGION  date_apprvd    sal
    a           20130101    1000
    a      20130201    2000
    a      20130301    3000
    b      20140101    4000
    b      20140201    5000
    b      20140301    6000
    c      20130101    7000
    c      20130201    8000
    c      20130301    9000
    d      20130101    1100
    d      20130201    2100
    d      20130301    3100
    e      20140101    4100
    e      20140201    5100
    e      20140301    6100


    pls help me to create a report

    input is 20130101

    region      sal      count
    a           1000          1
    b           0               0
    c           7000          1
    d           1100         1
    e            0              0

    thanks

    nick
       

    Tuesday, June 25, 2013 1:05 PM

Answers

  • User-578610739 posted

    Hi Nick,

    If you post in sqlserver ,then you get the result faster. Any way -Its very easy. I create a sample sqlquery, just run is sqlserver

    declare @a1 table (empno int, ename varchar(50))
    declare @a2 table (empno int, dob int)
    declare @a3 table (dob int, djoin varchar(50))
    
    insert into  @a1 values(1,'a')
    insert into  @a1 values(2,'b')
    insert into  @a1 values(3,'c')
    
    insert into  @a2 values(1,1)
    insert into  @a2 values(2,2)
    insert into  @a2 values(3,3)
    
    insert into  @a3 values(1,'20130101')
    insert into  @a3 values(1,'20130101')
    insert into  @a3 values(1,'20130101')
    insert into  @a3 values(2,'20140101')
    insert into  @a3 values(2,'20140101')
    insert into  @a3 values(2,'20140301')
    insert into  @a3 values(3,'20150101')
    insert into  @a3 values(3,'20150101')
    insert into  @a3 values(3,'20150101')
    
    
    SELECT     A1.empno, A1.ename, A2.dob, A3.djoin
    FROM   @A3 a3
    RIGHT OUTER JOIN @A2 a2 ON A3.dob = A2.dob 
    RIGHT OUTER JOIN @A1 a1 ON A2.empno = A1.empno
    
    DECLARE @PassDt varchar(50)= '20130101'
    
    SELECT A1.empno, A1.ename, COUNT(djoin)
    FROM   @A1 a1 
    LEFT OUTER JOIN @A2 a2 ON A2.empno = A1.empno
    LEFT OUTER JOIN @A3 a3 ON A3.dob = A2.dob  AND ( a3.djoin = @passdt  or @PassDt is null)
    group by A1.empno, A1.ename



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 27, 2013 4:53 AM

All replies

  • User-1716253493 posted
    Maybe you can use isnull(fieldname, 'text')
    Tuesday, June 25, 2013 1:10 PM
  • User1897897189 posted

    could you please give me an example

    Tuesday, June 25, 2013 1:11 PM
  • User143067745 posted

    Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

    (Select  Region,date_apprvd, count(*) as 'COUNT' from TABLE_NAME  where date_apprvd='VALUE' Group By  date_apprvd) tempFirst

    Left Join

    (Select  date_apprvd, isnull(sal,0) as 'SAL'  from  TABLE_NAME where date_apprvd='VALUE') tempSecond

    on  tempFirst.date_apprvd=tempSecond.date_apprvd) tbl;

    Try this query after replacing BOLD content by appropriate values.

    Tuesday, June 25, 2013 1:40 PM
  • User1897897189 posted

    it says

    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near ')'.

    Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from
    (Select  Region,date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By  date_apprvd)
    tempFirst Left Join
    (Select  date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101')tempSecond
    on  tempFirst.date_apprvd=tempSecond.date_apprvd) tbl

    Tuesday, June 25, 2013 1:56 PM
  • User143067745 posted

    Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

    (

    (Select  Region,date_apprvd, count(*) as 'COUNT' from TABLE_NAME  where date_apprvd='VALUE' Group By  date_apprvd) tempFirst

    Left Join

    (Select  date_apprvd, isnull(sal,0) as 'SAL'  from  TABLE_NAME where date_apprvd='VALUE') tempSecond

    on  tempFirst.date_apprvd=tempSecond.date_apprvd

    ) tbl;

    Sry I made a silly mistake.

    Tuesday, June 25, 2013 2:05 PM
  • User1897897189 posted

    infact i tried this before posting

    it says

    Msg 102, Level 15, State 1, Line 7
    Incorrect syntax near 'tbl'.


    Tuesday, June 25, 2013 2:07 PM
  • User143067745 posted

    Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

    (

    (Select  Region,date_apprvd, count(*) as 'COUNT' from TABLE_NAME  where date_apprvd='VALUE' Group By  date_apprvd) tempFirst

    Left Join

    (Select  date_apprvd, isnull(sal,0) as 'SAL'  from  TABLE_NAME where date_apprvd='VALUE') tempSecond

    on  tempFirst.date_apprvd=tempSecond.date_apprvd

    ) ;

    Tuesday, June 25, 2013 2:38 PM
  • User1897897189 posted

    Select  tempFirst.Region,tempSecond.SAL,tempFirst.COUNT  from

    (

    (Select  Region,date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By Region, date_apprvd) tempFirst

    Left Join

    (Select  date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101') tempSecond

    on  tempFirst.date_apprvd=tempSecond.date_apprvd

    ) ;

    THE RESULT IS

    a      1000    1
    a      7000    1
    a      1100    1
    c      1000    1
    c      7000    1
    c      1100    1
    d      1000    1
    d      7000    1
    d      1100    1

    WHICH IS WRONG

    nick

    Tuesday, June 25, 2013 2:41 PM
  • User143067745 posted

    Select  tempSecond.Region,tempSecond.SAL,tempFirst.COUNT  from

    (

    (Select  date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By date_apprvd) tempFirst

    Left Join

    (Select  Region,date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101') tempSecond

    on  tempFirst.date_apprvd=tempSecond.date_apprvd

    ) ;

    Tuesday, June 25, 2013 2:44 PM
  • User1897897189 posted

    my expected op is

    region      sal      count
    a           1000          1
    b           0               0
    c           7000          1
    d           1100         1
    e            0              0

    Tuesday, June 25, 2013 2:46 PM
  • User143067745 posted

    I realy very sry. One last time:

    Select  tempSecond.Region,tempSecond.SAL,tempFirst.COUNT  from
    
    (
    (Select  Region,date_apprvd, isnull(sal,0) as 'SAL'  from  ABC_VIEW where date_apprvd='20130101') tempSecond
    
    Left Join
    (Select  date_apprvd, count(*) as 'COUNT' from ABC_VIEW  where date_apprvd='20130101' Group By date_apprvd) tempFirst
    
    
    on  tempSecond.date_apprvd=tempFirst.date_apprvd
    
    ) ;



    Tuesday, June 25, 2013 2:51 PM
  • User1897897189 posted

    i really appreciate your help, no need for any sorry...

    still its not giving the expected op. following is the op it generated

    a      1000    3
    c      7000    3
    d      1100    3

    thanks

    nick

    Tuesday, June 25, 2013 2:53 PM
  • User-578610739 posted

    Hi Nicklibee,

    Please explain about your output. Out of 3 or 4 similar data, you want a one recod , the output logic is not fulfill to understand. i.e.

    a           20130101    1000
    a      20130201    2000
    a      20130301    3000

    for this 3  row , your output will  a           1000          1

    for b your outpu will be     b           0               0 

    It is not understand the logic. Please elaborate more.

    Wednesday, June 26, 2013 12:35 AM
  • User1897897189 posted

    Dear all,

    I have 3 tables and all the 3 tables are joined and created a command objet in crystal reports

    Following are the tables and the command code.

    My expected op is mentioned at the last.

    Pls help me to create a report.


    table a1

    empno ename

    1    a
    2    b
    3    c

    table a2

    empno dob

    1    1
    2    2
    3    3

    table a3

    dob djoin

    1    20130101
    1    20130201
    1    20130301
    2    20140101
    2    20140201
    2    20140301
    3    20150101
    3    20150201
    3    20150301

    SELECT     dbo.A1.empno, dbo.A1.ename, dbo.A2.dob, dbo.A3.djoin
    FROM         dbo.A3 RIGHT OUTER JOIN
                          dbo.A2 ON dbo.A3.dob = dbo.A2.dob RIGHT OUTER JOIN
                          dbo.A1 ON dbo.A2.empno = dbo.A1.empno

    empno ename dob djoin

    1    a    1    20130101
    1    a    1    20130201
    1    a    1    20130301
    2    b    2    20140101
    2    b    2    20140201
    2    b    2    20140301
    3    c    3    20150101
    3    c    3    20150201
    3    c    3    20150301



    ip parameter is djoin
    say 20130101 to 20130101
    i want the following op

    empno        empname        count
    1        a        1
    2        b        0
    3        c        0

    Thanks
    Nick

    Thursday, June 27, 2013 12:21 AM
  • User-578610739 posted

    Hi Nick,

    If you post in sqlserver ,then you get the result faster. Any way -Its very easy. I create a sample sqlquery, just run is sqlserver

    declare @a1 table (empno int, ename varchar(50))
    declare @a2 table (empno int, dob int)
    declare @a3 table (dob int, djoin varchar(50))
    
    insert into  @a1 values(1,'a')
    insert into  @a1 values(2,'b')
    insert into  @a1 values(3,'c')
    
    insert into  @a2 values(1,1)
    insert into  @a2 values(2,2)
    insert into  @a2 values(3,3)
    
    insert into  @a3 values(1,'20130101')
    insert into  @a3 values(1,'20130101')
    insert into  @a3 values(1,'20130101')
    insert into  @a3 values(2,'20140101')
    insert into  @a3 values(2,'20140101')
    insert into  @a3 values(2,'20140301')
    insert into  @a3 values(3,'20150101')
    insert into  @a3 values(3,'20150101')
    insert into  @a3 values(3,'20150101')
    
    
    SELECT     A1.empno, A1.ename, A2.dob, A3.djoin
    FROM   @A3 a3
    RIGHT OUTER JOIN @A2 a2 ON A3.dob = A2.dob 
    RIGHT OUTER JOIN @A1 a1 ON A2.empno = A1.empno
    
    DECLARE @PassDt varchar(50)= '20130101'
    
    SELECT A1.empno, A1.ename, COUNT(djoin)
    FROM   @A1 a1 
    LEFT OUTER JOIN @A2 a2 ON A2.empno = A1.empno
    LEFT OUTER JOIN @A3 a3 ON A3.dob = A2.dob  AND ( a3.djoin = @passdt  or @PassDt is null)
    group by A1.empno, A1.ename



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 27, 2013 4:53 AM