Combining Queries - SQL Server Forums

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

Combining Queries

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-15 : 10:20:50

There are 3 queries, its all the same except the date range. One is first 7 days which is denoted by +6 of start_date since the start date is 1 day already, then its the last 7 which is date_sub +7 and the last one is the past 1 day. How can I combined it all into 1 query where it shows

name, io_id,line_id, line.description, volumn of 1st 7 days, volumn of last 7 days, volumn of yesterday, sales of 1st 7 days, sales of last 7 days, sales of yesterday, and so forth?


SELECT  VZ.name,Sales.io_id, line.id, line.description, 
	SUM( sales.volume) AS Volumn,
	SUM(sales.Sales)AS Sales,
	CONCAT((SUM(Sales.sales)/SUM(sales.volumn))*100,'%') AS ATC,
	CONCAT(vz.goal,'%') AS Goal

FROM 	sales_daily sales,line_items line, line_Info VZ

WHERE 	sales.io_id = line.io_id AND sales.io_id = VZ.id AND sales.io_line_item_id = line.id
AND	sales.io_id IN('25761','25761','26889','24817','27991','25016','26338','25740','27707','26442','27644','27921','25960','25961','27305','26728','26351','26257','26564','26443','27738')
AND 	agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)

GROUP BY sales.io_id, sales.io_line_item_id


SELECT  VZ.name,Sales.io_id, line.id, line.description, 
	SUM( sales.volume) AS Volumn,
	SUM(sales.Sales)AS Sales,
	CONCAT((SUM(Sales.sales)/SUM(sales.volumn))*100,'%') AS ATC,
	CONCAT(vz.goal,'%') AS Goal

FROM 	sales_daily sales,line_items line, line_Info VZ

WHERE 	sales.io_id = line.io_id AND sales.io_id = VZ.id AND sales.io_line_item_id = line.id
AND	sales.io_id IN('25761','25761','26889','24817','27991','25016','26338','25740','27707','26442','27644','27921','25960','25961','27305','26728','26351','26257','26564','26443','27738')
AND 	line.start_date between line.start_date and line_start_date + interval 6. 

GROUP BY sales.io_id, sales.io_line_item_id


SELECT  VZ.name,Sales.io_id, line.id, line.description, 
	SUM( sales.volume) AS Volumn,
	SUM(sales.Sales)AS Sales,
	CONCAT((SUM(Sales.sales)/SUM(sales.volumn))*100,'%') AS ATC,
	CONCAT(vz.goal,'%') AS Goal

FROM 	sales_daily sales,line_items line, line_Info VZ

WHERE 	sales.io_id = line.io_id AND sales.io_id = VZ.id AND sales.io_line_item_id = line.id
AND	sales.io_id IN('25761','25761','26889','24817','27991','25016','26338','25740','27707','26442','27644','27921','25960','25961','27305','26728','26351','26257','26564','26443','27738')
AND 	agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)

GROUP BY sales.io_id, sales.io_line_item_id

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-15 : 10:28:37

?
Isn't it enough to just execute the first query?

No, you're never too old to Yak'n'Roll if you're too young to die.

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-15 : 10:35:24

quote:
Originally posted by webfred

?
Isn't it enough to just execute the first query?

No, you're never too old to Yak'n'Roll if you're too young to die.

The first query is to execute the last 7 days of data, the second query is to execute just yesterday's data. How can I combine those 2 s it shows side by side, yesterday and the last 7 days.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-15 : 19:51:04

If the three queries are the same, you could combine all three into one as shown below. I removed the date condition from the where clause and put it inside the aggregation.

Alternatively, you could wrap each select in a CTE and then join all three on whatever common things there are - or just on io_id if that will uniquely identify each row of the CTEs.

SELECT  VZ.name,Sales.io_id, line.id, line.description, 
   SUM( CASE WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN
         sales.volume
      END ) AS Volumn_FROM_FIRST_QUERY,
   SUM( CASE WHEN  line.start_date between line.start_date and line_start_date + interval 6. THEN
      sales.volume
      END ) AS Volumn_FROM_SECOND_QUERY,
      agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   SUM( CASE WHEN  agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)  THEN
      sales.volume
      END ) AS Volumn_FROM_THIRD_QUERY
   --- ETC

FROM    sales_daily sales,line_items line, line_Info VZ

WHERE    sales.io_id = line.io_id AND sales.io_id = VZ.id AND sales.io_line_item_id = line.id
AND   sales.io_id IN('25761','25761','26889','24817','27991','25016','26338','25740','27707','26442','27644','27921','25960','25961','27305','26728','26351','26257','26564','26443','27738')

GROUP BY sales.io_id, sales.io_line_item_id

The code above does not even parse, because of variable names like "interval 6.". So I am only trying to demonstrate the concept.

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-18 : 09:18:51

quote:
Originally posted by sunitabeck

If the three queries are the same, you could combine all three into one as shown below. I removed the date condition from the where clause and put it inside the aggregation.

Alternatively, you could wrap each select in a CTE and then join all three on whatever common things there are - or just on io_id if that will uniquely identify each row of the CTEs.
SELECT VZ.name,Sales.io_id, line.id, line.description,
SUM( CASE WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN
sales.volume
END ) AS Volumn_FROM_FIRST_QUERY,
SUM( CASE WHEN line.start_date between line.start_date and line_start_date + interval 6. THEN
sales.volume
END ) AS Volumn_FROM_SECOND_QUERY,
agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
SUM( CASE WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) THEN
sales.volume
END ) AS Volumn_FROM_THIRD_QUERY
--- ETC

FROM sales_daily sales,line_items line, line_Info VZ

WHERE sales.io_id = line.io_id AND sales.io_id = VZ.id AND sales.io_line_item_id = line.id
AND sales.io_id IN('25761','25761','26889','24817','27991','25016','26338','25740','27707','26442','27644','27921','25960','25961','27305','26728','26351','26257','26564','26443','27738')

GROUP BY sales.io_id, sales.io_line_item_id

The code above does not even parse, because of variable names like "interval 6.". So I am only trying to demonstrate the concept.

This is very helpful. Thank you for it. But how can I layer do the sum case if I wanted to divide the sales and volumn like this:

ifnull(CONCAT((SUM(Sales.sales)/SUM(sales.volumn))*100,'%'),0) AS ATC

Would it be like this: ifnull(Concat (case when agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) then (sum(sales.sales)/sum(sales/volumn)*100)*100,'%'),0) end ) as ATC ?

what if there is no Concat, and just the sum(sales.sales)/sum(sales.volumn)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 12:26:13

I did not quite follow the logic of calculating SUM(Sales.sales) and the SUM(sales.volumn). So I am guessing here that you want to calculate the percentage of each of this sums to the total volume. If that is so, you can do something like this. The things shown in red are place-holders, which I will describe below:


isnull(100.0*SUM(Sales.sales)/nullif(SUM(sales.volumn),0),0)

This will give you a floating point number. If you really wanted to add a percentage sign to it, you can cast it as varchar and then append a percentage sign as shown below, BUT:


cast(isnull(100.0*SUM(Sales.sales)/nullif(SUM(sales.volumn),0),0) as varchar(32))+'%'

The "BUT" I was going to say is that adding this percentage sign is better done in the presentation layer or wherever you are going to use it.

Now about the two sums that I have in red above. This is where the business logic is not clear to me. If you are trying to take the ratio of the data with the date filter applied and with the data without the date filter applied, then you would replace the part highlighted in red with this:


SUM( CASE WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN
         sales.volume
      END )
/nullif(
Sum(sales.volume),0)

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-18 : 14:09:28

quote:
Originally posted by sunitabeck

I did not quite follow the logic of calculating SUM(Sales.sales) and the SUM(sales.volumn). So I am guessing here that you want to calculate the percentage of each of this sums to the total volume. If that is so, you can do something like this. The things shown in red are place-holders, which I will describe below:

isnull(100.0*SUM(Sales.sales)/nullif(SUM(sales.volumn),0),0)

This will give you a floating point number. If you really wanted to add a percentage sign to it, you can cast it as varchar and then append a percentage sign as shown below, BUT:

cast(isnull(100.0*SUM(Sales.sales)/nullif(SUM(sales.volumn),0),0) as varchar(32))+'%'

The "BUT" I was going to say is that adding this percentage sign is better done in the presentation layer or wherever you are going to use it.

Now about the two sums that I have in red above. This is where the business logic is not clear to me. If you are trying to take the ratio of the data with the date filter applied and with the data without the date filter applied, then you would replace the part highlighted in red with this:

SUM( CASE WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN
sales.volume
END )
/nullif(
Sum(sales.volume),0)

Thanks for your help. I was being unclear, sorry about that. I really just wanted to see the ratio, sales/volumn with the same case as the original query, 1 for last 7 days, 1 for first 7 days, and 1 for yesterday. So I guess the query for the Last 7 days would be

SUM( CASE WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN
sales.volume
END )
/sum( Case( nullif(
(sales.volume),0) when agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) as 'ATC'

?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 15:05:25

I think all you would need to do is to sum the appropriate column in the numerator and in the denominator as shown below. The only difference between the numerator and denominator is what I have indicated in red.

SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.Sales
      END
   )
/ 
SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.volumn
      END
   )

You would want to add something more to prevent error if there was no sales.volumn in a period (which would cause a 0 in the denominator). That can be done by adding the nullif condition as shown below in red

SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.Sales
      END
   )
/
NULLIF(
SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.volumn
      END
   ),0)

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-18 : 15:23:52

quote:
Originally posted by sunitabeck

I think all you would need to do is to sum the appropriate column in the numerator and in the denominator as shown below. The only difference between the numerator and denominator is what I have indicated in red.
SUM(
CASE
WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.Sales
END
)
/
SUM(
CASE
WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.volumn
END
)
You would want to add something more to prevent error if there was no sales.volumn in a period (which would cause a 0 in the denominator). That can be done by adding the nullif condition as shown below in red
SUM(
CASE
WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.Sales
END
)
/
NULLIF(
SUM(
CASE
WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.volumn
END
),0)

one last question. If I want to add in the percentage sign to

SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.Sales
      END
   )
/
NULLIF(
SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.volumn
      END
   ),0)

------------------------------------------------------------------
Do I do



ATC=  
cast(SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.Sales
      END
   )
/
NULLIF(
SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.volumn
      END
   ),0) as as varchar(32))+'%',0)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 15:29:37

Many people on this forum who have a great more experience and knowledge of SQL than I will ever have will advise you to do that type of formatting - adding the percentage sign - on the client side - on the UI or presentation layer or wherever you are going to use it.

There are some very valid reasons for doing it that way - for example, your sorting and filtering may not work as you expect if you do the formatting on the SQL side.

But, if you want to do it AMA, cast the whole thing as varchar and then append the percentage sign to it. For example, like this:


cast(SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.Sales
      END
   )
/
NULLIF(
SUM(
      CASE 
         WHEN agg.date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN sales.volumn
      END
   ),0) as varchar(32)) + '%'

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-18 : 15:50:16

Thank you for all your help, i really appreciate it

mjpv.misc
Starting Member

1 Post

Posted - 2011-04-19 : 09:02:38

quote:
Originally posted by fengfeng

Thank you for all your help, i really appreciate it

Hi fengfeng,

I have a problem with one of my sql query
can you reply immediately so that we can chat online

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources