help to pull out data from sql db

hi there,
I have a very tricky question and I hope that someone can understand my problem and can help me

I have and orders table for each order I have some details of products like this :
Orders
OID   | DATE  | CID
100      5/5/17    10
101      10/6/17  11
102       5/8/17    12

Orders Details
ODID    |     OID     | PRODUCTID    |  PRORSUM   |  
1000          100               1                        100
1001          100               2                        200
1002          100               3                         0
1003          101               1                         0
1004          101               2                         0
1005          101               3                        75
1006          102               1                         0
1007          102               2                         0

now i need to get all the orders details that contain sum > 0 its enough if the only row in the order details is >0 to get all the rows but if all the orders details is 0 like in order 102
i don't want it to pull out, for example, this is the result that i need :

OID       |   DATE       |   CID       |    PRODUCTID   |   PRORSUM
100          5/5/17           10                    1                           100
100          5/5/17           10                    2                           200
100          5/5/17           10                   3                             0
101          10/6/17         11                    1                            0
101          10/6/17         11                    2                            0
101         10/6/17           11                   3                          75
now order 102 will not pull out because  all the PROSUM there are 0 so i don't want to see that
thanks....
Tech_MenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
Hi,
I think if you get a cumulative sum of Prosum for each OID then you can ignore the ones which end up being 0, you can include the following query into your query and ignore all OId's which end up being 0 if i understand your question correctly.


select Oid,max (ColSum) from (
      SELECT
            Oid,
            Odid,
            SUM(Prosum) OVER(PARTITION BY Oid ORDER BY Odid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ColSum
      FROM OrderDetails

) as List
group by Oid


I think the whole query should be something like this:


select * from Orders
left outer join OrderDetails
on Orders.Oid = OrderDetails.Oid
WHERE Orders.Oid not in
(
      select Oid from (
      select Oid,max (ColSum) as S from (
      
            SELECT
                  Oid,
                  Odid,
                  SUM(Prosum) OVER(PARTITION BY Oid ORDER BY Odid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ColSum
            FROM OrderDetails

      ) as List
      group by Oid
      ) as ignoreList
      where S = 0
)
Tech_MenAuthor Commented:
hi, thanks for your answer this is the real query maybe u can help me where to put your code :

the order details are: TMdetiles
the product sum is: TMParitSum
select tm.TMOpenDate as 'DATE','TEODA' as 'DOCTYPE',TeodotMisRunId  as 'DOCNUM',tm.TmCloseAsmhta as 'INVNUM',h.KablaNum as 'REC',p.ProAsmhta as 'RESHIMON',pt.ProductsTypeName as 'ENGTYPE',p.ProDiskitNum as 'TABOA' ,
ISNULL(tmd.TMparitKamot,1) * ISNULL(tmd.TMParitSum,0) - ISNULL(tmd.TMparitKamot,1) * ISNULL(tmd.TMParitSum,0) * (tmd.TMHanParit / 100)   as 'BEFORVAT' 
from

Products p 
inner join  ProTypes pt on p.ProTypeKey = pt.ProductTypeRunId
inner join  TMdetiles tmd  on tmd.TMmkt = p.ProductKey
inner join TedotMisMain tm on tmd.TMNum = tm.TeodotMisRunId
inner join Hasboniot h on tm.TmCloseAsmhta= h.HasbonitNum
where 
Year(tm.TMOpenDate) = 2016
and tm.TmCloseAsmhta is not null
order by TeodotMisRunId

Open in new window

Ares KurkluSoftware EngineerCommented:
I guess we don't need the orders table and we can do it in details, a little bit hard without being able run it but i guess should be something like this:



select tm.TMOpenDate as 'DATE','TEODA' as 'DOCTYPE',TeodotMisRunId  as 'DOCNUM',tm.TmCloseAsmhta as 'INVNUM',h.KablaNum as 'REC',p.ProAsmhta as 'RESHIMON',pt.ProductsTypeName as 'ENGTYPE',p.ProDiskitNum as 'TABOA' ,
ISNULL(tmd.TMparitKamot,1) * ISNULL(tmd.TMParitSum,0) - ISNULL(tmd.TMparitKamot,1) * ISNULL(tmd.TMParitSum,0) * (tmd.TMHanParit / 100)   as 'BEFORVAT'
from

Products p
inner join  ProTypes pt on p.ProTypeKey = pt.ProductTypeRunId
inner join  

(
      select * from  TMdetiles
      WHERE TMdetiles.Oid not in
      (
              select Oid from (
              select Oid,max (ColSum) as S from (
     
                        SELECT
                                Oid,
                                Odid,
                                SUM(Prosum) OVER(PARTITION BY Oid ORDER BY Odid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ColSum
                        FROM TMdetiles

              ) as List
              group by Oid
              ) as ignoreList
              where S = 0
      )
)as

 tmd  on tmd.TMmkt = p.ProductKey
inner join TedotMisMain tm on tmd.TMNum = tm.TeodotMisRunId
inner join Hasboniot h on tm.TmCloseAsmhta= h.HasbonitNum
where
Year(tm.TMOpenDate) = 2016
and tm.TmCloseAsmhta is not null
order by TeodotMisRunId

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Tech_MenAuthor Commented:
Amazing A++ thanks...
dbaSQLCommented:
This will work:

IF OBJECT_ID('tempdb..#orders') IS NOT NULL
DROP TABLE #orders
CREATE TABLE #orders (OID INT,DATE CHAR(8),CID INT)
INSERT #orders (OID,DATE,CID)
VALUES (100,'5/5/17',10),
(101,'10/6/17',11),
(102,'5/8/17',12);

IF OBJECT_ID('tempdb..#orderDetails') IS NOT NULL
DROP TABLE #orderDetails
CREATE TABLE #orderDetails (ODID INT,OID INT,PRODUCTID INT,PRORSUM INT)
INSERT #orderDetails (ODID,OID,PRODUCTID,PRORSUM)
VALUES(1000,100,1,100),
(1001,100,2,200),
(1002,100,3,0),
(1003,101,1,0),
(1004,101,2,0),
(1005,101,3,75),
(1006,102,1,0),
(1007,102,2,0);

/*
SELECT o.OID,o.DATE,o.CID,od1.PRODUCTID,od1.PRORSUM  <--- JUST TO CREATE A WORKING TABL FOR THE FINAL SELECT
INTO #data
FROM #orders o JOIN #orderDetails od1
  ON o.OID = od1.OID  */

DECLARE @orders TABLE (OID INT,DATE CHAR(8),CID INT,PRODUCTID INT,PRIMARY KEY(OID,PRODUCTID))
INSERT @orders
SELECT OID,DATE,CID,MIN(PRODUCTID) 
FROM #data 
WHERE (PRODUCTID > 1 AND PRORSUM <> 0)
GROUP BY OID,DATE,CID

SELECT d.OID,d.DATE,d.CID,d.PRODUCTID,d.PRORSUM
FROM #data d JOIN @orders o
  ON d.OID = o.OID
  AND d.DATE = o.DATE
  AND d.CID = o.CID
 

Open in new window

AndyAinscowFreelance programmer / ConsultantCommented:
Just an aside
>>I have a very tricky question...

This is actually trivial if ones knows some basic SQL statements.  You really ought to either attend a beginner course or study a basic book about SQL.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.