Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

cannot get SQL results into single line

Posted on 2010-08-17
13
Medium Priority
?
327 Views
Last Modified: 2012-06-21
I am trying to get this SQL query to report to one line. but i am receiving multiples.


The resaults i am looking for are like this.

order_no    1day         2-3           4-5        6-10        11+
1145181     99.941    0.0589         0          0                0

but instead i am getting

order_no    1day         2-3           4-5        6-10        11+
1145181     0.0588    0                  0          0                0
1145181     0             0.0589         0          0                0
1145181     0.2352    0                  0          0                0
1145181     0.2941    0                  0          0                0
1145181     0.3529    0                  0          0                0


Here is my SQL query

SELECT     p21_view_oe_hdr.order_no, CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) 
                      <= '1' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [1day], 
                      CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '2' AND 
                      '3' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [2-3], CASE WHEN DATEDIFF(day, 
                      p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '4' AND '5' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [4-5], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) BETWEEN '6' AND '10' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [6-10], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) > '11' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL 
                      END AS [11+]
FROM         p21_view_oe_line INNER JOIN
                      p21_view_oe_hdr ON p21_view_oe_line.order_no = p21_view_oe_hdr.order_no INNER JOIN
                      p21_view_oe_pick_ticket ON p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no INNER JOIN
                      p21_view_oe_pick_ticket_detail ON p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
GROUP BY p21_view_oe_pick_ticket.ship_date, p21_view_oe_hdr.order_no, p21_view_oe_pick_ticket_detail.ship_quantity, p21_view_oe_hdr.order_date
HAVING      (p21_view_oe_hdr.order_no = '1145181') AND (p21_view_oe_hdr.order_date BETWEEN CONVERT(DATETIME, '2010-08-01 00:00:00', 102) AND 
                      CONVERT(DATETIME, '2010-08-31 00:00:00', 102))

Open in new window

0
Comment
Question by:phoenixfire425
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
13 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 33456145
Try this

SELECT     p21_view_oe_hdr.order_no, SUM(CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) 
                      <= '1' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END) AS [1day], 
                      SUM(CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '2' AND 
                      '3' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END) AS [2-3], SUM(CASE WHEN DATEDIFF(day, 
                      p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '4' AND '5' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END) AS [4-5], SUM(CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) BETWEEN '6' AND '10' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END) AS [6-10], SUM(CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) > '11' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL 
                      END) AS [11+]
FROM         p21_view_oe_line INNER JOIN
                      p21_view_oe_hdr ON p21_view_oe_line.order_no = p21_view_oe_hdr.order_no INNER JOIN
                      p21_view_oe_pick_ticket ON p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no INNER JOIN
                      p21_view_oe_pick_ticket_detail ON p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
GROUP BY p21_view_oe_pick_ticket.ship_date, p21_view_oe_hdr.order_no, p21_view_oe_pick_ticket_detail.ship_quantity, p21_view_oe_hdr.order_date
HAVING      (p21_view_oe_hdr.order_no = '1145181') AND (p21_view_oe_hdr.order_date BETWEEN CONVERT(DATETIME, '2010-08-01 00:00:00', 102) AND 
                      CONVERT(DATETIME, '2010-08-31 00:00:00', 102))

Open in new window

0
 
LVL 6

Author Comment

by:phoenixfire425
ID: 33456166
"Cannot perform an aggregate function or an expression containing an aggregate or a subquery."
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33456172
Try with a sub Query Or you Can change your Columns with Date value as
Convert(Varchar,ColumnName,112) i.e., to YYYYMMDD format

I believe your date columns are in yyyy-mm-dd hh:mm:ss Format which when grouped will not give you expected result because of timeStamp

Ex;

SELECT     p21_view_oe_hdr.order_no, CASE WHEN DATEDIFF(day, CONVERT(VARCHAR,p21_view_oe_hdr.order_date,112), CONVERT(VARCHAR,p21_view_oe_pick_ticket.ship_date,112))
                      <= '1' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [1day],
.......................

GROUP BY CONVERT(VARCHAR,p21_view_oe_hdr.order_date,112), CONVERT(VARCHAR,p21_view_oe_pick_ticket.ship_date,112) .....
SELECT p21_view_oe_hdr.order_no, SUM([1day] as [1day], SUM([2-3]) as [2-3],SUM([4-5]) as [4-5], SUM([6-10]) as [6-10], SUM([11+]) as [11+] FROM 
(
SELECT     p21_view_oe_hdr.order_no, CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) 
                      <= '1' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [1day], 
                      CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '2' AND 
                      '3' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [2-3], CASE WHEN DATEDIFF(day, 
                      p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '4' AND '5' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [4-5], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) BETWEEN '6' AND '10' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [6-10], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) > '11' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL 
                      END AS [11+], p21_view_oe_hdr.order_date
FROM         p21_view_oe_line INNER JOIN
                      p21_view_oe_hdr ON p21_view_oe_line.order_no = p21_view_oe_hdr.order_no INNER JOIN
                      p21_view_oe_pick_ticket ON p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no INNER JOIN
                      p21_view_oe_pick_ticket_detail ON p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
GROUP BY p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date, p21_view_oe_hdr.order_no, p21_view_oe_pick_ticket_detail.ship_quantity
HAVING      (p21_view_oe_hdr.order_date BETWEEN @date1 AND @date2) AND (p21_view_oe_hdr.order_no = '1145181')
)A
GROUP BY p21_view_oe_hdr.order_no
ORDER BY p21_view_oe_hdr.order_no

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Author Comment

by:phoenixfire425
ID: 33456199
@vdr1620
I cannot even get that to let me run it.

0
 
LVL 13

Expert Comment

by:sameer2010
ID: 33456201
Ohh...you would need an OUTER SELECT with GROUP BY on p21_view_oe_hdr.order_no and SUM of all the CASE...END AS XXXX fields.
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 33456210
try this

select order_no, sum([1day])*100 [1day], sum([2-3]) [2-3], sum([4-5]) [4-5], sum([6-10]) [6-10], sum([11+]) [11+]
from (your query here) x
group by order_no
0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 332 total points
ID: 33456238
Try this
SELECT     p21_view_oe_hdr.order_no,SUM([1day]) as [1day],SUM([2-3]) as [2-3], SUM([4-5]) AS [4-5], SUM([6-10]) AS [6-10],SUM([11+]) AS [11+]
FROM(
SELECT     p21_view_oe_hdr.order_no AS ORDNO, CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) 
                      <= '1' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [1day], 
                      CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '2' AND 
                      '3' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [2-3], CASE WHEN DATEDIFF(day, 
                      p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '4' AND '5' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [4-5], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) BETWEEN '6' AND '10' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [6-10], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) > '11' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL 
                      END AS [11+]
FROM         p21_view_oe_line INNER JOIN
                      p21_view_oe_hdr ON p21_view_oe_line.order_no = p21_view_oe_hdr.order_no INNER JOIN
                      p21_view_oe_pick_ticket ON p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no INNER JOIN
                      p21_view_oe_pick_ticket_detail ON p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
GROUP BY p21_view_oe_pick_ticket.ship_date, p21_view_oe_hdr.order_no, p21_view_oe_pick_ticket_detail.ship_quantity, p21_view_oe_hdr.order_date
HAVING      (p21_view_oe_hdr.order_no = '1145181') AND (p21_view_oe_hdr.order_date BETWEEN CONVERT(DATETIME, '2010-08-01 00:00:00', 102) AND 
                      CONVERT(DATETIME, '2010-08-31 00:00:00', 102))
) AS T
GROUP BY ORDNO

Open in new window

0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 332 total points
ID: 33456252
My bad ..see the updated query
SELECT A.order_no, SUM([1day] as [1day], SUM([2-3]) as [2-3],SUM([4-5]) as [4-5], SUM([6-10]) as [6-10], SUM([11+]) as [11+] FROM 
(
SELECT     p21_view_oe_hdr.order_no, CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) 
                      <= '1' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [1day], 
                      CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '2' AND 
                      '3' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [2-3], CASE WHEN DATEDIFF(day, 
                      p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date) BETWEEN '4' AND '5' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [4-5], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) BETWEEN '6' AND '10' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) 
                      / SUM(p21_view_oe_line.qty_ordered) ELSE NULL END AS [6-10], CASE WHEN DATEDIFF(day, p21_view_oe_hdr.order_date, 
                      p21_view_oe_pick_ticket.ship_date) > '11' THEN (p21_view_oe_pick_ticket_detail.ship_quantity) / SUM(p21_view_oe_line.qty_ordered) ELSE NULL 
                      END AS [11+], p21_view_oe_hdr.order_date
FROM         p21_view_oe_line INNER JOIN
                      p21_view_oe_hdr ON p21_view_oe_line.order_no = p21_view_oe_hdr.order_no INNER JOIN
                      p21_view_oe_pick_ticket ON p21_view_oe_hdr.order_no = p21_view_oe_pick_ticket.order_no INNER JOIN
                      p21_view_oe_pick_ticket_detail ON p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
