Check For Data and Convert

  • 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

  • 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.

  • 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.

  • Solution above. I was editing while you were posting.

  • 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