Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: sql query help - urgent

    Hi,

    This the query to generate report which gives datewise report of how many paid subscribers,free subscribers, how many campaigns,emails sent,contacts
    Now currently there 330 paid subscribers and 627 free subscribers

    The subquery that generates paid & free subscribers are done based on the create date, the created date for the last paid subscriber was 10/25/2006 so when I generate this report for date range greater than 11/01/2006 to 11/06/2006 it displays a null value for the paid subscribers since there are no paid subscribers .


    But I want the output to still display the total as 330 even if I generate after November 1 if no data found on November 1 how would I code it.



    SELECT DISTINCT days.made, c.userid,
    DECODE (c.subscriptionpackageid,1,'F', 7, 'P', 8, 'P',
    9, 'P',11, 'P',12, 'P',13, 'P', 14, 'P', 15, 'P', 17, 'P'
    ) custtype,
    NVL (g.contacts_imported, 0) contacts_imported,
    NVL (dc.email_sent, 0) email_sent,
    NVL (dc.campaign_sent, 0) campaign_sent,
    u.paidsubscribers,
    coalesce(u.paidsubscrbers,
    f.freesubscribers
    FROM days,
    (SELECT companyid, TRUNC (made) made,
    SUM (COUNT) contacts_imported
    FROM GROUPS
    WHERE TRUNC (made) BETWEEN TO_DATE ('11/01/2006',
    'mm/dd/yyyy'
    )
    AND TO_DATE ('11/01/2006',
    'mm/dd/yyyy'
    )
    AND format LIKE '%Import%'
    AND status LIKE '%Completed%'
    GROUP BY companyid, TRUNC (made)) g,
    (SELECT companyid, TRUNC (made) made,
    COUNT (*) campaign_sent, SUM (numbersent)
    email_sent
    FROM dm_campaign
    WHERE TRUNC (made) BETWEEN TO_DATE ('09/01/2006',
    'mm/dd/yyyy'
    )
    AND TO_DATE ('10/01/2006',
    'mm/dd/yyyy'
    )
    GROUP BY companyid, TRUNC (made)) dc,
    (SELECT s.clientid companyid, s.userid userid,
    s.subscriptionpackageid
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    AND c.lastverisigntransactionid IS NOT NULL
    AND c.subscriptionpackageid NOT IN 1
    AND c.active = 1
    AND s.userid NOT LIKE 'qpt999%') c,
    (SELECT made, SUM (COUNT) OVER (ORDER BY made)
    paidsubscribers
    FROM (SELECT TRUNC (c.createdate) made,
    COUNT (s.userid) COUNT
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    AND c.lastverisigntransactionid IS NOT NULL
    AND c.subscriptionpackageid != 1
    and lower(voided) not like 'qa%'
    AND s.userid NOT LIKE 'qpt999%'
    GROUP BY c.createdate)) u,
    (SELECT made, SUM (COUNT) OVER (ORDER BY made)
    freesubscribers
    FROM (SELECT TRUNC (c.createdate) made,
    COUNT (s.clientid) COUNT
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    AND c.subscriptionpackageid = 1
    and lower(voided) is null
    AND s.userid NOT LIKE 'qpt999%'
    GROUP BY c.createdate)) f
    WHERE ( c.companyid = g.companyid
    AND c.companyid = dc.companyid
    AND days.made = g.made
    AND days.made = dc.made
    AND days.made = u.made
    AND days.made = f.made
    )
    ORDER BY days.made

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    1. Use [CODE] tags.
    2. Your query wont compile at all (there's no u.paidsubscrbers column in this query) -- suggestion: use REAL query.
    3. Use NVL, COALESCE, etc to achieve what you want.

  3. #3
    Join Date
    Sep 2006
    Posts
    10
    hi,

    There was an error while pasting this is the real query i use
    SELECT DISTINCT days.made, c.userid,
    DECODE (c.subscriptionpackageid,1,'F', 7, 'P', 8, 'P',
    9, 'P',11, 'P',12, 'P',13, 'P', 14, 'P', 15, 'P', 17, 'P'
    ) custtype,
    NVL (g.contacts_imported, 0) contacts_imported,
    NVL (dc.email_sent, 0) email_sent,
    NVL (dc.campaign_sent, 0) campaign_sent,
    u.paidsubscribers,
    u.paidsubscrbers,
    f.freesubscribers
    FROM days,
    (SELECT companyid, TRUNC (made) made,
    SUM (COUNT) contacts_imported
    FROM GROUPS
    WHERE TRUNC (made) BETWEEN TO_DATE ('11/01/2006',
    'mm/dd/yyyy'
    )
    AND TO_DATE ('11/01/2006',
    'mm/dd/yyyy'
    )
    AND format LIKE '%Import%'
    AND status LIKE '%Completed%'
    GROUP BY companyid, TRUNC (made)) g,
    (SELECT companyid, TRUNC (made) made,
    COUNT (*) campaign_sent, SUM (numbersent)
    email_sent
    FROM dm_campaign
    WHERE TRUNC (made) BETWEEN TO_DATE ('09/01/2006',
    'mm/dd/yyyy'
    )
    AND TO_DATE ('10/01/2006',
    'mm/dd/yyyy'
    )
    GROUP BY companyid, TRUNC (made)) dc,
    (SELECT s.clientid companyid, s.userid userid,
    s.subscriptionpackageid
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    AND c.lastverisigntransactionid IS NOT NULL
    AND c.subscriptionpackageid NOT IN 1
    AND c.active = 1
    AND s.userid NOT LIKE 'qpt999%') c,
    (SELECT made, SUM (COUNT) OVER (ORDER BY made)
    paidsubscribers
    FROM (SELECT TRUNC (c.createdate) made,
    COUNT (s.userid) COUNT
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    AND c.lastverisigntransactionid IS NOT NULL
    AND c.subscriptionpackageid != 1
    and lower(voided) not like 'qa%'
    AND s.userid NOT LIKE 'qpt999%'
    GROUP BY c.createdate)) u,
    (SELECT made, SUM (COUNT) OVER (ORDER BY made)
    freesubscribers
    FROM (SELECT TRUNC (c.createdate) made,
    COUNT (s.clientid) COUNT
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    AND c.subscriptionpackageid = 1
    and lower(voided) is null
    AND s.userid NOT LIKE 'qpt999%'
    GROUP BY c.createdate)) f
    WHERE ( c.companyid = g.companyid
    AND c.companyid = dc.companyid
    AND days.made = g.made
    AND days.made = dc.made
    AND days.made = u.made
    AND days.made = f.made
    )
    ORDER BY days.made

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Your problem is that you're joining on MADE columns on the subquery, which have different kind of dates as their criteria, reason you can't get the 330 suscribers you're talking about, since when you join on this condition it is never true.

    My suggestion to you would be to use SCALAR SUBQUERYS instead of subquerys. That is, instead of the way you're doing it now, you would use something like:
    Code:
    select ...,
             ( select .... query from the paidsuscribers here
                 from ... ) padsuscribers,
             ( select ....
                 from .... ) whatever
             ....
      from ....
     where ....

  5. #5
    Join Date
    Sep 2006
    Posts
    10

    thanks for the suggestion

    Hi,

    Thanks for the suggestion, made the modifications.
    I work in 8.1.7.0.0 which does not support a full outer join instead I used union in order to bring the rows that are missing on both tables. The output for the query I use but is incorrect it does not bring the rows with null values because for some users .i have doubt if at all would i be able to generate the correct output using sql or should i go for pl/sql.kindly suggest


    This is the desired output which I generate using the queries separately

    Correct Output
    Made Userid Type Contact Campsent Emailsent Paid Free

    6-Nov-06 F 479 1 0 331 669
    6-Nov-06 P 5658 0 0 331 669
    7-Nov-06 P 0 2 23818 331 671
    8-Nov-06 [email[/email] P 17 2 57 331 674
    8-Nov-06 P 0 1 11909 331 674
    9-Nov-06 F 500 2 0 331 676
    9-Nov-06 P 0 1 11909 331 676
    10-Nov-06 P 1978 0 0 331 678
    10-Nov-06 [email]/email] P 0 1 83 331 678
    11-Nov-06 [email]/email] F 500 0 0 331 682
    12-Nov-06 F 4 0 0 331 685


    but if join I get incorrect output

    Made Userid Type Contact Campsent Emailsent Paid Free
    6-Nov-2006 Free 479 1 0 331 669
    8-Nov-2006 Paid 17 2 57 331 674
    9-Nov-2006 Free 500 2 0 331 676


    Modified query

    select
    distinct days.made,
    nvl(customer.userid,0) userid,
    (case when customer.subscriptionpackageid > 1 then 'Paid' else 'Free' end )type,
    nvl( result.contact,0) Contacts_Imported,
    nvl(result.camp,0) Campaign_Sent,
    nvl(result.email,0) Email_Sent ,
    (case when paid.made is null then
    ( SELECT COUNT(*)
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    and c.subscriptionpackageid != 1
    and lower(voided) not like 'qa%'
    AND s.userid NOT LIKE 'qpt999%')
    else paid.paidsubscribers end) paid,
    free.freesubscribers free
    from days,
    (select g.made gmade,
    dc.made dcmade,
    g.companyid gid,
    dc.companyid dcid,
    g.contacts_imported contact,
    dc.campaign_sent camp,
    dc.email_sent email
    from days,
    (SELECT companyid,
    TRUNC (made) made,
    SUM (COUNT) contacts_imported
    FROM GROUPS
    wHERE TRUNC (made) BETWEEN TO_DATE ('11/06/2006','mm/dd/yyyy')
    AND TO_DATE ('11/12/2006','mm/dd/yyyy')
    AND format LIKE '%Import%'
    AND status LIKE '%Completed%'
    GROUP BY companyid, TRUNC (made)) g ,
    (SELECT companyid,
    TRUNC (made) made,
    COUNT (*) campaign_sent,
    SUM (numbersent) email_sent
    FROM dm_campaign
    WHERE TRUNC (made) BETWEEN TO_DATE ('11/06/2006','mm/dd/yyyy')
    AND TO_DATE ('11/12/2006','mm/dd/yyyy')
    GROUP BY companyid,
    TRUNC (made))dc
    where dc.companyid=g.companyid(+)
    union
    select
    g.made gmade,
    dc.made dcmade,
    g.companyid gid,
    dc.companyid dcid,
    g.contacts_imported contact,
    dc.campaign_sent camp,
    dc.email_sent email
    from days,
    (SELECT companyid,
    TRUNC (made) made,
    SUM (COUNT) contacts_imported
    FROM GROUPS
    wHERE TRUNC (made) BETWEEN TO_DATE ('11/06/2006','mm/dd/yyyy')
    AND TO_DATE ('11/12/2006', 'mm/dd/yyyy')
    AND format LIKE '%Import%'
    AND status LIKE '%Completed%'
    GROUP BY companyid, TRUNC (made)) g,
    (SELECT companyid,
    TRUNC (made) made,
    COUNT (*) campaign_sent,
    SUM (numbersent) email_sent
    FROM dm_campaign
    WHERE TRUNC (made) BETWEEN TO_DATE ('11/06/2006','mm/dd/yyyy')
    AND TO_DATE ('11/12/2006', 'mm/dd/yyyy')
    GROUP BY companyid, TRUNC (made))dc
    where dc.companyid(+)=g.companyid
    ) result,
    (SELECT s.clientid companyid, s.userid userid,
    s.subscriptionpackageid
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    AND s.userid NOT LIKE 'qpt999%') customer,
    ( SELECT made, SUM (COUNT) OVER (ORDER BY made )
    paidsubscribers
    FROM (SELECT TRUNC (c.createdate) made,
    COUNT (s.userid) COUNT
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    and c.subscriptionpackageid != 1
    and lower(voided) not like 'qa%'
    AND s.userid NOT LIKE 'qpt999%'
    GROUP BY c.createdate)) paid,
    ( SELECT made, SUM (COUNT) OVER (ORDER BY made)
    freesubscribers
    FROM (SELECT TRUNC (c.createdate) made,
    COUNT (s.userid) COUNT
    FROM client c, subscriber s
    WHERE c.clientid = s.clientid
    and c.subscriptionpackageid = 1
    and lower(voided) is null
    AND s.userid NOT LIKE 'qpt999%'
    GROUP BY c.createdate)) free

    where days.made=result.gmade(+)
    and days.made=result.dcmade(+)
    and result.gmade=result.dcmade
    and customer.companyid=result.gid
    and customer.companyid=result.dcid
    and days.made=paid.made(+)
    and days.made=free.made
    and days.made between '01-Nov-2006' and '13-Nov-2006'
    Last edited by wijisidd; 11-16-06 at 06:56.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Two things:

    1) Use code tags to make your queries readable
    2) Did you ask those people if they agree that you post their email addresses publicly? They probably receive a lot of spam now, just because of this posting. Please remove them

  7. #7
    Join Date
    Sep 2006
    Posts
    10
    hi shammat,

    please help me remove those emails i dont know to do it i dont have an edit option

    Thanks in advance

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you are logged in on dBforums, you should have an EDIT button at the bottom-right corner of your message. It looks like the attached image.
    Attached Thumbnails Attached Thumbnails dbforums_edit.JPG  

  9. #9
    Join Date
    Sep 2006
    Posts
    10
    hi all out there,
    you people are of great help cant explain how bad i felt when i realised my mistake .with your help i removed the mails thank you once again.

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    wijisidd, I suggested you to use scalar subquerys, not full outer joins.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •