Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
i am making a query which select the data again a particuler date.
I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.
I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.
Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?
I am really stuck on this, if anyone has some insight into this problem any help would appreciated... I'll try to explain what is happening the best I can:
We have a server running Windows Advanced Server 2000 (SP4) w/ SQL server 2000 (SP3) (from now on Server A). I have a publication on this machine with dynamic filters (Changing the HOST_NAME()). The publication is sending the snapshots to another machine (desktop machine). The Mobile agent is in the same machine as the snapshots.
The mobile application is syncing fine when hitting Server A. The sync is done Asynchronously.
Then we have Server B. Running Windows Server 2003 (SP1) w/ SQL 2000 (SP4), same publication w/ dynamic filter however the snapshots and the mobile agent are in this server.
The mobile application will sync the 1st time but any subsequent syncs will not work. I check on the Replication monitor and it tells me that the Merge was a success but the mobile application will not execute the download table callback, it will execute the Sync callback 5 times and not proceed in executing the download table callback.
If I change the configuration on the mobile app to point to Server A the sync will work just fine but, if I change it back to Server B the sync will work once then it will stop working.
Hello,I need to create a column that will store hours bigger than 24. Forexample '25:00:00', '129:23:12', etc).That column will be used too, for perform calculations betweendatetime intervals: 'time'.In MySQL there is a datatype that perfect fits that necessity.Does anyone know what is the corresponding datatype in SQL Server?Thanks a lot!
As in title. Is there any tool? I'm asking beceuse, I have some big bases, and processing may take a lot of time (at least few hours), and I'll be glad if it's possibility to know estimate time before query runs. I'm using MsSql 2005 Developer edition.
I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong? My original version that calls a store procedure Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String Dim mySqlCommand As New SqlCommand Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString()) Dim myPart_id As String mySqlCommand.CommandType = CommandType.StoredProcedure mySqlCommand.CommandText = "getPartId" mySqlCommand.Connection = mySqlConnection mySqlCommand.Parameters.Add(New SqlParameter("@part_supplierserialnumber", part_supplierserialnumber)) Try mySqlConnection.Open() myPart_id = mySqlCommand.ExecuteScalar() Catch ex As Exception myPart_id = "" Finally mySqlConnection.Close() mySqlConnection.Dispose() End Try Return myPart_id End Function My Store procedure create procedure getPartId@part_supplierserialnumber as nvarchar(50)as select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @part_supplierserialnumber order by part_datecreated desc
GO The new version I tried which happen the same thing Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String Dim myPart_id As String Dim strSql As String = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc" Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString()) Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection) Try mySqlConnection.Open() myPart_id = mySqlCommand.ExecuteScalar() Catch ex As Exception myPart_id = "" Finally mySqlConnection.Close() mySqlConnection.Dispose() End Try Return myPart_id End Function
I convert my DB from Access to SQL Server ,When i try to run my script (in ASP) it's not store the data atDate/Time field!!!And i have troble with insert the value TRUE in Sql Server (it's workwith access)I want to know if i need to change my scripts(in ASP) or the type offields?Please, HelpBest regards.
Hi Friends "I wonder why the boolean values Set to "Disable Property" using expressions does not work as expected (though Precedence constraints work fine). I find no way to disable individual task. "
I have a boolen variable V, (False) enabled by default at design time for a task T, I disable T based on a condition (if A ! = 'a') during runtime.
Now first time when I run the Package, Task gets disabled (works fine on the condition given). However T gets permanently disabled even after we stop debug mode, which is not the expected behavior. Further on when you run, it wont work at all.
Is it true that a task disabled at design time cannot be enabled during runtime via expressions ?
If things are working fine in your case, pls let me know what settings are required.
Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.
So for example
If this person
had a days holiday on the 22nd, shown in the HOLIDAY table as
The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?
I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.
Clean vista install, Clean full office 2007plus install Clean visual studio 2008pro install Many failed sql 2005dev installed, did SKUUPGRADE-1 Now have office2007 Smallbiz, VS2008pro, SqlExpress and sql 2005dev installed. Note outlook 2007 error on first run after sql2005dev. Seams to have broken and created a new sql connection, don€™t think its the original. but that€™s not the current issue ! web admin page error, could not connect to db I did regsql.exe from the net dir and the web admin work and the db is created for roles and such in vs 2008 via web admin page and mssql. Managed to create db in apps data folder and modify connection in server explorer. Added table to aspx page from apps folder and it runs via f5, but errors under localhost. €œCannot open user default database. Login failed.€? €œLogin failed for user 'NT AUTHORITYNETWORK SERVICE'€? Have seen some reference to iis permissions, but not sure what to change? Is it a file level permission, a virtual permission Works in vs2008 using f5 run! http://localhost:50115/vs2008/ Can work with apps folder db file in vs 2008 server explorer Had to modify db connection back to sqlexpress in server explorer Can work with db in sql management studio, have three local instances; Pc Pcsqlexpress Pcsmallbiz Can display db data in aspx page via f5 , but not localhost unless vs2008 is not running!!! Have not been able to make vs2008, Sql2005dev, sqlexpress all work together using iis in vista via localhost and vs2008 f5 at same time?? Can€™t do anything with db in solutions explorer, but can work in server explorer if I modify connection back to sqlexpress Can€™t display data aspx page via localhost http://localhost/vs2008/ Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITYNETWORK SERVICE' Localhost works in iis via virtual directory if I remove db grid in page or vs is not running Could someone test and tell how to use all of these together? What is going to happen if I get it to work local and then try to upload to shared host? <connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="Server=wabpc;Database=VS2008test;Integrated Security=true" providerName="System.Data.SqlClient" /> <add connectionString="Server=SQLEXPRESS;Database=Database.mdf;Integrated Security=true" name="sqlexpress" />
</connectionStrings> If I close vs2008 and the management studio, localhost works and displays the db info!! Open vs2008, modify connection back to sqlexpress and the aspx page breaks via localhost Close vs2008 and local host works Open vs2008 and get a error 25 in server explorer connection string invalid, but f5 works after the offline clears. I change db connection back to sqlexpress to work with db in server explorer! That breaks localhost, but f5 works
in one invoice has many po# number i already get the po number individually using explode... the ouput like this 001 002 003 and so on it is ok for that
but my problem is
i want to the po# should be selected i mean like a menu ..like the user select the which po# should be updated....
but i dont know how..
is there anyone can help me?
to elaborate more...
i can display the po# already without commas like in invoice# 1111 i can already display the po# number like this 001 002 003
after that i want it to be like a menu that user will only select the which po# should be updated..using arrow keys down and up....i mean just like selecting..
SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name , b.station_nbr, b.beg_eff_date, b.end_eff_date, Round( e.C02_pct,3)As C02_pct, MAX( e.sample_date)As Highest_Sample_Date, d.station_name from [HOU-TIES01].TIES_Gathering.dbo.group_detail a Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_group b on a.group_code = b.group_code Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_type c on b.station_nbr = c.station_nbr Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.station d ON c.station_nbr = d.station_nbr Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.gas_analysis e ON d.station_nbr = e.station_nbr Where (b.station_nbr=e.station_nbr)and (b.group_code = a.group_code) and (a.group_type = 'NPN')and( b.end_eff_date >=DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0)) and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))and (e.sample_date Between DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0) and DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) and e.seq_nbr = (select max(x.seq_nbr) From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x Where x.station_nbr = b.station_nbr and x.sample_date= e.sample_date ) and e.C02_pct = (Select max(x.C02_pct) From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x Where x.station_nbr = b.station_nbr and x.sample_date= e.sample_date ) Group by a.group_code, a.group_name, a.group_note, b.station_nbr, b.beg_eff_date, b.end_eff_date, e.c02_pct, d.station_name order by a.group_code
ok. Here is how the data looks. Group code station_nbr beg_eff_date end_eff_date C02 sample_date 0004 D01G000819 2007-09-01 3000-12-31 0.40 2007-10-02 0004 D01G000819 2007-09-01 3000-12-31 2.49 2007-10-09
What I am trying to do is to select the most current sample date. In this case "2007/10/09" when station_nbr and group_code are the same for both dates.
Thank You for all your help!!!! From the Jr.in SQL
This is a new query and I am facing a new problem. Here I am requesting all the records for a Contract Number 'GTH00001' for the specific date of 2/2/2007. I want the SUM(c.rcpt_nom_vol)AS Receipt and the b]SUM(c.rcpt_fuel)As Allocated_Fuel[/b] But Instead I am getting 147 records for Contract Number'GTH00001'for the date of 2/2/2007.
Declare @Req_Contract_nbr char (8), @Req_Day int, @Req_month int, @Req_year int
Set @Req_Contract_nbr = 'GTH00001' Set @Req_Day = '2' set @Req_month = '2' Set @Req_year ='2007'
SELECT Distinct a.contract_nbr, c.beg_eff_date, c.rcpt_dlvry_ind, SUM(c.rcpt_nom_vol)AS Receipt, SUM(c.rcpt_fuel)As Allocated_Fuel from dbo.contract a Inner Join dbo.NOm b on a.contract_nbr = b.contract_nbr Inner Join dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH') and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) and (c.rcpt_dlvry_ind ='R') and a.contract_nbr= @Req_Contract_nbr and Day(c.Beg_eff_date) = @Req_Day and (month(c.Beg_eff_date)= @Req_month ) Group by a.contract_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel
Hi, First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this. Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later. How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me. Skills Table
We are looking to purchase a new SQL2000 server.... Tada.... Right then, for around about the same money, we could have either 2 x PIII 1Ghz Xeon with 256Kb cache, or 2 x PIII 700Mhz Xeon with 1Mb cache.... Which choice is best for SQL Server 2000???
I hope I can explain this somewhat coherantly. Here goes..
I have a products database. I want to select product details for items having distinct style codes. IE if there are 20 products that different from one another in some way, and 3 different style numbers throughout them, I want to end up with 3 products selected. I don't care which ones, as long as i have a sample product for each style. I've attemped to use GROUP BY to do this, but it's not giving me what I want. I managed to get right number of products, but the query seems to have mixed and matched the fields together in an undesireable way. I guess this kinda makes sense, since providing aggregate functions for each of the extra fields is necessary. SO THE QUESTION IS: How do I select (number of distinct styles) products, and still keep the records in tact as they should be?
Here is a sample...
Code:
SELECT DISTINCT Min(team) as team, min(pict) as pict, min(colors) as colors, min(league) as league, min(product_description) as product_description, min(category) as category, min(subcategory) as subcategory, min(style) as style FROM prods WHERE (style='style1' or style='style2' or style='style3') AND league='leaguex' AND soldout='' GROUP by style ORDER by style
Thanks very much for any help!! I'm sure I'm going about this all wrong.
Select DOJ AS 'JoiningDate' from emp where DOJ like '%2008/09/04%'
there are 8 records on this time.But it displays no result . The column is datetime datatype year month date or we need to mention any thing other than this.
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.
Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:
NetworkId Full Name Sec Class Desc Sec Type Desc SecValue tte Test Scenario Accounting Budget Center 142- ?
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
------------------
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
i need some help i have a database which has single column containing 10,000 records. i enter a number say 1000. so it divides the 10,000 records into 10 different column containing 1000 record each.how do i do it?
While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause: e.g. select * from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1
Is logically the same as: select * from Table1 a inner join Table2 b ON a.key = b.key where a.a = 1
Being new to this forum and to SQL, I would to apologise upfront for my clumsy code and explanations as I try and get to grips with this subject.
I have created this query which does work:
SELECT T1.U_Serial, case when (U_RDate < GETDATE() AND U_ASDate Is NULL) then 'NA' when (U_RDate Is NULL) then 'No Request' when (U_ASDate Is Not NULL And U_AEDate Is NULL) then 'NA' when (U_ASDate Is Not NULL And U_AEDate Is Not NULL) then 'A' when (U_RDate > GETDATE() AND U_ASDate Is NULL) then 'NA' else 'DN' end As Available, case when (U_RDate < GETDATE() AND U_ASDate Is NULL) then 'Late' when (U_RDate Is NULL) then 'No Request' when (U_ASDate Is Not NULL And U_AEDate Is NULL) then 'Busy' when (U_ASDate Is Not NULL And U_AEDate Is Not NULL) then 'Done' when (U_RDate > GETDATE() AND U_ASDate Is NULL) then 'Waiting' else 'DN' end As Progress,
FROM [dbo].[@IDH_JOBENTR] T0 , [dbo].[@IDH_JOBSHD] T1
WHERE T0.Code = T1.U_JobNr AND T1.U_Serial not like ''
ORDER BY T1.U_Serial, T1.U_AEDate DESC, T1.U_JobNr DESC ,T1.Code DESC,Today, T1.U_RDate, Available, Progress, T1.U_Status,T1.U_JobTp,T0.U_CardNM, T0.U_ZpCd, T1.U_ASDate, T1.U_ItemCd, T1.U_ItemDsc
The tables I’m using are IDH_JOBENTR which is the header records for my jobs and IDH_JOBSHD, which holds the row details for the jobs.
I’m trying to extract from the IDH_JOBSHD table the last (row) detail record for the (header) job.
The way the query works now, the first row for each unique serial number is the record I need, but this is all the info I would like to display I don’t need the other lines, and I don't know how to strip out the rest of the data
