convert VBA to TSQL

how would you convert this to TSQL from VBA
Sum(IIf(Month([CheckDate])=12 And Year([CheckDate])=2017 And tblPayStatisticHistory.[PlanSummaryID]=73,

note: I am the project manager on this payroll conversion and I have a SQL guy who gave me this query, although the query does run and returns the right number of records the arithmetic is ludicrous- way off… help-suggestions-examples would be great thanks
hoursWIPDuex.sql
Bartholomew RollinsAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
I'm afraid that if the real question is how do you make the arithmetic correct then translating one row of your query into a case expression isn't going to solve that problem.

If the results are "way off" then grouping across a join is probably multiplying the number of rows and so the real problem is somewhere  in the FROM clause.

    FROM tblPayPlanHistory JOIN tblPayStatisticHistory
       ON tblPayPlanHistory.EmployeeID = tblPayStatisticHistory.EmployeeID << multiplied rows

Probably need a subquery! And I could propose what that subquery should look like if I knew which table EVERY column comes from.
i.e. There is another problem that you should get every developer to avoid in every query:  
I have no idea which table every columns comes from because they have not used table names (or table aliases) on EVERY COLUMN REFERENCE

As a guess the following might be used as a subquery IF every column it uses comes from the history table.
SELECT
      EmployeeID
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursAccrued], 0))                                                   AS accdv
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursCashedOut], 0))                                                 AS cov
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursEliminated], 0))                                                AS elimv
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursTaken], 0))                                                     AS takev
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursAccrued], 0))                                                  AS accdvy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursCashedOut], 0))                                                AS covy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursEliminated], 0))                                               AS elimvy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [HoursTaken], 0))                                                    AS takevy
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 73, [EndingBalance], 0))                                                  AS balv
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursAccrued], 0))                                                   AS accds
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursCashedOut], 0))                                                 AS cos
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursEliminated], 0))                                                AS elims
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursTaken], 0))                                                     AS takes
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursAccrued], 0))                                                  AS accdsy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursCashedOut], 0))                                                AS cosy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursEliminated], 0))                                               AS elimsy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [HoursTaken], 0))                                                    AS takesy
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 74, [EndingBalance], 0))                                                  AS bals
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursAccrued], 0))                                                   AS accdc
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursCashedOut], 0))                                                 AS coc
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursEliminated], 0))                                                AS elimc
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursTaken], 0))                                                     AS takec
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursAccrued], 0))                                                  AS accdcy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursCashedOut], 0))                                                AS cocy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursEliminated], 0))                                               AS elimcy
    , SUM(IIF(MONTH([CheckDate]) <= 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [HoursTaken], 0))                                                    AS takecy
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 75, [EndingBalance], 0))                                                  AS balc
    , SUM(IIF(MONTH([CheckDate]) = 12 AND YEAR([CheckDate]) = 2017 AND [PlanSummaryID] = 76 AND [StatisticType] = 1 AND [PlanDescription] >= 'a', [Statistic], 0)) AS taker
FROM tblPayStatisticHistory
WHERE CheckDate >= '20170101' and CheckDate < '20180101'
GROUP BY
      EmployeeID
;

Open in new window

Note, do NOT use "WHERE YEAR(checkdate)=2017" that is a very inefficient way to filter for dates that will cause poor performance
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please show us the entire query, as I see a SUM based on three conditions, but nothing that says what is being summed up.

For starters...
SUM(CASE WHEN Month([CheckDate])=12 And Year([CheckDate])=2017 And tblPayStatisticHistory.[PlanSummaryID]=73 THEN {ColumnNameGoesHere} END) as {MakeUpANameHere}

Open in new window

1
 
Bartholomew RollinsAuthor Commented:
added the qry
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Mark WillsTopic AdvisorCommented:
Since 2012, it is surprisingly similar / same
sum(IIf(Month([CheckDate])=12 And Year([CheckDate])=2017 And tblPayStatisticHistory.[PlanSummaryID]=73, <some value if true>,0) 

Open in new window

Otherwise use CASE as Jim suggested above.
0
 
HuaMin ChenSystem AnalystCommented:
Here is the same criteria in TSQL

Substring(Convert (varchar,CheckDate,112),1,6)='201712' and planSummaryID=73 ...

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
@HuaMinChen

How is that the "same criteria" as opposed to just being a different way ?

SQL Server does have month() and year() functions - that would be the same...

And for future reference, I wouldnt substring a date like that, especially using convert(). Simply use / size the datatype ie convert(char(6), checkdate ,112)  or format(checkdate,'yyyyMM')

Besides, where is the SUM() ?

Just thought you might want/need some feedback about your post....
0
 
Mark WillsTopic AdvisorCommented:
*laughing* when did the attachment turn up ?

Actually, looking at the raw code, it should run in SQL Server as well as VBA, albeit not efficiently.

So, it means the real problem is the unknown statement : the arithmetic is ludicrous- way off

Why ?

It is currently producing a number of columns - the last letter representing the [PlanSummaryID] or [PlanSummaryID] with a Y for the year-to-date

That accounts for PlanSummaryID's 73,74,75 and then deviates for PlanSummaryID's 76 and 78 with the added criteria of [StatisticType]=1 And [PlanDescription]>='a'   (or = 'a' or < 'a')  with a couple of other columns checking [debit] condition.

The result of 76 and 78 is either [STATISTIC] or 0 to be summed - which possibly doent make a great deal of sense to sum.

But that is data sensitive and without knowing your requirement or why the arithmetic is ludicrous- way off then it is pretty much impossible to tell if the SQL is doing what you need other than a syntax check - and the syntax will run, will return results, and does give you the correct number of rows....

Any suggestions on how to fix the code will be variation on what the code is currently doing - it is impossible to make it answer your question without knowing what your question really is...

So, the question is "why" ludicrous - way off.

How can we help ?
0
 
Mark WillsTopic AdvisorCommented:
Hello ?

Do you still need some help ? Would still like to know how / why you think the arithmetic is ludicrous - syntactically it does appear to be reasonable (given the aforementioned comments).
0
 
HuaMin ChenSystem AnalystCommented:
Rollins,

Is this resolved?
0
 
Mark WillsTopic AdvisorCommented:
Bartholomew Rollins is invited to return to this question and respond to the comments added and give clarity to "ludicrous".
 
Without that, I can only consider the opening question statement of converting from VBA to SQL and suggest a more appropriate close process.

Both Jim and I suggested a viable converted syntax in SQL for the VBA provided

Paul responded admirably with a plausible case,

And yet there is an outstanding question of 'ludicrous' which prevents a succinct answer.

Bartholomew Rollins is more then welcome to re-engage.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.