May 23, 2007 at 11:02 am
Hallo all,
first sorry for my English its not so. I have an problem with an table.
In this table there are 6 Collumns and this data
ID,Name,Date ,Code,Start,Stop
1 ,Max ,20.05.2007,TA ,00:00,00:30
1 ,Max ,20.05.2007,TA ,00:30,00:45
1 ,Max ,20.05.2007,TA ,05:00,05:30
1 ,Max ,20.05.2007,TO ,12:00,12:30
The ID can have max 42 different Codes a Day.
What i need is to fill a Second Table or an Select Statement to show this:
ID,Name,Date,Code,1,2,3,4,5,6,7,8....94 (each number for an intervall of 15 minutes)
1 ,Max ,20.05.2007,TA,0,0,1,1,.......
1 ,Max ,20.05.2007,TO,0,0,0,0,1,1,1.....
And so on.
The table or select must show
the count 1 for the intervall per day and code per id.
I hope all understand my question and what i want.
Thanks for Help
Regards
Thomas
May 23, 2007 at 11:56 am
If you resut set:
1) is just for reporting purposes
2) will be no more than 65K lines
just use the excel functionality to connect to the SQL DB and crosstab the data.
Else read the How to PIVOT help. There are more than one solution available online on how to create SQL crosstab tables, even in this Forum.
May 23, 2007 at 12:02 pm
Hi thanks for answere,
1) it not only for reporting. It show me some Data i need for an realtime report.
2) no more then 65 lines?? But how i show it in an other way ??
I don't take it in Excel only on the Server to build asp or other views on it.
Thanks for the Tip of Pivot.
Greetings
Thomas
June 4, 2007 at 5:22 am
-- Prepare sample data
SET
DATEFORMAT DMY
DECLARE
@Sample TABLE (ID INT, [Name] VARCHAR(3), Date DATETIME, Code VARCHAR(2), Start DATETIME, [Stop] DATETIME)
INSERT
@Sample
SELECT
1, 'Max', '20.05.2007', 'TA', '00:00', '00:30' UNION ALL
SELECT
1, 'Max', '20.05.2007', 'TA', '00:30', '00:45' UNION ALL
SELECT
1, 'Max', '20.05.2007', 'TA', '05:00', '05:30' UNION ALL
SELECT
1, 'Max', '20.05.2007', 'TO', '12:00', '12:30'
-- Show the data
SELECT
ID,
[Name]
,
Date
,
Code
,
MAX(CASE WHEN Start <= 1 AND [Stop] >= 1 THEN 1 ELSE 0 END) AS '1',
MAX(CASE WHEN Start <= 2 AND [Stop] >= 2 THEN 1 ELSE 0 END) AS '2',
MAX(CASE WHEN Start <= 3 AND [Stop] >= 3 THEN 1 ELSE 0 END) AS '3',
MAX(CASE WHEN Start <= 4 AND [Stop] >= 4 THEN 1 ELSE 0 END) AS '4',
MAX(CASE WHEN Start <= 5 AND [Stop] >= 5 THEN 1 ELSE 0 END) AS '5',
MAX(CASE WHEN Start <= 6 AND [Stop] >= 6 THEN 1 ELSE 0 END) AS '6',
MAX(CASE WHEN Start <= 7 AND [Stop] >= 7 THEN 1 ELSE 0 END) AS '7',
MAX(CASE WHEN Start <= 8 AND [Stop] >= 8 THEN 1 ELSE 0 END) AS '8',
MAX(CASE WHEN Start <= 9 AND [Stop] >= 9 THEN 1 ELSE 0 END) AS '9',
MAX(CASE WHEN Start <= 10 AND [Stop] >= 10 THEN 1 ELSE 0 END) AS '10',
MAX(CASE WHEN Start <= 11 AND [Stop] >= 11 THEN 1 ELSE 0 END) AS '11',
MAX(CASE WHEN Start <= 12 AND [Stop] >= 12 THEN 1 ELSE 0 END) AS '12',
MAX(CASE WHEN Start <= 13 AND [Stop] >= 13 THEN 1 ELSE 0 END) AS '13',
MAX(CASE WHEN Start <= 14 AND [Stop] >= 14 THEN 1 ELSE 0 END) AS '14',
MAX(CASE WHEN Start <= 15 AND [Stop] >= 15 THEN 1 ELSE 0 END) AS '15',
MAX(CASE WHEN Start <= 16 AND [Stop] >= 16 THEN 1 ELSE 0 END) AS '16',
MAX(CASE WHEN Start <= 17 AND [Stop] >= 17 THEN 1 ELSE 0 END) AS '17',
MAX(CASE WHEN Start <= 18 AND [Stop] >= 18 THEN 1 ELSE 0 END) AS '18',
MAX(CASE WHEN Start <= 19 AND [Stop] >= 19 THEN 1 ELSE 0 END) AS '19',
MAX(CASE WHEN Start <= 20 AND [Stop] >= 20 THEN 1 ELSE 0 END) AS '20',
MAX(CASE WHEN Start <= 21 AND [Stop] >= 21 THEN 1 ELSE 0 END) AS '21',
MAX(CASE WHEN Start <= 22 AND [Stop] >= 22 THEN 1 ELSE 0 END) AS '22',
MAX(CASE WHEN Start <= 23 AND [Stop] >= 23 THEN 1 ELSE 0 END) AS '23',
MAX(CASE WHEN Start <= 24 AND [Stop] >= 24 THEN 1 ELSE 0 END) AS '24',
MAX(CASE WHEN Start <= 25 AND [Stop] >= 25 THEN 1 ELSE 0 END) AS '25',
MAX(CASE WHEN Start <= 26 AND [Stop] >= 26 THEN 1 ELSE 0 END) AS '26',
MAX(CASE WHEN Start <= 27 AND [Stop] >= 27 THEN 1 ELSE 0 END) AS '27',
MAX(CASE WHEN Start <= 28 AND [Stop] >= 28 THEN 1 ELSE 0 END) AS '28',
MAX(CASE WHEN Start <= 29 AND [Stop] >= 29 THEN 1 ELSE 0 END) AS '29',
MAX(CASE WHEN Start <= 30 AND [Stop] >= 30 THEN 1 ELSE 0 END) AS '30',
MAX(CASE WHEN Start <= 31 AND [Stop] >= 31 THEN 1 ELSE 0 END) AS '31',
MAX(CASE WHEN Start <= 32 AND [Stop] >= 32 THEN 1 ELSE 0 END) AS '32',
MAX(CASE WHEN Start <= 33 AND [Stop] >= 33 THEN 1 ELSE 0 END) AS '33',
MAX(CASE WHEN Start <= 34 AND [Stop] >= 34 THEN 1 ELSE 0 END) AS '34',
MAX(CASE WHEN Start <= 35 AND [Stop] >= 35 THEN 1 ELSE 0 END) AS '35',
MAX(CASE WHEN Start <= 36 AND [Stop] >= 36 THEN 1 ELSE 0 END) AS '36',
MAX(CASE WHEN Start <= 37 AND [Stop] >= 37 THEN 1 ELSE 0 END) AS '37',
MAX(CASE WHEN Start <= 38 AND [Stop] >= 38 THEN 1 ELSE 0 END) AS '38',
MAX(CASE WHEN Start <= 39 AND [Stop] >= 39 THEN 1 ELSE 0 END) AS '39',
MAX(CASE WHEN Start <= 40 AND [Stop] >= 40 THEN 1 ELSE 0 END) AS '40',
MAX(CASE WHEN Start <= 41 AND [Stop] >= 41 THEN 1 ELSE 0 END) AS '41',
MAX(CASE WHEN Start <= 42 AND [Stop] >= 42 THEN 1 ELSE 0 END) AS '42',
MAX(CASE WHEN Start <= 43 AND [Stop] >= 43 THEN 1 ELSE 0 END) AS '43',
MAX(CASE WHEN Start <= 44 AND [Stop] >= 44 THEN 1 ELSE 0 END) AS '44',
MAX(CASE WHEN Start <= 45 AND [Stop] >= 45 THEN 1 ELSE 0 END) AS '45',
MAX(CASE WHEN Start <= 46 AND [Stop] >= 46 THEN 1 ELSE 0 END) AS '46',
MAX(CASE WHEN Start <= 47 AND [Stop] >= 47 THEN 1 ELSE 0 END) AS '47',
MAX(CASE WHEN Start <= 48 AND [Stop] >= 48 THEN 1 ELSE 0 END) AS '48',
MAX(CASE WHEN Start <= 49 AND [Stop] >= 49 THEN 1 ELSE 0 END) AS '49',
MAX(CASE WHEN Start <= 50 AND [Stop] >= 50 THEN 1 ELSE 0 END) AS '50',
MAX(CASE WHEN Start <= 51 AND [Stop] >= 51 THEN 1 ELSE 0 END) AS '51',
MAX(CASE WHEN Start <= 52 AND [Stop] >= 52 THEN 1 ELSE 0 END) AS '52',
MAX(CASE WHEN Start <= 53 AND [Stop] >= 53 THEN 1 ELSE 0 END) AS '53',
MAX(CASE WHEN Start <= 54 AND [Stop] >= 54 THEN 1 ELSE 0 END) AS '54',
MAX(CASE WHEN Start <= 55 AND [Stop] >= 55 THEN 1 ELSE 0 END) AS '55',
MAX(CASE WHEN Start <= 56 AND [Stop] >= 56 THEN 1 ELSE 0 END) AS '56',
MAX(CASE WHEN Start <= 57 AND [Stop] >= 57 THEN 1 ELSE 0 END) AS '57',
MAX(CASE WHEN Start <= 58 AND [Stop] >= 58 THEN 1 ELSE 0 END) AS '58',
MAX(CASE WHEN Start <= 59 AND [Stop] >= 59 THEN 1 ELSE 0 END) AS '59',
MAX(CASE WHEN Start <= 60 AND [Stop] >= 60 THEN 1 ELSE 0 END) AS '60',
MAX(CASE WHEN Start <= 61 AND [Stop] >= 61 THEN 1 ELSE 0 END) AS '61',
MAX(CASE WHEN Start <= 62 AND [Stop] >= 62 THEN 1 ELSE 0 END) AS '62',
MAX(CASE WHEN Start <= 63 AND [Stop] >= 63 THEN 1 ELSE 0 END) AS '63',
MAX(CASE WHEN Start <= 64 AND [Stop] >= 64 THEN 1 ELSE 0 END) AS '64',
MAX(CASE WHEN Start <= 65 AND [Stop] >= 65 THEN 1 ELSE 0 END) AS '65',
MAX(CASE WHEN Start <= 66 AND [Stop] >= 66 THEN 1 ELSE 0 END) AS '66',
MAX(CASE WHEN Start <= 67 AND [Stop] >= 67 THEN 1 ELSE 0 END) AS '67',
MAX(CASE WHEN Start <= 68 AND [Stop] >= 68 THEN 1 ELSE 0 END) AS '68',
MAX(CASE WHEN Start <= 69 AND [Stop] >= 69 THEN 1 ELSE 0 END) AS '69',
MAX(CASE WHEN Start <= 70 AND [Stop] >= 70 THEN 1 ELSE 0 END) AS '70',
MAX(CASE WHEN Start <= 71 AND [Stop] >= 71 THEN 1 ELSE 0 END) AS '71',
MAX(CASE WHEN Start <= 72 AND [Stop] >= 72 THEN 1 ELSE 0 END) AS '72',
MAX(CASE WHEN Start <= 73 AND [Stop] >= 73 THEN 1 ELSE 0 END) AS '73',
MAX(CASE WHEN Start <= 74 AND [Stop] >= 74 THEN 1 ELSE 0 END) AS '74',
MAX(CASE WHEN Start <= 75 AND [Stop] >= 75 THEN 1 ELSE 0 END) AS '75',
MAX(CASE WHEN Start <= 76 AND [Stop] >= 76 THEN 1 ELSE 0 END) AS '76',
MAX(CASE WHEN Start <= 77 AND [Stop] >= 77 THEN 1 ELSE 0 END) AS '77',
MAX(CASE WHEN Start <= 78 AND [Stop] >= 78 THEN 1 ELSE 0 END) AS '78',
MAX(CASE WHEN Start <= 79 AND [Stop] >= 79 THEN 1 ELSE 0 END) AS '79',
MAX(CASE WHEN Start <= 80 AND [Stop] >= 80 THEN 1 ELSE 0 END) AS '80',
MAX(CASE WHEN Start <= 81 AND [Stop] >= 81 THEN 1 ELSE 0 END) AS '81',
MAX(CASE WHEN Start <= 82 AND [Stop] >= 82 THEN 1 ELSE 0 END) AS '82',
MAX(CASE WHEN Start <= 83 AND [Stop] >= 83 THEN 1 ELSE 0 END) AS '83',
MAX(CASE WHEN Start <= 84 AND [Stop] >= 84 THEN 1 ELSE 0 END) AS '84',
MAX(CASE WHEN Start <= 85 AND [Stop] >= 85 THEN 1 ELSE 0 END) AS '85',
MAX(CASE WHEN Start <= 86 AND [Stop] >= 86 THEN 1 ELSE 0 END) AS '86',
MAX(CASE WHEN Start <= 87 AND [Stop] >= 87 THEN 1 ELSE 0 END) AS '87',
MAX(CASE WHEN Start <= 88 AND [Stop] >= 88 THEN 1 ELSE 0 END) AS '88',
MAX(CASE WHEN Start <= 89 AND [Stop] >= 89 THEN 1 ELSE 0 END) AS '89',
MAX(CASE WHEN Start <= 90 AND [Stop] >= 90 THEN 1 ELSE 0 END) AS '90',
MAX(CASE WHEN Start <= 91 AND [Stop] >= 91 THEN 1 ELSE 0 END) AS '91',
MAX(CASE WHEN Start <= 92 AND [Stop] >= 92 THEN 1 ELSE 0 END) AS '92',
MAX(CASE WHEN Start <= 93 AND [Stop] >= 93 THEN 1 ELSE 0 END) AS '93',
MAX(CASE WHEN Start <= 94 AND [Stop] >= 94 THEN 1 ELSE 0 END) AS '94',
MAX(CASE WHEN Start <= 95 AND [Stop] >= 95 THEN 1 ELSE 0 END) AS '95',
MAX(CASE WHEN Start <= 96 AND [Stop] >= 96 THEN 1 ELSE 0 END) AS '96'
FROM (
SELECT ID,
[Name]
,
Date
,
Code
,
CASE
WHEN Start < [Stop] THEN 1 + DATEDIFF(MINUTE, 0, Start) / 15
ELSE 1 + DATEDIFF(MINUTE, 0, [Stop]) / 15
END AS Start,
CASE
WHEN Start < [Stop] THEN 1 + DATEDIFF(MINUTE, 0, [Stop]) / 15
ELSE 1 + DATEDIFF(MINUTE, 0, Start) / 15
END AS [Stop]
FROM @Sample
) AS d
GROUP
BY ID,
[Name]
,
Date
,
Code
ORDER
BY ID,
[Name]
,
Date
,
Code
N 56°04'39.16"
E 12°55'05.25"
June 4, 2007 at 6:02 am
Hallo Peter,
much Thanks for your Statement. It works PERFECT !!
Thanks & Greetings
Thomas
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply