Get Group and multiply records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get Group and multiply records

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-20 : 17:43:32

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-20 : 19:29:27

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-21 : 04:31:42

Thanks a Lot.. It is working ..

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-21 : 04:31:58

Thanks a Lot.. It is working ..
and thanks for correcting my insert command too

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-21 : 16:15:39

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-21 : 17:11:08

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-22 : 02:39:54

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-22 : 08:19:13

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-22 : 09:03:10

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-22 : 11:59:20

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-22 : 12:13:40

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-22 : 12:26:35

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-22 : 13:22:48

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-22 : 13:50:27

[code]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[/code]

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-12-22 : 14:19:10

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources