Combing 2 Combined Select Statements

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Combing 2 Combined Select Statements

Author

Topic

paulmoss
Starting Member

14 Posts

Posted - 2009-07-17 : 06:46:22

Hi I was hoping that you will be able to help me. I am trying to create a sql script that will display the results of 2 combined select statements. I am looking to combine information for year to date sales information from 2009 and for the same period in 2008. The information is held in the same table and can only be filtered by a date range.

I need to be able to create 2 new columns 1 showing the values for 2009 and one showing the values for 2008. I have attached my code

SELECT Combined.customer, Combined.name, Combined.territory, Combined.order_no, Combined.date_entered, Combined.product, Combined.product_group_a, Combined.product_group_b, Combined.orderqty, Combined.2008ytd, Combined.2009ytd
FROM	(Select Axim2009.customer, Axim2009.name, Axim2009.territory, Axim2009.order_no, Axim2009.date_entered, Axim2009.product, Axim2009.product_group_a, Axim2009.product_group_b, Axim2009.orderqty, Axim2009.value as 2009ytd 
		FROM	(SELECT SalesOrderCombined.customer, SalesOrderCombined.name, SalesOrderCombined.territory, SalesOrderCombined.order_no, SalesOrderCombined.date_entered, SalesOrderCombined.product, SalesOrderCombined.product_group_a, SalesOrderCombined.product_group_b, SalesOrderCombined.orderqty, SalesOrderCombined.value
				FROM cs3live.dbo.SalesOrderCombined SalesOrderCombined
				WHERE (SalesOrderCombined.territory<>'83') AND (SalesOrderCombined.product_group_a='02') AND (SalesOrderCombined.date_entered between '01/01/2008' and '06/30/2008') AND (SalesOrderCombined.product_group_b <> ' ') AND (SalesOrderCombined.status <> 'q')
				UNION
				SELECT TOP (100) PERCENT SalesOrderCombined.customer, SalesOrderCombined.name, SalesOrderCombined.territory, SalesOrderCombined.order_no, SalesOrderCombined.date_entered, SalesOrderCombined.product, SalesOrderCombined.product_group_a, SalesOrderCombined.product_group_b, SalesOrderCombined.orderqty, SalesOrderCombined.value
				FROM cs3live.dbo.SalesOrderCombined SalesOrderCombined
				WHERE (SalesOrderCombined.territory<>'83') AND (SalesOrderCombined.date_entered between '01/01/2008' and '06/30/2008') AND (SalesOrderCombined.product_group_b LIKE '2HD') AND (SalesOrderCombined.status <> 'q'))as Axim2009
		UNION
		SELECT Axim2008.customer, Axim2008.name, Axim2008.territory, Axim2008.order_no, Axim2008.date_entered, Axim2008.product, Axim2008.product_group_a, Axim2008.product_group_b, Axim2008.orderqty, Axim2008.value as 2008ytd
		FROM	(SELECT SalesOrderCombined.customer, SalesOrderCombined.name, SalesOrderCombined.territory, SalesOrderCombined.order_no, SalesOrderCombined.date_entered, SalesOrderCombined.product, SalesOrderCombined.product_group_a, SalesOrderCombined.product_group_b, SalesOrderCombined.orderqty, SalesOrderCombined.value
				FROM cs3live.dbo.SalesOrderCombined SalesOrderCombined
				WHERE (SalesOrderCombined.territory<>'83') AND (SalesOrderCombined.product_group_a='02') AND (SalesOrderCombined.date_entered between '01/01/2009' and '06/30/2009') AND (SalesOrderCombined.product_group_b <> ' ') AND (SalesOrderCombined.status <> 'q')
				UNION
				SELECT TOP (100) PERCENT SalesOrderCombined.customer, SalesOrderCombined.name, SalesOrderCombined.territory, SalesOrderCombined.order_no, SalesOrderCombined.date_entered, SalesOrderCombined.product, SalesOrderCombined.product_group_a, SalesOrderCombined.product_group_b, SalesOrderCombined.orderqty, SalesOrderCombined.value
				FROM cs3live.dbo.SalesOrderCombined SalesOrderCombined
				WHERE (SalesOrderCombined.territory<>'83') AND (SalesOrderCombined.date_entered between '01/01/2009' and '06/30/2009') AND (SalesOrderCombined.product_group_b LIKE '2HD') AND (SalesOrderCombined.status <> 'q')) as Axim2008)) as Combined

Many thanks

Paul

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 07:08:54

Hi

Here formatted query

SELECT Combined.customer        ,
       Combined.name            ,
       Combined.territory       ,
       Combined.order_no        ,
       Combined.date_entered    ,
       Combined.product         ,
       Combined.product_group_a ,
       Combined.product_group_b ,
       Combined.orderqty        ,
       Combined.2008ytd         ,
       Combined.2009ytd
FROM
       (SELECT Axim2009.customer        ,
               Axim2009.name            ,
               Axim2009.territory       ,
               Axim2009.order_no        ,
               Axim2009.date_entered    ,
               Axim2009.product         ,
               Axim2009.product_group_a ,
               Axim2009.product_group_b ,
               Axim2009.orderqty        ,
               Axim2009.value AS 2009ytd
       FROM
               (SELECT SalesOrderCombined.customer        ,
                       SalesOrderCombined.name            ,
                       SalesOrderCombined.territory       ,
                       SalesOrderCombined.order_no        ,
                       SalesOrderCombined.date_entered    ,
                       SalesOrderCombined.product         ,
                       SalesOrderCombined.product_group_a ,
                       SalesOrderCombined.product_group_b ,
                       SalesOrderCombined.orderqty        ,
                       SalesOrderCombined.value
               FROM    cs3live.dbo.SalesOrderCombined SalesOrderCombined
               WHERE (
                               SalesOrderCombined.territory <> '83'
                       )
                   AND (
                               SalesOrderCombined.product_group_a = '02'
                       )
                   AND (
                               SalesOrderCombined.date_entered BETWEEN '01/01/2008' AND '06/30/2008'
                       )
                   AND (
                               SalesOrderCombined.product_group_b <> ' '
                       )
                   AND (
                               SalesOrderCombined.status <> 'q'
                       )
               
               UNION
               
               SELECT TOP (100) PERCENT
                      SalesOrderCombined.customer        ,
                      SalesOrderCombined.name            ,
                      SalesOrderCombined.territory       ,
                      SalesOrderCombined.order_no        ,
                      SalesOrderCombined.date_entered    ,
                      SalesOrderCombined.product         ,
                      SalesOrderCombined.product_group_a ,
                      SalesOrderCombined.product_group_b ,
                      SalesOrderCombined.orderqty        ,
                      SalesOrderCombined.value
               FROM   cs3live.dbo.SalesOrderCombined SalesOrderCombined
               WHERE (
                             SalesOrderCombined.territory <> '83'
                      )
                  AND (
                             SalesOrderCombined.date_entered BETWEEN '01/01/2008' AND '06/30/2008'
                      )
                  AND (
                             SalesOrderCombined.product_group_b LIKE '2HD'
                      )
                  AND (
                             SalesOrderCombined.status <> 'q'
                      )
               )AS Axim2009
       
       UNION
       
       SELECT Axim2008.customer        ,
              Axim2008.name            ,
              Axim2008.territory       ,
              Axim2008.order_no        ,
              Axim2008.date_entered    ,
              Axim2008.product         ,
              Axim2008.product_group_a ,
              Axim2008.product_group_b ,
              Axim2008.orderqty        ,
              Axim2008.value AS 2008ytd
       FROM
              (SELECT SalesOrderCombined.customer        ,
                      SalesOrderCombined.name            ,
                      SalesOrderCombined.territory       ,
                      SalesOrderCombined.order_no        ,
                      SalesOrderCombined.date_entered    ,
                      SalesOrderCombined.product         ,
                      SalesOrderCombined.product_group_a ,
                      SalesOrderCombined.product_group_b ,
                      SalesOrderCombined.orderqty        ,
                      SalesOrderCombined.value
              FROM    cs3live.dbo.SalesOrderCombined SalesOrderCombined
              WHERE (
                              SalesOrderCombined.territory <> '83'
                      )
                  AND (
                              SalesOrderCombined.product_group_a = '02'
                      )
                  AND (
                              SalesOrderCombined.date_entered BETWEEN '01/01/2009' AND '06/30/2009'
                      )
                  AND (
                              SalesOrderCombined.product_group_b <> ' '
                      )
                  AND (
                              SalesOrderCombined.status <> 'q'
                      )
              
              UNION
              
              SELECT TOP (100) PERCENT
                     SalesOrderCombined.customer        ,
                     SalesOrderCombined.name            ,
                     SalesOrderCombined.territory       ,
                     SalesOrderCombined.order_no        ,
                     SalesOrderCombined.date_entered    ,
                     SalesOrderCombined.product         ,
                     SalesOrderCombined.product_group_a ,
                     SalesOrderCombined.product_group_b ,
                     SalesOrderCombined.orderqty        ,
                     SalesOrderCombined.value
              FROM   cs3live.dbo.SalesOrderCombined SalesOrderCombined
              WHERE (
                            SalesOrderCombined.territory <> '83'
                     )
                 AND (
                            SalesOrderCombined.date_entered BETWEEN '01/01/2009' AND '06/30/2009'
                     )
                 AND (
                            SalesOrderCombined.product_group_b LIKE '2HD'
                     )
                 AND (
                            SalesOrderCombined.status <> 'q'
                     )
              ) AS Axim2008
       )) AS Combined

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 07:22:10

Hi
Combined.2008ytd
Combined.2009ytd

it's not recognized...you can try this...

Combined.ytd2008
Combined.ytd2009

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 07:24:22

Hi

I think no need last bracket
as Axim2008)) as Combined
as Axim2008) as Combined

-------------------------
R..

paulmoss
Starting Member

14 Posts

Posted - 2009-07-17 : 07:39:41

Cheers for that I am no able to parse the script with no errors however when I run it I get the following message

Msg 207, Level 16, State 1, Line 10
Invalid column name 'ytd2008'

Many thanks

Paul

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 07:48:58

Hi

In earlier u have a column like this 2008ytd....!!!!!

-------------------------
R..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources