Here is your actual SQL statement. You'll need to head over to a SQL forum to get someone to help. Someone here might be able to assist, but a SQL forum as a bunch of people that can assist.
SELECT Leg."legnr",
Leg."salesorder",
Row_number()
OVER (
partition BY Leg.salesorder
ORDER BY legnr) AS SalesOrderLine,
Leg."legstatus",
Leg."planned",
Leg."salesinvoice",
Isnull(Leg.ediprovider, '987654321') AS EDIProvider,
Leg."distance",
Leg."orderleg",
Leg."plangroup" AS 'FK PlanGroup',
Leg."findate",
Leg."totalamount",
Leg."costs",
Leg."cmrreceived",
Leg.[emailsalesinvoice],
Leg.[reference],
Leg."creationuser",
Leg.[cargo],
--Leg.BeginActivity,
--leg.EndActivity,
Isnull(B."address", ( b.activitynr + 900000000 )) AS BeginAddress,
B."date" AS BeginDate,
B."datetill" AS BeginDateTill,
B."time" AS BeginTime,
B."timetill" AS BeginTimeTill,
B."resourcecombination" AS
BeginResourceCombination,
B."tripid" AS BeginTripID,
B."startdate" AS BeginStartDate,
B."starttime" AS BeginStartTime,
B."enddate" AS BeginEndDate,
B."endtime" AS BeginEndTime,
B."activityduration" AS
BeginActivityDuration,
Isnull(E."address", ( e.activitynr + 800000000 )) AS EndAddress,
E."date" AS EndDate,
E."datetill" AS EndDateTill,
E."time" AS EndTime,
E."timetill" AS EndTimeTill,
E."resourcecombination" AS
EndResourceCombination,
E."tripid" AS EndTripID,
E."startdate" AS EndStartDate,
E."starttime" AS EndStartTime,
E."enddate" AS EndEndDate,
E."endtime" AS EndEndTime,
E."activityduration" AS EndActivityDuration,
leg.creationdate,
Sum(Cargo."weight") AS Weight,
Sum(Cargo."loadingmeter") AS LoadingMeter,
Sum(Cargo."palletplaces") AS PalletPlaces,
CASE
WHEN legnr IN (SELECT DISTINCT [leg] AS [pl_orders]
FROM so_cargo
WHERE unit = 78) THEN 'pl'
ELSE NULL
END AS [pl order]
-- unit 78 = pl
FROM so_leg AS Leg
LEFT JOIN so_activity AS B
ON leg.beginactivity = B.activitynr
LEFT JOIN so_activity AS E
ON leg.endactivity = E.activitynr
LEFT JOIN so_cargo AS cargo
ON leg.[legnr] = cargo.leg
WHERE findate >= Dateadd(year, -3, CONVERT (DATE, Dateadd(year, Datediff(year,
0,
Getdate()) + 1,
0)))
GROUP BY Leg."legnr",
Leg."salesorder",
Leg."legtype",
Leg."legstatus",
Leg."planned",
Leg."salesinvoice",
Leg.ediprovider,
Leg."distance",
Leg."orderleg",
Leg."plangroup",
Leg."findate",
Leg."totalamount",
Leg."costs",
Leg."cmrreceived",
Leg.[emailsalesinvoice],
Leg.[reference],
Leg."creationuser",
Leg.cargo,
--Leg.BeginActivity,
--Leg.EndActivity,
leg.creationdate,
B.activitynr,
B."activitykind",
B."address",
B."date",
B."datetill",
B."time",
B."timetill",
B."resourcecombination",
B."tripid",
B."startdate",
B."starttime",
B."enddate",
B."endtime",
B."activityduration",
E."activitykind",
E."address",
E."date",
E."datetill",
E."time",
E."timetill",
E."resourcecombination",
E."tripid",
E."startdate",
E."starttime",
E."enddate",
E."endtime",
E."activityduration",
E.activitynr
Or, post some sample data and tell us what you are trying to do and we can start from scratch with Power Query.
You should rarely/never use that advanced SQL dialog box. Either create a View on the SQL server, or do it in Power Query. Doing it via an advanced SQL statement can cause permissions issue and prevents simple edits. I could adjust an M statement to only get the last 4 days of info in 2-3 lines that would fold back to the server. Nothing after an advanced SQL statement entry will allow folding.
Did I answer your question? Mark my post as a solution!Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
