January 29, 2019 at 3:12 pm
Below is the qry, now I have to sub queries pulling data they are both using the same date range, so for simplicity I only want to put the date in one. I don't remember the syntax to do that DECLARE....... mind you I am using Aginity (IBM) so not sure if SYNTAX is the same as TSQL
SELECT
CASE
WHEN F.AUCTION_NAME_LONG IS NULL
THEN C.AUCTION_NAME_LONG
ELSE F.AUCTION_NAME_LONG
END AS AUC_NAME,
F.FLEET_CNT AS FLEET,
TMC,
TOY
FROM
(SELECT
A.AUCTION_NAME_LONG,
COUNT(*) AS FLEET_CNT
FROM
ADMIN.V_REMARKT_FLEET_DAILY_FACT F
LEFT JOIN ADMIN.V_REMARKT_AUCTION_DIM A ON F.AUCTION_DIM_ID = A.AUCTION_DIM_ID
WHERE
F.AUCT_SALE_DATE BETWEEN '2018-12-01' AND '2018-12-31'
GROUP BY
A.AUCTION_NAME_LONG
) F
FULL JOIN
(SELECT
AUC.AUCTION_NAME_LONG,
SUM(CASE
WHEN A.CLIENT_ID_CODE LIKE ('TMC')
THEN 1
ELSE 0
END) AS TMC,
SUM(CASE
WHEN A.CLIENT_ID_CODE LIKE ('TOY')
THEN 1
ELSE 0
END) AS TOY
FROM
ADMIN.V_REMARKT_AUTOIMS_DAILY_FACT A
LEFT JOIN ADMIN.V_REMARKT_AUCTION_DIM AUC ON A.AUCTION_DIM_ID = AUC.AUCTION_DIM_ID
WHERE
A.SOLD_DATE BETWEEN '2018-12-01' AND '2018-12-31'
GROUP BY
AUC.AUCTION_NAME_LONG
) C
ON F.AUCTION_NAME_LONG = C.AUCTION_NAME_LONG
GROUP BY
AUC_NAME,
F.FLEET_CNT,
TMC,
TOY
January 30, 2019 at 7:36 am
, @EndDate Date = '12/31/2018'
Then replace your dates in your code with the variables.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply