July 29, 2011 at 1:44 am
I apologize for most likely not describing my question in the best terms possible.
Let me define how the data is collected
This is nutritional data collection set, where over the span of a week or so several phone interviews are conducted collecting what the subjects have consumed each as its own record with a maximum of 1 entry per day. This process is done several times at intervals of 2 to 3 months between each collection set.
I have a set of data files given to me, this data is provided by an outside source, and cannot be changed.
The data consists of an Id, collected Date, and several values.
Data that is collected over the course of 1 to 2 weeks is considered part of the same collection point. Collection consists of a minimum of 3 records, and up to 5 or 6 depending on various criteria that might force additional interviews to occur.
Query question:
I need to be able to create a summary record for each short term 'collection' point by combining the 3 or more records, and computing the count of the concatenated records and the min/max/avg for each of the provided values.
I have created a sample table, and data that is representative of what I need to work with located below.
If you can point me to an article, or help me through some samples of how this might be done I would be grateful.
I know there has to be a better way to do this then what I am thinking. I tried doing a search and unfortunately I was probably using the wrong search terms.
My initial thoughts, I was thinking of creating a temp table that I would compute some sort of delta value between the dates, and using that to group that data based upon the interval being less then that 14 day window as there should be a month or 3 between each collected set. Thoughts?
CREATE TABLE [dbo].[DietTable](
[Id] [int] NOT NULL,
[Date] [date] NOT NULL,
[Peas] [decimal](18, 3) NOT NULL,
[Carrots] [decimal](18, 3) NOT NULL,
[Beets] [decimal](18, 3) NOT NULL,
[Apples] [decimal](18, 3) NOT NULL,
[Oranges] [decimal](18, 3) NOT NULL,
[Cherrys] [decimal](18, 3) NOT NULL,
CONSTRAINT [PK_DietTable] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[DietTable]([Id], [Date], [Peas], [Carrots], [Beets], [Apples], [Oranges], [Cherrys])
SELECT 14474, '20110327 00:00:00.000', 1903.890, 0.935, 1.524, 27.030, 276.870, 14.664 UNION ALL
SELECT 14474, '20110328 00:00:00.000', 2325.707, 5.391, 0.210, 35.768, 141.611, 23.555 UNION ALL
SELECT 14474, '20110329 00:00:00.000', 1297.534, 7.848, 0.208, 15.450, 108.116, 6.087 UNION ALL
SELECT 14475, '20110403 00:00:00.000', 3584.749, 9.890, 5.317, 20.257, 68.933, 16.981 UNION ALL
SELECT 14475, '20110406 00:00:00.000', 2775.388, 10.417, 1.495, 17.687, 112.599, 22.357 UNION ALL
SELECT 14475, '20110407 00:00:00.000', 2565.219, 3.427, 1.142, 22.226, 48.308, 14.372 UNION ALL
SELECT 14475, '20110612 00:00:00.000', 1599.527, 0.841, 0.415, 15.163, 61.213, 6.139 UNION ALL
SELECT 14475, '20110613 00:00:00.000', 1483.612, 4.079, 0.583, 39.364, 85.143, 18.052 UNION ALL
SELECT 14475, '20110614 00:00:00.000', 3194.720, 3.044, 1.154, 7.293, 108.205, 23.391 UNION ALL
SELECT 14476, '20110406 00:00:00.000', 2006.348, 29.414, 6.585, 35.178, 165.546, 16.245 UNION ALL
SELECT 14476, '20110407 00:00:00.000', 2428.055, 12.584, 3.728, 32.096, 110.855, 14.628 UNION ALL
SELECT 14476, '20110410 00:00:00.000', 3649.215, 4.807, 4.776, 21.528, 194.156, 16.931 UNION ALL
SELECT 14476, '20110612 00:00:00.000', 2094.443, 10.503, 2.360, 39.715, 37.992, 10.809 UNION ALL
SELECT 14476, '20110613 00:00:00.000', 1964.249, 2.375, 3.162, 22.835, 65.402, 15.660 UNION ALL
SELECT 14476, '20110614 00:00:00.000', 2081.500, 6.855, 3.012, 57.464, 72.063, 8.424 UNION ALL
SELECT 14477, '20110416 00:00:00.000', 2328.648, 21.637, 3.317, 40.784, 116.822, 13.508 UNION ALL
SELECT 14477, '20110419 00:00:00.000', 2474.442, 29.605, 2.077, 17.580, 122.085, 29.459 UNION ALL
SELECT 14477, '20110421 00:00:00.000', 2862.525, 12.699, 13.170, 61.366, 79.415, 24.471 UNION ALL
SELECT 14478, '20110417 00:00:00.000', 888.556, 2.575, 1.553, 40.425, 65.742, 9.207 UNION ALL
SELECT 14478, '20110419 00:00:00.000', 931.014, 12.313, 1.910, 15.692, 69.703, 13.407 UNION ALL
SELECT 14478, '20110421 00:00:00.000', 1282.271, 8.158, 0.988, 16.457, 38.662, 8.019 UNION ALL
SELECT 14478, '20110422 00:00:00.000', 1496.497, 1.497, 3.740, 38.626, 73.176, 7.395 UNION ALL
SELECT 14478, '20110606 00:00:00.000', 714.708, 13.795, 0.647, 12.805, 53.639, 10.467 UNION ALL
SELECT 14478, '20110612 00:00:00.000', 1986.979, 6.753, 0.781, 13.454, 71.784, 15.775 UNION ALL
SELECT 14478, '20110613 00:00:00.000', 1028.263, 14.256, 0.586, 14.437, 91.725, 8.274 UNION ALL
SELECT 14479, '20110501 00:00:00.000', 2670.838, 40.585, 3.569, 44.609, 128.564, 19.574 UNION ALL
SELECT 14479, '20110505 00:00:00.000', 2311.163, 25.977, 3.030, 34.398, 97.457, 16.587 UNION ALL
SELECT 14479, '20110508 00:00:00.000', 2056.229, 7.368, 1.526, 32.867, 76.903, 16.870 UNION ALL
SELECT 14480, '20110515 00:00:00.000', 1759.028, 11.315, 3.531, 39.386, 57.173, 13.570 UNION ALL
SELECT 14480, '20110517 00:00:00.000', 2674.616, 12.608, 3.236, 35.620, 45.431, 14.616 UNION ALL
SELECT 14480, '20110519 00:00:00.000', 2458.867, 17.220, 1.767, 29.384, 101.819, 30.498 UNION ALL
SELECT 14481, '20110425 00:00:00.000', 2170.917, 21.186, 3.416, 37.742, 76.961, 12.386 UNION ALL
SELECT 14481, '20110501 00:00:00.000', 2469.980, 7.489, 1.699, 45.140, 67.805, 23.245 UNION ALL
SELECT 14481, '20110502 00:00:00.000', 2832.320, 5.306, 0.254, 55.891, 117.105, 27.764 UNION ALL
SELECT 14482, '20110425 00:00:00.000', 3539.347, 24.912, 14.681, 60.966, 205.759, 30.698 UNION ALL
SELECT 14482, '20110427 00:00:00.000', 2245.376, 24.520, 9.704, 45.632, 90.456, 16.239 UNION ALL
SELECT 14482, '20110501 00:00:00.000', 1933.465, 11.105, 2.783, 34.111, 78.782, 12.470 UNION ALL
SELECT 14482, '20110619 00:00:00.000', 2139.347, 41.619, 5.008, 98.307, 76.329, 17.382 UNION ALL
SELECT 14482, '20110621 00:00:00.000', 2616.795, 10.109, 2.682, 41.392, 102.181, 29.784 UNION ALL
SELECT 14482, '20110623 00:00:00.000', 2467.289, 30.829, 2.835, 77.410, 171.159, 29.448 UNION ALL
SELECT 14483, '20110501 00:00:00.000', 2021.502, 22.322, 1.456, 19.129, 94.608, 17.748 UNION ALL
SELECT 14483, '20110503 00:00:00.000', 1824.789, 6.344, 1.134, 24.278, 103.827, 15.251 UNION ALL
SELECT 14483, '20110505 00:00:00.000', 2318.633, 24.154, 4.758, 20.172, 87.035, 15.213 UNION ALL
SELECT 14483, '20110605 00:00:00.000', 3401.789, 1.687, 0.990, 21.695, 31.347, 15.077 UNION ALL
SELECT 14483, '20110606 00:00:00.000', 3194.820, 24.960, 4.516, 19.749, 112.844, 20.203 UNION ALL
SELECT 14483, '20110607 00:00:00.000', 3324.832, 20.298, 2.677, 58.468, 94.741, 24.621 UNION ALL
SELECT 14484, '20110501 00:00:00.000', 2065.760, 23.958, 1.039, 22.943, 116.344, 23.484 UNION ALL
SELECT 14484, '20110503 00:00:00.000', 1469.069, 24.424, 0.567, 7.677, 32.291, 6.020 UNION ALL
SELECT 14484, '20110508 00:00:00.000', 2428.996, 14.625, 1.405, 63.211, 126.927, 13.670 UNION ALL
SELECT 14484, '20110619 00:00:00.000', 1991.741, 11.739, 1.656, 50.890, 92.730, 25.070
COMMIT;
RAISERROR (N'[dbo].[DietTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[DietTable]([Id], [Date], [Peas], [Carrots], [Beets], [Apples], [Oranges], [Cherrys])
SELECT 14484, '20110621 00:00:00.000', 1817.054, 39.883, 1.847, 6.207, 108.871, 10.638 UNION ALL
SELECT 14484, '20110623 00:00:00.000', 1708.185, 21.167, 1.841, 12.499, 53.892, 14.406 UNION ALL
SELECT 14486, '20110508 00:00:00.000', 3698.119, 7.947, 2.866, 74.549, 115.926, 17.643 UNION ALL
SELECT 14486, '20110510 00:00:00.000', 3254.569, 12.310, 1.585, 51.577, 101.149, 15.525 UNION ALL
SELECT 14486, '20110512 00:00:00.000', 2966.675, 1.829, 2.296, 50.703, 101.920, 12.158 UNION ALL
SELECT 14486, '20110612 00:00:00.000', 1989.225, 7.221, 8.741, 21.345, 92.047, 12.437 UNION ALL
SELECT 14486, '20110613 00:00:00.000', 2126.867, 8.539, 4.423, 23.605, 62.913, 8.706 UNION ALL
SELECT 14486, '20110614 00:00:00.000', 2382.375, 4.175, 0.586, 96.901, 81.104, 22.292 UNION ALL
SELECT 14487, '20110529 00:00:00.000', 2166.859, 14.051, 0.754, 46.447, 76.455, 12.049 UNION ALL
SELECT 14487, '20110531 00:00:00.000', 2173.038, 2.561, 0.617, 35.583, 71.628, 10.904 UNION ALL
SELECT 14487, '20110602 00:00:00.000', 2693.475, 28.432, 1.501, 18.211, 77.602, 16.705 UNION ALL
SELECT 14488, '20110515 00:00:00.000', 1661.095, 9.056, 2.929, 13.166, 81.832, 6.062 UNION ALL
SELECT 14488, '20110517 00:00:00.000', 2499.579, 22.001, 1.316, 51.726, 76.933, 19.394 UNION ALL
SELECT 14488, '20110519 00:00:00.000', 1657.366, 6.633, 1.387, 46.120, 109.512, 8.977 UNION ALL
SELECT 14489, '20110508 00:00:00.000', 2646.200, 37.437, 0.635, 24.266, 116.326, 24.638 UNION ALL
SELECT 14489, '20110510 00:00:00.000', 2100.929, 9.429, 6.407, 46.749, 120.071, 15.356 UNION ALL
SELECT 14489, '20110512 00:00:00.000', 2715.443, 22.262, 1.374, 61.202, 180.929, 38.330 UNION ALL
SELECT 14490, '20110519 00:00:00.000', 4831.598, 9.852, 4.274, 40.704, 130.430, 22.833 UNION ALL
SELECT 14490, '20110521 00:00:00.000', 4048.823, 31.370, 4.705, 36.557, 199.018, 83.205 UNION ALL
SELECT 14490, '20110523 00:00:00.000', 3025.745, 1.238, 2.322, 43.823, 135.383, 26.586 UNION ALL
SELECT 14491, '20110619 00:00:00.000', 2073.683, 5.658, 3.745, 11.238, 139.493, 20.350 UNION ALL
SELECT 14491, '20110621 00:00:00.000', 1786.272, 3.590, 7.840, 18.019, 183.214, 17.799 UNION ALL
SELECT 14491, '20110623 00:00:00.000', 2662.205, 9.857, 4.544, 40.616, 148.638, 18.537 UNION ALL
SELECT 14492, '20110529 00:00:00.000', 2682.266, 1.858, 0.673, 44.972, 61.917, 15.002 UNION ALL
SELECT 14492, '20110531 00:00:00.000', 2837.603, 3.418, 0.684, 30.680, 171.611, 21.417 UNION ALL
SELECT 14492, '20110602 00:00:00.000', 3131.160, 4.204, 0.363, 38.914, 62.724, 26.861 UNION ALL
SELECT 14493, '20110523 00:00:00.000', 4615.975, 0.000, 1.394, 15.462, 27.059, 16.496 UNION ALL
SELECT 14493, '20110526 00:00:00.000', 4555.074, 19.648, 2.481, 46.920, 76.235, 20.738 UNION ALL
SELECT 14493, '20110528 00:00:00.000', 4923.151, 10.005, 2.838, 16.982, 148.192, 20.678 UNION ALL
SELECT 14494, '20110523 00:00:00.000', 5115.695, 9.358, 1.258, 67.213, 231.653, 36.970 UNION ALL
SELECT 14494, '20110526 00:00:00.000', 3246.174, 6.621, 0.985, 58.340, 103.629, 17.574 UNION ALL
SELECT 14494, '20110529 00:00:00.000', 3471.174, 2.775, 6.767, 40.194, 114.828, 27.495 UNION ALL
SELECT 14495, '20110522 00:00:00.000', 6612.680, 19.849, 13.396, 7.512, 267.852, 28.379 UNION ALL
SELECT 14495, '20110523 00:00:00.000', 5797.220, 3.067, 5.396, 51.093, 107.444, 21.753 UNION ALL
SELECT 14495, '20110525 00:00:00.000', 5783.902, 1.426, 4.886, 30.757, 129.041, 19.468 UNION ALL
SELECT 14497, '20110605 00:00:00.000', 2322.667, 0.290, 6.145, 22.205, 145.301, 18.578 UNION ALL
SELECT 14497, '20110606 00:00:00.000', 2201.349, 0.300, 2.378, 20.662, 95.328, 11.135 UNION ALL
SELECT 14497, '20110607 00:00:00.000', 1990.931, 1.652, 5.197, 12.982, 128.032, 10.352 UNION ALL
SELECT 14498, '20110605 00:00:00.000', 1727.512, 0.470, 0.068, 17.929, 48.023, 6.129 UNION ALL
SELECT 14498, '20110608 00:00:00.000', 2749.368, 9.141, 4.390, 27.113, 171.727, 24.073 UNION ALL
SELECT 14498, '20110615 00:00:00.000', 1838.687, 7.503, 2.635, 23.427, 108.299, 9.793 UNION ALL
SELECT 14499, '20110531 00:00:00.000', 2337.418, 13.520, 1.384, 18.333, 99.601, 18.946 UNION ALL
SELECT 14499, '20110602 00:00:00.000', 2984.534, 15.420, 6.001, 40.478, 118.352, 20.107 UNION ALL
SELECT 14499, '20110605 00:00:00.000', 3271.843, 22.497, 12.167, 83.006, 149.987, 26.930 UNION ALL
SELECT 14500, '20110605 00:00:00.000', 1952.299, 0.239, 0.725, 4.962, 60.184, 10.966 UNION ALL
SELECT 14500, '20110606 00:00:00.000', 1879.591, 2.597, 5.154, 5.225, 85.801, 25.191 UNION ALL
SELECT 14500, '20110608 00:00:00.000', 1651.670, 3.077, 0.742, 13.463, 108.675, 11.583 UNION ALL
SELECT 14506, '20110612 00:00:00.000', 1334.015, 2.924, 1.021, 57.378, 81.352, 10.318 UNION ALL
SELECT 14506, '20110613 00:00:00.000', 2432.603, 1.468, 0.904, 19.856, 129.725, 26.903 UNION ALL
SELECT 14506, '20110614 00:00:00.000', 2270.357, 16.070, 2.168, 56.000, 113.783, 19.424
COMMIT;
RAISERROR (N'[dbo].[DietTable]: Insert Batch: 2.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[DietTable]([Id], [Date], [Peas], [Carrots], [Beets], [Apples], [Oranges], [Cherrys])
SELECT 14507, '20110613 00:00:00.000', 3341.162, 37.098, 1.889, 58.483, 132.395, 15.039 UNION ALL
SELECT 14507, '20110614 00:00:00.000', 3766.646, 48.109, 1.640, 39.766, 192.856, 31.115 UNION ALL
SELECT 14507, '20110621 00:00:00.000', 3175.141, 26.979, 3.366, 81.024, 223.423, 27.519
COMMIT;
RAISERROR (N'[dbo].[DietTable]: Insert Batch: 3.....Done!', 10, 1) WITH NOWAIT;
GO
July 29, 2011 at 1:11 pm
In all honesty I think the biggest challenge you are facing here is poorly designed tables. How are you going to handle adding new foods? You have to add more columns and all the queries around them. What about when you suddenly have 500 different foods?
I don't know enough about your application to begin suggesting how to change this.
And just fyi, the plural of a cherry is cherries. I know that is nitpicking but spelling errors like that will drive you nuts for years. I worked on a system years ago where the table name for Companies was spelled Companys. I typed it incorrectly so many times that to this day I have to stop and think about what is correct. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2011 at 3:06 pm
The names for the columns are just names I made up quickly and very late last nite so pardon the pluralization errors please.
There are about 200 fields with names like:
Total Vitamin A Activity (International Units) (IU)
Beta-Carotene Equivalents (derived from provitamin A carotenoids) (mcg)
Retinol (mcg)
Vitamin D (calciferol) (mcg)
Total Alpha-Tocopherol Equivalents (mg)
but that just added noise to the problem don't ou think? 😉
I have only the composite key of Id and Date to use to descriminate each record.
July 29, 2011 at 3:14 pm
Yes I would agree that would make the problem even more difficult. 😛
I am totally unclear on the rules for the "groups". Can you provide what the desired output would be given your sample data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2011 at 3:56 pm
The solution is simple. For each date, compute an auxiliary date that is 13 days later.
Then apply my 4 second solution found here http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851 on how to pack the date and time intervals. Then all original dates within 14 days will be in the same virtual group.
Using that information, it will be simple to calculate which row in your original table that "belong" to each other.
N 56°04'39.16"
E 12°55'05.25"
July 29, 2011 at 4:11 pm
Thanks for the helping hand Sean, and SwePeso.
It sounds like SwePeso might have hit the nail on the head.
Let me see if I can understand it and take what he's given me to solve my need.
July 29, 2011 at 8:17 pm
Ok, I was able to go the the blog that SwePeso pointed out and tested several of the contest entries there and being able to do exactly what I needed.
I did end up using his entry implemented with the CTE.
That said, I still am not clear on how it actually works (don't you just love magic code), but was able to easily modify it to use my specific situation.
Thank you for pointing me in the right direction.
July 30, 2011 at 12:37 am
Thank you.
N 56°04'39.16"
E 12°55'05.25"
July 30, 2011 at 6:31 am
R. Scott Mobley (7/29/2011)
...That said, I still am not clear on how it actually works (don't you just love magic code), but was able to easily modify it to use my specific situation.
...
Just an advice (a rather strong one though): don't ever use any code in production that you don't fully understand and/or are not able to explain to someone else how it actually works.
Study the code you're planning to implement and if there's something not clear, please ask. We'll be glad to expain it while you (and we) have the time available instead of waiting for an "urgent request for help"... 😉
July 30, 2011 at 10:45 am
Hi LutzM,
Thanks for you reply and I agree 100% with your comment.
I would never use something like this without understanding it in such an enviroment.
Fortunately what I am trying to do is to give examples and show the potential of using SQL Server, instead of loading all these files into Excel to do this type of analysis.
It also gives me something that I can look at to learn how to use features I would have never probably used on my own.
While I can work my way around SQL Server I am at best (compared to those that do this day in and day) out a duffer (Golf player with a huge handicap for those that might not know that use of the word 😉 )
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply