I've got a website with dynamic content, each page (subject) got an ID. On every page there can be a number of links. These are either links to internal other pages on that website or external links.
For the internal links the only thing I need is the ID and Title of that page. Those can be found in the Tbl_subjects. As for external links I need ID, Title and URL which can be found in the Tbl_ext_links.
I've got a table named Tbl_linkboxes with:
- a Subject ID which means that this link belongs on this subject page.
- Link ID which is either an ID from Tbl_subjects or Tbl_ext_links
- External a boolean column to indicate if the Link ID refers to the Subject table or the External links table
There's basically 2 questions:
1) How to make this work? I've got a query below as feeble attempt
2) Should I really really really consider to use 2 columns for IDs and removing the External boolean. And simply setting one of those fields in the columns to >0 while the other is 0.
Okay, here's my attempt
PHP Code:
SELECT s.Sub_id, s.Link_id
(l.external IS FALSE, (SELECT Title FROM Tbl_subjects), (SELECT Title,URL FROM Tbl_ext_links)
FROM Tbl_subjects s
WHERE s.Sub_id = <some id>
Not sure if I should work with IIF here to make it work or something else. I'm almost tempted to kick the boolean column overboard and introduce a JOINT on both columns then, one for external link ids and other for internal page ids.
Amazing how long one can stare at a query and not being able to get it right
select * from linkboxes l left outer join subjects s on l.sub_link_id = s.sub_id left outer join external_links e on l.ext_link_id = e.ext_id where l.sub_id = subid
Objective:The primary table I loaded into MySql has 2.5 MM records: ID, Ticker,Date, Price; and all works well. My need is to write a QUERY to exportoutfile?) multiple text files. For example, I have 6 years worth ofdata, and need to generate 1 file per day that contains all the Tickersand Prices for that day. Furthermore, I need the text file name to bethe name of the date (e.g. April 4, 1998 with 1000 Tickers & Priceswould result in a file that was named "040498.txt" (either csv or tabdelimited).Is this possible? If so, can someone please help me in this effort...thealternative is not pretty.Thanks in advance![color=blue]>> Trevor[/color]*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
From my query: (Please note the Where clause values will look odd to you, however they are not broken or wrong (its how the system reads it (odd I know)). The only issue I have is the subquery.
/*Schedule Summary*/ SET NOCOUNT ON
--Patient Appointments
DECLARE @Today DATETIME DECLARE @Tomorrow DATETIME SET @Today = '10/29/2007' SET @Tomorrow = dateadd(d, 1, '10/29/2007')
SELECT Date=convert(datetime,convert(char(12),Start,1)), convert(datetime,aps.Start) AS ResourceStart, convert(datetime,aps.Stop) AS ApptStop, ApptTime = datediff(minute, aps.Start, aps.Stop), df.Listname AS Resource, Facility= f.ListName, d.Listname AS DoctorName, --'Available / No Appt' AS Type, (SELECT dbo.sfnGetAllocsForSlot(aps.ApptSlotId) FROM ApptSlot aps JOIN Schedule s ON aps.ScheduleId = s.ScheduleId)AS TYPE, 'Available' AS 'Patient Name', 1 as ApptKind
FROM ApptSlot aps JOIN Schedule s ON aps.ScheduleId = s.ScheduleId JOIN DoctorFacility df ON s.DoctorResourceId = df.DoctorFacilityId JOIN DoctorFacility f ON aps.FacilityId = f.DoctorFacilityId JOIN DoctorFacility d ON s.DoctorResourceId = d.DoctorFacilityId LEFT JOIN Appointments a ON aps.apptid = a.appointmentsID
WHERE --Filter on resource ( ('7' IS NOT NULL AND df.DoctorFacilityId IN (7)) OR ('7' IS NULL) ) AND ( (NULL IS NOT NULL AND aps.FacilityId IN (NULL)) OR (NULL IS NULL) ) AND (Start >= @Today OR @Today IS NULL) AND (Start < @Tomorrow OR @Tomorrow IS NULL) AND ApptId IS NULL AND APS.ListOrder <> -1
My client came back and wanted me to add in a filter for Transaction Date from my Query (see full query below):
WHERE pm.Source = 1 AND t.Amount <> 0 AND --Filter on date range ( (pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900') AND pm.DateOfEntry < DATEADD(d,1,ISNULL('09/04/2007','1/1/3000'))) )
The issue is how the following subquerys are calculating their values. I think I need to add in something now on if the pm.DateOfEntry is between Date1 and Date2. Would this be right and if so, can someone assist me on the syntax or at least give me a start on it ...?? It appears as if the subqueries are not respecting any date logic, only the id being passed into them.
Section of Code I need assistance with:
PatBalance = (SELECT SUM(pva.PatBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId), InsBalance = (SELECT SUM(pva.InsBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId), Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId), Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId), Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId)
/* Get the subset of visits for this report */ INSERT #Visit SELECT pv.PatientVisitId, pv.PatientProfileId FROM PatientVisit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitID = pva.PatientVisitID INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId WHERE pp.PatientProfileId = 462 AND pva.PatPayment <> 0 AND --Filter on date type and range ( ('1' = '1' AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))) OR ('1' = '2' AND pv.Entered >= ISNULL(NULL,'1/1/1900') AND pv.Entered < dateadd(d,1,ISNULL(NULL,'1/1/3000'))) ) AND --Filter on doctor ( (NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR (NULL IS NULL) ) AND --Filter on facility ( (NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR (NULL IS NULL) ) AND --Filter on company ( (NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR (NULL IS NULL) )
/* Get demographics for the patient */ INSERT #Ledger ( PatientProfileId, Type, PatientId, Birthdate, PatientName, PatientAddress1, PatientAddress2, PatientCity, PatientState, PatientZip, PatientPhone1, PatientPhone1Type, PatientPhone2, PatientPhone2Type, PatBalance, InsBalance, Charges, Payments, Adjustments )
SELECT DISTINCT pp.PatientProfileId, 1, pp.PatientId, pp.Birthdate, RTRIM(RTRIM(RTRIM(ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) + ' ' + pp.Last) + ' ' + ISNULL(pp.Suffix, '')) AS PatientName, pp.Address1, pp.Address2, pp.City, pp.State, pp.Zip, pp.Phone1, pp.Phone1Type, pp.Phone2, pp.Phone2Type, PatBalance = (SELECT SUM(pva.PatBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId), InsBalance = (SELECT SUM(pva.InsBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId), Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId), Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId) FROM PatientProfile pp INNER JOIN #Visit tv ON pp.PatientProfileId = tv.PatientProfileId /* Get visit information for the patient */ INSERT #Ledger (PatientProfileId, Type, PatientVisitId, VisitDateOfService, VisitDateOfEntry, DoctorId, DoctorName, FacilityId, FacilityName, CompanyId, CompanyName, TicketNumber ) SELECT pv.PatientProfileId, 2, pv.PatientVisitId, pv.Visit, pv.Entered, pv.DoctorId, d.ListName AS DoctorName, pv.FacilityId, f.ListName AS FacilityName, pv.CompanyId, c.ListName AS CompanyName, pv.TicketNumber FROM #Visit tv INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId /* Get diagnosis information for the patient's visits */ INSERT #Ledger ( PatientProfileId, Type, PatientVisitId, InternalCode, ExternalCode, Description ) SELECT pv.PatientProfileId, 3, pv.PatientVisitId, pvd.Code, pvd.ICD9Code, pvd.Description FROM #Visit tv INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId /* Get transaction information for the patient's visits */ INSERT #Ledger ( PatientProfileId, Type, PatientVisitId, PatientVisitProcsId, TransactionDateOfServiceFrom, TransactionDateOfEntry, Description, Payer, PatAmount, InsAmount, Action, Notes ) SELECT pv.PatientProfileId, 5, pv.PatientVisitId, NULL, ISNULL(pm.CheckDate, b.Entry), b.Entry, at.Description + CASE WHEN pm.CheckCardNumber IS NULL THEN '' ELSE ' - Check # ' + pm.CheckCardNumber + ' ' + CASE WHEN pm.CheckDate IS NULL THEN '' ELSE CONVERT(varchar(12), pm.CheckDate, 101) END END, pm.PayerName, CASE WHEN pm.Source = 1 THEN t.Amount END, CASE WHEN pm.Source = 2 THEN t.Amount END, t.Action, CASE WHEN ISNULL(t.ShowOnStatement, 0) <> 0 THEN t.Note ELSE NULL END FROM #Visit tv INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitId INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action = 'P' INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId AND ISNULL(pm.InsuranceTransfer, 0) = 0 INNER JOIN Batch b ON pm.BatchId = b.BatchId LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId WHERE pm.Source = 1 AND t.Amount <> 0 AND --Filter on date range ( (pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900') AND pm.DateOfEntry < DATEADD(d,1,ISNULL('06/30/2005','1/1/3000'))) )
SELECT tl.* FROM #Ledger tl ORDER BY tl.PatientProfileId, tl.PatientVisitId, tl.PatientVisitProcsId, tl.Type
I have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter". The Branch Parameter is a numeric field. I need (Branch = %) wildcard to select all the records. Can someone tell me the proper syntax for this? SELECT EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM dbo.vw_Compliance_Percentage_EmployeeWHERE (Branch = @branch)ORDER BY Branch I receive the message failed to convert parameter value from string to an int32
Hello everyone. I've attached a copy of my recently created stored procedure but now I want to expound on it by creating synatx within it that will allow me to create a 'weighting' type of scenario based on pre-determined weight/ranking classifications (example: a selection of skill '1' would grant the user 2 points toward their ranking, a selection of skill '2' might grant the user 4 pts., etc.) In the end, the users would be tallied and sorted based on the highest ranking (in pts) to the lowest. The business I'm in is that we develop a web site interface for recruiters and potential job seekers to post resumes, develop a career plan and rank their current work status against the open market. In short, does anyone out there know how I can implement a "ranking" type system into the syntax provided below? I've considered the CASE statement but was not clear on how it would work. Any suggestions would be great.
set nocount on select @jobcity = city, @jobposition = position_id, @jobrelocate = relocate_assist, @jobtravel = travel_id, @jobstate = state, @jobyears = position_yrs from t_job_order where job_id = @job_id
select @totcount = count(*) from t_job_vstat where job_id = @job_id
select @totcount = @totcount + 3
DECLARE Cand_Cursor CURSOR FOR
select candidate_id, key_skill_desc, strongest from t_cand_vstat, t_key_skill where t_cand_vstat.key_skill_id in (select key_skill_id from t_job_vstat where job_id = @job_id) and t_cand_vstat.key_skill_id = t_key_skill.key_skill_id order by candidate_id
CREATE TABLE #ReturnTemp ( candidateid uniqueidentifier NOT NULL, displayid int, city varchar(50), state varchar(2), Industry varchar(50), travel varchar(50), position varchar(50), hitcount smallint, tpercent numeric, ks1 varchar(50), ks2 varchar(50), ks3 varchar(50), ks4 varchar(50), ks5 varchar(50) )
OPEN Cand_Cursor
declare @candidate_id uniqueidentifier
FETCH NEXT FROM Cand_Cursor into @candidate_id, @key_skill_desc, @strongest select @holdid = @candidate_id
WHILE @@FETCH_STATUS = 0 BEGIN
if @candidate_id <> @holdid begin select @icount = @icount + 1 if @match = 1 update #ReturnTemp set hitcount = @icount, tpercent = (@icount/@totcount * 100), ks1 = @ks1, ks2 = @ks2, ks3 = @ks3, ks4 = @ks4, ks5 = @ks5 where candidateid = @holdid select @match = 1 select @ks1 = "" select @ks2 = "" select @ks3 = "" select @ks4 = "" select @ks5 = "" select @holdid = @candidate_id select @icount = 1 select @candrelocate = relocate, @candtravel = travel_id from t_cand_pref where candidate_id = @candidate_id select @candcity = city, @candstate = state, @displayid = display_id from t_candidate1 where candidate_id = @candidate_id select @candposition = position_id, @candyears = position_yrs, @candindustry = cat_sub_cat_id from t_cand_seek where candidate_id = @candidate_id if @candposition = @jobposition select @icount = @icount + 10 if @candyears = @jobyears select @icount = @icount + 8 if @candtravel = @jobtravel begin select @icount = @icount + 2 end
else if @jobtravel <> '91C858C8-4A46-4FD8-9B73-87FEE00F799E' begin if @candtravel = '91C858C8-4A46-4FD8-9B73-87FEE00F799E' begin select @match = 0 end else begin
select @icount = @icount + 1 end end
DECLARE City_Cursor CURSOR FOR
select distinct city, state from t_cand_pref_city_state C, t_city_state S where c.city_state = s.city_state and C.candidate_id = @candidate_id
OPEN City_Cursor
FETCH NEXT FROM City_Cursor into @prefcity, @prefstate
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM City_cursor into @prefcity, @prefstate select @citymatch = 0 if ((@prefcity = @jobcity) and (@prefstate = @jobstate)) begin --do nothing select @citymatch = 1 select @icount = @icount + 1 end
END
CLOSE City_Cursor
DEALLOCATE City_Cursor
if @citymatch = 0 select @match = 0
if @candindustry <> @jobindustry select @match = 0 if @strongest = 1 begin if @ks1 = "" select @ks1 = @key_skill_desc else if @ks2 = "" select @ks2 = @key_skill_desc else if @ks3 = "" select @ks3 = @key_skill_desc else if @ks4 = "" select @ks4 = @key_skill_desc else if @ks5 = "" select @ks5 = @key_skill_desc end if @match = 1 begin
select @candIndustrydesc = cat_sub_desc from t_cat_sub_cat where cat_sub_cat_id = @candIndustry select @candPositiondesc = position_desc from t_position where position_id = @candPosition select @candTraveldesc = travel_desc from t_travel where travel_id = @candtravel
INSERT INTO #ReturnTemp(Candidateid, displayid, city, state, Industry, travel, position, hitcount)
Hello, I am trying to update a table based on values that I have imported into a temporary table. Basically I have a list of lab codes (EMR_temp_labtest_configupdate) and each lab has a zseg code tied to it. The definitions for zseg code are in a separate table called (EMR_zseg_code_descriptions)
I need to update the lab_test_add_conf to add in each lab code that does not have any configuration information (not exists in lab_test_add_conf) based on the zsegcode.
For example a zsegcode (defined in the emr_zseg_code_descriptions table) is ZBL and the lab code 003277 fits into the zseg category according to the temp table. For each lab code that first into the ZBL category a row needs to be inserted for
I want to look at the data in the temp table and determine which category it is in and then insert into the lab_test_add_conf table a row for each lab test each zseg table value code that exists.
My Final Goal: lab_test_add_conf:
lab test code, valuecode 003277, PATRAC 003277, HERITG
I know I need to do an update statement but I am not sure how to set up the SET statement or if there is anythign that I need to take into consideration. Here is my code so far.....any thoughts?
select a.labtestcode from EMR_temp_labtest_configupdate a Where Not Exists (Select * From lab_test_add_conf b where a.labtestcode = b.labtest_key)
update table lab_test_add_conf
select a.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units, b.tablename,b.fieldname from EMR_temp_labtest_configupdate a join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
I am getting the following syntax error to my query below. This query is similar to many I currently use and I have not been able to track down the error. Can anyone spot the error?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'data'.
Select
e.account_number,
epi.Provider_Role = [1],
epi.Provider_Role = [2],
epi.Provider_Role = [3],
epi.Provider_Role = [4],
epi.Provider_Role = [5],
epi.Provider_Role = ,
epi.Provider_Role = [7],
epi.Provider_Role = ,
epi.Provider_Role = [9],
epi.Provider_Role = [10],
epi.Provider_Role = [11]
from (SELECT e.account_number,
row_number() over (partition by epi.PROVIDER_ROLE order by e.account_number asc) as rownum,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
CONVERT(int,pm.PatientAge),
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
pm.PrinOpPhys,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind? Stored ProcedureCREATE PROCEDURE InsertNearMiss @Branch Int, @Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@Branch) SET @Identity = SCOPE_IDENTITY() GO
I am trying to build a related article display. I have a SQLDataSource that I want to be able to select information (Category) from the table (Articles) based on a querystring (ID). SELECT [Category] FROM [Articles] WHERE ([ArticleID] = @ID) which for an example, lets say ID = 1, and it results as Category = Health.That is easy enough, but how would I then select all columns from the table WHERE Category = Result of first SELECT? SELECT * FROM [Articles] WHERE ([Category] = ??? Result of prior select) Can this be done in 1 select command, or would a store procedure need to be written? I am a little lost, sincr I am using a SQLDataSource, and it will be displayed with a Repeater. Thanks!
First off, here is my query:SELECT DeviationDist.DEVDN, DeviationDist.DEVDD, DEVDA, DEVCT, DEVST, DEVBG, DEVDV, DEVPS, DEVAT, DEVCN, DEVCF, DEVCP, DEVCEFROM DeviationDistINNER JOIN DeviationContact ON DeviationContact.DEVDN = DeviationDist.DEVDNWHERE DeviationDist.DEVDN = '200270'ORDER BY Deviationdist.DEVDN DESCI'm joining the deviationcontact table to the deviation dist table by DEVDN. This query works fine except when the DeviationContact table doesnt contain any records for the DEVDN that the DeviationDist table does contain. In my app, Deviationdist will always have an record for each DEVDN, but DeviationContact may not. I would like to still get the results back for what records exist in the DeviationDist table, even if DeviationContact has no associated records, but still show them if it does...
I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum( CASE WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and ( CASE WHEN a.order_id <> b.order_id THEN a.transaction_date ELSE b.delivery_date END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN a.amount END) earn_amount_rtp_curr,
I am trying to pull some "notes" from a sql database.....the notes thatare put into the database come via the web and the user is entering itfor a certain task. they are stored in their own table and field andget assigned and incremental ID #.I want to be able to pull up the latest entry to the task, not all ofthe notes just the latest one.. The entry does get a timestamp in thefield so I am thinking I might be able to look at that fieldsomehow.... Right now my query shows all notes / entries for the task.I am an intermediate sql query guy so I hopefully expained enough toget assistance.Let me know if you need to know more.
I am trying to get a running total. I need the query to reset the running total for each year/id. Below is the sample query. Any help would be greatly appreciated.
SELECT v.k1, v.date, v.groupByThis, v.c1, RunningTotal=SUM(a.c1) FROM ( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank FROM #valueset ) v
CROSS JOIN
( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank FROM #valueset ) a WHERE a.Rank <= v.Rank AND a.groupByThis = v.groupByThis GROUP BY v.k1, v.date, v.groupByThis, v.c1 ORDER BY v.groupByThis, v.date
I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.
Source table.
Address ID Workstation
Test-a 1 WS1
Test-b 2 WS1
Test-a 5 WS2
Test-d 3 WS2
Test-b 7 WS2
I am trying to write a query that will display this result into Excel.
Address
Duplicate
WS1
WS2
Test-a
Yes
1
5
Test-b
Yes
2
7
Test-d
No
Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation. I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.
Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.
I have two tables, USER and ROTATION. What I would like to display is the Maximum Start_Date and Maximum End_Date from the ROTATION Table along with the First_Name and Last_Name that is associated with that max entry.
When I just create a select statement that asks for the max value of the Start_Date and End_Date, I get the value I see. However when I try to add the First_Name and Last_Name to the mix, I get the Max Start and End Date Values of all the people in the User Table. I only want one result returned. I'm probably missing something very simple here. Any assistance would be appreciated.
The SQL code I am currently using:
Code SnippetSELECT [USER].FIRST_NAME, [USER].LAST_NAME, MAX(ROTATION.ONCALL_START_DATE) AS Expr1, MAX(ROTATION.ONCALL_END_DATE) AS Expr2 FROM ([USER] INNER JOIN ROTATION ON [USER].USER_ID = ROTATION.USER_ID) GROUP BY [USER].FIRST_NAME, [USER].LAST_NAME
Example of my desired result:
First_Name Last_Name OnCall_Start_Date OnCall_End_Date John Doe 8/10/08 8/17/08
John Doe should be the only result returned because he would have the highest OnCall_Start and OnCall_End in the ROTATION Table.
I am new to SQL and aftering reading my SQL For Dummies book, I still am unsure how to accomplish this task.
The table I need to query contains resident census information. There are multiple rows for each resident. I need to determine if the resident is still active, so I only need to read the last row for each resident to make this determination.
What method can I use to read only the last row for the resident. Here is what I tried previously, but it returns multiple rows for each resident.
select a.firm_id_code as FacAddOnNum, f.shortname as FacName, left(a.resident_code,6) as ResidentCode, (r.res_first_name + ' ' + r.res_last_name) as ResidentName, a.LastDate, a.cens_trans_type
from (select firm_id_code, resident_code, max(dt_cens_trans) as LastDate, cens_trans_type from arrescensus where firm_id_code = @FacId group by firm_id_code, resident_code, dt_cens_trans, cens_trans_type) as a join corp_info.dbo.facilityinfo as f on (a.firm_id_code = f.addonnum) join arresidents as r on ((r.firm_id_code = a.firm_id_code) and (r.resident_code = a.resident_code))
I'm attempting to write and update query. So far I have written the following: update vwDISTCITY_TAXCODE set tax_code='04' where DIST_CITY='04'AND year_id=2007 AND frozen_id=0 AND p_id=93549 AND total_taxes=isnull
The last part of the query "total_taxes=isnull" is where the problem lies. Essentially I want to say if all of the other things are true and there is a null value in the total_taxes column, then I I want to set the tax_code to '04'. However how would I phrase the last part correctly?
Good Day;I would appreciate assistance developing a query that I haven't beenable to develop without using a second table. I wish to count thenumber of records that are still open on the first of each month.Each record has an open date and a close date or the close date isnull i.e., the record is not yet closed. I've previously beaten thisby building a table, simply a list of the dates for the first of eachmonth for the next ten years or so, and then selecting values basedupon a date selected from that table. However I'd be happier if Icould do it without the second table. I'd be prepared to accept theMin(Date) for each month as being the first of the month.I've included some DDL statements to build and populate the table ifthat helps. Since the selection is rather small and all the opendates are very close together I think the result will be simply adecreasing count from the month the first record is opened till today.A pseudo code select statement might look likeSelect Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]FROM DetailTWhere DateReceived > DateOfInterest or DateReceived is Null andDateOpened < DateOfInterestGroup by Min(DateOpened)Order by Min(DateOpened)I hope I've explained it sufficiently well.CREATE TABLE [dbo].[DetailT] ([Autonum] [int] IDENTITY (1, 1) NOT NULL ,[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[DateOpened] [smalldatetime] NOT NULL ,[DateReceived] [smalldatetime] NULL ,)Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('C15788', '06/04/2005 9:35', 07/04/2005)Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('B16091', '06/04/2005 9:36', '07/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('B15001', '06/04/2005 9:51', '08/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('M18696', '06/04/2005 9:56', '06/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('C14969', '06/04/2005 10:05', '10/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('O10091', '06/04/2005 10:08', '12/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('D01197', '06/04/2005 10:13')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('H15001', '06/04/2005 10:15', '08/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('J15090', '06/04/2005 10:24', '08/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('J01202', '06/04/2005 10:31')Insert into DetailT (QDNumber, DateOpened)VALUES('G01193', '06/04/2005 10:32')Insert into DetailT (QDNumber, DateOpened)VALUES('K01164', '06/04/2005 10:35')Insert into DetailT (QDNumber, DateOpened)VALUES('K01162', '06/04/2005 10:48')Insert into DetailT (QDNumber, DateOpened)VALUES('F01124', '06/04/2005 10:59')Insert into DetailT (QDNumber, DateOpened)VALUES('H01147', '06/04/2005 11:01')Insert into DetailT (QDNumber, DateOpened)VALUES('S15068', '06/04/2005 11:10')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('E12322', '06/04/2005 11:32', '07/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('A12205', '06/04/2005 11:37', '06/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('D12259', '06/04/2005 11:40', '07/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('C03394', '06/04/2005 11:51')If you made it this far thank you for your patience. Any help would beappreciated.Thank you.Bill
I am trying to generate some datasets with some queries...With a given series information, it should return PART_NOs that has STD= 1 and a unique price at that particular 'START', and keeping the'TYPE' in consideration...DB examples below:Main DBIDPART_NOSERIESSTD1A-1A12A-2A13A-3A14D-1D15D-2D0Price DBIDPART_IDTYPESTARTPRICE501X100050511X1000040521Y100060531Y1000050542X100050552X1000040562Y100060572Y1000050582X100090etc.main.ID and Price.PART_ID are paired together.So in an example case, lets say I am querying for SERIES A, with TYPEX. A table should be outputted something likePART_NOA-1100050A-11000040A-3100090Note how it skipped printing A2 because the price is the same as A1.I'm really looking for the SQL code here... I can't get it to filter ondistinct price.SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICEFROM MAIN, PRICINGWHERE (MAIN.SERIES LIKE 'A')AND (MAIN.STD = '1')AND (PRICING.PRICE != '')AND (PRICING.TYPE = 'X')AND (MAIN.ID = PRICING.PART_ID)I've been trying to use GROUP BY and HAVING to get what I need but itdoesn't seem to fit the bill. I guess I'm not terribly clear on how Ican use the SQL DISTINCT command...? If I try and use it in my WHEREstatement it gives me syntax errors, from what I understand you canonly have distinct in the select statement? I'm not sure how tointegrate that into the query to suit my needs.Thanks for any help.
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum( CASE WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and ( CASE WHEN a.order_id <> b.order_id THEN a.transaction_date ELSE b.delivery_date END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN a.amount END) earn_amount_rtp_curr,
I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC
I got some help on here before with building my query. I thought this was working fine but it turns out when there are multiple records for a column type, it only grabs the first one. I need to get all records. Is there an alternative to MAX? I needed to structure it like this because I needed to return each row as a column and this was the way suggessted before. My query:SELECT TOP (100) PERCENT PRODUCT_NUMBER, PRODUCT_NAME, MAX(CASE WHEN ColumnName = 'Federal Specification Number' THEN TheValue ELSE NULL END) AS [Federal Specification Number]FROM (SELECT dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID, dbo.SHARED_FEATURE_VALUES.FEATURE_TEXT_VALUE AS TheValue, dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE AS ColumnName, dbo.PRODUCTS.PRODUCT_NUMBER, dbo.PRODUCTS.PRODUCT_NAME FROM dbo.PRODUCT_FEATURE_VALUES INNER JOINdbo.SHARED_FEATURE_TYPES ON dbo.PRODUCT_FEATURE_VALUES.FEATURE_TYPE_ID = dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE_ID INNER JOINdbo.SHARED_FEATURE_VALUES ON dbo.PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = dbo.SHARED_FEATURE_VALUES.FEATURE_VALUE_ID INNER JOINdbo.PRODUCTS ON dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID = dbo.PRODUCTS.PRODUCT_ID UNIONSELECT dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID, ISNULL(dbo.EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, dbo.EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE) AS TheValue, dbo.EXTENDED_ATTRIBUTES.COLUMN_NAME AS ColumnName, PRODUCTS_1.PRODUCT_NUMBER, PRODUCTS_1.PRODUCT_NAME FROM dbo.EXTENDED_ATTRIBUTE_VALUES INNER JOINdbo.EXTENDED_ATTRIBUTES ON dbo.EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = dbo.EXTENDED_ATTRIBUTES.EXT_ATT_ID INNER JOIN dbo.PRODUCTS AS PRODUCTS_1 ON dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID = PRODUCTS_1.PRODUCT_ID) AS t1 WHERE PRODUCT_NUMBER = '02083' GROUP BY PRODUCT_NUMBER, PRODUCT_NAME ORDER BY PRODUCT_NUMBER
This returns: Product_Number Product_Name Federal Specification Number 02083 Di-Electric Grease, 10.5 Wt Oz FDZ-CFR-21-178.3570 There is another record for Federal Specification Number I need to return as well. If I change to MIN, it gets the other record. Anyway I can get both?
I have a couple of tables that look like this (not excactly but closeenough):[Contact]id intfname varchar(50)lname varchar(50)[ContactPhoneNumber]id intnumber varchar(15)ext varchar(6)contact_id intpriority int (indicates primary, secondary... numbers)type int (indicates type of number: fax, cell, land line)I'm looking for a more optimized method of displaying this informationin this format:fname, primary business phoneUsing a derived column like this works, but seems to be slow with manyrecords, despite tuning indexes:SELECT c.fname AS [First Name],( SELECT TOP 1numberFROM ContactPhoneNumber cpnWHERE cpn.type = 1AND cpn.contact_id = c.idORDER BY cpn.priority) AS NumberFROM Contact cI can get the same results using a join, and it's a lot faster. But I'mnot sure how to select only the primary phone number this way...basically the first phone number whose priority is either NULL or 1.Any suggestions?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
I have two tables that are pretty standard I think. Table a has product descriptions and one of those fields is a price. I have a second table that contains fees based on the price. so table B looks like this.
min max fee 0 19.99 2.50 20.00 49.99 3.50 50.00 1000.00 5.50
the max ends up around a million just to be sure we cover all prices. my problem is this I need a very efficient query to poll all the values from A and the correct value from B. All the attempts I have made are not working. I also have to make sure this query is extremely efficient as it is executed several times a minute. If there is a better way in general to structure this I am all ears. I wanted to avoid placing the fees in the product table as the fees are updated often, but if its the only way to get this to work, then that is where I will go.
The View SQL below takes 1:50 minutes to execute. I am looking for a way to improve processing time and keep the view select statements as simple as possible.
One part of the view that could be restructured is the lookup for the current term (TERM_TBL - bolded). When I hard code the current term my execution time drops to about 13 seconds
There are three possible values for the current term; one for each of our three acedemic careers (UGRD, CONT, & EXED). Could this information be looked up once and then each enrollment selected based upon the appropriate academic career current term value? If so, how would it be restructured? Currently, the lookup is done for every enrollment record.
There are no common fields between the NAMES table and the TERMS_TBL.
SELECT DISTINCT T.STRM FROM TERM_TBL T WHERE T.INSTITUTION = B.INSTITUTION AND T.ACAD_CAREER = B.ACAD_CAREER AND T.TERM_BEGIN_DT <= GETDATE() AND T.TERM_END_DT >= GETDATE())
AND A.EMPLID NOT IN (
SELECT DISTINCT C.EMPLID FROM SRVC_IND_DATA C WHERE C.INSTITUTION = 'AAAAA' AND C.SRVC_IND_CD = 'DPL' )
AND A.NAME_TYPE = 'PRI'
AND A.EFFDT = (
SELECT MAX(D.EFFDT)
FROM NAMES D
WHERE D.EMPLID = A.EMPLID
AND D.NAME_TYPE = 'PRI'
AND D.EFFDT <= GETDATE() )
Any suggestions will be gladly accepted. Keep in mind that the new and much improved must be legal within the context of a view select statement.
This is a fairly detailed problem, so this will be a long post... I do appologize. I have been agonizing over this now for over a week and cannot find a viable solution. Hopefully one of you can help.
First off, I work for a realestate company, and this query will display a list of properties based on a number of different criteria and criteria types. There are multiple tables involved:
dbo.Prop: Property Database. Holds basic info about each property
dbo.Prop_Features: holds all the features (such as Pool, Carpet, Drapes etc) for each property. The only information stored in this table are PropID and FeatureID (PropID being the Identity of the Prop table, FeatureID being the Identity of the Features Table)
dbo.Features Holds information on each possable "feature" in the system.
dbo.Members Holds basic information and criteria for each of our members.
dbo.Members_Features This table holds the MemberID and FeatureID where members have chosen one ore more features to be used for criteria when searching for a property.
Ok, now... That said, here is my problem. The query I had written (by a professional hired thru Robert Half Technologies) takes over 30 seconds to execute. I will post a copy of that SP below. This is unacceptable. We have to process thousands of these per hour, and a 30 second process time is very bad. Can any of you give me a idea of how to better approach this problem?
In the code below, you will notice there are other tables I did not mention - they are not important. The Speed problem is surrounding a single function, which I will mention below.
CREATE PROCEDURE dbo.Member_Get_List ( @MemberID Int, @UpdatesOnly Bit
SET @PropCount = (SELECT Count(*) FROM Members_Features WHERE MemberID = @MemberID ) END
IF @PropCount = 0 BEGIN
SELECT Top 100 P.PropID, P.Bedrooms, P.Baths, P.Garage, PT.PropName, P.Rent, P.Address, P.Xstreets, 'DateAvailable' = CASE WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!' ELSE CONVERT(varchar(10), P.DateAvailable, 101) End, P.Lease, dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End as 'Features', P.Deposit, Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4), A.AreaName, Z.County, Z.City, Z.State
FROM Prop P
INNER JOIN Area_Zipcode AZ
ON P.Zip = AZ.Zipcode
INNER JOIN Area_Areas A
ON AZ.AreaID = A.AreaID
INNER JOIN Members_Areas MA
ON A.AreaID = MA.AreaID
INNER JOIN Members_PropTypes MP
ON P.PropType = MP.PropType
INNER JOIN Prop_Types PT
ON P.PropType = PT.PropType
INNER JOIN LandLords L ON P.LandLordID = L.LandLordID
INNER JOIN ZipCode Z ON P.ZIP = Z.ZipCode
WHERE
P.Active = 1 AND P.Rent BETWEEN @RentMin AND @RentMax AND P.Bedrooms BETWEEN @BedMin AND @BedMax AND P.Baths >= @MinBaths AND P.Garage >= @MinGarage AND P.Acreage >= @Acreage
AND MA.MemberID = @MemberID AND MP.MemberID = @MemberID
AND P.ListDate > Case @UpdatesOnly When 0 then '01/01/1900' When 1 then @LastUpdate End
CREATE TABLE #Prop ( PropID int, Bedrooms tinyint, Baths float, Garage float, DisplayText varchar(75), Rent float, Address varchar(100), Xstreets varchar(100), DateAvailable varchar(10), Lease tinyint, Features Varchar(3500), Deposit float, Phone1 varchar(12), AreaName Varchar(50), County Varchar(30), City Varchar(30), State Varchar(75) )
DECLARE curProp Cursor FORWARD_ONLY for
SELECT Top 100 P.PropID, P.Bedrooms, P.Baths, P.Garage, PT.PropName, P.Rent, P.Address, P.Xstreets, 'DateAvailable' = CASE WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!' ELSE CONVERT(varchar(10),P.DateAvailable, 101) End, P.Lease, dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End AS 'Features', P.Deposit, Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4), A.AreaName, Z.County, Z.City, Z.State
FROM Prop P
INNER JOIN Area_Zipcode AZ
ON P.Zip = AZ.Zipcode
INNER JOIN Area_Areas A
ON AZ.AreaID = A.AreaID
INNER JOIN Members_Areas MA
ON A.AreaID = MA.AreaID
INNER JOIN Members_PropTypes MP
ON P.PropType = MP.PropType
INNER JOIN Prop_Types PT
ON P.PropType = PT.PropType
INNER JOIN LandLords L ON P.LandLordID = L.LandLordID
INNER JOIN ZipCode Z ON P.ZIP = Z.ZipCode
WHERE
P.Active = 1 AND P.Rent BETWEEN @RentMin AND @RentMax AND P.Bedrooms BETWEEN @BedMin AND @BedMax AND P.Baths >= @MinBaths AND P.Garage >= @MinGarage AND P.Acreage >= @Acreage AND MA.MemberID = @MemberID AND MP.MemberID = @MemberID
AND P.ListDate > Case @UpdatesOnly When 0 then '01/01/1900' When 1 then @LastUpdate End
dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures) WHEN 0 THEN '' ELSE ', ' + P.CustomFeatures End + CASE Len(P.Comments) WHEN 0 THEN '' ELSE ', ' + P.Comments End AS 'Features',
This function is the cause of the speed problem, methinks. When run alone, it takes 29 seconds to process with the same MemberID that takes about 33 seconds to process completely (the whole Proc). This function simply returns a list of comma delimited features for each property, for displaying on a customer list. Here is that function:
CREATE Function dbo.Prop_Get_Feature_List_Fun (@PropID int) RETURNS Varchar(3500)
AS BEGIN
Declare @FeatureList Varchar(3500)
select @FeatureList = Coalesce(@FeatureList + ', ', '' ) + F.FeatureName FROM Prop P
INNER JOIN Prop_Features PF
ON P.PropID = PF.PropID
INNER JOIN Features F
ON PF.FeatureID = F.FeatureID
WHERE
P.PropID = @PropID
ORDER BY
F.FeatureName
Set @FeatureList = isnull(@FeatureList,'Please call for features.')
RETURN @FeatureList END
Now, I know I probably gave lots more information then needed to solve this issue - but its better to have too much then not enough.
Any help at all in speeding up this function or describing another way to do this would be most appreciative.
I have a query that returns the following date interval (int) calls (int) 2008-01-07 00:00:00 700 0 2008-01-07 00:00:00 730 0 2008-01-07 00:00:00 800 0 2008-01-07 00:00:00 830 9 2008-01-07 00:00:00 900 8
I am looking for a way to get my table mentioned above to look like this row_date [700] [730] [800] [830] [900] 2008-1-7 0 0 0 9 8
does anyone have any slick ideas on how I can accomplish this task?
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
Hi, TABLES (Association CONTAINS COLUMNS AssociationID), (Group CONTAINS COLUMNS GroupID, GroupName, AssociationID), (GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved), (UserProfiles CONTAINS COLUMNS UserProfileID, UserID), (AllUsers CONTAINS COLUMNS UserID, FirstName, LastName) I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I. Note that Users can belong to more than one group and have more than one profile. I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
