Skip to content

Instantly share code, notes, and snippets.

/gist:a37988118b1c4932a0d0ee7db5895790

Created April 1, 2016 08:50
Show Gist options
  • Save anonymous/a37988118b1c4932a0d0ee7db5895790 to your computer and use it in GitHub Desktop.
Save anonymous/a37988118b1c4932a0d0ee7db5895790 to your computer and use it in GitHub Desktop.
Download ZIP
April fools query
Raw
gistfile1.txt
SELECT "T0"."C0" AS "DATAAREAID" ,
"T0"."C1" AS "PROJECT" ,
"T0"."C2" AS "PROJECT_NAME" ,
"T0"."C3" AS "PROJID" ,
"T0"."C4" AS "SUBPROJECT_NAME" ,
…
FROM (
SELECT "Q_ProjectBased43"."DATAAREAID" AS "C0" ,
"Q_ProjectBased43"."PROJECT" AS "C1" ,
"Q_ProjectName44"."PROJECT_NAME" AS "C2" ,
"Q_ProjectBased43"."PROJID" AS "C3" ,
"Q_ProjectBased43"."SUBPROJECT_NAME" AS "C4" ,
' ' AS "C5" ,
"Q_ProjectBased43"."DIMENSION6_" AS "C6" ,
"Q_ProjectBased43"."PROJECT_BUDGET_DESCRIPTION" AS "C7" ,
"Q_ProjectBased43"."EMPLID" AS "C8" ,
"Q_ProjectBased43"."NAME" AS "C9" ,
"Q_ProjectBased43"."LEDGERTRANSDATE" AS "C10" ,
"Q_ProjectBased43"."PROJTRANSDATE" AS "C11" ,
Sum("Q_ProjectBased43"."QTY") AS "C12" ,
Sum("Q_ProjectBased43"."AMOUNTMST") AS "C13" ,
"Q_ProjectBased43"."TXT" AS "C14" ,
"Q_ProjectBased43"."VOUCHER" AS "C15" ,
"Q_ProjectBased43"."CATEGORYID" AS "C16" ,
0 AS "C17" ,
"Q_ProjectBased43"."Year16" AS "C18" ,
"Q_ProjectBased43"."Month17" AS "C19" ,
'On Projects' AS "C20" ,
"Q_ProjectBased43"."c18" AS "C21" ,
"Q_ProjectBased43"."ORGANIZATIONUNITID" AS "C22"
FROM (
SELECT "ProjTransPosting___C2"."DATAAREAID" AS "DATAAREAID" ,
CASE
WHEN Charindex('.', "ProjTransPosting___C2"."PROJID") = 0 THEN Substring("ProjTransPosting___C2"."PROJID", 1, 4)
ELSE LEFT("ProjTransPosting___C2"."PROJID", Charindex('.', "ProjTransPosting___C2"."PROJID") - 1)
END AS "PROJECT" ,
"ProjTransPosting___C2"."PROJID" AS "PROJID" ,
CASE
WHEN "ProjTransPosting___C2"."PROJID" = "ProjTable___C234"."PROJID" THEN "ProjTable___C234"."NAME"
END AS "SUBPROJECT_NAME" ,
"ProjTransPosting___C2"."DIMENSION6_" AS "DIMENSION6_" ,
"T"."PROJECT_BUDGET_DESCRIPTION" AS "PROJECT_BUDGET_DESCRIPTION" ,
"ProjEmplTrans___C2"."EMPLID" AS "EMPLID" ,
"BSXDirPartyTableView___C2"."NAME" AS "NAME" ,
"ProjTransPosting___C2"."LEDGERTRANSDATE" AS "LEDGERTRANSDATE" ,
"ProjTransPosting___C2"."PROJTRANSDATE" AS "PROJTRANSDATE" ,
Sum("ProjTransPosting___C2"."QTY") AS "QTY" ,
Sum("ProjTransPosting___C2"."AMOUNTMST") AS "AMOUNTMST" ,
"ProjEmplTrans___C2"."TXT" AS "TXT" ,
"ProjTransPosting___C2"."VOUCHER" AS "VOUCHER" ,
"ProjTransPosting___C2"."CATEGORYID" AS "CATEGORYID" ,
Year("ProjTransPosting___C2"."LEDGERTRANSDATE") AS "Year16" ,
Month("ProjTransPosting___C2"."LEDGERTRANSDATE") AS "Month17" ,
"T38"."c5" AS "c18" ,
"Position___Hours_reports"."ORGANIZATIONUNITID" AS "ORGANIZATIONUNITID"
FROM ( ( ( ( ( "DynamicsProd"."dbo"."PROJTRANSPOSTING" "ProjTransPosting___C2"
LEFT JOIN "DynamicsProd"."dbo"."PROJTABLE" "ProjTable___C234"
ON "ProjTransPosting___C2"."PROJID" = "ProjTable___C234"."PROJID" microsoft confidential page 93 1/16/2015
AND "ProjTransPosting___C2"."DATAAREAID" = "ProjTable___C234"."DATAAREAID" )
LEFT JOIN (
(
SELECT "SRSANALYSISENUMS"."ENUMITEMVALUE" AS "ENUMITEMVALUE" ,
"SRSANALYSISENUMS"."ENUMITEMNAME" AS "c5"
FROM "DynamicsProd"."dbo"."SRSANALYSISENUMS" "SRSANALYSISENUMS"
WHERE "SRSANALYSISENUMS"."ENUMID" = 1162
AND "SRSANALYSISENUMS"."LANGUAGEID" = N'en-us' ) "T38"
INNER JOIN "DynamicsProd"."dbo"."PROJEMPLTRANS" "ProjEmplTrans___C2"
ON "T38"."ENUMITEMVALUE" = "ProjEmplTrans___C2"."TRANSSTATUS" )
ON "ProjTransPosting___C2"."TRANSID" = "ProjEmplTrans___C2"."TRANSID"
AND "ProjTransPosting___C2"."DATAAREAID" = "ProjEmplTrans___C2"."DATAAREAID" )
LEFT JOIN "DynamicsProd"."dbo"."EMPLTABLE" "T41"
ON "ProjEmplTrans___C2"."EMPLID" = "T41"."EMPLID"
AND "ProjEmplTrans___C2"."DATAAREAID" = "T41"."DATAAREAID" )
LEFT JOIN "DynamicsProd"."dbo"."HRPPARTYPOSITIONTABLERELAT2226" "Position___Hours_reports"
ON "T41"."EMPLID" = "Position___Hours_reports"."REFERENCE"
AND "T41"."DATAAREAID" = "Position___Hours_reports"."DATAAREAID" )
LEFT JOIN ( "DynamicsProd"."dbo"."BSXDIRPARTYTABLEVIEW" "BSXDirPartyTableView___C2"
INNER JOIN "DynamicsProd"."dbo"."EMPLTABLE" "EmplTable___C2"
ON "BSXDirPartyTableView___C2"."DATAAREAID" = "EmplTable___C2"."DATAAREAID"
AND "BSXDirPartyTableView___C2"."PARTYID" = "EmplTable___C2"."PARTYID" )
ON "ProjTransPosting___C2"."EMPLITEMID" = "EmplTable___C2"."EMPLID"
AND "ProjTransPosting___C2"."DATAAREAID" = "EmplTable___C2"."DATAAREAID" )
LEFT JOIN
(
SELECT "DIMENSIONS"."DESCRIPTION" AS "PROJECT_BUDGET_DESCRIPTION" ,
"DIMENSIONS"."NUM" AS "NUM" ,
"DIMENSIONS"."DATAAREAID" AS "DATAAREAID"
FROM "DynamicsProd"."dbo"."BSXDIMENSIONSVIEW" "DIMENSIONS"
WHERE "DIMENSIONS"."DIMENSIONCODE" = 102 ) "T"
ON "ProjTransPosting___C2"."DIMENSION6_" = "T"."NUM"
AND "ProjTransPosting___C2"."DATAAREAID" = "T"."DATAAREAID"
WHERE "ProjTransPosting___C2"."DATAAREAID" = N'b6sa'
AND
CASE
WHEN (
charindex('.', "ProjTransPosting___C2"."PROJID") = 0) THEN substring("ProjTransPosting___C2"."PROJID", 1, 4)
ELSE LEFT("ProjTransPosting___C2"."PROJID", charindex('.', "ProjTransPosting___C2"."PROJID") - 1)
END IN (N'C031',
N'C032',
N'C033',
N'C034',
N'C035')
AND "ProjTransPosting___C2"."LEDGERTRANSDATE" BETWEEN CONVERT(datetime2, '2014-01-01 00:00:00.000000000') AND CONVERT(datetime2, '2014-12-31 00:00:00.000000000')
AND "ProjTransPosting___C2"."COSTSALES" = 1
AND "ProjTransPosting___C2"."POSTINGTYPE" <> 122
AND "ProjTransPosting___C2"."CATEGORYID" NOT IN (N'IBH',
N'SE53')
AND "ProjTransPosting___C2"."DIMENSION6_" IN (N'S11',
N'W12')
GROUP BY "ProjTransPosting___C2"."DATAAREAID" ,
CASE
WHEN charindex('.', "ProjTransPosting___C2"."PROJID") = 0 THEN substring("ProjTransPosting___C2"."PROJID", 1, 4)
ELSE LEFT("ProjTransPosting___C2"."PROJID", charindex('.', "ProjTransPosting___C2"."PROJID") - 1)
END ,
"ProjTransPosting___C2"."PROJID" ,
CASE
WHEN "ProjTransPosting___C2"."PROJID" = "ProjTable___C234"."PROJID" THEN "ProjTable___C234"."NAME"
END ,
"ProjTransPosting___C2"."DIMENSION6_" ,
"T"."PROJECT_BUDGET_DESCRIPTION" ,
"ProjEmplTrans___C2"."EMPLID" ,
"BSXDirPartyTableView___C2"."NAME" ,
"ProjTransPosting___C2"."LEDGERTRANSDATE" ,
"ProjTransPosting___C2"."PROJTRANSDATE" ,
"ProjEmplTrans___C2"."TXT" ,
"ProjTransPosting___C2"."VOUCHER" ,
"ProjTransPosting___C2"."CATEGORYID" ,
year("ProjTransPosting___C2"."LEDGERTRANSDATE") ,
month("ProjTransPosting___C2"."LEDGERTRANSDATE") ,
"T38"."c5" ,
"Position___Hours_reports"."ORGANIZATIONUNITID" ) "Q_ProjectBased43" ,
(
SELECT DISTINCT "ProjTable___C242"."DATAAREAID" AS "DATAAREAID" ,
"ProjTable___C242"."PROJLEDGERPOSTING" AS "PROJLEDGERPOSTING" ,
"ProjTable___C242"."PROJID" AS "PROJID" ,
"ProjTable___C242"."NAME" AS "PROJECT_NAME"
FROM "DynamicsProd"."dbo"."PROJTABLE" "ProjTable___C242" ) "Q_ProjectName44"
WHERE "Q_ProjectBased43"."PROJECT" = "Q_ProjectName44"."PROJID"
AND "Q_ProjectBased43"."DATAAREAID" = "Q_ProjectName44"."DATAAREAID"
GROUP BY "Q_ProjectBased43"."DATAAREAID" ,
"Q_ProjectBased43"."PROJECT" ,
"Q_ProjectName44"."PROJECT_NAME" ,
"Q_ProjectBased43"."PROJID" ,
"Q_ProjectBased43"."SUBPROJECT_NAME" ,
"Q_ProjectBased43"."DIMENSION6_" ,
"Q_ProjectBased43"."PROJECT_BUDGET_DESCRIPTION" ,
"Q_ProjectBased43"."EMPLID" ,
"Q_ProjectBased43"."NAME" ,
"Q_ProjectBased43"."LEDGERTRANSDATE" ,
"Q_ProjectBased43"."PROJTRANSDATE" ,
"Q_ProjectBased43"."TXT" ,
"Q_ProjectBased43"."VOUCHER" ,
"Q_ProjectBased43"."CATEGORYID" ,
"Q_ProjectBased43"."Year16" ,
"Q_ProjectBased43"."Month17" ,
"Q_ProjectBased43"."c18" ,
"Q_ProjectBased43"."ORGANIZATIONUNITID" ) "T0"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment