SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get Group and multiply records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/20/2013 :  17:43:32  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result

======================================================================
CREATE TABLE and Insert Data
======================================================================
use master
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BT' ,'BOAT' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'CA' ,'CAR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BI' ,'BIKE' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'CY' ,'CYCLE' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'TR' ,'TRAM' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BM' ,'BMW' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AI' ,'AUDI' ,'No')



CREATE TABLE [dbo].[Travel_Occurs](
[Load_Id] [int] NULL,
[Mode_Sno] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Parent_Sno] [int] NULL,
[Quantity] [numeric](18, 0) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,1,'AP',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,2,'SE',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,3,'SP',1,3)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,4,'BT',1,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,5,'RD',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,6,'BU',5,10)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,7,'CA',5,20)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,8,'BI',5,15)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,9,'CY',5,2)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,10,'TR',5,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,11,'BM',7,6)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,12,'AI',7,14)





======================================================================
CREATE #TempTable -- for easy result generation (Optional )
======================================================================
Concept Used ::
Quantity of the High Nodes to be Multiply with Leaf level Data

Mode_Info Mode_Info_Detail Mode_Info_Inculdes Has_Nodes Quantity Calc_Quantity
AIR AIR No 4 4
SEA Yes 2 no display + used for calc only
SEA SHIP No 3 6
SEA BOAT No 5 10
ROAD Yes 3 no display + used for calc only
ROAD BUS No 10 30
ROAD CAR Yes 20 no display + used for calc only
ROAD BIKE No 15 45
ROAD CYCLE No 2 6
ROAD TRAM No 5 15
ROAD CAR BMW No 6 360
ROAD CAR AUDI No 14 840





===============================================================================
Expected Result ================================================================================


Mode_Info Mode_Detail Quantity
Air 4
SEA SHIP 6
SEA BOAT 10
ROAD BUS 30
ROAD BIKE 4
ROAD CYCLE 6
ROAD TRAM 15
ROAD BMW 360
ROAD AUDI 840



Small mistake on insert query
Updated the Quantity Values as 0 for the below columns
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'AP')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'SE')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'RD')

Kindly provide me the query


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Edited by - shanmugaraj on 12/21/2013 16:14:42

bitsmed
Constraint Violating Yak Guru

397 Posts

Posted - 12/20/2013 :  19:29:27  Show Profile  Reply with Quote
Line 3 and 4 when adding to table travel_occurs, are pointing to "air". Should be pointing to "sea".

Try this sql:

with cte
  as (select load_id
            ,mode_sno
            ,mode_id
            ,mode_parent_sno
            ,quantity
            ,mode_id as last_mode_id
        from travel_occurs
       where mode_parent_sno=-1
      union all
      select a.load_id
            ,a.mode_sno
            ,a.mode_id
            ,b.mode_parent_sno
            ,cast(b.quantity*a.quantity as numeric(18,0)) as quantity
            ,b.mode_id as last_mode_id
        from travel_occurs as a
             inner join cte as b
                     on b.load_id=a.load_id
                    and b.mode_sno=a.mode_parent_sno
       where a.mode_parent_sno<>-1
     )
select c.mode_info
      ,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
      ,b.quantity
  from travel_master as a
       inner join cte as b
               on b.load_id=a.load_id
              and b.mode_id=a.mode_id
       inner join travel_master as c
               on c.load_id=b.load_id
              and c.mode_id=b.last_mode_id
 where a.has_nodes='No'
 order by a.load_id
         ,b.mode_sno
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/21/2013 :  04:31:42  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Thanks a Lot.. It is working ..

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/21/2013 :  04:31:58  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Thanks a Lot.. It is working ..
and thanks for correcting my insert command too

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/21/2013 :  16:15:39  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Above CTE is working in case of having values in Quantity.
An update in my data where we have -1, the quantity will be 0.
Kindly provide me the query

UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'AP')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'SE')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'RD')

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Edited by - shanmugaraj on 12/21/2013 16:20:53
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

397 Posts

Posted - 12/21/2013 :  17:11:08  Show Profile  Reply with Quote
quote:
Originally posted by shanmugaraj

Above CTE is working in case of having values in Quantity.
An update in my data where we have -1, the quantity will be 0.
Kindly provide me the query

UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'AP')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'SE')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'RD')

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com


As per your requirement
quote:
Quantity of the High Nodes to be Multiply with Leaf level Data
the result should be zero. But with a small tweak, you can avoid it with this sql:

