June 5, 2015 at 3:38 pm
Hi every one, I am new to SSC . I have a table that has NO.OF WORKING HOURS for 30 days. I am trying to convert all the values into INT as MINS to generate a SSRS report. Any suggestions would be helpful.
Note: There are some records with negative value (-4h15m)
Sample
NAME D1 D2 D3 ....D31
X1 9H 30M NULL 10M
X2 0M 1H 30M -4H45M
X3 20M -1H NULL
Thanks in advance...._/\_
June 7, 2015 at 7:06 pm
reader97 (6/5/2015)
Hi every one, I am new to SSC . I have a table that has NO.OF WORKING HOURS for 30 days. I am trying to convert all the values into INT as MINS to generate a SSRS report. Any suggestions would be helpful.Note: There are some records with negative value (-4h15m)
Sample
NAME D1 D2 D3 ....D31
X1 9H 30M NULL 10M
X2 0M 1H 30M -4H45M
X3 20M -1H NULL
Thanks in advance...._/\_
My heart goes out to you for having such an awkward data structure to have to deal with. Can you change it to something a little more appropriate for a relational database?
After all, we wouldn't want the Federal Relational Protection Agency [/url]coming after you.
If not:
WITH SampleData (NAME, D1, D2, D3, D31) AS
(
SELECT 'X1','9H','30M',NULL,'10M'
UNION ALL SELECT 'X2','0M','1H','30M','-4H45M'
UNION ALL SELECT 'X3','20M','-1H',NULL, NULL
)
SELECT NAME, TotalMin=SUM(a*b)
FROM
(
SELECT NAME, a=ROUND(POWER(10., SUM(LOG10(1.*ABS(a)))), 0)
,b=MIN(SIGN(MIN(a))) OVER (PARTITION BY NAME, DM)
FROM SampleData a
CROSS APPLY (VALUES (D1,1),(D2,2),(D3,3),(D31,31)) b (D, DM)
CROSS APPLY PatternSplitCM(CASE WHEN D NOT IN ('0M', '0H') THEN D END, '[-0-9]') c
CROSS APPLY
(
SELECT a=CASE
WHEN Item = 'H'
THEN '60'
WHEN Item = 'M'
THEN '1'
WHEN Item = '0'
THEN NULL
ELSE Item
END
) d
GROUP BY NAME, DM, (ItemNumber-1)/2
) a
GROUP BY NAME;
There are two links in my signature to help understand this:
- The PatternSplitCM function can be found in the link about splitting strings based on patterns
- The CROSS APPLY VALUES approach to UNPIVOT is explained in that signature link
And this blog post on logarithms may also help:
Stupid T-SQL Tricks – Part 1: Logarithms[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 8, 2015 at 2:15 am
dwain.c (6/7/2015)
reader97 (6/5/2015)
Hi every one, I am new to SSC . I have a table that has NO.OF WORKING HOURS for 30 days. I am trying to convert all the values into INT as MINS to generate a SSRS report. Any suggestions would be helpful.Note: There are some records with negative value (-4h15m)
Sample
NAME D1 D2 D3 ....D31
X1 9H 30M NULL 10M
X2 0M 1H 30M -4H45M
X3 20M -1H NULL
Thanks in advance...._/\_
My heart goes out to you for having such an awkward data structure to have to deal with. Can you change it to something a little more appropriate for a relational database?
After all, we wouldn't want the Federal Relational Protection Agency [/url]coming after you.
If not:
WITH SampleData (NAME, D1, D2, D3, D31) AS
(
SELECT 'X1','9H','30M',NULL,'10M'
UNION ALL SELECT 'X2','0M','1H','30M','-4H45M'
UNION ALL SELECT 'X3','20M','-1H',NULL, NULL
)
SELECT NAME, TotalMin=SUM(a*b)
FROM
(
SELECT NAME, a=ROUND(POWER(10., SUM(LOG10(1.*ABS(a)))), 0)
,b=MIN(SIGN(MIN(a))) OVER (PARTITION BY NAME, DM)
FROM SampleData a
CROSS APPLY (VALUES (D1,1),(D2,2),(D3,3),(D31,31)) b (D, DM)
CROSS APPLY PatternSplitCM(CASE WHEN D NOT IN ('0M', '0H') THEN D END, '[-0-9]') c
CROSS APPLY
(
SELECT a=CASE
WHEN Item = 'H'
THEN '60'
WHEN Item = 'M'
THEN '1'
WHEN Item = '0'
THEN NULL
ELSE Item
END
) d
GROUP BY NAME, DM, (ItemNumber-1)/2
) a
GROUP BY NAME;
There are two links in my signature to help understand this:
- The PatternSplitCM function can be found in the link about splitting strings based on patterns
- The CROSS APPLY VALUES approach to UNPIVOT is explained in that signature link
And this blog post on logarithms may also help:
Multiplication over rows, I'll have to remember that Dwain! Having a peruse of that article now +10
June 8, 2015 at 7:38 am
This is a slightly different take on Dwain's method. I'd guess they perform about the same.
WITH SampleData (NAME, D1, D2, D3, D31) AS
(
SELECT 'X1','9H','30M',NULL,'10M'
UNION ALL SELECT 'X2','0M','1H','30M','-4H45M'
UNION ALL SELECT 'X3','20M','-1H',NULL, NULL
)
SELECT
s.Name,
INTMinutes = SUM((([Hours]*60) + [Minutes]) * CASE WHEN neg = 1 THEN -1 ELSE 1 END)
FROM SampleData s
CROSS APPLY (VALUES (1, D1), (2, D2), (3, D3), (31, D31)) x ([Day], [Time])
CROSS APPLY (
SELECT
h = NULLIF(CHARINDEX('H',x.[Time]),0),
m = NULLIF(CHARINDEX('M',x.[Time]),0),
neg = CASE WHEN LEFT(x.[Time],1) = '-' THEN 1 ELSE 0 END
) y
CROSS APPLY (
SELECT
[Hours] = ISNULL(CAST(SUBSTRING([Time],neg+1,h-1-neg) AS INT),0),
[Minutes] = ISNULL(CAST(SUBSTRING([Time],ISNULL(h,-1)+1,m-ISNULL(h,0)-neg) AS INT),0)
) z
GROUP BY s.Name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 8, 2015 at 9:03 am
Thanks everyone. It worked. I have applied your logic as below and it worked fine. CASE WHEN (charindex('H',D1_VAL)>0) and (charindex('M',D1_VAL)<1)
THEN CONVERT(FLOAT, SUBSTRING(D1_VAL, 0, charindex('H',D1_VAL))) * 60
WHEN (charindex('H',D1_VAL)<1) and (charindex('M',D1_VAL)>0)
THEN CONVERT(FLOAT, REPLACE(SUBSTRING(D1_VAL, charindex('H',D1_VAL)+1, 2),'M',''))
WHEN (charindex('H',D1_VAL)>0) and (charindex('M',DAY1_VAL)>0)
THEN CONVERT(FLOAT, SUBSTRING(DAY1_VAL, 0, charindex('H',D1_VAL))) * 60 + CONVERT(FLOAT, REPLACE(SUBSTRING(D1_VAL, charindex('H',D1_VAL)+1, 2),'M',''))
END as D1
Thanks again..
June 8, 2015 at 6:21 pm
ChrisM@Work (6/8/2015)
This is a slightly different take on Dwain's method.
Seems Dohsan gets the credit for that one. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 9, 2015 at 1:00 am
dwain.c (6/8/2015)
ChrisM@Work (6/8/2015)
This is a slightly different take on Dwain's method.Seems Dohsan gets the credit for that one. 🙂
hah I try my best Dwain!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply