I am trying to return 2 different counts on the same table with different where clauses. The DLVDET.DLV_DATE returned in the first query would be equal to the ORD_SHIP_RELEASE.RELEASE_DATE in the second query. Do I need to join these two queries somehow so it would return something like TotalShipped, TotalDue, and date. With the query below I receive the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Blockquote
SELECT
(SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO), DLVDET.DLV_DATE
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/06/2017') AND (ORDERS.PLT_NO = '1') AND
(DLVDET.DLV_DATE = ORD_SHIP_RELEASE.RELEASE_DATE) AND (DLVDET.COMP_FLG = 'C')
GROUP BY DLVDET.DLV_DATE) AS TotalShipped,
(SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO), ORD_SHIP_RELEASE.RELEASE_DATE
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/06/2017') AND (ORDERS.PLT_NO = '1')
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE) AS TotalDue> Blockquote
Is there another way to get what I want?
Regards,
Stacy
Without relevant sample data and expected output, it's hard to test if this is what you're looking for:
select sum(shipped) as TotalShipped
,sum(due) as TotalDue
,release_date as [date]
from (select sign(sum(case
when d.dlv_date=s.release_date
and d.comp_flg='C'
then 1
else 0
end
)
)
as shipped
,1 as due
,s.release_date
from dlvdet as d
inner join orders as o
on o.order_no=d.order_no
and o.plt_no='1'
inner join ord_ship_release as s
on s.order_no=d.order_no
and s.release_no=d.release_no
and s.release_date>=cast('20170206' as date)
and s.release_date<cast('20170207' as date)
group by s.release_date
,s.order_no
) as a
group by release_date
;
bitsmed that was brilliant! It worked perfectly unfortunately one of the queries I posted was incorrect. I am really trying to combined the two queries below into one query. I will try to dissect the query you posted and see if I can combined these two queries.
Blockquote
SELECT count(case when DLVDET.DLV_DATE <= ORD_SHIP_RELEASE.RELEASE_DATE then 1 else null end) as TotalShipped, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE INNER JOIN
ORDERS ON ORD_SHIP_RELEASE.ORDER_NO = ORDERS.ORDER_NO INNER JOIN
DLVDET AS DLVDET ON ORDERS.ORDER_NO = DLVDET.ORDER_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= @StartDate) AND (ORD_SHIP_RELEASE.RELEASE_DATE <= @StopDate) AND (DLVDET.DLV_DATE <= @StopDate) AND
(DLVDET.COMP_FLG = 'C') AND (ORDERS.PLT_NO = @SearchValue)
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE
SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO) AS TotalDue, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= @StartDate) AND (ORD_SHIP_RELEASE.RELEASE_DATE <= @StopDate) AND (ORDERS.PLT_NO = @SearchValue)
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE> Blockquote
Regards,
Stacy
This is as close as I can get it and I get the following error Column 't.TotalOnTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Blockquote
select *
from ((SELECT count(case when DLVDET.DLV_DATE <= ORD_SHIP_RELEASE.RELEASE_DATE then 1 else null end) as TotalOnTime, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE INNER JOIN
ORDERS ON ORD_SHIP_RELEASE.ORDER_NO = ORDERS.ORDER_NO INNER JOIN
DLVDET AS DLVDET ON ORDERS.ORDER_NO = DLVDET.ORDER_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/10/2017') AND (DLVDET.DLV_DATE <= '02/10/2017') AND
(DLVDET.COMP_FLG = 'C') AND (ORDERS.PLT_NO = '1')
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE
) union all
(SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO) AS TotalDue, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/10/2017') AND (ORDERS.PLT_NO = '1')
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE
)
) t
group by rptdate
Blockquote
Thanks,
Stacy
Something like:
select sum(shipped) as TotalShipped
,sum(due) as TotalDue
,release_date as [date]
from (select sign(sum(case
when d.dlv_date<=s.release_date
and d.comp_flg='C'
then 1
else 0
end
)
)
as shipped
,1 as due
,s.release_date
from dlvdet as d
inner join orders as o
on o.order_no=d.order_no
and o.plt_no=@searchvalue
inner join ord_ship_release as s
on s.order_no=d.order_no
and s.release_no=d.release_no
and s.release_date>=cast(@startdate as date)
and s.release_date<cast(@stopdate as date)
group by s.release_date
,s.order_no
) as a
group by release_date
;
1 Like
I cannot thank you enough bitsmed. Someday I hope to be able to write queries like this.
Thanks again,
Stacy
SELECT
TotalShipped = SUM(shipped),
TotalDue = SUM(due),
[Date] = release_date
FROM
(SELECT
Shipped = SIGN(SUM(CASE WHEN d.dlv_date <= s.release_date
AND d.comp_flg = 'C'
THEN 1 ELSE 0 END )),
Due = 1,
ReleaseDate = s.release_date
FROM dlvdet d
INNER JOIN orders o
ON o.order_no = d.order_no
AND o.plt_no = @searchvalue
INNER JOIN ord_ship_release s
ON s.order_no = d.order_no
AND s.release_no = d.release_no
AND s.release_date >= CAST(@startdate As Date)
AND s.release_date < CAST(@stopdate As Date)
GROUP BY
s.release_date,
s.order_no
) a
GROUP BY
release_date