Solved

cannot get SQL results into single line

Posted on 2010-08-17
13
318 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore 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.
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

16 Experts available now in Live!

Get 1:1 Help Now