Solved

cannot get SQL results into single line

Posted on 2010-08-17
13
317 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
  • 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
 
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 51

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now