Feed variables into Cross-Tab Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Feed variables into Cross-Tab Query?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-02-23 : 10:17:34

I have been tasked by the powers-that-be to sort through >49000 labor transactions for CY2009 and summarize their hours and dollars information by workcenter. I've already got a rudimentary cross-tab query hashed out (thanks to searching SQLTEAM.COM) but there are a couple tricks involved...

First, each 'superjob' has potentially 9999 'subjobs' - they want the subjob data displayed in aggregate with the superjob data. No sweat, that, already done.

BUT...

There are 38 distinct workcenters, and not every job uses the same subset of workcenters. I don't want to display 76 columns (One for each hour and dollar sum) especially if 64 of them will always be empty. Even moreso than not wanting to display that much empty garbage, I don't want to hand-code for each!

This is the query I have drawn up so far:

SELECT
	DISTINCT(left(ladetail.fjobno, 5)) as JONO,
	SUM(CASE WHEN ladetail.fpro_id = 'DESIGN ' THEN (CAST(DATEDIFF(s, ladetail.fsdatetime, ladetail.fedatetime) as numeric) / 3600) ELSE 0 END) as DESIGN_HR,
	SUM(CASE WHEN ladetail.fpro_id = 'DESIGN ' THEN ladetail.ftotpcost ELSE 0 END) AS DESIGN$,
	SUM(CASE WHEN ladetail.fpro_id = 'CONTROL' THEN (CAST(DATEDIFF(s, ladetail.fsdatetime, ladetail.fedatetime) as numeric) / 3600) ELSE 0 END) AS CONTROL_HR,
	SUM(CASE WHEN ladetail.fpro_id = 'CONTROL' THEN ladetail.ftotpcost ELSE 0 END) AS CONTROL$
	
from
	ladetail
where
	ladetail.fdate between ('2009-01-01') and ('2009-12-31')
	and
	LEFT(ladetail.fjobno, 5) in ('20386')
GROUP BY
	LEFT(ladetail.fjobno, 5)

which yields

quote:
JONO DESIGNHR DESIGN$ CONTROLHR CONTROL$
------- -------------- ------------ ---------------- --------------
20386 181.50 3001.10 2824.00 57599.29

As it is, to give them what they want I will have to run the same query 1024 times (once for each distinct JONO) - I'll be handling that from an ASP page so I'm not as concerned as I am with the workcenters issue.

Any ideas??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:23:00

the DISTINCT is not required here
Also you need to define precision and scale value for numeric else you don't get decimal part as under default conditions scale has default value of 0.
Also whats the field having workcentres value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-02-23 : 10:55:18

quote:
Originally posted by visakh16

quote:
the DISTINCT is not required here

Right! Corrected, thank you!

quote:
Also you need to define precision and scale value for numeric else you don't get decimal part as under default conditions scale has default value of 0.

I'm not sure I follow; I'm grabbing the difference in seconds between a clock-in (fsdatetime) and clock-out (fedatetime) - I'm not sure I would need to set precision since I'll always be working with integers?

quote:
Also whats the field having workcentres value?

Workcenters are represented in table ladetail by a 7-wide char field fpro_id; the external table that they appear in is called inwork, and in that table they go by the name fcpro_id.

Thanks for taking time to look at this; I need all the help I can get :)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 11:05:51

quote:
Originally posted by Metcalf

quote:
Originally posted by visakh16

quote:
the DISTINCT is not required here

Right! Corrected, thank you!
quote:
Also you need to define precision and scale value for numeric else you don't get decimal part as under default conditions scale has default value of 0.

I'm not sure I follow; I'm grabbing the difference in seconds between a clock-in (fsdatetime) and clock-out (fedatetime) - I'm not sure I would need to set precision since I'll always be working with integers?
quote:
Also whats the field having workcentres value?

Workcenters are represented in table ladetail by a 7-wide char field fpro_id; the external table that they appear in is called inwork, and in that table they go by the name fcpro_id.

Thanks for taking time to look at this; I need all the help I can get :)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]
then why cast them to numeric?
isnt your requirement to show figures for each workcenters where data exists?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-02-23 : 11:22:36

quote:
Originally posted by visakh16
then why cast them to numeric?
isnt your requirement to show figures for each workcenters where data exists?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I'm using datediff s to get the number of seconds between punch-in and punch-out. Sometimes people will put in 28800 seconds (8 hours) and sometimes they put in 31500 seconds - 8 and 3/4 hours. In order to display the fractional remainder of division of total number of seconds by one hour (3600s) I cast it to a numeric so that 31500 yields 8.75 instead of 8, and 1800 seconds yields 0.5 instead of 0, which is what I get if I don't cast it to numeric.

Though I have never had trouble with the CAST & division, I get the sense that you've identified some potential problem that I have not seen that could be addressed with a different approach?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 12:16:01

still if you need result as 8.75,etc you need to specify an explicit value for precision and scale.

see the below for reason

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-02-23 : 15:19:09

quote:
Originally posted by visakh16

still if you need result as 8.75,etc you need to specify an explicit value for precision and scale.

see the below for reason

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for the tip; any ideas regarding what I might be able to do for the workcenter issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 09:39:01

i really didnt get the issue. can you explain it giving a data sample?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources