One For The SQL Experts - Dare I Say TRICKY SQL!
Feb 7, 2006
Guys,
Hopefully someone can help.
We have a monitoring program that has threads which start and stop
monitoring at various times. There are two tables:
THREADLIFECYCLE
unique_id
start_time (always populated)
end_time (not populated until the thread ends)
MONITORRESULTS
unique_id
time_of_measurement
value
What I am trying to do is find the average value for each of the
numbers of running threads. To explain further, threads will start,
stop independently and overlap each other.
I want an output that says:
When 1 thread was running: average value was x
When 3 threads were running: average value was y
Due to the start and stop nature there could be 1 thread running at the
beginning of the test, mid way through, a number of occassions, etc.
Also, the number of threads does not necessarily ramp sequantially -
the number running at any time could be like this sequence: 1, 5, 10,
7, 12, 4, 2
ANY help would be much appreciated - it really has stumped me but looks
like it should be so simple .... But aren't they always the hard ones
;-(
Thanks
Graham
View 22 Replies
ADVERTISEMENT
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Feb 12, 2001
Hi,
I've taken up maintaining new server where there were
50+ DTS packages scheduled. How do I document those tasks?
I know the methods of copy to file as dts and cOM objects.
Is there a way to copy it in readable format like VB or Sql?
Thanks
Sam
View 1 Replies
View Related
Apr 26, 2004
[FONT=Garamond][COLOR=RoyalBlue][SIZE=2]
Hi guys,
Scenario:
We are currently running an Accounting software in Visual Foxpro 8. All are freetables. We are migrating the whole VFP database to sql server 2000 to provide better service to our users. The leagacy VFP system currently running on the production server. Our intension is to migrate the whole system concurrently without intervening/down the system. We found a feasible way to import data from VFP8 with the help or OLEDB VFP.Now I am the one who needs to take care of the Data Synchronization between VFP and Sql Server. I am quite new to DBA routines. I don't have fair idea to synchronize between VFP and SQL Server 2000. If anybody can suggest which replication methodology can I follow to replicate between VFP and SQL SERVER 2000 or even do some DTS routine to achive this. I hope so there should be way to do it in SQL SERVER 2000 :confused: :confused: .
Please guide me to do this....
View 2 Replies
View Related
Apr 16, 2008
I have the following query. I have two problems with this. For each Region I should only have a CustomerNumber listed once with their SOExtChargeAmount summed. I have some showing up more than once.
The second problem is that after my UNION ALL I need to sum 'AllOthers' as one row of data. I can't figure this out. Any help would be absolutely awesome. Thanks.
SELECT
Region, Location, WarehouseCode, CustomerNumber, CustomerName, MonthLessEleven, MonthLessTen, MonthLessNine, MonthLessEight, MonthLessSeven,
MonthLessSix, MonthLessFive, MonthLessFour, MonthLessThree, MonthLessTwo, MonthLessOne, CurrentMonth, CurrentYearTotal, LastYearYTD,
LastYearTotal, CustRank
FROM
(
SELECT
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE 'Unknown'END AS Region,
case
when substring(gla.Account,5,3)='900' then 'ALE'
when substring(gla.Account,5,3)='902' then 'ATO'
when substring(gla.Account,5,3)='904' then 'BOW'
when substring(gla.Account,5,3)='906' then 'BRY'
when substring(gla.Account,5,3)='908' then 'BPT'
when substring(gla.Account,5,3)='910' then 'BYD'
when substring(gla.Account,5,3)='912' then 'BUF'
when substring(gla.Account,5,3)='914' then 'CLE'
when substring(gla.Account,5,3)='916' then 'GRN'
when substring(gla.Account,5,3)='920' then 'DXN'
when substring(gla.Account,5,3)='924' then 'CTH'
when substring(gla.Account,5,3)='926' then 'ELC'
when substring(gla.Account,5,3)='928' then 'FTL'
when substring(gla.Account,5,3)='930' then 'FTW'
when substring(gla.Account,5,3)='932' then 'I35'
when substring(gla.Account,5,3) IN ('936','000') then 'GAI'
when substring(gla.Account,5,3)='939' then 'STW'
when substring(gla.Account,5,3)='940' then 'GRE'
when substring(gla.Account,5,3)='942' then 'HEN'
when substring(gla.Account,5,3)='944' then 'FTS'
when substring(gla.Account,5,3)='948' then 'JAC'
when substring(gla.Account,5,3)='952' then 'JEN'
when substring(gla.Account,5,3)='956' then 'KIL'
when substring(gla.Account,5,3)='957' then 'MCA'
when substring(gla.Account,5,3)='958' then 'MIN'
when substring(gla.Account,5,3)='960' then 'NOC'
when substring(gla.Account,5,3)='962' then 'ODE'
when substring(gla.Account,5,3)='964' then 'BTP'
when substring(gla.Account,5,3)='966' then 'RA'
when substring(gla.Account,5,3)='968' then 'RIF'
when substring(gla.Account,5,3)='970' then 'SWD'
when substring(gla.Account,5,3)='971' then '3PS'
when substring(gla.Account,5,3)='972' then 'ROC'
when substring(gla.Account,5,3)='976' then 'SJO'
when substring(gla.Account,5,3)='978' then 'SMB'
when substring(gla.Account,5,3)='980' then 'STO'
when substring(gla.Account,5,3)='982' then 'TOL'
when substring(gla.Account,5,3)='984' then 'VEL'
when substring(gla.Account,5,3)='985' then 'CFP'
when substring(gla.Account,5,3)='986' then 'CLM'
when substring(gla.Account,5,3)='988' then 'WHI'
when substring(gla.Account,5,3)='992' then 'WRA'
when substring(gla.Account,5,3)='995' then 'ADM' ELSE 'Unknown'END AS Location,
ihh.WarehouseCode,ihh.CustomerNumber, cm.CustomerName,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD([year], - 1, getdate()) > ihh.SOTransDate AND DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,
ROW_NUMBER() OVER(PARTITION BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END
ORDER BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) desc) AS CustRank
FROMMAS_BIF_AR1_CustomerMaster AS cm INNER JOIN
MAS_BIF_ARN_InvHistoryHeader AS ihh ON cm.CustomerNumber = ihh.CustomerNumber INNER JOIN
MAS_BIF_ARO_InvHistoryDetail AS ihd ON ihh.InvoiceNumber = ihd.InvoiceNumber INNER JOIN
MAS_BIF_GL_Account AS gla ON ihd.SOGLSalesAcct = gla.AccountKey
GROUP BYihh.CustomerNumber, cm.CustomerName, gla.Account, ihh.WarehouseCode
) X
WHERECustRank < 20
union all
SELECT
Region, Location, WarehouseCode, 'AllOthers', CustomerName, MonthLessEleven, MonthLessTen, MonthLessNine, MonthLessEight, MonthLessSeven,
MonthLessSix, MonthLessFive, MonthLessFour, MonthLessThree, MonthLessTwo, MonthLessOne, CurrentMonth, CurrentYearTotal, LastYearYTD,
LastYearTotal, 20
FROM
(
SELECT
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE 'Unknown'END AS Region,
case
when substring(gla.Account,5,3)='900' then 'ALE'
when substring(gla.Account,5,3)='902' then 'ATO'
when substring(gla.Account,5,3)='904' then 'BOW'
when substring(gla.Account,5,3)='906' then 'BRY'
when substring(gla.Account,5,3)='908' then 'BPT'
when substring(gla.Account,5,3)='910' then 'BYD'
when substring(gla.Account,5,3)='912' then 'BUF'
when substring(gla.Account,5,3)='914' then 'CLE'
when substring(gla.Account,5,3)='916' then 'GRN'
when substring(gla.Account,5,3)='920' then 'DXN'
when substring(gla.Account,5,3)='924' then 'CTH'
when substring(gla.Account,5,3)='926' then 'ELC'
when substring(gla.Account,5,3)='928' then 'FTL'
when substring(gla.Account,5,3)='930' then 'FTW'
when substring(gla.Account,5,3)='932' then 'I35'
when substring(gla.Account,5,3) IN ('936','000') then 'GAI'
when substring(gla.Account,5,3)='939' then 'STW'
when substring(gla.Account,5,3)='940' then 'GRE'
when substring(gla.Account,5,3)='942' then 'HEN'
when substring(gla.Account,5,3)='944' then 'FTS'
when substring(gla.Account,5,3)='948' then 'JAC'
when substring(gla.Account,5,3)='952' then 'JEN'
when substring(gla.Account,5,3)='956' then 'KIL'
when substring(gla.Account,5,3)='957' then 'MCA'
when substring(gla.Account,5,3)='958' then 'MIN'
when substring(gla.Account,5,3)='960' then 'NOC'
when substring(gla.Account,5,3)='962' then 'ODE'
when substring(gla.Account,5,3)='964' then 'BTP'
when substring(gla.Account,5,3)='966' then 'RA'
when substring(gla.Account,5,3)='968' then 'RIF'
when substring(gla.Account,5,3)='970' then 'SWD'
when substring(gla.Account,5,3)='971' then '3PS'
when substring(gla.Account,5,3)='972' then 'ROC'
when substring(gla.Account,5,3)='976' then 'SJO'
when substring(gla.Account,5,3)='978' then 'SMB'
when substring(gla.Account,5,3)='980' then 'STO'
when substring(gla.Account,5,3)='982' then 'TOL'
when substring(gla.Account,5,3)='984' then 'VEL'
when substring(gla.Account,5,3)='985' then 'CFP'
when substring(gla.Account,5,3)='986' then 'CLM'
when substring(gla.Account,5,3)='988' then 'WHI'
when substring(gla.Account,5,3)='992' then 'WRA'
when substring(gla.Account,5,3)='995' then 'ADM' ELSE 'Unknown'END AS Location,
ihh.WarehouseCode,ihh.CustomerNumber, cm.CustomerName,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF([MONTH], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD([year], - 1, getdate()) > ihh.SOTransDate AND DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,
ROW_NUMBER() OVER(PARTITION BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END
ORDER BY
casewhen substring(gla.Account,5,3) = '936' then 'North Region'
when substring(gla.Account,5,3) = '908' then 'East Region' ELSE ' 'END,
SUM(CASE WHEN DATEDIFF([YEAR], ihh.SOTransDate, getdate()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) desc) AS CustRank
FROMMAS_BIF_AR1_CustomerMaster AS cm INNER JOIN
MAS_BIF_ARN_InvHistoryHeader AS ihh ON cm.CustomerNumber = ihh.CustomerNumber INNER JOIN
MAS_BIF_ARO_InvHistoryDetail AS ihd ON ihh.InvoiceNumber = ihd.InvoiceNumber INNER JOIN
MAS_BIF_GL_Account AS gla ON ihd.SOGLSalesAcct = gla.AccountKey
GROUP BYihh.CustomerNumber, cm.CustomerName, gla.Account, ihh.WarehouseCode
) X
WHERECustRank > 19
View 8 Replies
View Related
Jul 24, 2007
How can I download a report model from report server into a folder programmatically?
View 3 Replies
View Related
Mar 15, 2007
We are doing a community wide project where we need to extract phone numbers and emails from over 1000s local non-profit and gov. webpages. Any suggestion on the best tool outthere that could help us automate this somewhat?
View 1 Replies
View Related
Jun 19, 2007
ANYONE, FAMILIAR WITH THIS.....
REPORT MODEL> ADD NEW ITEM>AUTO GENERATE>CLOSE SOLUTION>OPEN SOLUTION>OPEN MODEL>VIEW DESIGNER
"THE PROCESS COMPLETED SUCCESSFULLY"
YET A WARNING MESSAGE OF ERROR...
View 7 Replies
View Related
Apr 29, 2004
I have this on my page
Dim backUpDB2 As SqlClient.SqlCommand
backUpDB2 = New SqlClient.SqlCommand
backUpDB2.CommandType = CommandType.StoredProcedure
backUpDB2.CommandText = "msdb.dbo.SP_RESUMENFAC"
backUpDB2.Connection = SqlConnection1
backUpDB2.ExecuteNonQuery()
The SP has this
CREATE PROCEDURE .[SP_RESUMENFAC] AS
EXEC sp_start_job @job_name = 'TransferirDatos(FACT) '
GO
WHen I execute the page after the SP it fills some datagrid but the data is not updated bacuase the job takes 1 minute or more to finish.
Is there anyway to prevent to show the old data? or to detect when the job has finished?
Thanks
View 6 Replies
View Related
Jun 17, 2003
Hello all I am trying to write a SQL Statement that will return the fields specified in the Select. However, in some occassion there is no data located in the PS_LEDGER_BUDG C table. I would like to return a zero if possible. When there is no data located in PS_LEDGER_BUDG certain data does not show up. Here is the SQL Statement:
SELECT
A.BUSINESS_UNIT,
A.JOURNAL_ID,
A.JOURNAL_DATE,
B.DEPTID,
B.ACCOUNT,
B.PROJECT_ID,
B.TOTAL_EST_AMOUNT,
C.POSTED_TOTAL_AMT,
B.LEGIS_REF_NBR,
A.OPRID,
B.BUDGET_PERIOD,
A.ACCOUNTING_PERIOD
FROM PS_BUD_JRNL_HEADER A, PS_BUD_JRNL_LN B,
PS_LEDGER_BUDG C
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.JOURNAL_ID = B.JOURNAL_ID
AND A.JOURNAL_DATE = B.JOURNAL_DATE
AND A.UNPOST_SEQ = B.UNPOST_SEQ
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.ACCOUNT = C.ACCOUNT
AND B.DEPTID = C.DEPTID
AND B.BUDGET_PERIOD = C.BUDGET_PERIOD
AND B.PROJECT_ID = C.PROJECT_ID
AND C.FISCAL_YEAR = 9999
AND A.JRNL_HDR_STATUS = 'V'
AND A.OPRID = 'ENRI'
I would greatly appreciate any suggestions. Thank you in advance.
David.
View 3 Replies
View Related
Feb 2, 2007
I just started using Crystal, and I have to say, its a pain in the arse.
Can I just run a simple SQL query and get the results I want, without having to use Crystal Syntax?
Here is the query I want to use:
Code:
select * from esmpvald, esmprmtr
where esmpvald.permit_id = esmprmtr.permit_id and
esmprmtr.issue_date >= ('01/01/06') and
esmprmtr.issue_date <= ('12/31/06') and
esmprmtr.permit_id in (select esmvardd.permit_id
from esmvardd, esmudfvr
where esmvardd.var_fld_id = esmudfvr.var_fld_id and
esmvardd.permit_id in
(select esmvardd.permit_id from esmvardd, esmudfvr where esmvardd.var_fld_id = esmudfvr.var_fld_id and
(esmvardd.var_fld_value = 'a') and
(esmudfvr.var_fld_name = 'Commercial Type')) and 1=1)
I will be looping through the "esmvardd.var_fld_value = 'a'" part, going a through f, but for now, I just want to start with the 'a'.
I could do this manually, and throw it in a Word doc, but I'm trying learn this thing. Ideas?
View 1 Replies
View Related
Jul 20, 2005
The scenario:two tablesCustomerTable---------------CustomerIDOrderIDCustomerNameCustomerEmailCustomerPhoneOrderTable---------------OrderIDProductIDProductNameProductCostThis database was handed to me and I was asked to solve a problem - it lookslike an inner join solution would apply, but I'm not 100% sure.There are 14 products total (numbers 1 through 14).I'm looking to get a list of all the customers who have ordered product #1,UNLESS they've ordered product #14 in which case I don't want to know aboutthat customer at all.Any help would be greatly appreciated! I'll watch the newsgroup for theanswer - hopefully your response can help someone else too. However, if youprefer to email me directly, you can send it to me at bunchah at yahoo dotcom.Thanks in advance!(if it'll help, I'll buy the person offering the correct solution a beer -pending age verification of course) ;)-Al
View 11 Replies
View Related
Feb 27, 2008
Here is the original script
select count(*) FROM (select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:10:22.000' or price_chg_dt > '2008-02-17 18:10:22.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcode
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd
I want to add a condition [(substring(AECPRDA_1.upc_1,1,11) = substring(z.upc,1,11) or
(AECPRDA_1.product_id = z.vendorcode))] instead of [AECPRDA_1.product_id = z.vendorcode] TO THE ABOVE STATEMENT.
It is a secondary match condition
1. If aecprda_1.upc_1 doesn€™t find a match on zfmt.upc then
2. Match on aecprda_1. product_id = zfmt. Vendorcode
3. I also want to add a condition if there is a product match on both 1 & 2 , then take only the first one and don€™t take the second one.
Something looks like below statement but needs one more condition mentioned at item 3 to be added.
select count(*) FROM (select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2007-10-22 00:00:00.000' or price_chg_dt > '2007-10-22 00:00:00.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on (substring(AECPRDA_1.upc_1,1,11) = substring(z.upc,1,11) or
(AECPRDA_1.product_id = z.vendorcode))
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd --- I ran this query executing takes hour eventhough appropriate indices were added.
Any help is greatly appreciated.
View 7 Replies
View Related
May 15, 2008
I am an Application Developer.
I know just this about T-SQL :
Insert, UPDATE, delete aRecord.
JOIN tables.
Create a Hierarchy column.
execute MERGE Statement. [in order to create IDENTICAL tables .]
Create a Master-detail TABLE . [PrimaryKey-Foreig-Key]
WHAT ELSE SHOULD I KNOW widly speaking about T-SQL ??
What are ESSENTIAL things you should know to Be a BIT of an EXPERT ???
Please be STRAIGHT and SIMPLE.
THANKs a LOT.
View 8 Replies
View Related
Jul 28, 1999
Scenario..
Table 1 = Product (one row per productID, also has second key, KEY2)
Table 2 = Usage (many rows per KEY2)
I have a view that aggregates Usage GROUP BY Key2 - Usage_VIEW
I create a view over Product and Usave_view joining on KEY2 - Final_VIEW
When I query Product with a where Product = 'x'
the time is under 1 second return.
I then take the Key2 value returned and query my Usage_View
the time is under 1 second to return.
BUT, if I query My Final_VIEW with where product = 'x'
I can take a coffee break.
Question? Does the aggregate of Usage get performed before limiting to the matching rows in Product?
Can I use "Force Order" hint to make the first table limit the search on table 2? If so, please give syntax on FORCE ORDER. I can't seem to figure it out.
View 2 Replies
View Related
Sep 6, 2006
Hi,
I am a Microsoft Recruiter, and we are hiring SQL Premier Field Engineers in DC, AZ, CA, CO, and MA. Is there a place on the site where I can post these roles? If you know anyone who may be interested, please have them to contact me. I would be happy to send a job description.
Thank you.
View 4 Replies
View Related
Jan 15, 2004
Our Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting.
SQL is an obvious choice because we have it already for our retail system.
However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem.
We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores.
What we don't want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work.
The problem is that if a store doesn't post their transactions e.g. because of a system failure then these will get missed.
What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night.
We thought of adding a flag onto the Informix server but we are not able to make any modifications to it.
I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server.
With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link......
View 1 Replies
View Related
Feb 5, 2007
Hi all I have the following CLR stored procedure :
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub sssGetActiveRepositoryByTitle( _
ByVal title As String)
' Add your code here
Using conn As New SqlConnection("context connection=true")
Dim objCommand As New SqlCommand()
Dim TitleParam As New SqlParameter("@Title", SqlDbType.VarChar, 100)
TitleParam.Value = title
objCommand.Connection = conn
conn.Open()
'build the delete command
objCommand.CommandText = _
"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString
SqlContext.Pipe.ExecuteAndSend(objCommand)
conn.Close()
End Using
End Sub
Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :
Imports System.Data.SqlClient
Imports NBS.SURVEYSDATABASEservice.DBMS
Public Class clsClient
' it inherits the stored procedures from the DBMS class which is the name
'of the CLR dll
Inherits StoredProcedures
Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet
Try
Dim i As SqlDataReader
'parameters are stored in an array (zero based) for use in the base class
Dim parmArrSqlParms(0) As SqlClient.SqlParameter
' Dim fff As Int32
i = sdsGetClientByVirtualPath(pstrVirtualPath)
' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)
Catch ex As Exception
'log the error
'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)
'raise the error to the caller for handling
Throw ex
End Try
End Function
I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"
any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .
Help please !
View 1 Replies
View Related
Jun 26, 2007
I am trying to insert a value into a field in a database named ASPNETDB.MDF. The table name is "profiles_BasicProperties" and the field name is "UserID". I get an error when I attempt to do this. See the code I am using to try to do this below...and then the error that I get which is further down in this post. Note...both the code and the database are on my laptop. I can connect to the database just fine using Server Explorer in MS VS 2005. Thanks in advance for any help anybody can offer...
Here is the code I am using:
<%@ Page Language="VB" MasterPageFile="~/Master02.master" Title="Create Your Free Account" Debug="true"%><%@ Import Namespace="System.Data.SqlClient" %><%@ Import Namespace="System.Web.Configuration" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder_Main" Runat="Server">
<script runat="server"> Sub CreateUserWizard_CreatedUser(ByVal sender As Object, ByVal e As EventArgs) Dim CWZ As CreateUserWizard CWZ = CType(Me.LoginView1.FindControl("CreateUserWizard"), Wizard)
CreateUserProfile(CWZ.UserName)
Private Sub CreateUserProfile(ByVal UserName As String) Dim conString As String = WebConfigurationManager.ConnectionStrings("Main").ConnectionString Dim con As New SqlConnection(conString) Dim cmd As New SqlCommand("INSERT profiles_BasicProperties (UserName) VALUES (@UserID)", con) cmd.Parameters.AddWithValue("@UserID", UserName) Using con con.Open() cmd.ExecuteNonQuery() End Using End Sub
</script>
...and here is the error and stack trace (the offending Line 49 is in bold):
Server Error in '/Site_Dev' Application.--------------------------------------------------------------------------------
An attempt to attach an auto-named database for file ~App_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file ~App_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
Line 47: cmd.Parameters.AddWithValue("@UserID", UserName)Line 48: Using conLine 49: con.Open()Line 50: cmd.ExecuteNonQuery()Line 51: End Using
Source File: C:UsersmdcraggDocumentsWebsiteSite_DevUser_Create.aspx Line: 49
Stack Trace:
[SqlException (0x80131904): An attempt to attach an auto-named database for file ~App_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736211 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 ASP.user_create_aspx.CreateUserProfile(String UserName) in C:UsersMatthewDocumentsGroup 02 - PoliticoreSite_DevUser_Create.aspx:49 ASP.user_create_aspx.CreateUserWizard_CreatedUser(Object sender, EventArgs e) in C:UsersMatthewDocumentsGroup 02 - PoliticoreSite_DevUser_Create.aspx:30 System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105 System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341 System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105 System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453 System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149 System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
--------------------------------------------------------------------------------Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312
View 1 Replies
View Related
May 15, 2005
Hi,
I have 2 design related questions.
Q1: We are developing a huge .NET e-commerce web application with a number of modules - Shopping, 'For Sell' , 'For Rent', News, Jobs, Community, Matchmaking etc. These modules will store data into SQL server 2000 database server. 'For Sell' module will be used for all user ADs for selling items(addupdatesearch), similarly 'For Rent' module will be for Rentals ADs. The site will be open for 20+ different countries initially and will store unlimited ADs (eg. 200,000 For Sell ADs), Shopping Catalog (100,000 items).
We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc. The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.
Q2: Will it be better to create a single web application or different web application for each module?
Please give us your expert inputsuggestions ips that will guide our team to the right direction.
Thanks
Jennifer
View 5 Replies
View Related
Jul 27, 2006
Dear Group.Wondered if any of you has any suggestion for the following?Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, mymistake was probably that I had installed Visual Studio 2005Professional Eval before which installed an MSSQL Express instance.Since then I didn't get 'Enterprise Manager' (excuse my ignorance, Iknow it's called differently in2005 but it's late and I'm tired) forMSSQL 2005 installed.Don't remember the error exactly but after sometrying I received some error during the MSSQL Management Studio (isthat right?) installation that it needs to be upgraded in some ini filewhich I looked for to no avail. ANYWAY, after some desperate registrydeletes and following MS Kbase articles I arrived at: 'The installerhas encountered an unexpected error installing this package. This mayindicate a problem with the package. The error code is 2718.' duringthe installation of SQL Server Backward-Compatibility files.Which is of course complete rubbish. We all now that Microsoft has donean awful job in getting SQL Express and SQL Server installedside-by-side but it never has been as bad as this on any machinebefore. Needless to say the package is fine as I've used it many timesbefore. Any suggestions?Gratefully yours,Martin'Just wanna get on with work. Tired of starring at error messages.'
View 1 Replies
View Related
Dec 7, 2007
Hi,
I'm accessing MS SQL server standard edition using ASP.net 1.1 web applications
my site has traffic ranking 84,000 on alexa.com. all web applications
are mounted on One sigle Application pool. I have developed
monitoring webservice for monitoring the runtime errors occured during
transactions, where I came across below errors all the time. during
heavy traffic.
1. SQL Server does not exist or access denied.
2. General network error. Check your network documentation.
3. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
First one is very important as Microsoft it self agree that it was bug in their products. How can I fix it.
http://support.microsoft.com/kb/328306
Client uses TCP/IP and Named Pipes.
I'm hoping to get it fixed using this thread.
Thanks,
Satalaj.
View 9 Replies
View Related
Jul 14, 2005
QUESTIONs:What schema is the best for high speed search for a classified web application?
Is our schema design looks OK? It is a STAR schema and will be used for OLTP type app. Is this OK? or Are we missing something? Please let me know if you need more diagramatic description.
BACKGROUND:Our group is making a classified website (like classified.yahoo.com) where people can place online ad to sell items. like cars, computers, electronics etc. Users will fill out webform for each category(car or computers) with all attributes of the item forsell to post an add.
Main 3 operations the web users will perform: Quick Searching(most frequent): category=car, subcategory=sedan, country=USA city=LosAngeles Zip=empty
Advanced Search(less frequent): User can include all fieldsattributes or then can user a subset to query to do advanced search like :
category=car, subcategory=sedan, country=USA, city=Los Angeles, Year= in(00, 03), transmission=Auto, engine=V6, Maker=Honda, Model=Accord, color=Red and Price < 10000
category=car, subcategory=SUV, country=USA, city=NY, Year= in(99, 00, 03), transmission=Auto, Maker=Toyota, and Price between 11000 and 14500
Insert(least frequent): By filling out web form. For Car, the form will have different dimensionsattributes (year, make, model, transmission, mileage, color, price etc)
Current Schema design: Set of core dimensionlookup tables: stores corecommon attributes for lookups eg. status={open, new, expired}, country={USA, Canada, India,...}
One custom Lookup table: for all custom attribute lookup: transmission{auto, manual}, engine type{V4, V6} for car, processor{PII, PIII, PIV), RAM(512MB, 1GB, 2GB} for Computer & so
3 Fact Tables: Main factPivot table that stores all sell common attributes eg. price, title, year_made, post_date, expire_date, user_name, description etc. One fact table to store custom string, int, float, date field values of ads. One fact table to store custom dropdown field selection values
Concerns and issues: Looking at the schema, it seems to be a STAR schema with multiple fact tables where all core lookup tables connected to the main Pivot table and custom lookup table connected to the 2nd and 3rd fact table.
Quick search only queries the Pivot fact table. While Advanced search query requires to join 3 fact tables. Both query requires to join 3 fact tables with all dimension tables(15 to 20 each having avg of 20 values) to get the look up names so that users sees text instead of ids. Search speed is the Main concerns. Insertionupdate speed doesn't matter that much as that is less frequently done.
View 5 Replies
View Related
Apr 17, 2004
Hello, I was wondering if any of you experts might assist me in properly creating a database for my application. I've been pondering for a few days on how to accomplish this, but it seems that it doesnt have to be as complex as I am thinking...I just have to know how.
First off, I want to populate a dataset from a database in which I can databind to a datalist or repeater control. My datalist or repeater will have the following information in the ItemTemplate. Each ItemTemplate will consist of a table with 1 row with 3 columns cells. In each cell, the data will be laid out as accordingly.
[Unique Number] [Description ][Price ]
1000 XYZ $100.00
1001 ZXY $250.00
When the datalist or repeater is populated, it will need to be tried against a value that the user selects. For instance, the user selects 100,000 from a listbox and fetches the next page which will show the diagram above...he/she will see those results. However, if the user selects 110,000, the diagram above will have for the most part the same Unique Number and Descriptions, but the prices will vary. The reason I say for the most part with the Unique Number and Descriptions, is because I want to later be able to add admin access to add additional rows and appropriate prices to each Unique Number and Description.
Now the tricky part is, I could just add the listbox values to a table as a unique key and associated them with the Unique Number, Description and Price, however there are about 50 different options the user can select from and approx 50 different rows of Unique Numbers, Descriptions and Price . So you can see, I would have to set up the diagram each time for every possible selection from the listbox, which wouldnt really be efficient I presume.
I want to be able to populate the datalist or repeater so it could have say 50 Unique Numbers with Descriptions and Price at selection 100,000. And also that it might only populate with 45 Unique Numbers and Descrptions and Price at selection 110,000 because the 5 missing dont pertain to the selection of 110,000. I am trying to do it this way because at sometime, I want the admin to be able to add/delete a Unique Number, a Description for it and a corresponding price that correlates to the selection from the listbox.
Thanks for all your help guys. I really appreciate it. For the most part I understand what Im doing, I just need to be walked thru it a bit. Thanks again!
View 1 Replies
View Related
Mar 21, 2007
I am attempting to do the following....
I have standard tree setup. The tree can be up to 4 nodes deep. User permissions may be assigned at any level in the tree. Any
permission should cascade down the tree to the lowest child node.
For example, if a user had a role of 1 for the root node (101), the sql should return:
OrgID RoleID
101 1
102 1
103 1
etc...
My table structure is as follows....
Org
OrgID ParentID
101 Null
102 101
103 101
105 102
106 102
107 105
108 105
109 106
110 106
111 106
UserOrgRole
UserID OrgID RoleID
User1 101 1
User1 102 2
User1 103 2
User1 107 2
User2 101 1
User3 106 3
etc...
What I would like to retrive from the above table data is....
OrgID RoleID
102 2
105 2
106 2
107 2
108 2
109 2
110 2
111 2
This is so because all the nodes (except for 101 and 103) are somehow decedent from the 102 node and 102 has a roleid of 2. I am only concerned with the RoleID 2 and User1.
I have worked for two days trying to figure out how to do this. I am not a DBA or SQL expert by any means. I cannot seem to figure
out how to traverse multiple levels of the tree. I have been using the new CTE and made some progress, but I think I reached my
plateau and haven't been able to get any further.
If someone could help me, I would be forever in your debt! I am really starting to get very frustrated and I know there are some of
you experts out there that would know exactly what to do.
thanks!
View 19 Replies
View Related
Mar 14, 2008
I have been tring to get this one line figured out for a few days now.
'Job2 Info
Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM '" & CompanyKey & "'" '<<-------HERE
Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
(I have the full code below.) So here is the problem, this code is not populating the datagrid. There is data in the table I am selecting from. When I log in, my CompanyKey value displays in the label as "21". When I take out the "CompanyKey" variable, and just type in 21, the grid is populated. It is confusing the heck out of me. I have tried it this way:
selectSQL2 = "SELECT * FROM [" & CompanyKey & "]"
-and this way:
selectSQL2 = "SELECT * FROM & CompanyKey
and all the other ways I could think of. I researched it and can just not get it to work any way I do it. Any suggestions? Full code below:
____________________________________
Imports System.DataImports System.Data.CommonImports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'Job1(Info)Dim currentUserID
currentUserID = Context.User.Identity.Name.ToString()
Label1.Text = currentUserID
Dim selectSQL1 As StringselectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"
Dim cmd1 As New SqlCommand(selectSQL1, con) Dim reader As SqlDataReader
Dim CompanyKey
'Job1 Select
Try
con.Open()
reader = cmd1.ExecuteReader()Do While reader.Read()
CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
con.Close()
End Try
'Job2 Info
Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM [" & CompanyKey & "]"
Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
GridView1.DataSource = reader
GridView1.DataBind()
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
ReaderResults.Text = CompanyKey
con.Close()End TryEnd Sub
End Class
View 4 Replies
View Related
Nov 8, 2004
I have 2 tables joined together by the IDs, People and the pets they
own
PEOPLE
ID NAME
1 JohnSMith
2 JaneDoe
PETS
ID PET
1 Dog
2 Cat
2 Hamster
2 Hamster
2 Fish
I have create another where the PETS are in one column separated by
semi-colons and removing the dups
NEW TABLE
ID NAME ALLPETS
1 JohnSmith Dog
2 JaneDoe Cat;Hamster;Fish
What is the best way to do it? The only way I can think of is to run
an update where it checks to see if the value already exists
THanks!
View 1 Replies
View Related
Mar 18, 2008
Hello,
I have four diffrent tabels:
bo_ Class, bo_Competition, bo_Result, bo_Licence
And list all Licence where bo_Class.classRankingNbr >0
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------
M70891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M050887PON01 Pontus Svensson U23 SM 2007 - Herrar 2 1 H
M181188MAR01 Marcus Edlund U23 SM 2007 - Herrar 3 1 H
M190291JOH01 Johan Helldén U23 SM 2007 - Herrar 4 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K050785CAR01 Carin Johansson U23 SM 2007- Damer 3 1 D
If bo_Class.ClassRowNbr for an Class is 1 get out place 1 from that competition,
and if an ClassRowNbr is 2 get out top2 from that competition.
And so on.
From that list i want an SubQuery list where all licens order by where
bo_Result.ResultPlace = bo_Class.ClassRowNbr.
In text form:
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------------------
M170891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D
Hope someone can take the time and help me out.
Best regards
Gert Lindholm
View 6 Replies
View Related
Oct 18, 2005
One statistic questiong from an db Resultat.
SELECT m.Namn + ' ' + m.ENamn AS Spelare, SUM(r.Serier) AS Ser, SUM(r.Poang) AS Po, ROUND(SUM(r.Resultat) / SUM(r.Serier * 1.0), 2, 1)AS Snitt, ROUND(SUM(r.Poang * 1.0) / SUM(r.Serier), 2) AS [P Snitt], ROUND(SUM(r.Miss * 1.0 / r.Serier), 2, 1) AS Miss, SUM(r.Miss) AS [Sa Miss], MAX(r.Resultat) AS Bästa, MIN(r.Resultat) AS Sämsta
FROM Resultat r INNER JOIN
Medlemmar m ON r.Spelare = m.ID
WHERE (r.Omgang IN (SELECT DISTINCT TOP 3 Omgang
FROM Resultat
ORDER BY Omgang DESC))
GROUP BY m.Namn + ' ' + m.ENamn
ORDER BY 4 DESC
What i want to do is sort out Min(r.Resultat) where serier is mor than 3, Not WHERE (r.Resultat) >3. More like in Access "Min(IIf(r.serier=4,r.resultat,Null)) AS [Sämsta]" But that i cant do in SQL
Have also tryed with "WHERE (SELECT MIN(r.Serier) FROM Resultat
Resultat r INNER JOIN
medlemmar m ON r.Spelare = m.ID"
Get lowest result same on all players.
Best reg
Gerten
View 2 Replies
View Related
Feb 7, 2006
Hello allI've got this tricky situation that I would like to solve in SQL, butdon't know how to do. This is the table:Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00GetRows @Time='15:30' will return row with Id=4GetRows @Time='16:30' will return row with Id=4 and row=9Logic behind this:Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and sameVId.Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, andTime1 of Id = 4 is higher than @Time => return row with Id = 4.This got a bit messy but if someone could decipher this and possiblygive an answer I'd be very glad.regardsJohnny
View 5 Replies
View Related
Jan 28, 2008
I have an integer in the database that was saved in reverse byte order (BigEndian). Due to some backward compatibility issues (long story) I can't just convert the number to the normal format and save it that way in the database.
Instead, when I read the number in my program, I just reverse its bytes and display the proper value, and translate the number back when it has to be saved back to the database.
Now, the problem is that there are some views that pull this numbers directly from the database and display it.
My question is: can this number be converted from BigEndian to LittleEndian similarly to how I do in my program using T-SQL?
Thanks.
View 4 Replies
View Related
Jul 18, 2007
I have a LastName field which holds this dataLastNameJohnson|VasquesAdams|Fox|JohnsonVasques|Smith Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.The @LastName can be something like this: “Fox|Smith�.I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith . Thank you.
View 14 Replies
View Related
Dec 13, 2007
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.
I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
View 2 Replies
View Related
