Professional Web Applications Themes

Query Help - Microsoft SQL / MS SQL Server

SELECT * FROM Sometable WHERE employee IN (SELECT employee FROM Sometable WHERE col3 IN (3,6,9) GROUP BY employee HAVING COUNT(DISTINCT col3)=3) -- David Portas ------------ Please reply only to the newsgroup --...

  1. #1

    Default Re: Query Help

    SELECT *
    FROM Sometable
    WHERE employee IN
    (SELECT employee
    FROM Sometable
    WHERE col3 IN (3,6,9)
    GROUP BY employee
    HAVING COUNT(DISTINCT col3)=3)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  2. #2

    Default Re: Query Help

    create table test
    (col1 int primary key
    , col2 int
    , col3 int
    , employee char(10)
    , col5 int)

    insert into test
    select 1 , 30 , 6 , 'ABC', 1
    union
    select 2 , 30 , 9 , 'ABC' , 2
    union
    select 3 , 30 , 3 , 'ABC' , 3
    union
    select 6 , 31 , 3 , 'PQR' , 3
    union
    select 9 , 29 , 9 , 'PQR' , 2

    select
    t.*
    from test t
    join (select
    t1.employee
    from test t1
    join test t2 on t1.employee = t2.employee and t1.col3 <> t2.col3 and t1.col3 < t2.col3
    join test t3 on t2.employee = t3.employee and t2.col3 <> t3.col3 and t2.col3 < t3.col3) dt
    on t.employee = dt.employee


    drop table test

    --
    Dean Savovic
    www.teched.hr


    "Satish" <com> wrote in message news:0a0e01c34ba3$dfb79a10$gbl... 


    Dean Guest

  3. #3

    Default Re: Query Help

    Satish,

    Your output and your description do not match. Your
    description says "only those employees whose COL3 values
    are 6,9,3." Assuming you mean "are 6, 9 or 3", you should
    have all 5 rows in the output.

    Your 3 rows of output could be selected by many filters. Some
    simple ones are:

    where COL2 = 30
    where EMPLOYEE='ABC'
    where COL2 = COL5

    If you want something else, please try to describe it in a way that
    matches your output and with data that doesn't have other easy
    descriptions.

    Steve Kass
    Drew University
    4A8F1164-5CFB-47FF-B360-4C24F00DDD92


    Satish wrote:
     

    Steve Guest

  4. #4

    Default Re: Query Help

    Based on the result set given, I read it as Relational Division.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  5. #5

    Default Re: Query Help

    Another way to do that. This method would assumes that
    you are looking for all employee records where the
    employee records contain all the possible values from col3.
    select t.* from test t
    where (select count(distinct col3) from test)
    =
    (select count(distinct col3) from test
    where employee=t.employee) 
    6 , 'ABC', 1 
    9 , 'ABC' , 2 
    3 , 'ABC' , 3 
    3 , 'PQR' , 3 
    9 , 'PQR' , 2 
    t2.col3 and t1.col3 < t2.col3 
    t3.col3 and t2.col3 < t3.col3) dt 
    news:0a0e01c34ba3$dfb79a10$gbl... [/ref]
    - - [/ref]
    6,9,3 [/ref]
    - - 
    >
    >
    >.
    >[/ref]
    Gregory Guest

  6. #6

    Default Re: Query Help

    Probably the right guess - and we'll find out if not...

    SK

    David Portas wrote:
     

    Steve Guest

  7. #7

    Default Query help


    I am trying to qry a third party accounts package. Using
    the help already given on the sales ledger side of things
    i tried applying it to the purchase ledger but have run
    into difficulties due to the way the system handles
    entries

    Example A shows a payment on supplier account CBI012.
    this payment is for the total invoice price. using the
    qry i can get the monies recieved into each Profit & Loss
    account ( example E) However this is not true for Exapmle
    C as it is only a part payment, As you can see from
    example D the full invoice amount (349.96) has been added
    into the results example E and not just the payment value.

    The correct result should be example F.
    I have only selected 2 payments out of hundreds as
    examples. I am unable to create the DDL. Can anyboby give
    any suggestion how to modify my qry to get the desired
    result (example F). The matchdate is important as
    i use this to detirmine in whice month the payments were
    made

    Thanks in advance

    Query

    SELECT b.ACNO AS PLCODE ,SUM(b.AMOUNT) AS TOTALS
    FROM OPURTRAN AS a
    inner JOIN NOMDET AS b
    ON a.TRANREF = b.TRANREF
    WHERE LEFT(b.ACNO,1) = 'P' AND
    a.TRNTYPE = b.TRNTYPE
    AND DATEPART(MONTH, a.MATCHDATE ) = '01'
    AND a.AMOUNT > '0.00'
    GROUP BY b.ACNO


    EXAMPLE A
    ACNO TRANREF TRNTYPE AMOUNT MATCHDATE
    ------------ ------- ------------- ----------------------
    CBI012 351 IND 57.7600 2003-01-31 00:00:00.000

    (1 row(s) affected)

    EXAMPLE B
    ACNO TRANREF TRNTYPE AMOUNT
    ------------ ----------- ------- ---------------------
    P075120 351 IND -44.7600
    P090000 351 IND -2.0000
    P090000 351 IND -1.0000
    P090000 351 IND -10.0000
    B025000 351 IND 57.7600

    (5 row(s) affected)

    EXAMPLE C
    ACNO TRANREF TRNTYPE AMOUNT MATCHDATE
    ------------ ----------- ------- ---------------
    CBI064 308 ISP 87.4900 2003-01-02 00:00:00.000

    (1 row(s) affected)

    EXAMPLE D
    ACNO TRANREF TRNTYPE AMOUNT
    ------------ ----------- ------- -----------------------
    P074100 308 IND -349.9600
    B025000 308 IND 349.9600

    (2 row(s) affected)

    EXAMPLE E
    PLCODE TOTALS
    ------------ ----------------------------------------
    P074100 -349.9600
    P075120 -44.7600
    P090000 -13.0000

    ExAMPLE F

    PLCODE TOTALS
    ------------ ----------------------------------------
    P074100 -87.9000
    P075120 -44.7600
    P090000 -13.0000


    Peter Guest

  8. #8

    Default Re: Query Help

    1. To verify that r_end >= r_begin, add a CHECK constraint to the table:
    CHECK (r_end>=r_begin)

    2. To find invalid rows where the r_begin is not >= r_end for a previous row
    for the grp_id:
    SELECT DISTINCT R2.*
    FROM member_range AS R1
    JOIN member_range AS R2 ON
    R1.r_id < R2.r_id
    AND R1.grp_id = R2.grp_id
    AND R2.r_begin<R1.r_end

    3. To find invalid, overlapping ranges for the grp_id:
    SELECT DISTINCT R1.*
    FROM member_range AS R1
    JOIN member_range AS R2 ON
    R1.r_id <> R2.r_id
    AND R1.grp_id = R2.grp_id
    AND NOT (R1.r_end<R2.r_begin OR R1.r_begin>R2.r_end)

    You don't need a cursor for any of this. If the above queries return nothing
    then the data is valid.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  9. #9

    Default Re: Query Help

    >> verify that all the new date ranges (r_being, r_end) don't overlap
    per grp_id and r_id. <<

    Your data and your DDL do not match; the range begin and end values are
    shown as character data in the DDL and numeric in the sample data. I'll
    gues you meant numeric ranges.

    You can protect yourself with constraints on the table before you get
    any data in it:

    CREATE TABLE Member_Ranges
    (grp_id INTEGER NOT NULL,
    r_id INTEGER NOT NULL,
    r_begin INTEGER NOT NULL,
    r_end INTEGER(15) NOT NULL,
    r_desc VARCHAR(50) NOT NULL,
    PRIMARY KEY (grp_id, r_id, r_begin),
    UNIQUE (grp_id, r_id, r_end),
    CHECK (r_begin <= r_end));

    Here is a quick check for gaps that works if all ranges are disjoint:

    SELECT R1.*
    FROM Member_Ranges AS R1
    GROUP BY grp_id, r_id
    HAVING MAX(r_end) MIN(r_begin) + 1
    <> SUM(r_end - r_begin + 1);

    There are a few ways of finding overlaps:

    SELECT R1.*
    FROM Member_Ranges AS R1,
    Member_Ranges AS R2
    WHERE R1.grp_id = R2.grp_id -- same group
    AND R1.r_id <> R2.r_id -- different range
    AND R1.r_begin BETWEEN R2.r_begin AND R2.r_end;

    Another way is to have a table of all the integers in the ranges, but
    this does not look like a good idea for your situation. If a sequence
    number is missing, there is a gap; if it appears more than once, there
    is an overlap. Simplifying a bit, to one group and a range of 1 to 100,
    the skeleton looks like this:

    -- overlaps
    SELECT S1.seq
    FROM Sequence AS S1
    LEFT OUTER JOIN
    Ranges AS R1
    ON S1.seq BETWEEN R1.r_begin AND R1.r_end
    WHERE seq BETWEEN 1 AND 100
    GROUP BY S1.seq
    HAVING COUNT(S1.seq) <> 1;

    -- gaps
    SELECT S1.seq
    FROM Sequence AS S1
    WHERE NOT EXISTS
    (SELECT *
    FROM Ranges AS R1
    WHERE seq BETWEEN R1.r_begin AND R1.r_end)
    AND seq BETWEEN 1 AND 100;

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  10. #10

    Default Query Help

    Hello All

    I need a query help.

    Following is my DDL and results expected from a query:

    CREATE TABLE [dbo].[PIReportTemplateColumns1] (
    [ReportTemplateColumnID] [int] IDENTITY (1, 1) NOT
    NULL ,
    [ReportTemplateIDFK] [int] NOT NULL ,
    [ReportTemplateColumnNumber] [smallint] NOT NULL ,
    [ReportTemplateColumnName] [varchar] (50) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ReportTemplateColumnDescription] [varchar] (100)
    COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ReportTemplateColumnCalcType] [varchar] (50)
    COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReportTemplateColumnCalc] [varchar] (200) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReportTemplateColumnCalcValue] [varchar] (200)
    COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReportTemplateColumnBeginDate] [smalldatetime]
    NULL ,
    [ReportTemplateColumnEndDate] [smalldatetime] NULL
    ) ON [PRIMARY]
    GO

    insert into PIReportTemplateColumns1 values
    (58,1,'TMPLMETROSEWAGEFLOWDATA01001Col001','West
    Venturi','TAG','METWVENTFLOW',10098,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,2,'TMPLMETROSEWAGEFLOWDATA01001Col002','East
    Venturi','TAG','METEVENTFLOW',10097,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,3,'TMPLMETROSEWAGEFLOWDATA01001Col003','West
    Total','UDF','Met_udf_PITotalWestPrimaryInfluentFl ow','Met_
    udf_PITotalWestPrimaryInfluentFlow','2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,4,'TMPLMETROSEWAGEFLOWDATA01001Col004','East Primary
    Meter # 2','TAG','METEPRIINFMAG2',7543,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,5,'TMPLMETROSEWAGEFLOWDATA01001Col005','East Primary
    Meter # 3','TAG','METEPRIINFMAG3',7544,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,6,'TMPLMETROSEWAGEFLOWDATA01001Col006','East Primary
    Meter # 4','TAG','METEPRIINFMAG4',7572,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,7,'TMPLMETROSEWAGEFLOWDATA01001Col007','East Primary
    Meter # 5','TAG','METEPRIINFMAG5',7573,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,8,'TMPLMETROSEWAGEFLOWDATA01001Col008','Dye Test
    Adj','UDF','undefined',null,'2003-01-01 00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,9,'TMPLMETROSEWAGEFLOWDATA01001Col009','East
    Total','UDF','Met_udf_PITotalEastPrimaryInfluentFl ow','Met_
    udf_PITotalEastPrimaryInfluentFlow','2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,10,'TMPLMETROSEWAGEFLOWDATA01001Col010','Plant
    Total','UDF','Met_udf_PITotalPlantFlow','Met_udf_P ITotalPla
    ntFlow','2003-01-01 00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,10,'TMPLMETROSEWAGEFLOWDATA01001Col010','Plant
    Total','UDF','Met_udf_PITotalPlantFlow','Met_udf_P ITotalPla
    ntFlow1','2003-01-01 00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,11,'TMPLMETROSEWAGEFLOWDATA01001Col011','West S &
    G','MLB','METM0012',5973,'2003-01-01 00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,12,'TMPLMETROSEWAGEFLOWDATA01001Col012','Secon dary
    Diversion','TAG','MetEastSecBypass',7316,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,13,'TMPLMETROSEWAGEFLOWDATA01001Col013','Prima ry
    Effluent West','MLB','METM0013',5974,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,14,'TMPLMETROSEWAGEFLOWDATA01001Col014','Preci pitation'
    ,'MLB','METM0890',1729,'2003-01-01 00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,15,'TMPLMETROSEWAGEFLOWDATA01001Col015','Plant
    Recycle','UDF','Met_udf_PIPlantRecycle','Met_udf_P IPlantRec
    ycle','2003-01-01 00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,15,'TMPLMETROSEWAGEFLOWDATA01001Col015','Plant
    Recycle','UDF','Met_udf_PIPlantRecycle','Met_udf_P IPlantRec
    ycle1','2003-02-01 00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,16,'TMPLMETROSEWAGEFLOWDATA01001Col016','So. St. Paul
    Flow','TAG','MetSSPInfFlow',7571,'2003-01-01
    00:00:00',null)
    insert into PIReportTemplateColumns1 values
    (58,17,'TMPLMETROSEWAGEFLOWDATA01001Col017','So. St. Paul
    Oxygen Injection','MLB','MetDummy',10360,'2003-01-01
    00:00:00',null)

    I want to query the table so that for a given
    ReportTemplateIDFK in this case 58, and for a given
    datetime I want to select all columns when
    ReportTemplateColumnBeginDate <= givendatetime order by
    ReportTemplateColumnBeginDate desc

    That is if given datetime is '2003-01-29 00:00:00' I
    should have following results:

    1 58 1 TMPLMETROSEWAGEFLOWDATA01001Col001
    West Venturi TAG METWVENTFLOW 10098
    2003-01-01 00:00:00 NULL
    2 58 2 TMPLMETROSEWAGEFLOWDATA01001Col002
    East Venturi TAG METEVENTFLOW 10097
    2003-01-01 00:00:00 NULL
    3 58 3 TMPLMETROSEWAGEFLOWDATA01001Col003
    West Total UDF
    Met_udf_PITotalWestPrimaryInfluentFlow
    Met_udf_PITotalWestPrimaryInfluentFlow 2003-01-01
    00:00:00 NULL
    4 58 4 TMPLMETROSEWAGEFLOWDATA01001Col004
    East Primary Meter # 2 TAG METEPRIINFMAG2
    7543 2003-01-01 00:00:00 NULL
    5 58 5 TMPLMETROSEWAGEFLOWDATA01001Col005
    East Primary Meter # 3 TAG METEPRIINFMAG3
    7544 2003-01-01 00:00:00 NULL
    6 58 6 TMPLMETROSEWAGEFLOWDATA01001Col006
    East Primary Meter # 4 TAG METEPRIINFMAG4
    7572 2003-01-01 00:00:00 NULL
    7 58 7 TMPLMETROSEWAGEFLOWDATA01001Col007
    East Primary Meter # 5 TAG METEPRIINFMAG5
    7573 2003-01-01 00:00:00 NULL
    8 58 8 TMPLMETROSEWAGEFLOWDATA01001Col008
    Dye Test Adj UDF undefined NULL
    2003-01-01 00:00:00 NULL
    9 58 9 TMPLMETROSEWAGEFLOWDATA01001Col009
    East Total UDF
    Met_udf_PITotalEastPrimaryInfluentFlow
    Met_udf_PITotalEastPrimaryInfluentFlow 2003-01-01
    00:00:00 NULL
    10 58 10 TMPLMETROSEWAGEFLOWDATA01001Col010
    Plant Total UDF Met_udf_PITotalPlantFlow
    Met_udf_PITotalPlantFlow 2003-01-01 00:00:00
    NULL
    11 58 11 TMPLMETROSEWAGEFLOWDATA01001Col011
    West S & G MLB METM0012 5973
    2003-01-01 00:00:00 NULL
    12 58 12 TMPLMETROSEWAGEFLOWDATA01001Col012
    Secondary Diversion TAG MetEastSecBypass
    7316 2003-01-01 00:00:00 NULL
    13 58 13 TMPLMETROSEWAGEFLOWDATA01001Col013
    Primary Effluent West MLB METM0013
    5974 2003-01-01 00:00:00 NULL
    14 58 14 TMPLMETROSEWAGEFLOWDATA01001Col014
    Precipitation MLB METM0890 1729
    2003-01-01 00:00:00 NULL
    15 58 15 TMPLMETROSEWAGEFLOWDATA01001Col015
    Plant Recycle UDF Met_udf_PIPlantRecycle
    Met_udf_PIPlantRecycle 2003-01-01 00:00:00
    NULL
    16 58 16 TMPLMETROSEWAGEFLOWDATA01001Col016
    So. St. Paul Flow TAG MetSSPInfFlow
    7571 2003-01-01 00:00:00 NULL
    17 58 17 TMPLMETROSEWAGEFLOWDATA01001Col017
    So. St. Paul Oxygen Injection MLB MetDummy
    10360 2003-01-01 00:00:00 NULL

    That is if given datetime is '2003-02-03 00:00:00' I
    should have following results:

    1 58 1 TMPLMETROSEWAGEFLOWDATA01001Col001
    West Venturi TAG METWVENTFLOW 10098
    2003-01-01 00:00:00 NULL
    2 58 2 TMPLMETROSEWAGEFLOWDATA01001Col002
    East Venturi TAG METEVENTFLOW 10097
    2003-01-01 00:00:00 NULL
    3 58 3 TMPLMETROSEWAGEFLOWDATA01001Col003
    West Total UDF
    Met_udf_PITotalWestPrimaryInfluentFlow
    Met_udf_PITotalWestPrimaryInfluentFlow 2003-01-01
    00:00:00 NULL
    4 58 4 TMPLMETROSEWAGEFLOWDATA01001Col004
    East Primary Meter # 2 TAG METEPRIINFMAG2
    7543 2003-01-01 00:00:00 NULL
    5 58 5 TMPLMETROSEWAGEFLOWDATA01001Col005
    East Primary Meter # 3 TAG METEPRIINFMAG3
    7544 2003-01-01 00:00:00 NULL
    6 58 6 TMPLMETROSEWAGEFLOWDATA01001Col006
    East Primary Meter # 4 TAG METEPRIINFMAG4
    7572 2003-01-01 00:00:00 NULL
    7 58 7 TMPLMETROSEWAGEFLOWDATA01001Col007
    East Primary Meter # 5 TAG METEPRIINFMAG5
    7573 2003-01-01 00:00:00 NULL
    8 58 8 TMPLMETROSEWAGEFLOWDATA01001Col008
    Dye Test Adj UDF undefined NULL
    2003-01-01 00:00:00 NULL
    9 58 9 TMPLMETROSEWAGEFLOWDATA01001Col009
    East Total UDF
    Met_udf_PITotalEastPrimaryInfluentFlow
    Met_udf_PITotalEastPrimaryInfluentFlow 2003-01-01
    00:00:00 NULL
    10 58 10 TMPLMETROSEWAGEFLOWDATA01001Col010
    Plant Total UDF Met_udf_PITotalPlantFlow
    Met_udf_PITotalPlantFlow 2003-01-01 00:00:00
    NULL
    11 58 11 TMPLMETROSEWAGEFLOWDATA01001Col011
    West S & G MLB METM0012 5973
    2003-01-01 00:00:00 NULL
    12 58 12 TMPLMETROSEWAGEFLOWDATA01001Col012
    Secondary Diversion TAG MetEastSecBypass
    7316 2003-01-01 00:00:00 NULL
    13 58 13 TMPLMETROSEWAGEFLOWDATA01001Col013
    Primary Effluent West MLB METM0013
    5974 2003-01-01 00:00:00 NULL
    14 58 14 TMPLMETROSEWAGEFLOWDATA01001Col014
    Precipitation MLB METM0890 1729
    2003-01-01 00:00:00 NULL
    15 58 15 TMPLMETROSEWAGEFLOWDATA01001Col015
    Plant Recycle UDF Met_udf_PIPlantRecycle
    Met_udf_PIPlantRecycle1 2003-02-01 00:00:00
    NULL
    16 58 16 TMPLMETROSEWAGEFLOWDATA01001Col016
    So. St. Paul Flow TAG MetSSPInfFlow
    7571 2003-01-01 00:00:00 NULL
    17 58 17 TMPLMETROSEWAGEFLOWDATA01001Col017
    So. St. Paul Oxygen Injection MLB MetDummy
    10360 2003-01-01 00:00:00 NULL

    Note: It should select ReportTemplateColumnCalcValue
    = 'Met_udf_PIPlantRecycle1' for
    ReportTemplateCoulumnNumber 15 along with other 16 rows.

    Similarly if given datetime is '2003-08-01 00:00:00' I
    should have following results:

    1 58 1 TMPLMETROSEWAGEFLOWDATA01001Col001
    West Venturi TAG METWVENTFLOW 10098
    2003-01-01 00:00:00 NULL
    2 58 2 TMPLMETROSEWAGEFLOWDATA01001Col002
    East Venturi TAG METEVENTFLOW 10097
    2003-01-01 00:00:00 NULL
    3 58 3 TMPLMETROSEWAGEFLOWDATA01001Col003
    West Total UDF
    Met_udf_PITotalWestPrimaryInfluentFlow
    Met_udf_PITotalWestPrimaryInfluentFlow 2003-01-01
    00:00:00 NULL
    4 58 4 TMPLMETROSEWAGEFLOWDATA01001Col004
    East Primary Meter # 2 TAG METEPRIINFMAG2
    7543 2003-01-01 00:00:00 NULL
    5 58 5 TMPLMETROSEWAGEFLOWDATA01001Col005
    East Primary Meter # 3 TAG METEPRIINFMAG3
    7544 2003-01-01 00:00:00 NULL
    6 58 6 TMPLMETROSEWAGEFLOWDATA01001Col006
    East Primary Meter # 4 TAG METEPRIINFMAG4
    7572 2003-01-01 00:00:00 NULL
    7 58 7 TMPLMETROSEWAGEFLOWDATA01001Col007
    East Primary Meter # 5 TAG METEPRIINFMAG5
    7573 2003-01-01 00:00:00 NULL
    8 58 8 TMPLMETROSEWAGEFLOWDATA01001Col008
    Dye Test Adj UDF undefined NULL
    2003-01-01 00:00:00 NULL
    9 58 9 TMPLMETROSEWAGEFLOWDATA01001Col009
    East Total UDF
    Met_udf_PITotalEastPrimaryInfluentFlow
    Met_udf_PITotalEastPrimaryInfluentFlow 2003-01-01
    00:00:00 NULL
    10 58 10 TMPLMETROSEWAGEFLOWDATA01001Col010
    Plant Total UDF Met_udf_PITotalPlantFlow
    Met_udf_PITotalPlantFlow1 2003-06-01 00:00:00
    NULL
    11 58 11 TMPLMETROSEWAGEFLOWDATA01001Col011
    West S & G MLB METM0012 5973
    2003-01-01 00:00:00 NULL
    12 58 12 TMPLMETROSEWAGEFLOWDATA01001Col012
    Secondary Diversion TAG MetEastSecBypass
    7316 2003-01-01 00:00:00 NULL
    13 58 13 TMPLMETROSEWAGEFLOWDATA01001Col013
    Primary Effluent West MLB METM0013
    5974 2003-01-01 00:00:00 NULL
    14 58 14 TMPLMETROSEWAGEFLOWDATA01001Col014
    Precipitation MLB METM0890 1729
    2003-01-01 00:00:00 NULL
    15 58 15 TMPLMETROSEWAGEFLOWDATA01001Col015
    Plant Recycle UDF Met_udf_PIPlantRecycle
    Met_udf_PIPlantRecycle1 2003-02-01 00:00:00
    NULL
    16 58 16 TMPLMETROSEWAGEFLOWDATA01001Col016
    So. St. Paul Flow TAG MetSSPInfFlow
    7571 2003-01-01 00:00:00 NULL
    17 58 17 TMPLMETROSEWAGEFLOWDATA01001Col017
    So. St. Paul Oxygen Injection MLB MetDummy
    10360 2003-01-01 00:00:00 NULL

    Note: It should select ReportTemplateColumnCalcValue
    = 'Met_udf_PIPlantRecycle1'and BeginDate = '2003-02-01
    00:00:00' for ReportTemplateCoulumnNumber 15 AND
    ReportTemplateColumnCalcValue = Met_udf_PITotalPlantFlow1'
    and BeginDate = '2003-06-01 00:00:00' along with other 15
    rows.

    No matter what the result should have total of 17 rows
    based on BeginDatetime.

    Thanks Alot Guys.


    Ricky Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139