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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy