Help with Group query... 1

[Solo4357]

I got a pile of records I need to get so I can throw them in a pivot table in Excel. I'm not sure the best way to do it.

I have a view that gives me Ctrl_Job_number, JobNumber, Post_date, Cost, Billed, and PM. When I put it into a pivot table, I use group by to sum up the cost and billed but still get a lot of records due to all the post dates.

I import that into Excel and use Excel's group by to set up by Year, Quarter and month. The results are like so, with indentations being aggregates. PM has ctrlJobs, ctrlJobs have jobs, all roll up to the PM:

Jan Feb

PM Cost Billed Cost Billed
Bob 500 1000 200 400
CtrJobNum1 100 200 100 200
Jobnum1a 50 100 100 200
JobNum1b 50 100 0 0
CtrlJobNum2 400 800 100 200
JobNum2a 400 800 100 200
Jim
CtrlJobNum3


etc.. etc...

That all works fine. Excel can group my dates nicely so even though I get rawer data, it looks fine. My issue is with getting a count of the subjobs. The raw data could have several lines of the subjobs.

Take the above example, you may have 10 entries for JobNum2a at 40 bucks apiece that get rolled up to the 400 seen in the excel. But a count will give me 15 when I only have one. If I remove the postdate, then I rollup everything just fine in the initial query but I lose the ability to group by month, quarter, and year.

So, How can I group and aggregate these multiple rows and get an accurate count of the unique jobnumbers?

Here's a basic query I'm running:

SELECT     Job_Code, JCDT_POST_DATE, SUM(CASE vw_CMIC_Ctrl_Job_Rollup.JCDT_TYPE_CODE WHEN 'C' THEN vw_CMIC_Ctrl_Job_Rollup.JCDT_AMT ELSE 0 END) 
                      AS Costs, SUM(CASE vw_CMIC_Ctrl_Job_Rollup.JCDT_TYPE_CODE WHEN 'B' THEN vw_CMIC_Ctrl_Job_Rollup.JCDT_AMT ELSE 0 END) AS Billed, 
                      JCDT_JOB_CODE
FROM         dbo.vw_CMIC_Ctrl_Job_Rollup
WHERE     (JCDT_COMP_CODE = 'WN')
GROUP BY Job_Code, JCDT_POST_DATE, JCDT_JOB_CODE

I removed the PM to simplify it a bit (lotta joins). The heiarchy is Job_code then JCDT_JOB_CODE. JCDT_JOB_CODE is the one I'm trying to get an accurate count of. Count distinct doesn't work since there are many entries and I still end up with dups that mess up the count.

Thanks in advance!

 

[ESquared]

Show where in your final pivot you would put the job count. And where will the job count be shown after you do some switching around to view by quarter or year or month?

That will help answer your question. The problem is, the job count is grouped at a separate level. That number can change depending on your date grouping. For example, in January there may be only 1 subjob. But in the whole year there could be 10 subjobs. So if the subjob can't be part of the date grouping it will be the same no matter what and not have anything to do with the date grouping level, which is wrong. But if the subjob is part of the date grouping then it will be multiplied out where it shouldn't be.

If you make the date calculated to be the first of the month in SQL Server (instead of the day), that would make the job count correct, but only when you were viewing the result grouped by month. If you viewed the result only grouped by Quarter, then you will get one count of each subjob per subjob per month.

Try this and I think you'll eventually see what I mean. I've added an extra record that will fall into some date period, that has 0 cost and billable, that gives 1 Jobcount. Sum this new column to get your number of jobs.

Now see that there is no one expression to put in this date column for the extra row that will do the trick. If you put just the min() in, as I have, the job count will only show up in the first time period. No matter what you do to push job count into the final pivot table, it will ALWAYS have a date-grouping level in it that can't be extracted or transformed like the other date levels and still answer the question "unique subjobs per period." The only possible way to do this would be if Excel offered a distinct aggregate, which I don't think it does.

So you're left with displaying the job count as a separate value outside the pivot table. You might have to make it a dynamic lookup supported by another pivot table or by some VB code.

Particularly pay attention to the question "distinct job count over what time period?" Remember my example of only one subjob in January, but 10 subjobs for the whole year. How can you display a 1 in january but a 10 for the whole year when the subjob that was in january could be in each other month as well, adding 12 to the yearly total?

SELECT
   Job_Code,
   JCDT_JOB_CODE,
   CASE WHEN GROUPING(JCDT_POST_DATE) = 1 THEN Min(JCDT_POST_DATE) ELSE JCDT_POST_DATE END,
   CASE WHEN GROUPING(JCDT_POST_DATE) = 0 THEN SUM(CASE vw_CMIC_Ctrl_Job_Rollup.JCDT_TYPE_CODE WHEN 'C' THEN vw_CMIC_Ctrl_Job_Rollup.JCDT_AMT ELSE 0 END) ELSE 0 END AS Costs,
   CASE WHEN GROUPING(JCDT_POST_DATE) = 0 THEN SUM(CASE vw_CMIC_Ctrl_Job_Rollup.JCDT_TYPE_CODE WHEN 'B' THEN vw_CMIC_Ctrl_Job_Rollup.JCDT_AMT ELSE 0 END) ELSE 0 END AS Billed,
   CASE WHEN GROUPING(JCDT_POST_DATE) = 1 THEN 1 ELSE 0 END AS JCDT_JOB_CODE_Count
FROM dbo.vw_CMIC_Ctrl_Job_Rollup
WHERE JCDT_COMP_CODE = 'WN'
GROUP BY
   Job_Code,
   JCDT_JOB_CODE,
   JCDT_POST_DATE
WITH ROLLUP
HAVING GROUPING(JCDT_JOB_CODE) = 0

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Solo4357]

Whoa.. lot to think about. I guess it's most important that the job count at the control job level is correct, and total number of jobs for the PM is correct. Subjobs don't need a count since they are always 1.

I've not used "grouping" before so I'll play with it, but this may work like I need it to. I'll check it out. I think getting the correct counts per month will be ok since I can do sums in the excel pivot table.

 

[ESquared]

Try your original query but convert the dates to the first of the month:

SELECT DateAdd(mm, DateDateDiff(mm, 0, DateCol), 0)

You would use this expression in place of your DateCol and group by this entire expression as well. That will lower the number of jobs you're getting with your count. Perhaps it will help.

 

[Solo4357]

This worked awesome. Everything counts correctly, even when expanding the pivot table, no changes required. Thanks you very much!

 

[ESquared]

From this I take it that you will always show month-level detail and never just show the year-level detail?

 

[Solo4357]

no both. The pivot table adds it correctly.