cannot get SQL results into single line

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

LVL 6
phoenixfire425Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sameer2010Commented:
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
phoenixfire425Author Commented:
"Cannot perform an aggregate function or an expression containing an aggregate or a subquery."
0
vdr1620Commented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

phoenixfire425Author Commented:
@vdr1620
I cannot even get that to let me run it.

0
sameer2010Commented:
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
HainKurtSr. System AnalystCommented:
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
sameer2010Commented:
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
vdr1620Commented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
phoenixfire425Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.