GROUP BY p21_view_oe_hdr.order_date, p21_view_oe_pick_ticket.ship_date, p21_view_oe_hdr.order_no, p21_view_oe_pick_ticket_detail.ship_quantity
HAVING      (p21_view_oe_hdr.order_date BETWEEN @date1 AND @date2) AND (p21_view_oe_hdr.order_no = '1145181')
)A
GROUP BY A.order_no
ORDER BY A.order_no

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33460572
Try it this way (notice the GROUP BY clause items and how you should not be using a HAVING clause but rather a WHERE clause in thise case):
SELECT  h.order_no,
        SUM(
			CASE 
				WHEN DATEDIFF(DAY, h.order_date, t.ship_date) <= 1 THEN d.ship_quantity / .qty_ordered
				ELSE 0
			END) [1day],
        SUM(
			CASE 
				WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 2 AND 3 THEN d.ship_quantity / l.qty_ordered
				ELSE 0
			END) [2-3],
		SUM(
			CASE 
				WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 4 AND 5 THEN d.ship_quantity / l.qty_ordered
				ELSE 0
			END) [4-5],
        SUM(
			CASE 
				WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 6 AND 10 THEN d.ship_quantity / l.qty_ordered
                ELSE 0
			END) [6-10],
        SUM(
			CASE 
				WHEN DATEDIFF(DAY, h.order_date, t.ship_date) > 11 THEN d.ship_quantity / l.qty_ordered
				ELSE 0
			END) [11+]
FROM    p21_view_oe_line l
        INNER JOIN p21_view_oe_hdr h ON l.order_no = h.order_no
        INNER JOIN p21_view_oe_pick_ticket t ON h.order_no = t.order_no
        INNER JOIN p21_view_oe_pick_ticket_detail d ON t.pick_ticket_no = d.pick_ticket_no
WHERE	h.order_no = '1145181'
        AND (h.order_date BETWEEN CONVERT(DATETIME, '2010-08-01 00:00:00', 102)
                              AND CONVERT(DATETIME, '2010-08-31 00:00:00', 102))
GROUP BY 
        h.order_no

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33460578
Hopefully this format will be a tad clearer:
SELECT	h.order_no,
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) <= 1 THEN d.ship_quantity / .qty_ordered
			ELSE 0
		END) [1day],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 2 AND 3 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [2-3],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 4 AND 5 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [4-5],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 6 AND 10 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [6-10],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) > 11 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [11+]
FROM    p21_view_oe_line l
        INNER JOIN p21_view_oe_hdr h ON l.order_no = h.order_no
        INNER JOIN p21_view_oe_pick_ticket t ON h.order_no = t.order_no
        INNER JOIN p21_view_oe_pick_ticket_detail d ON t.pick_ticket_no = d.pick_ticket_no
WHERE	h.order_no = '1145181'
        AND (h.order_date BETWEEN CONVERT(DATETIME, '2010-08-01 00:00:00', 102)
                              AND CONVERT(DATETIME, '2010-08-31 00:00:00', 102))
GROUP BY 
        h.order_no

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 336 total points
ID: 33460584
And it looks like I managed to drop an alias, it should be (sorry for the multiple posts):
SELECT	h.order_no,
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) <= 1 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [1day],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 2 AND 3 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [2-3],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 4 AND 5 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [4-5],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) BETWEEN 6 AND 10 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [6-10],
	SUM(
		CASE 
			WHEN DATEDIFF(DAY, h.order_date, t.ship_date) > 11 THEN d.ship_quantity / l.qty_ordered
			ELSE 0
		END) [11+]
FROM    p21_view_oe_line l
        INNER JOIN p21_view_oe_hdr h ON l.order_no = h.order_no
        INNER JOIN p21_view_oe_pick_ticket t ON h.order_no = t.order_no
        INNER JOIN p21_view_oe_pick_ticket_detail d ON t.pick_ticket_no = d.pick_ticket_no
WHERE	h.order_no = '1145181'
        AND (h.order_date BETWEEN CONVERT(DATETIME, '2010-08-01 00:00:00', 102)
                              AND CONVERT(DATETIME, '2010-08-31 00:00:00', 102))
GROUP BY 
        h.order_no

Open in new window

0
 
LVL 6

Author Comment

by:phoenixfire425
ID: 33464637
Thank you everyone for helping with this. I am going over everything now.

But it looks like my project is going to be a little more complicated then what i initially thought.

So i will Post my results sometime today.!

Thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question