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

[BxWill]

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

[Bold]
Context
[/Bold]
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;

[Pre]
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
[/Pre]


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.

[Pre]
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
[/Pre]

[BOLD]
QUESTION
[/BOLD]

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.

 

[dhookom]

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:

[pre]
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
[/pre]


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

 

[TamarGranor]

Sounds like what you're looking for is a pivot. Here's a good intro article on the subject:

http://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/.

Tamar

 

[BxWill]

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 ?

 

[dhookom]

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

rngTitle rngMin rngMax
0 - < 25K 0 25000
25K - < 50K 25000 50000
50K - < - 200K 50000 200000[/pre]

It looks like you need to add another range

Then create a query like:

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

[pre]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
[/pre]

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

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

 

[BxWill]

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.

 

[dhookom]

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:

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

 

[BxWill]

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.