with cte
  as (select load_id
            ,mode_sno
            ,mode_id
            ,mode_parent_sno
            ,quantity
            ,mode_id as last_mode_id
        from travel_occurs
       where mode_parent_sno=-1
      union all
      select a.load_id
            ,a.mode_sno
            ,a.mode_id
            ,b.mode_parent_sno
            ,cast(case when b.quantity=0 then 1 else b.quantity end*case when a.quantity=0 then 1 else a.quantity end as numeric(18,0)) as quantity
            ,b.mode_id as last_mode_id
        from travel_occurs as a
             inner join cte as b
                     on b.load_id=a.load_id
                    and b.mode_sno=a.mode_parent_sno
       where a.mode_parent_sno<>-1
     )
select c.mode_info
      ,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
      ,b.quantity
  from travel_master as a
       inner join cte as b
               on b.load_id=a.load_id
              and b.mode_id=a.mode_id
       inner join travel_master as c
               on c.load_id=b.load_id
              and c.mode_id=b.last_mode_id
 where a.has_nodes='No'
 order by a.load_id
         ,b.mode_sno
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/22/2013 :  02:39:54  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Hi, The Answer is wrong.. which is below.
Reload Records..

delete
FROM [NCGProductReports].[dbo].[Travel_Occurs]


INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,1,'AP',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,2,'SE',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,3,'SP',2,3)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,4,'BT',2,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,5,'RD',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,6,'BU',5,10)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,7,'CA',5,20)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,8,'BI',5,15)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,9,'CY',5,2)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,10,'TR',5,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,11,'BM',7,6)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,12,'AI',7,14)



mode_info mode_detail quantity
AIR 0
SEA SHIP 3
SEA BOAT 5
ROAD BUS 10
ROAD BIKE 15
ROAD CYCLE 2
ROAD TRAM 5
CAR BMW 120
CAR AUDI 280

Problem : Car is comming inspite of road.
Expected Result


AIR 0
SEA SHIP 3
SEA BOAT 5
ROAD BUS 10
ROAD BIKE 15
ROAD CYCLE 2
ROAD TRAM 5
ROAD BMW 120
ROAD AUDI 280



THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Edited by - shanmugaraj on 12/22/2013 03:51:25
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

397 Posts

Posted - 12/22/2013 :  08:19:13  Show Profile  Reply with Quote
Try this:
with cte
  as (select load_id
            ,mode_sno
            ,mode_id
            ,mode_parent_sno
            ,quantity
            ,mode_id as last_mode_id
        from travel_occurs
      union all
      select a.load_id
            ,a.mode_sno
            ,b.mode_id
            ,b.mode_parent_sno
            ,cast(case when b.quantity=0 then 1 else b.quantity end*case when a.quantity=0 then 1 else a.quantity end as numeric(18,0)) as quantity
            ,a.mode_id as last_mode_id
        from travel_occurs as a
             inner join cte as b
                     on b.load_id=a.load_id
                    and b.mode_sno=a.mode_parent_sno
     )
select c.mode_info
      ,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
      ,b.quantity
  from travel_master as a
       inner join cte as b
               on b.load_id=a.load_id
              and b.last_mode_id=a.mode_id
       inner join travel_master as c
               on c.load_id=b.load_id
              and c.mode_id=b.mode_id
 where a.has_nodes='No'
   and b.mode_parent_sno=-1
 order by a.load_id
         ,b.mode_sno
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/22/2013 :  09:03:10  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Hi bistmed,
Thanks for your reply and the query is working fine.
Could you please also consider the below scenario of my table travel_occurs where I have 0 in Root and the row should not display
My below example, i update AI Quantity as 0 and in the result, AI row should not be considered.


Query
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'AI')


Expected Result


AIR 0
SEA SHIP 3
SEA BOAT 5
ROAD BUS 10
ROAD BIKE 15
ROAD CYCLE 2
ROAD TRAM 5
ROAD BMW 120

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

397 Posts

Posted - 12/22/2013 :  11:59:20  Show Profile  Reply with Quote
quote:
Originally posted by shanmugaraj

Hi bistmed,
Thanks for your reply and the query is working fine.
Could you please also consider the below scenario of my table travel_occurs where I have 0 in Root and the row should not display
My below example, i update AI Quantity as 0 and in the result, AI row should not be considered.


Query
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'AI')


Expected Result


AIR 0
SEA SHIP 3
SEA BOAT 5
ROAD BUS 10
ROAD BIKE 15
ROAD CYCLE 2
ROAD TRAM 5
ROAD BMW 120

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com


with cte
  as (select load_id
            ,mode_sno
            ,mode_id
            ,mode_parent_sno
            ,quantity
            ,mode_id as last_mode_id
        from travel_occurs
      union all
      select a.load_id
            ,a.mode_sno
            ,b.mode_id
            ,b.mode_parent_sno
            ,cast(case when b.quantity=0 then 1 else b.quantity end*case when a.quantity=0 then 1 else a.quantity end as numeric(18,0)) as quantity
            ,a.mode_id as last_mode_id
        from travel_occurs as a
             inner join cte as b
                     on b.load_id=a.load_id
                    and b.mode_sno=a.mode_parent_sno
     )
select c.mode_info
      ,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
      ,b.quantity
  from travel_master as a
       inner join cte as b
               on b.load_id=a.load_id
              and b.last_mode_id=a.mode_id
       inner join travel_master as c
               on c.load_id=b.load_id
              and c.mode_id=b.mode_id
 where a.has_nodes='No'
   and b.mode_parent_sno=-1
   and b.quantity>0
 order by a.load_id
         ,b.mode_sno
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/22/2013 :  12:13:40  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Not working :(
Still i can see Result as below
ROAD AUDI 20

where 20 is the parent value of AUDI.


============================
RESULT FROM SQL on Execution of Query
============================

mode_info mode_detail quantity
AIR 0
SEA SHIP 3
SEA BOAT 5
ROAD BUS 10
ROAD BIKE 15
ROAD CYCLE 2
ROAD TRAM 5
ROAD BMW 120
ROAD AUDI 20



THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Edited by - shanmugaraj on 12/22/2013 12:16:30
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

397 Posts

Posted - 12/22/2013 :  12:26:35  Show Profile  Reply with Quote
Ahh, I see. Try this then:
with cte
  as (select load_id
            ,mode_sno
            ,mode_id
            ,mode_parent_sno
            ,quantity
            ,mode_id as last_mode_id
        from travel_occurs
      union all
      select a.load_id
            ,a.mode_sno
            ,b.mode_id
            ,b.mode_parent_sno
            ,cast(case when b.quantity=0 then 1 else b.quantity end*case when a.quantity=0 then 1 else a.quantity end as numeric(18,0)) as quantity
            ,a.mode_id as last_mode_id
        from travel_occurs as a
             inner join cte as b
                     on b.load_id=a.load_id
                    and b.mode_sno=a.mode_parent_sno
       where a.quantity>0
     )
select c.mode_info
      ,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
      ,b.quantity
  from travel_master as a
       inner join cte as b
               on b.load_id=a.load_id
              and b.last_mode_id=a.mode_id
       inner join travel_master as c
               on c.load_id=b.load_id
              and c.mode_id=b.mode_id
 where a.has_nodes='No'
   and b.mode_parent_sno=-1
 order by a.load_id
         ,b.mode_sno
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/22/2013 :  13:22:48  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
The Above query is working if have my Car value > 0.
Consider my immediate parent value is 0 , i dont see its children.
I expect to have the children values to display also [ my below should also display BMW value ]


Query
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'CA')

Result

mode_info mode_detail quantity
AIR 0
SEA SHIP 3
SEA BOAT 5
ROAD BUS 10
ROAD BIKE 15
ROAD CYCLE 2
ROAD TRAM 5


Expected
=========
mode_info mode_detail quantity
AIR 0
SEA SHIP 3
SEA BOAT 5
ROAD BUS 10
ROAD BIKE 15
ROAD CYCLE 2
ROAD TRAM 5
ROAD BMW 6

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Edited by - shanmugaraj on 12/22/2013 13:23:51
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

397 Posts

Posted - 12/22/2013 :  13:50:27  Show Profile  Reply with Quote
with cte
  as (select load_id
            ,mode_sno
            ,mode_id
            ,mode_parent_sno
            ,quantity
            ,mode_id as last_mode_id
        from travel_occurs
      union all
      select a.load_id
            ,a.mode_sno
            ,b.mode_id
            ,b.mode_parent_sno
            ,cast(case when b.quantity=0 then 1 else b.quantity end*case when a.quantity=0 then 1 else a.quantity end as numeric(18,0)) as quantity
            ,a.mode_id as last_mode_id
        from travel_occurs as a
             inner join cte as b
                     on b.load_id=a.load_id
                    and b.mode_sno=a.mode_parent_sno
     )
select c.mode_info
      ,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
      ,b.quantity
  from travel_master as a
       inner join cte as b
               on b.load_id=a.load_id
              and b.last_mode_id=a.mode_id
       inner join travel_master as c
               on c.load_id=b.load_id
              and c.mode_id=b.mode_id
       inner join travel_occurs as d
               on d.load_id=b.load_id
              and d.mode_sno=b.mode_sno
 where a.has_nodes='No'
   and b.mode_parent_sno=-1
   and (d.mode_parent_sno=-1
    or  d.quantity>0
       )
 order by a.load_id
         ,b.mode_sno
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 12/22/2013 :  14:19:10  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Some Issue in New Join made with table d. Please Help
I get the needed result for the small sample data i gave and it is fine.
It is not fine since the time taken is more and i can see more records in my table and query takes more time to execute
Earlier result for my table rows were 580+
Now it is showing 18,000+

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Edited by - shanmugaraj on 12/23/2013 01:00:06
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000