April 10, 2012 at 6:31 am
I am using the below script to generate a market sales report that displays YTD, LYYTD, and YTD VAR.
Now the powers that be want me to include a date parameter.
How do I include a date parameter with out affecting my formula for my LYYTD (Last Year Year To Date)
Thanks
SELECT
cmp_name, sct_code, ord_no, tot_sls_amt, inv_dt, mfg_loc, orig_ord_type, Sector, LYYTD, YTD, YTDCredits, YTDQuotes, (YTD-(((YTDCredits*2))+YTDQuotes)) as GrandTotalYTD
FROM
(SELECT
"cicmpy"."cmp_name", RTrim(Convert(Varchar(7),sct_code)) as sct_code, "oehdrhst_sql"."ord_no",
"oehdrhst_sql"."tot_sls_amt", "oehdrhst_sql"."inv_dt", "oehdrhst_sql"."mfg_loc", "oehdrhst_sql"."orig_ord_type", (Case when sct_code = '01' THEN 'Pool'
when sct_code = '02' THEN 'Water Conditioning'
when sct_code = '03' THEN 'Water Treatment'
when sct_code = '04' THEN 'Animal Health'
when sct_code = '05' THEN 'Car Wash'
when sct_code = '06' THEN 'Industrial'
when sct_code = '07' THEN 'Municipal'
when sct_code = '08' THEN 'Food & Beverage'
when sct_code = '09' THEN 'Irrigation'
when sct_code = '10' THEN 'Metal Finishing'
when sct_code = '11' THEN 'Institutional'
when sct_code = '12' THEN 'Specialty'
ELSE 'Misc.*'
END) as Sector,
(SELECT SUM(tot_sls_amt) AS Expr1
FROM oehdrhst_sql Subquery1
WHERE Subquery1.ID = oehdrhst_sql.ID
and inv_dt between dbo.DateSerial(YEAR(GETDATE())-1,1,1)
and dbo.dateserial(YEAR(GETDATE())-1,MONTH(GETDATE()),DAY(GETDATE()))) LYYTD,
(SELECT ISNULL(SUM(tot_sls_amt),0) AS Expr2
FROM oehdrhst_sql Subquery2
WHERE Subquery2.ID = oehdrhst_sql.ID
and YEAR(inv_dt) = YEAR(GETDATE())) YTD,
(SELECT ISNULL(SUM(tot_sls_amt),0) AS Expr3
FROM oehdrhst_sql Subquery3
WHERE Subquery3.ID = oehdrhst_sql.ID
and YEAR(inv_dt) = YEAR(GETDATE()) and orig_ord_type = 'C') YTDCredits,
(SELECT ISNULL(SUM(tot_sls_amt),0) AS Expr4
FROM oehdrhst_sql Subquery4
WHERE Subquery4.ID = oehdrhst_sql.ID
and YEAR(inv_dt) = YEAR(GETDATE()) and orig_ord_type = 'Q') YTDQuotes
FROM "001"."dbo"."oehdrhst_sql" "oehdrhst_sql" INNER JOIN "001"."dbo"."cicmpy" "cicmpy"
ON "oehdrhst_sql"."cus_no"="cicmpy"."cmp_code"
WHERE ("oehdrhst_sql"."mfg_loc"='JX' OR "oehdrhst_sql"."mfg_loc"='KC' OR "oehdrhst_sql"."mfg_loc"='NY' OR "oehdrhst_sql"."mfg_loc"='RP')
AND (Year("oehdrhst_sql"."inv_dt")>=YEAR(GETDATE())-1)) as AUX
April 10, 2012 at 6:46 am
Just one question to start, why all the quotes? I don't see any reason for them just looking at the code.
April 10, 2012 at 6:51 am
Do they hurt or slow anything down.
I am jumping between Crystal and SQL and sometimes forget where I am at. 😀
April 10, 2012 at 7:31 am
I don't know about others, but it makes the code harder for me to read.
April 10, 2012 at 7:58 am
Would you please post the code for this function: dbo.dateserial. I think I know what it does, but I would like to be sure.
April 10, 2012 at 8:01 am
Also, the DDL (CREATE TABLE statement) for the table(s) used in the query. Looking at it I am pretty sure this can be rewritten to be more efficient.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply