July 22, 2010 at 8:40 am
Hi,
This is a a follow up question (but different issue), to my previous post on http://www.sqlservercentral.com/Forums/Topic953298-338-1.aspx?Update=1... Thank you again Nevyn for your help.
This is a different issue that I run into while analizing the data. In this case I'm looking for where with in the last year (365 days) if the product size for a Client has below 1GB (1024MB), I don't want to make any conversion to the size. But if all the size with in the last year for the product size is over 1GB, I would like to convert the SizeType to GB, convert the Size to GB for that client. Below are some examples how the Display should look like and how the data looks like and scripts. If my script is incorrect, and if you suggest I should approach this in a different way, please advice.
Thanks in advance,
Display
ClientID ProductName SizeType Size MeasuredDate
107DPTPCSCBMB989.832010-02-01
107DPTPCSCBMB1012.752010-03-01
107DPTPCSCBMB1246.802010-04-01
107DPTPCSCBMB1388.942010-05-01
107DPTPCSCBMB1491.252010-06-01
107DPTPCSCBMB1659.252010-07-01
114bkcsdzMB102.862010-02-01
114bkcsdzMB109.282010-03-01
114bkcsdzMB114.022010-04-01
114bkcsdzMB122.122010-05-01
114bkcsdzMB135.562010-06-01
114bkcsdzMB144.192010-07-01
120xvwfmGB48.002010-03-01
120xvwfmGB49.062010-04-01
120xvwfmGB49.962010-05-01
120xvwfmGB51.412010-06-01
120xvwfmGB76.942010-07-01
Data
MeasurID ClientID ProductName Size MeasuredDate
39107DPTPCSCB989.832010-02-23
58107DPTPCSCB1012.752010-03-24
63107DPTPCSCB1205.472010-04-08
74107DPTPCSCB1288.132010-04-24
90107DPTPCSCB1388.942010-05-25
108107DPTPCSCB1491.252010-06-29
129107DPTPCSCB1659.252010-07-14
43114bkcsdz102.862010-02-23
62114bkcsdz109.282010-03-24
51114bkcsdz112.342010-04-07
78114bkcsdz115.692010-04-24
94114bkcsdz122.122010-05-25
116114bkcsdz135.562010-06-29
138114bkcsdz144.192010-07-14
48120xvwfm49152.892010-03-23
66120xvwfm50235.122010-04-24
82120xvwfm51155.652010-05-24
122120xvwfm52646.692010-06-29
144120xvwfm78787.632010-07-14
CREATE TABLE MeasureProd
(MeasurID INT
,ClientID INT
,ProductName VARCHAR(12)
,Size DECIMAL(9,2)
,MeasuredDate DATETIME)
INSERT INTO MeasureProd
SELECT 39,107,'DPTPCSCB',989.83,'2010-02-23' UNION
SELECT 58,107,'DPTPCSCB',1012.75,'2010-03-24' UNION
SELECT 63,107,'DPTPCSCB',1205.47,'2010-04-08' UNION
SELECT 74,107,'DPTPCSCB',1288.13,'2010-04-24' UNION
SELECT 90,107,'DPTPCSCB',1388.94,'2010-05-25' UNION
SELECT 108,107,'DPTPCSCB',1491.25,'2010-06-29' UNION
SELECT 129,107,'DPTPCSCB',1659.25,'2010-07-14' UNION
SELECT 43,114,'bkcsdz',102.86,'2010-02-23' UNION
SELECT 62,114,'bkcsdz',109.28,'2010-03-24' UNION
SELECT 51,114,'bkcsdz',112.34,'2010-04-07' UNION
SELECT 78,114,'bkcsdz',115.69,'2010-04-24' UNION
SELECT 94,114,'bkcsdz',122.12,'2010-05-25' UNION
SELECT 116,114,'bkcsdz',135.56,'2010-06-29' UNION
SELECT 138,114,'bkcsdz',144.19,'2010-07-14' UNION
SELECT 48,120,'xvwfm',49152.89,'2010-03-23' UNION
SELECT 66,120,'xvwfm',50235.12,'2010-04-24' UNION
SELECT 82,120,'xvwfm',51155.65,'2010-05-24' UNION
SELECT 122,120,'xvwfm',52646.69,'2010-06-29' UNION
SELECT 144,120,'xvwfm',78787.63,'2010-07-14'
--drop table MeasureProd
SELECT * FROM dbo.MeasureProd ORDER BY Clientid, MeasuredDate
SELECT MeasurID, ClientID, ProductName, 'SizeType' =
CASE WHEN Size >= 1024 THEN 'GB' ELSE 'MB' END,
Size,
--'Size' = CASE WHEN MP.Size >= 1024 THEN CAST(MP.Size/1024 AS DECIMAL(9,2)) ELSE CAST(MP.Size AS DECIMAL(9,2)) END,
MeasuredDate
into #MeasureProd_temp
FROM MeasureProd MP
WHERE MeasuredDate > GETDATE() - 365
ORDER BY Clientid, MeasuredDate
select ClientID, ProductName, SizeType,
--'Size' = CASE WHEN Size >= 1024 THEN CAST(Size/1024 AS DECIMAL(9,2)) ELSE CAST(Size AS DECIMAL(9,2)) END,
CAST(Avg(Size) AS DECIMAL(9,2)) as Size,
dateadd(month, datediff(month, 0, MeasuredDate),0) as MeasuredDate
--into #ConvertMB_Temp
from #MeasureProd_temp
group by ClientID, ProductName, dateadd(month, datediff(month, 0, MeasuredDate),0), SizeType
July 22, 2010 at 9:44 am
First and foremost, I'd say that doing this is probably a bad idea. Im not sure if you're talking about updating the table (a really bad idea), or just changing the display on a detail report, but either way the measurement unit of your columns should be consistent.
Not only does your expected output not provide any context to tell whether the size is in MB or GB, but because different rows will be in different units, but the same number of decimal places, they won't have the same precision level.
If possible, I would recommend instead displaying duplicate columns, one showing size in MB and one showing size in GB.
EDIT: whoops, didnt notice the sizetype column. That gives you context at least, but you still will have precision level issues.
Anyway, if you must do it this way, here is how I came up with your expected output:
CREATE TABLE #MeasureProd
(MeasurID INT
,ClientID INT
,ProductName VARCHAR(12)
,Size DECIMAL(9,2)
,MeasuredDate DATETIME)
INSERT INTO #MeasureProd
SELECT 39,107,'DPTPCSCB',989.83,'2010-02-23' UNION
SELECT 58,107,'DPTPCSCB',1012.75,'2010-03-24' UNION
SELECT 63,107,'DPTPCSCB',1205.47,'2010-04-08' UNION
SELECT 74,107,'DPTPCSCB',1288.13,'2010-04-24' UNION
SELECT 90,107,'DPTPCSCB',1388.94,'2010-05-25' UNION
SELECT 108,107,'DPTPCSCB',1491.25,'2010-06-29' UNION
SELECT 129,107,'DPTPCSCB',1659.25,'2010-07-14' UNION
SELECT 43,114,'bkcsdz',102.86,'2010-02-23' UNION
SELECT 62,114,'bkcsdz',109.28,'2010-03-24' UNION
SELECT 51,114,'bkcsdz',112.34,'2010-04-07' UNION
SELECT 78,114,'bkcsdz',115.69,'2010-04-24' UNION
SELECT 94,114,'bkcsdz',122.12,'2010-05-25' UNION
SELECT 116,114,'bkcsdz',135.56,'2010-06-29' UNION
SELECT 138,114,'bkcsdz',144.19,'2010-07-14' UNION
SELECT 48,120,'xvwfm',49152.89,'2010-03-23' UNION
SELECT 66,120,'xvwfm',50235.12,'2010-04-24' UNION
SELECT 82,120,'xvwfm',51155.65,'2010-05-24' UNION
SELECT 122,120,'xvwfm',52646.69,'2010-06-29' UNION
SELECT 144,120,'xvwfm',78787.63,'2010-07-14'
SELECT a.ClientID,
a.ProductName,
SizeType = CASE WHEN minSize >= 1024 THEN 'GB' ELSE 'MB' END,
Size = CASE WHEN minSize >= 1024 THEN CAST(AVG(a.Size)/1024 AS DECIMAL(9,2)) ELSE CAST(AVG(a.Size) AS DECIMAL(9,2)) END,
MeasuredDateMonth=dateadd(month, datediff(month, 0, a.MeasuredDate),0)
FROM #MeasureProd a
INNER JOIN
(SELECT ClientID,
minSize=MIN(Size)
FROM #MeasureProd
WHERE MeasuredDate > GETDATE() - 365
GROUP BY ClientID) AS b
ON a.ClientID = b.ClientID
WHERE MeasuredDate > GETDATE() - 365
GROUP BY a.ClientID,a.ProductName, dateadd(month, datediff(month, 0, a.MeasuredDate),0), minSize
Your code isnt working because you are only ever checking the size of the current record. You need to compare with all records matching the current client in order to meet your specs. You are also never actually converting the size, most likely because of the first problem.
One quick note about my solution: I took the specs literally. If a client has any record where it is under 1024 it is in MB. So for example if the a client has 2 records in june with size 1000 and 1050, then it will be in MB even though every monthly average for the client is over 1024. If that's not what you want, let me know and I'll see about doing a solution the other way.
July 22, 2010 at 10:27 am
No, I don't want to make changes to the table... maybe create a temp table to display the data.
The issue that I'm running into is that if the clients data is with in a year changes from MB to GB, when the data is displayed in a chart, it looks like, for example, the size went from 1020 to 1.2 because 1228.8 MB has been converted to 1.2GB.
If a client has has a product size below 1024 or above 1024, the chart looks fine.
July 22, 2010 at 10:36 am
Solution above. I was editing while you were posting.
July 22, 2010 at 11:16 am
This works perfectly fine... Thank you so much for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply