Monday, April 8, 2013

[SQL Server] timeline in cross-tab??

[SQL Server] timeline in cross-tab??

timeline in cross-tab??

Posted: 08 Apr 2013 05:33 AM PDT

Hello experts,My problem is as follows. On one hand I have got the beginning and end date of a tournement, on the other hand I have the beginning and end dates of the several events and the name of the events within that tournement.The end result should be a sort of time schedule disguised as a cross tab. On the y-ax there are the names of the several events, on the x-ax there are the tournement dates (eg from 2012-07-27 to 2012-08-11). In the middle of the cross-tab we can see the timelines related to the several events.In the database we have a table Editions and a table Events (details below). Is this question an impossible question or the contrary?Reference data:-- first table #EditionsIf Object_Id('TempDB..#Editions', 'U') Is Not Null Drop Table #EditionsCreate Table #Editions(Edition_ID Int Primary Key Clustered,Edition_Year int,Edition_Start datetime,Edition_Finish datetime) Set Dateformat DMYInsert into #Editions (Edition_ID, Edition_Year, Edition_Start, Edition_Finish)Select '1', '2012', '27-07-2012', '11-08-2012' UNION ALLSelect '2', '2008', '07-08-2008', '23-08-2008'-- second table #EventsIf Object_Id('TempDB..#Events', 'U') Is Not Null Drop Table #EventsCreate Table #Events(Event_ID Int Primary Key Clustered,Event_Name nvarchar(10),Event_Year int,Event_Start datetime,Event_Finish datetime)Set Dateformat DMYInsert into #Events (Event_ID, Event_Name, Event_Year, Event_Start, Event_Finish)Select '1', 'AA', '2012', '04-08-2012','07-08-2012' UNION ALLSelect '2', 'BB', '2012', '07-08-2012','10-08-2012' UNION ALLSelect '3', 'CC', '2012', '04-08-2012','06-08-2012' UNION ALLSelect '4', 'DD', '2012', '31-07-2012','01-08-2012' UNION ALLSelect '5', 'EE', '2012', '27-07-2012','03-08-2012' UNION ALLSelect '6', 'AA', '2008', '17-08-2012','20-08-2012' UNION ALLSelect '7', 'BB', '2008', '12-08-2012','13-08-2012' UNION ALLSelect '8', 'CC', '2008', '20-08-2012','20-08-2012'To give you some idea of the meant end-result (unfortunately in Excel) the following table for the year 2012: 27-7 28-7 29-7 30-7 31-7 1-8 2-8 3-8 4-8 5-8 6-8 7-8 8-8 9-8 10-8 11-8AA X X X X BB X X X X CC X X X DD X X EE X X X X X X X X I left the year out in the top and the 'X' can be anything else, eg a black or colourful square.When the above is possible I want to end up with an in-database solution eg stored procedure or function. I don't know which form is the best.Is there anyone out there who can help me out?Thanks in advance,Robert.

Temp Table Column name or number of supplied values does not match table definition

Posted: 07 Apr 2013 08:02 PM PDT

Hi all, Starting to play around with PBM and found an example of using DBCC LOGINFO. When i copy the code, i get the error message[code="sql"]Msg 213, Level 16, State 7, Line 1Column name or number of supplied values does not match table definition.[/code] meaning im using the wrong column type, I found another example of putting dbcc loginfo into a temp table and it had different values for the cols. I tried that and again it failed with the same error. Im guessings its because im in the UK so using a different collation to the online examples? Is there a way to find out what col types are for dbcc results? or is it a case of keep trying different values till you get it to work?

synonyms for table column

Posted: 07 Apr 2013 08:32 PM PDT

Dear AllThe way we hide the tbale name from user by using the synonyms. Is there a way to hide the column name.RegardsKrishna1

Two basic MySQL questions

Posted: 07 Apr 2013 08:30 PM PDT

Due to my lack of expertise with MySQL I have two rather basic questions.I use this query to give me the results I need, but I have to adjust the bold figures manually:SELECT computer_ID, 100 - ( nr -1) * (100/[b]157[/b]), 100 - (nr_rpi - 1)*(100/[b]157[/b]), ... FROM xyz WHERE Version LIKE 6.0 ORDER BY nrThe problem is the number 157. Ideally this should read 'COUNT(*)-1' or 'MAX(nr)-1'. But due to the aggregate nature this only gives me a single row, not the complete list. What I need to do is change absolute rankings on different variables to a scale of 0 to 100. I have - in this example - rankings from 1 to 158 (1 being top) and I need to adjust them to a scale of 100 (100 being top) to 0.What do I need to change in this query?I need to generate the rankings used above and currently I use manual labor to create these, but obviously it is far from ideal.This is what I use to manually generate these ranking figures:First empty Benchmark_results. Then get rank for total time:Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)SELECT @rank:=@rank+1 AS Rank,@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,Ref_IDFROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS iWHERE 1 ORDER BY [b]total_time[/b], crpi, cgain ASC LIMIT 0, 5000Now update rank for total time:UPDATE Personal_data, Benchmark_resultsSET Personal_data.nr=Benchmark_results.RankCS6WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0Now empty Benchmark_results again and repeat for crpi:Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)SELECT @rank:=@rank+1 AS Rank,@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,Ref_IDFROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS iWHERE 1 ORDER BY [b]crpi[/b], total_time, cgain ASC LIMIT 0, 5000Now update rank for crpi only:UPDATE Personal_data, Benchmark_resultsSET Personal_data.nr_rpi=Benchmark_results.nr_rpiWHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0Now repeat for Disk_io:Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)SELECT @rank:=@rank+1 AS Rank,@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,Ref_IDFROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS iWHERE 1 ORDER BY [b]Diskio[/b], crpi, total_time, cgain ASC LIMIT 0, 5000And update rank for Diskio:UPDATE Personal_data, Benchmark_resultsSET Personal_data.nr_disk=Benchmark_results.nr_diskWHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0Repeat for dvd:Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)SELECT @rank:=@rank+1 AS Rank,@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,Ref_IDFROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS iWHERE 1 ORDER BY [b]dvd[/b], crpi, total_time, cgain ASC LIMIT 0, 5000And update rank for dvd:UPDATE Personal_data, Benchmark_resultsSET Personal_data.nr_mpeg=Benchmark_results.nr_dvdWHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0Repeat for h264:Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)SELECT @rank:=@rank+1 AS Rank,@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,Ref_IDFROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS iWHERE 1 ORDER BY [b]h264[/b], crpi, total_time, cgain ASC LIMIT 0, 5000And update rank for h264:UPDATE Personal_data, Benchmark_resultsSET Personal_data.nr_h264=Benchmark_results.nr_h264WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0Now clean up again:Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID, Total, RPI, Gain)SELECT @rank:=@rank+1 AS Rank,@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,Ref_ID, Diskio+dvd+h264+IFNULL(MPE_On,MPE_Off) As Total, FORMAT((50.893*Diskio+158.333*dvd+77.027*h264+950*IFNULL(MPE_On,MPE_O ff))/12138.0,4) As RPI, FORMAT(MPE_Off/IFNULL(MPE_On,MPE_Off),3) As GainFROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS iWHERE 1 ORDER BY total_time, crpi, cgain ASC LIMIT 0, 5000This of course is far from ideal, but the only way I figured out that works, but among you geniuses there must be someone who can help me optimize this workflow and I'm open to all suggestions.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog