Solved

cannot get SQL results into single line

Posted on 2010-08-17
13
320 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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 53

Expert Comment

by:Huseyin KAHRAMAN
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 83 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 83 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 84 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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