I’ve just remembered a very small yet challenging SQL statement brain teaser a friend of mine asked me long ago.
Though as simple as it might seem, I still couldn’t figure out how to solve it until this day. I just thought that some of you guys might have an answer to it.
Here is the question:
You have a table named “table1� that has only 2 fields: “name� and “phone�.
This table is populated with the following sample data
NamePhone
---------------------
John111111
Tom222222
Jack333333
John444444
Smith555555
----------------------
5 records
To get all the distinct names in this table, you would write the sql statement:
Select distinct name from table1
And the result would be:
Name
-------
John
Tom
Jack
Smith
-------
4 records
The question is: can you write a sql statement that can return all the distinct names along with their phone numbers.
This one has been stumping me for several days. I can run a query thatreturns several different items from several different manufacturers,each with a ranking score. Each manufacturer can have any number ofitems:Item_Name Manufacturer rankItem 1 Manu_A 82Item 2 Manu_A 65Item 3 Manu_A 41Item 4 Manu_B 32Item 5 Manu_C 21Item 6 Manu_B 19However, I would like the records to be ordered so that the highestranking item is shown first, then the next highest item from adifferent manufacturer is shown second, then the next highest item froma third manufacturer is show, etc.:Item 1 Manu_A 82Item 4 Manu_B 32Item 5 Manu_C 21Item 2 Manu_A 65Item 6 Manu_B 19Item 3 Manu_A 41Does anyone have any thoughts on how to order the results in thisfashion?thanks,Matt Weiner
I have a requirement (motivated by a SOX thing) that is just giving mefits. I know it should be easy and I'm probably overthinking it, but Ijust can seem to find the best way to get where I need to go.I have some payment projection data derived from a huge procedure thatI'm dumping into a temp table that looks like looks this:Key Pd Start End AnnualAmt MonthAmt DailyAmt6789 1 2005-06-01 2010-05-31 49,500.00 4,125.00 135.6164386789 2 2010-06-01 2015-05-31 54,450.00 4,537.50 149.1780826789 3 2015-06-01 2020-05-31 59,895.00 4,991.25 164.0958906789 4 2020-06-01 2024-05-31 65,884.50 5,490.38 180.505479(there are actually 6 levels of keys, but you get the idea)I need it to get into a reporting table looking like this:Key Rev Year ProjectedAmt6789 2005 29,021.926789 2006 49,500.006789 2007 49,500.006789 2008 49,500.006789 2009 49,500.006789 2010 20,478.086789 2010 31,924.116789 2011 54,450.006789 2012 54,450.006789 2013 54,450.006789 2014 54,450.006789 2015 22,525.886789 2015 35,117.406789 2016 59,895.006789 2017 59,895.006789 2018 59,895.006789 2019 59,895.006789 2020 24.779.10etc...I'm having a problem wrapping my head around how to get the rows in themiddle of each period.The other, probably minor and statistically insignificant, issue isproration on a leap year. If a proration occurs on a leap year and Ihave to calculate the proration based on a DATEDIFF and an Annual orMonthly Amount, I'm going to be a day over.Anybody have any tricks or ideas???Thanks so much for your help!Jody
I need to produce a result that has 1 column that is the concatenation of each COORD separated by a '.' by increasing LEVEL
EX: The result should look like this 1.1.1 1.2.1 1.2.2 1.2.3 1.2.4 1.2.5 1.3.1 1.3.2 1.3.3 1.3.4 1.3.5
Please note that the answer must be flexible enough to handle any number of levels and coordinates (although levels will most likely be less than 5 and coordinates less than 100)
the part I€™m having trouble with is the next part of my code where i would search the rows in tbltimes and find the row WHERE employeename = Lblname.text & TimeIn is not null & TimeOut IS null. Then INSERT Lbltime into the Timeout column of that existing row.
Any ideas as to the easiest way to accomplish this would be a great help, I€™m officially stumped atm.
I feel like I am having a total brain fart. I am trying to use a select * where statement to get all the records from my database that match a given setup and using a URL variable: Code:
SELECT * FROM dbo.TerraLogMain WHERE Volume = '#URL.VolumeID#'
Now I know for a fact that there are multiple records for Volume = Maps6 (for example) but I am only getting one record returned.
It also looks like the URL variable is getting passed okay: Code:
Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array? Any comments are greatly welcomed.
Hello,I am working on a Blog and a Documents systems.What I need is:1. Each blog can have various language versions.2. Each document can have various language versions.I have been thinking about this and I end up with two approaches:1. Use a structure where all tables depend on a localized table:BLOGS|---- BlogsLocalized|---- BlogsPosts|---- BlogsRatings|---- BlogsComments2. Use a structure where each table has a localized versionBLOGS|---- BlogsLocalized|---- BlogsPosts|---- BlogsPostsLocalized|---- BlogsComments|---- BlogsCommentsLocalized3. Create a simpler, without localization, in SQL and in my web sites have different versions for each language.The same approach is under thinking for DocumentsTables.Could someone give me some advice?I have been looking in internet but until no I couldn't find anything really useful.Thanks,Miguel
Any help is sincerely appreciated:I have data in a table that represents the following:Admin Visit Type Registration Date Discharge Date D 20050301 20050301D 20050301 20050301W 20050301 20050301E 20050301 20050301D 20050301 20050302W 20050301 20050303W 20050301 20050311D 20050301 20050301Patient Type is always and I for the records I want but there are also Patient Types = O which I don't care about..What I would like to do is accoumlate a counter on the number of Registrations per date as well as Discharges per date.There can be thousands of registrations per day as well as thousands of discharges per day. So lets say I want to pass a date parameter to accumulate the total registrations and discharges per day. I have beat my head against the desk for the last two days because I believe this is a simple query but I just cannot get the results I want - so any help is greatly appreciated. I have written the following sql but I do not get a sum of the total registrations and discharges and EXPR3 and Expr4 always equal each other which is not the case . For example on 20040301 I have 88 registrations and 17 discharges but I can't ever get the correct totals...I wrote the following in Query Analyzer - but it does not work and I have went around in circles and have tried so many things I am just frustrated.........Declare @Parm_Beg_Date as nvarchar(8)Set @Parm_Beg_Date = 20040313 SELECT Patient_Visit_Result_Master.PVR_Admin_Visit_Type,Patient_Visit_Result_Master.PVR_Patient_Type, Patient_Visit_Result_Master.PVR_Registration_Date,Patient_Visit_Result_Master.PVR_Discharge_Date, Count(Distinct(Patient_Visit_Result_Master.PVR_Registration_Date)) as Expr3,Count(Distinct(Patient_Visit_Result_Master.PVR_Discharge_Date)) as Expr4 FROM Patient_Visit_Result_Master INNER JOINPatient_Visit_Result_Master Patient_Visit_Result_Master_1 ON Patient_Visit_Result_Master.PVR_Hospital_ID = Patient_Visit_Result_Master_1.PVR_Hospital_ID AND @Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Registration_Date as nvarchar(8))or@Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Discharge_Date as nvarchar(8)) WHERE (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'E') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @Parm_Beg_Date) OR (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @Parm_Beg_Date) Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @Parm_Beg_Date) Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @Parm_Beg_Date) GROUP BY Patient_Visit_Result_Master.PVR_Admin_Visit_Type,Patient_Visit_Result_Master.PVR_Patient_Type, Patient_Visit_Result_Master.PVR_Registration_Date,Patient_Visit_Result_Master.PVR_Discharge_Date Order By Patient_Visit_Result_Master.PVR_Admin_Visit_TypeThanks in advance
The principal and the mirror server are both running SQL Server 2005 64-bit Enterprise Edition. Witness is running Workgroup Edition. We are running in high availability mode (SAFETY ON).
3 nights before something strange happened and 1 database failed over (out of the six being mirrored) for some unknown reason. The other 5 didn't. We failed that one back and all seemed ok over the weekend. We came in Monday and found that the main live OLTP database was showing as the Principal on BOTH servers (in SMO/Management Studio and also in sys.database_mirroring). This is the "split brain" scenario that the presence of the witness is supposed to prevent. Both databases were accessible with a USE statement - clearly not right.
I pondered what to do, eventually I decided to remove mirroring from this database. That was ok until suddenly a few minutes later we realised the (original) Principal was in recovery! uisers of course were kicked out/unable to connect. I tried to force recovery with RESTORE DATABASE dbname WITH RECOVERY but it complained users were connected!
I had to KILL the users then recovery proceeded and the database became available again. I forced the mirror offline to prevent accidental usage.
This is obviously a nasty situation where mirroriing - which is supposed to prevent downtime - actually caused it instead.
I intend to log a call with CSS but I wanted to warn other users if they encounter something similar - it has shaken my confidence in mirroring quite severely.
Ok... I know this is something that I've seen and probably done before....
I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.
The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.
For anyone who can help me resolve this minor data issue. I am trying to clean a phone number column so that all numbers are reflected in the same way. I have phone numbers in my column in this this format:
(123)123-1234 123-123-1234 (123) 123-1234
How can I clean this column so that the numbers are reflected in this way: 1231231234
I'm having brain freez right now. Help me please!!! Thanks in Advance
I need to display only the char having start with 'ACCT -AMOUNT',Now problem is that some records having the lower case character like 'acct amount'. But i want to display only the upper case char start with 'ACCT-AMOUNT'.
I have to used the 'like ' statement but it is showing all the row inculding the lower case also.
Hi,Probable there is a simple solution for this, hopefully someone candirect me in the right direction.I have a table with a persons firstname, lastname, birthdate andaddress. However, I want to select only one person per address, namelythe eldest of all persons living on the same address.Can anyone provide me a solution?Thanks in advance.Duncan
HelloI have a case where Partners are some kind of Super-Users and arestored in a SQL Server database. Best is IMO to put both in the sametable:table Customers:CustomerID[pr.key][blabla]PartnerIDBut of course I have to reference the partnerid from another table andI want SQL Server to maintain the integrity rules. I could splitCustomers en Partners into different tables, but that would not bewise i think.Or I could just reference the CustomerID from the other table and-know- that we are talking about a partner, but in that case it itpossible to reference a customer that is not a partner, and i want toavoid that.Any ideas?Freek Versteijn
I have a report that groups by vendor and then by invoice within a vendor.
I want to create a report totals that contains
Number of vendors=2 Number of invoices=3 Total Inv Amounts=600.00 Total Item Amounts=475.00
How? Number of vendors is =count(distinct vendors) ?? Cant do that for # Invoices because of possible duplicate invoices used by different vendors. How do i get the total invamt? Each occurrence of the invamt fld is in a list2 as =first(invamt). I really need the sum of each first(invamt).
I currently use JET 4.0 as my database in a small app I distribute. SQL Server Express is way way too big to distribute. I am looking to move to a better database.
Is there a version of SQL that gives simple database CRUD support, but is super small to distribute? I am also considering Firebird as it's a full database and only 3MB on the client as an embedded tool.
i have table cosists of 3 columns i need to select multiple rows from the table example : i need to select rows with id=(1,5,9,7,11,15,20,23,42,65) in one select statement can any body answer me? and i will give him a( )
I'm just about to launch a forum, and right now its built in ASP classic with an Access db. I origianlly used Access because most hosts charge extra for MS-SQL server. Recently I switched to Jodohost who offers Access, MS-SQL and MySql for at no additional cost. So now that I have the option, I would like to pick the best solution before I launch.
* My questions are...
- What is the best db solution to go with for a currently small forum?
- How problematic do you think a data migration would be in the future if I stayed with Access for now and upgraded to MS-SQL with a full forum?
- Is it just smarter to go with MS-SQL now, with an empty forum, regardless of any preformance issues because potential migration problems are a greater risk?
- At what point does the speed of MS-SQL at high volumes over come the potential lags in accessing MS-SQL if it is hosted on a different machine from the one hosting the webpages?
* And please keep in mind...
- I have no db training. I can muddle through Access well enough, but administrating MS-SQL I think might be another story
- This fourm will start off very small, but could grow to be quite large
- I may not stay with jodohost, and would therefore likely have to pay more for MS-SQL (which I woudl rather not do)
Hello, could someone pls help me with this table design.
I have a project table and a code table. The code table has things like priorities (High, Medium, Low).
Now, I want all projects to be able to use these 'global' codes as well as define their own. So, they could define their own priority code 'Critical', that only their project can see.
I have listed a view below and a portion of the result set that is returned when I run the code in Query Analyzer. This is part of a timesheet application that logs hours per SCHLSTUID per SECTIONID per week. This returns the SCHLSTUID(user's ID), SECTIONID, Date that the week starts, the first date that time was logged. The user could be in several SECTIONID's for the same week. I need to modify this so that it returns the date that the first time was logged for any of the SECTIONID's per week. I know that this is probably something simple that I'm overlooking but I just can't get it to work correctly.Example: SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120This would need to return 2005-09-18 20:59:21.120------------------------------------------------------------------------------------------------------601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427This would need to return either 2005-10-02 20:59:37.427------------------------------------------------------------------------------------------------------601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823This would need to return 2005-10-09 20:59:37.823----------------------------------------------------------------------------------------------------------------------------------------------------CREATE VIEW dbo.vExportStartWeekASSELECT TOP 100 PERCENT schlstuid, sectionid, ATTSTARTDT, MIN(TimesheetDate) AS FirstTimeEnteredOnFROM (SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuemns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuehrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frimns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frihrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Satmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Sathrs AS TimeEntered FROM TimeSheetDailyAttendance) TimesheetDatesWHERE (TimeEntered <> 0)GROUP BY schlstuid, sectionid, ATTSTARTDTORDER BY schlstuid----------------------------------------------------------------------------------------------------------------------------------------------------This is a portion of what is returned:SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120601868445 EN4AR001 2005-09-25 20:59:36.670 2005-09-25 20:59:36.670601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823----------------------------------------------------------------------------------------------------------------------------------------------------Thank you for any help that you can give me.Scott
when i insert an empty date from an asp page, sql 7.0 generates a default value 1/1/1900. This is normal. However, I need to know how do I turn that feature off so I would not generate the default value.
Currently, my front in application uses asp--vb scripts. Please help.
I tried assigning a null value to my variant, but sql still generate that default date/time
Hello, I'd like to confirm something regarding SQL Server account permissions. Is the NT domain admin also a member of the sysadmin role on the local server running SQL Server 7.0 on an NT 4.0 platform? Thank you
We have a large and active MSSQL 2000 database. Recently, after a rebuild of the server, we had a problem with the SQL service SQLSERVERAGENT. The service could not start as the service account lost local permission to the registry. During this time, all of the data being sent to the database from our application accumulated into the database .ldf file. By the time we were able to get the service restarted, our .ldf file was approx. 28 Gigs. When the service restarded, the .ldf file shrunk down to regular size,about 40 megs, and the .trx tlog file grew up to 28 gigs for that specific period (new file every hour).
The problem is, the database file (database.mdf) stayed about the same as it was before the service was restarted. When the .ldf transfered to the .trn none of the 28 gigs of data got stored in the database. What does this mean? Perhaps with the service stopped the application using the db saw problems and did not commit the data making it all useless? Or is it possible that the data in the .trn log just needs to be forced to commit to the .mdf???
Is there any way to verify the data in the 28 gig .trn file and figure out if we should get it stored to the database? If yes, how would we go about verifying it, and after that how would we force it to commit to the .mdf file? Am I on the right track here or is it not as I see it??
i have performed all the steps for the replication there are two problems i encounytring (i) how to check whether the replication is working or not (ii) can replication on the subscribing server automatically create the table which publishing server is publshing (iii)please tell me someother forums for sql server waiting for reply ashish bhatnagar
Hello All, I have a database and I am trying to run a query against it, it returns the data fine, I just want it displayed differently. Here is the example; I am a novice at SQL I dabble here and there so any help would be great!
Base Code Select LOA.Account0 as 'Account Name' From local_accounts LOA Where LOA.Account0 like '%Microsoft%' Group by LOA.ACCOUNT0
Account Name Win32_UserAccount.Domain='Microsoft',Name='Jason'
I would like the data to be displayed differently in my query. I would like just the domain user name to appear in the row.
Account Name MicrosoftJason
Does anyone have example code of something like this.
