November 12, 2013 at 3:33 am
Hi,
I'm working with an imported excel sheet, it's a parking rota.
table is defined as fields F1 FLOAT, [Parking Rota] NVARCHAR(255), F3 FLOAT, F4 NVARCHAR(255).... F255 NVARCHAR(255)
CREATE TABLE tblParking --f92 through f248 contain no data in excel so are guessed as being floats in sql
(
[F1] [float] NULL,
[Parking Rota] [nvarchar](255) NULL,
[F3] [float] NULL,
[F4] [nvarchar](255) NULL,
[F5] [nvarchar](255) NULL,
[F6] [nvarchar](255) NULL,
[F7] [nvarchar](255) NULL,
[F8] [nvarchar](255) NULL,
[F9] [nvarchar](255) NULL,
[F10] [nvarchar](255) NULL,
[F11] [nvarchar](255) NULL,
[F12] [nvarchar](255) NULL,
[F13] [nvarchar](255) NULL,
[F14] [nvarchar](255) NULL,
[F15] [nvarchar](255) NULL,
[F16] [nvarchar](255) NULL,
[F17] [nvarchar](255) NULL,
[F18] [nvarchar](255) NULL,
[F19] [nvarchar](255) NULL,
[F20] [nvarchar](255) NULL,
[F21] [nvarchar](255) NULL,
[F22] [nvarchar](255) NULL,
[F23] [nvarchar](255) NULL,
[F24] [nvarchar](255) NULL,
[F25] [nvarchar](255) NULL,
[F26] [nvarchar](255) NULL,
[F27] [nvarchar](255) NULL,
[F28] [nvarchar](255) NULL,
[F29] [nvarchar](255) NULL,
[F30] [nvarchar](255) NULL,
[F31] [nvarchar](255) NULL,
[F32] [nvarchar](255) NULL,
[F33] [nvarchar](255) NULL,
[F34] [nvarchar](255) NULL,
[F35] [nvarchar](255) NULL,
[F36] [nvarchar](255) NULL,
[F37] [nvarchar](255) NULL,
[F38] [nvarchar](255) NULL,
[F39] [nvarchar](255) NULL,
[F40] [nvarchar](255) NULL,
[F41] [nvarchar](255) NULL,
[F42] [nvarchar](255) NULL,
[F43] [nvarchar](255) NULL,
[F44] [nvarchar](255) NULL,
[F45] [nvarchar](255) NULL,
[F46] [nvarchar](255) NULL,
[F47] [nvarchar](255) NULL,
[F48] [nvarchar](255) NULL,
[F49] [nvarchar](255) NULL,
[F50] [nvarchar](255) NULL,
[F51] [nvarchar](255) NULL,
[F52] [nvarchar](255) NULL,
[F53] [nvarchar](255) NULL,
[F54] [nvarchar](255) NULL,
[F55] [nvarchar](255) NULL,
[F56] [nvarchar](255) NULL,
[F57] [nvarchar](255) NULL,
[F58] [nvarchar](255) NULL,
[F59] [nvarchar](255) NULL,
[F60] [nvarchar](255) NULL,
[F61] [nvarchar](255) NULL,
[F62] [nvarchar](255) NULL,
[F63] [nvarchar](255) NULL,
[F64] [nvarchar](255) NULL,
[F65] [nvarchar](255) NULL,
[F66] [nvarchar](255) NULL,
[F67] [nvarchar](255) NULL,
[F68] [nvarchar](255) NULL,
[F69] [nvarchar](255) NULL,
[F70] [nvarchar](255) NULL,
[F71] [nvarchar](255) NULL,
[F72] [nvarchar](255) NULL,
[F73] [nvarchar](255) NULL,
[F74] [nvarchar](255) NULL,
[F75] [nvarchar](255) NULL,
[F76] [nvarchar](255) NULL,
[F77] [nvarchar](255) NULL,
[F78] [nvarchar](255) NULL,
[F79] [nvarchar](255) NULL,
[F80] [nvarchar](255) NULL,
[F81] [nvarchar](255) NULL,
[F82] [nvarchar](255) NULL,
[F83] [nvarchar](255) NULL,
[F84] [nvarchar](255) NULL,
[F85] [nvarchar](255) NULL,
[F86] [nvarchar](255) NULL,
[F87] [nvarchar](255) NULL,
[F88] [nvarchar](255) NULL,
[F89] [nvarchar](255) NULL,
[F90] [nvarchar](255) NULL,
[F91] [nvarchar](255) NULL,
[F92] [float] NULL,
[F93] [float] NULL,
[F94] [float] NULL,
[F95] [float] NULL,
[F96] [float] NULL,
[F97] [float] NULL,
[F98] [float] NULL,
[F99] [float] NULL,
[F100] [float] NULL,
[F101] [float] NULL,
[F102] [float] NULL,
[F103] [float] NULL,
[F104] [float] NULL,
[F105] [float] NULL,
[F106] [float] NULL,
[F107] [float] NULL,
[F108] [float] NULL,
[F109] [float] NULL,
[F110] [float] NULL,
[F111] [float] NULL,
[F112] [float] NULL,
[F113] [float] NULL,
[F114] [float] NULL,
[F115] [float] NULL,
[F116] [float] NULL,
[F117] [float] NULL,
[F118] [float] NULL,
[F119] [float] NULL,
[F120] [float] NULL,
[F121] [float] NULL,
[F122] [float] NULL,
[F123] [float] NULL,
[F124] [float] NULL,
[F125] [float] NULL,
[F126] [float] NULL,
[F127] [float] NULL,
[F128] [float] NULL,
[F129] [float] NULL,
[F130] [float] NULL,
[F131] [float] NULL,
[F132] [float] NULL,
[F133] [float] NULL,
[F134] [float] NULL,
[F135] [float] NULL,
[F136] [float] NULL,
[F137] [float] NULL,
[F138] [float] NULL,
[F139] [float] NULL,
[F140] [float] NULL,
[F141] [float] NULL,
[F142] [float] NULL,
[F143] [float] NULL,
[F144] [float] NULL,
[F145] [float] NULL,
[F146] [float] NULL,
[F147] [float] NULL,
[F148] [float] NULL,
[F149] [float] NULL,
[F150] [float] NULL,
[F151] [float] NULL,
[F152] [float] NULL,
[F153] [float] NULL,
[F154] [float] NULL,
[F155] [float] NULL,
[F156] [float] NULL,
[F157] [float] NULL,
[F158] [float] NULL,
[F159] [float] NULL,
[F160] [float] NULL,
[F161] [float] NULL,
[F162] [float] NULL,
[F163] [float] NULL,
[F164] [float] NULL,
[F165] [float] NULL,
[F166] [float] NULL,
[F167] [float] NULL,
[F168] [float] NULL,
[F169] [float] NULL,
[F170] [float] NULL,
[F171] [float] NULL,
[F172] [float] NULL,
[F173] [float] NULL,
[F174] [float] NULL,
[F175] [float] NULL,
[F176] [float] NULL,
[F177] [float] NULL,
[F178] [float] NULL,
[F179] [float] NULL,
[F180] [float] NULL,
[F181] [float] NULL,
[F182] [float] NULL,
[F183] [float] NULL,
[F184] [float] NULL,
[F185] [float] NULL,
[F186] [float] NULL,
[F187] [float] NULL,
[F188] [float] NULL,
[F189] [float] NULL,
[F190] [float] NULL,
[F191] [float] NULL,
[F192] [float] NULL,
[F193] [float] NULL,
[F194] [float] NULL,
[F195] [float] NULL,
[F196] [float] NULL,
[F197] [float] NULL,
[F198] [float] NULL,
[F199] [float] NULL,
[F200] [float] NULL,
[F201] [float] NULL,
[F202] [float] NULL,
[F203] [float] NULL,
[F204] [float] NULL,
[F205] [float] NULL,
[F206] [float] NULL,
[F207] [float] NULL,
[F208] [float] NULL,
[F209] [float] NULL,
[F210] [float] NULL,
[F211] [float] NULL,
[F212] [float] NULL,
[F213] [float] NULL,
[F214] [float] NULL,
[F215] [float] NULL,
[F216] [float] NULL,
[F217] [float] NULL,
[F218] [float] NULL,
[F219] [float] NULL,
[F220] [float] NULL,
[F221] [float] NULL,
[F222] [float] NULL,
[F223] [float] NULL,
[F224] [float] NULL,
[F225] [float] NULL,
[F226] [float] NULL,
[F227] [float] NULL,
[F228] [float] NULL,
[F229] [float] NULL,
[F230] [float] NULL,
[F231] [float] NULL,
[F232] [float] NULL,
[F233] [float] NULL,
[F234] [float] NULL,
[F235] [float] NULL,
[F236] [float] NULL,
[F237] [float] NULL,
[F238] [float] NULL,
[F239] [float] NULL,
[F240] [float] NULL,
[F241] [float] NULL,
[F242] [float] NULL,
[F243] [float] NULL,
[F244] [float] NULL,
[F245] [float] NULL,
[F246] [float] NULL,
[F247] [float] NULL,
[F248] [float] NULL,
[F249] [nvarchar](255) NULL,
[F250] [nvarchar](255) NULL,
[F251] [nvarchar](255) NULL,
[F252] [nvarchar](255) NULL,
[F253] [nvarchar](255) NULL,
[F254] [nvarchar](255) NULL,
[F255] [nvarchar](255) NULL
)
The data is dreadful. Where the column [Parking Rota] = 'Name', fields F4 though F91 (can go up to F255 if there are enough employees) contain the names of employees who are allocated parking.
Let's say for example my name is in field F33.
INSERT INTO tblParking ([Parking Rota], F33) VALUES ('Name','Ben Ward')
On rows where the field [Parking Rota] contains a date, field F33 will contain the parking space number that I have been allocated for that day (or the string 'No' if I have not been allocated a space)
INSERT INTO tblParking ([Parking Rota], F33) VALUES ('20-Nov-2013','243')
INSERT INTO tblParking ([Parking Rota], F33) VALUES ('21-Nov-2013','No')
I want to write a query/stored procedure that takes a name string as an input and a date as an input and returns the space number or 'No'
This spreadsheet is produced monthly so I cannot guarantee that my name will always be in column F33.
What (I think) I need to do is return the column name where my name exists in that column on the row where [Parking Rota] = 'Name'
Is this possible? Is there a better way of acheiving my goal?
If someone could please point me in the right direction for this...
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
November 12, 2013 at 6:01 pm
Messy indeed!
What I'd try to do second (first I'd try to get the format of the spreadsheet changed into a more homogeneous table). is to read the row of names and then un-pivot the columns containing the names. See the first article link in my signature for a way to do this. Along with each actual name, you should include a second (un-pivoted) column with the Excel column name.
The second step would be to match the name you want out of the un-pivoted table.
Finally, use dynamic SQL with the now-known Excel column name to extract the parking space number from the row of the appropriate date.
Matching on the dates will also be somewhat problematic as it appears they'll end up getting stored as VARCHARs. Hopefully whoever thought up this worksheet will at least format them consistently.
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
November 13, 2013 at 2:56 am
Thanks pal I'll have a read of your article and post how it goes.
Cheers.
Ben
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply