×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sql Server - Create summary table using detail data in another table
2

Sql Server - Create summary table using detail data in another table

Sql Server - Create summary table using detail data in another table

(OP)

Timely insight appreciated.
(Note, I did create a similar post in the MS Office forum and will delete that one)


Context

Receive a text file that contains data from a Sql Server database.

I import the text file into MS Excel to create a Report.

Data within MS Excel is like this;

Equip Code Sale Date Sale Price
12345 1/25/2019 8000
12346 1/26/2019 10000
89023 1/18/2019 24569
88456 2/25/2019 25000
88457 2/1/2019 13000
11567 1/12/2019 24000
11567 2/13/2019 53000
24680 1/10/2019 21000
24680 2/9/2019 14000
24699 1/5/2019 45257
12345 2/19/2019 61000
12346 1/5/2019 117500
89023 2/27/2019 25600
88456 2/20/2019 10222
88457 1/5/2019 30000
11567 1/3/2019 125000


Step 2 of the process - Using sumproduct formulae within MS Excel to create a matrix report like this;

	Sales Price Range					
	0 - < 25K			25K - < 50K		
Equip Code	1/1/2019	2/1/2019	3/1/2019	1/1/2019	2/1/2019	3/1/2019
12346	2	0		0	0	
89023	1	0		0	1	
88456	0	1		0	1	
88457	0	1		1	0	
11567	1	0		0	0	
24680	1	1		0	0	
24699	0	0		1	0	
12345	1	0		0	0	
 


Eventually, in Step 3 of the process, I plan to export the summarized data back to a Sql Server database table for
readily available analysis of trends/patterns across months/years. This summarized table would be continuously updated.

Equip Code	Month	SalesPriceLevel	Nbr
12346	1/1/2019	0 - < 25K	2
89023	1/1/2019	0 - < 25K	1
88456	1/1/2019	0 - < 25K	0
88457	1/1/2019	0 - < 25K	0
11567	1/1/2019	0 - < 25K	1
24680	1/1/2019	0 - < 25K	1
24699	1/1/2019	0 - < 25K	0
12345	1/1/2019	0 - < 25K	1
12346	2/1/2019	0 - < 25K	0
89023	2/1/2019	0 - < 25K	0
88456	2/1/2019	0 - < 25K	1
88457	2/1/2019	0 - < 25K	1
11567	2/1/2019	0 - < 25K	0
24680	2/1/2019	0 - < 25K	1
24699	2/1/2019	0 - < 25K	0
12345	2/1/2019	0 - < 25K	0
 


QUESTION


Is it feasible/possible to create the data that is displayed in Step 3 of the process directly using functionality solely within Sql Server?

In essence, it would take the detail data within the Sql Server database table and create summarized data and load into a different table? Sort of like a make table query in MS Access...

Fine with using MS Excel as an intermediate tool to summarize the data but just thought about this idea.

Maybe not possible nor feasible.

Appreciate insight.

RE: Sql Server - Create summary table using detail data in another table

Why are there 16 records in your data from a Sql Server database and the sum of Nbr is 9? Where are the 25k-50k records in your "analysis of trends/patterns across months/years"?

I'm fairly positive someone will provide a SQL only solution but you might need to explain and consider using TGML like:

Equip Code  Sale Date   Sale Price
12345       1/25/2019	8000
12346       1/26/2019	10000
89023       1/18/2019	24569
88456       2/25/2019	25000
88457       2/1/2019	13000
11567       1/12/2019	24000
11567       2/13/2019	53000
24680       1/10/2019	21000
24680       2/9/2019	14000
24699       1/5/2019	45257
12345       2/19/2019	61000
12346       1/5/2019	117500
89023       2/27/2019	25600
88456       2/20/2019	10222
88457       1/5/2019	30000
11567       1/3/2019	12500
 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Sql Server - Create summary table using detail data in another table

(OP)
dhookum,

Due to the confidentiality and volume of the data, I provided a subset of the data.

Per additional review, it really appears that I eventually need to create a cube using SSAS or possibly create a cube using MS Excel.

For now, a SQL only solution would be sufficient.

Considering the data that was initially provided, the sale price bins of course would be adjusted to maybe 0 - <20K, 20K - <40K, etc.

But, regardless of the scale of the sale price bins, the issue would be the same - Aggregate the detail data within a table in the Sql Server Db and readily produce summary-level reports or continue to extract the detail data into MS Excel and aggregate in MS Excel.

Was just interested in obtaining insight as to how others have resolved the issue of quickly providing aggregated reports to the end users.

Many options - Run Sql to generate a pivot within Sql Server Management Studio or External query - pulling data from Sql Server into a MS Excel template or create a cube using SSAS or ?

RE: Sql Server - Create summary table using detail data in another table

You need to create a table of sale price bins/ranges:

rngTitle        rngMin    rngMax
0 - < 25K       0         25000
25K - < 50K     25000     50000
50K - < - 200K  50000     200000 

It looks like you need to add another range

Then create a query like:

CODE --> sql

SELECT [Equip Code], DATEADD(d,-DATEPART(day,[Sale Date])+1,[Sale Date]) As Mth, rngTitle SalesPriceLevel, Count(*) as Nbr
FROM tblFromSQLServer JOIN tblPriceRanges on [Sale Price]>=rngMin AND [Sale Price]<rngMax 
GROUP BY [Equip Code], rngTitle, DATEADD(d,-DATEPART(day,[Sale Date])+1,[Sale Date]) 

Results based on your initial sample data and desired final output

Equip Code   Mth           SalesPriceLevel    Nbr
11567        2019-01-01    0 - < 25K          2
11567        2019-02-01    50K - < 200K       1
12345        2019-01-01    0 - < 25K          1
12345        2019-02-01    50K - < 200K       1
12346        2019-01-01    0 - < 25K          1
12346        2019-01-01    50K - < 200K       1
24680        2019-01-01    0 - < 25K          1
24680        2019-02-01    0 - < 25K          1
24699        2019-01-01    25K - < 50K        1
88456        2019-02-01    0 - < 25K          1
88456        2019-02-01    25K - < 50K        1
88457        2019-02-01    0 - < 25K          1
88457        2019-01-01    25K - < 50K        1
89023        2019-01-01    0 - < 25K          1
89023        2019-02-01    25K - < 50K        1
 

If you can't create a table, you can use a CTE.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Sql Server - Create summary table using detail data in another table

(OP)
Appreciate the insight.

Vaguely familiar with CTEs.

Will research further.

It appears that then loading into a Sql Server table and using a composite primary key of "Equip Code" , "Mth", and
"Sales Price Level" will enable the unique identification of each record rather than an arbitrary id field.








RE: Sql Server - Create summary table using detail data in another table

I don't know what you are talking about with "arbitrary id field".

If you can't create a table, you can use SQL like:

CODE --> sql

WITH 
cteR as
(SELECT '0 - < 25K' as rngTitle, 0 as rngMin, 25000 as rngMax
UNION
SELECT '25K - < 50K', 25000, 50000
UNION
SELECT '50K - < 200K', 50000, 200000)

SELECT [Equip Code], DATEADD(d,-DATEPART(day,[Sale Date])+1,[Sale Date]) As Mth, rngTitle SalesPriceLevel, Count(*) as Nbr
FROM tblFromSQLServer JOIN cteR on [Sale Price]>=rngMin AND [Sale Price]<rngMax 
GROUP BY [Equip Code], rngTitle, DATEADD(d,-DATEPART(day,[Sale Date])+1,[Sale Date]) 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Sql Server - Create summary table using detail data in another table

(OP)
Well review in detail.

Meant to imply that designating a composite primary key ("Equip Code" , "Mth", and
"Sales Price Level") for this "new aggregated table" would appear to be preferable instead of
setting up a incrementing ID field.

Particularly, since I will add data on a monthly basis.

"Arbitrary" may have been the wrong adjective to use.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close