locked
Sql Query RRS feed

  • Question

  • Hi All,

    i have one table. i want to dispaly data, if i pass (Startdate(2004) and Enddate (2009).

    i want to display Pname if present for all years otherwise i don't want to dispaly that Pname.

    Thanks in advance....


    • Edited by BHVS Tuesday, April 12, 2016 7:42 AM i don't want to dispaly that Pname
    Tuesday, April 12, 2016 7:40 AM

Answers

  • Thanks for quick response...

    i want to display pname, if it is available for all years...

    I am passing startdate and enddate parameters....

    I am using single table....

    Thanks in Advance....

    I think you can use a logic like this

    ;With Numbers(N)
    AS
    (
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 
    ),NumberMatrix(N)
    AS
    (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM Numbers n1
    CROSS JOIN NUmbers n2
    CROSS JOIN NUmbers n3
    CROSS JOIN NUmbers n4
    CROSS JOIN NUmbers n5
    )
    SELECT m.Pname
    FROM
    (
    SELECT @StartDate + (N -1) AS Yr,
    t.Pname
    FROM NumberMatrix n
    CROSS JOIN (SELECT DISTINCT PName FROM Table) t
    WHERE @StartDate + (N -1) <= @EndDate
    )m
    LEFT JOIN Table n
    ON n.Pname = m.Pname
    AND n.[Year] = m.Yr
    GROUP BY m.Pname
    HAVING SUM(CASE WHEN n.Pname IS NULL THEN 1 ELSE 0 END) = 0


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by BHVS Tuesday, April 12, 2016 9:12 AM
    Tuesday, April 12, 2016 8:29 AM

All replies

  • Hi

    try this to show items from 2004 to 2009

    select pname from Table

    WHERE

    (year =< 2009 )

    AND

    (year >= 2004 )

    ORDER BY Pname

    try this to show items for year= 2004 OR year =  2009

    select pname from Table

    WHERE

    (year = 2009 )

    OR

    (year = 2004 )

    ORDER BY Pname


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Tuesday, April 12, 2016 7:49 AM
  • Thanks for quick response...

    i want to display pname, if it is available for all years...

    I am passing startdate and enddate parameters....

    I am using single table....

    Thanks in Advance....

    Tuesday, April 12, 2016 7:54 AM
  • Try this. Work out the number of years between the two you want, and then do a count of the distinct rows grouped by year and product (to remove dups).

    DECLARE @T TABLE(id INT IDENTITY(1,1),Year INT,pname NVARCHAR(20))
    INSERT INTO @T(Year,pname) SELECT 2004,'Pen'
    INSERT INTO @T(Year,pname) SELECT 2005,'Pen'
    INSERT INTO @T(Year,pname) SELECT 2006,'Pen'
    INSERT INTO @T(Year,pname) SELECT 2007,'Pen'
    INSERT INTO @T(Year,pname) SELECT 2008,'Pen'
    INSERT INTO @T(Year,pname) SELECT 2009,'Pen'
    INSERT INTO @T(Year,pname) SELECT 2004,'Pencil'
    INSERT INTO @T(Year,pname) SELECT 2005,'Pencil'
    INSERT INTO @T(Year,pname) SELECT 2006,'Pencil'
    INSERT INTO @T(Year,pname) SELECT 2007,'Pencil'
    INSERT INTO @T(Year,pname) SELECT 2009,'Pencil'
    INSERT INTO @T(Year,pname) SELECT 2004,'Soap'
    INSERT INTO @T(Year,pname) SELECT 2005,'Soap'
    INSERT INTO @T(Year,pname) SELECT 2006,'Soap'
    INSERT INTO @T(Year,pname) SELECT 2007,'Soap'
    INSERT INTO @T(Year,pname) SELECT 2009,'Soap'
    
    DECLARE @St INT=2004,@En INT=2009
    
    SELECT Pname,COUNT(*) FROM (
    SELECT Year,Pname 
    FROM @T
    GROUP BY Year,Pname) d
    GROUP BY Pname
    HAVING COUNT(*)=(@En-@St+1)


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    Tuesday, April 12, 2016 8:02 AM
  • Thanks Gavin Clayton...

    it's working...

     
    Tuesday, April 12, 2016 8:26 AM
  • Thanks for quick response...

    i want to display pname, if it is available for all years...

    I am passing startdate and enddate parameters....

    I am using single table....

    Thanks in Advance....

    I think you can use a logic like this

    ;With Numbers(N)
    AS
    (
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 
    ),NumberMatrix(N)
    AS
    (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM Numbers n1
    CROSS JOIN NUmbers n2
    CROSS JOIN NUmbers n3
    CROSS JOIN NUmbers n4
    CROSS JOIN NUmbers n5
    )
    SELECT m.Pname
    FROM
    (
    SELECT @StartDate + (N -1) AS Yr,
    t.Pname
    FROM NumberMatrix n
    CROSS JOIN (SELECT DISTINCT PName FROM Table) t
    WHERE @StartDate + (N -1) <= @EndDate
    )m
    LEFT JOIN Table n
    ON n.Pname = m.Pname
    AND n.[Year] = m.Yr
    GROUP BY m.Pname
    HAVING SUM(CASE WHEN n.Pname IS NULL THEN 1 ELSE 0 END) = 0


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by BHVS Tuesday, April 12, 2016 9:12 AM
    Tuesday, April 12, 2016 8:29 AM