Trying To Figure Out A Suitable Sql Structure

Oct 17, 2007

I'm new to sql and have come up with a problem and can't seem to find the answer. Most would probably find it simple but I cant get my head around it! :p

I have the following table structure

User_Table
-----------
User_ID (Key)
FirstName
LastName


Contacts_Table
--------------
User_ID (F key)
Contact_User_ID


User_Table stores all users details and assigns them a User_ID. Users can add other users to their contacts, and this will be stored in Contacts_Table on a one-to-many basis.

*deep breath*... So User_ID in Contacts_Table, will store the User_ID from User_Table, and the Contact_User_ID in Contacts_Table will store the User_ID from User_Table. Does this seem ok? Sorry if I confused everyone!

So my question is, how do I select a user and show all his contacts (names etc)? I thought I could use innerjoin but I dont think it would work here.

Any ideas?

Thanks!

View 3 Replies


ADVERTISEMENT

Real Figure Instead Of Exponential Figure Needed

May 29, 2008

Hello,

I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.

Example: 1225000.00

When I select this value using SSMS I get the correct value:

1225000

Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))

1.225e+006

I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.

Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?

Thanks: Peter

View 5 Replies View Related

Including MTD And YTD Figure With A Daily Figure

Feb 4, 2008

Hello,
I have a query that returns a daily revenue figure. The query is as follows:

SELECT top 1000
ds.AcctCode,
ds.TxnDate,
SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue,
--"MTD" = ?,
--"YDT" = ?,
ps.TC,
CASE
WHEN ps.Proj = 100 THEN 'New Account'
WHEN ps.Proj = 200 THEN 'Current Account'
END AS ProjStatus,
ps.FSR,
ps.SubmitRep1

FROM
TxnRptg.dbo.tbl_DailySummary ds
INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps
ON ds.AcctCode = ps.Acct

WHERE
MONTH(ds.TxnDate) = 1
AND
Proj IN (100,200)
AND TC = 'HV'

GROUP BY
ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1

ORDER BY
ds.AcctCode, ds.TxnDate

--*********************************

TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?


Thank you for your help!


cdun2

View 5 Replies View Related

What Is Suitable To Learn DBA Or Oracle ?

Jul 16, 2003

Hi:
AS a newly Master degree graduate student in IT field. My M.S. degree training has been emphasized in database developer, Web developer, Web/database developer, network, and network and computer security.
My personal interesting are database and web. have experienceon java servlet and database(create a web site), try ASP and dataabase now, but I don't have any work experience(come from other country and change major)

hope can get advice from kindness prople what is suitable to learn DBA or oracle ? conside about difficult level, cost, and suite for future job market

Thank you

View 5 Replies View Related

What Is The Problem And Suitable Solution In This Case

Dec 3, 1998

Hi,

I am trying to dump-load a db from one server to another.I don't have any
problem with the dump and load , the problem is with scheduling the copying of
the dump file fron server1 to server2.

I tried using xp_cmdshell 'copy filename filename ' to schedule it but it
says "access denied" zero files copied...(using sa account and standard
security)
But when I telnet to server1 and issue copy filename filename it works fine.
how can I correct this problem and schedule the dump-copy-load??????

View 1 Replies View Related

Mirroring Suitable For Version Update?

Jun 11, 2007

In the near future I need to move a 27GB database from SQL2000 to SQL 2005 that is located in a different data center. I can do a backup, copy the .bak file over the WAN and do a restore, but that will take several hours over the WAN. I think the new server will be standalone, not a cluster. We have not purchased it as yet.



What options do I have to minimize downtime? I don't think I can log ship between SQL versions, but I might be able to run two instances of SQL on the new server (2000 and 2005), although I consider that an iffy proposition as well. The OS would be Win2003 sp2 on the new server. What other options do I have? Would mirroring work between versions? Thanks for any suggestions.

View 3 Replies View Related

Suitable Topology For SQL Server Replication

Mar 15, 2006

Hi,

I am given with the responsibility to set up database replication in my organisation.

Before starting the replication set up, I would like to know which is the best suited replication for my application.

My application is much process oriented, and the users are working on records parallely(but with different records).

Say for ex:

There is a module for Insurance claim processing, it is handled in the following manner:

There are many employees with CSR roles, and all are working on some set of records to be processed. But No two employees should get the same record for processing. For that, we are doing locking of the cases by having one column called 'LOCKED_STATUS' and setting value as LOCKED. So that other users wont get the same case. Once the case is processed, the record is marked as unlocked.

My analysis for replication is as follows:

1. Snapshot replication: This wont be a suitable type of replication as the updations are to be immediately updated on all the subscribers, because when the user executes select query to get the next unlocked case, No 2 user should be able to get the same case.

I think, the best replication to chose from is, Transactional replication with Immediate Updation/Queued updation. But the problem is, when one of the subscriber goes offline, the updations are queued up. But in my case, say for ex. One case is updated as locked, but the subscriber is offline and the updations are queued up. At the same time, the other subscriber can fire an select query to get the next unlocked case. Since the update is on queue, there are possibilies for this subscriber to get

the same case.

Can anyone let me know, are the select statements also queued up(i.e., along with update and insert statements.)? If not please let me know how to approach for this problem.

Thanks in advance.

Prashant N M









View 1 Replies View Related

How Do I Write A Query (which Uses A Union) Suitable For Paging

Aug 13, 2007

How do I write a query (using a union) suitable for paging
I currently have a query which results from two queries combined by a union. It returns 3000 rows. I want to replace that query with one which returns only 50 rows from the relevant page [using ROW_NUMBER()].
Is there a way to do this with only 1 CTE or will I need to use two consecutive CTEs [the first get a union on the queries and the second to apply ROW_NUMBER()]. I can't see a way of doing it with only 1 CTE table.
 Alternatively is there a very efficient way to do it using derived tables?

View 1 Replies View Related

SQL 2012 :: Is Table Compression Suitable To Reduce I/O

Feb 11, 2015

I have huge table, often I need to query the table on the whole; ie., data may required at any part of the table. So I think of to compress the table with the aim of reducing I/O.

1. is that really suitable for this requirement? If So which compression is good (Row or page )?

2. Or is there any other alternate to reduce the I/O (Except Indexing)?

3. Pros and cons ?

View 2 Replies View Related

To Create Invoice Report Which Control Is Suitable

Jul 11, 2007

I need to create an invoice report, each page represents a single invoice. like a form report.



Which control is good, i am new to reports. i have used table control for few of our reports still learning.



Thank you very much for your help.

View 1 Replies View Related

Suitable Laptop Spec For Running SQL Server Enterprise Edition

Mar 17, 2008



Hi Guys!

I want to buy a laptop, so that I can install and play with the trial SS 2005 Enterprise Edition and hopefully get familiar with SSIS. At work I have only used SS 2000. I want a laptop which will run this with a reasonable speed, but don't want to pay for more juice than is actually required. Does anyone have any advice on what kind of spec would be good enough for SS 2005 Enterprise? If I go for the basics suggested by the system requirements, will it run like a tortoise?


Thanks!!

Clare

View 3 Replies View Related

Any Structure On Sql Server Like WITH ... SELECT Structure On DB2

Jul 20, 2005

Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh

View 3 Replies View Related

HELP! Can't Figure This One Out!

Jan 19, 2004

I'm in desperate need of help. I'm setting up an intranet portal using DNN. I added an event calendar module, but whenever I try to add events to it, the system rejects it with a nasty Sql exception saying the conversion from char to datetime produced an out of bounds result.

The string the table uses to convert to datetime is (I have not modified it, the module is exactly as it came when i downloaded)

(convert(varchar,getdate(),101))

The whole stack trace for the error is:

Stack Trace:


[SqlException: La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.]
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +642
System.Data.SqlClient.SqlCommand.ExecuteReader() +11
DotNetNuke.AVCalendarDB.Save() +1067
DotNetNuke.AVCalendarEdit.updateButton_Click(Objec t sender, EventArgs e) +3367
System.Web.UI.WebControls.LinkButton.OnClick(Event Args e) +108
System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138
System.Web.UI.Page.ProcessRequestMain() +1263

At first I thought it could be a language issue (DNN and the module are in english and my system runs XP Pro in Spanish) but I discarded it since it didn't work when I installed XP Pro in english

Any ideas?? I would really appreciate your help
Best regards from Chile
Javier L.

View 5 Replies View Related

Please Help Me Figure This One Out.

May 1, 2008



Hi, I am somewhat new to t-sql and what I do know is from trial & error and help from this forum. What I need to do is add a column of counts for id numbers that are not unique.

This is my code:

ALTER procedure [dbo].[rptMgmtRpt]


@RptMonthBegin datetime,

@RptMonthEnd datetime


as




SELECT Summons.ID, Convert(varchar, Venire.VenireDateTime,101)as VenireDateTime, Venire.VenireStatusID, Summons.SummonsStatusID, Summons.ExcusalCodeID,

Venire.ID AS VenireID, { fn WEEK(Venire.VenireDateTime) } AS wkNum, { fn DAYOFWEEK(Venire.VenireDateTime) } AS wkDay, Venire.JuryTypeID,

Venire.VenireLocationID, summons.compensationTypeID

,{fn IFNULL(AccountingTransactionRequest.PaymentAmount,0)} AS payAmt

,Convert(varchar,AccountingTransactionRequest.ServiceDate,101) as AcctServDate

,{fn count(summons.id)}

FROM Venire INNER JOIN

Summons ON Venire.ID = Summons.VenireID

LEFT outer JOIN

AccountingTransactionRequest ON Summons.ID = AccountingTransactionRequest.SummonsID

Where Venire.VenireLocationID <> 16 and (VenireDateTime BETWEEN @RptMonthBegin AND @RptMonthEnd)

ORDER BY wknum, wkday , Summons.ID

This is a sample of what I get for results:

640404 11/5/2007 128 144 2866 45 2 8 0 11/5/2007
640888 11/5/2007 128 32 2866 45 2 8 0 11/5/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/5/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/6/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/7/2007
641533 11/5/2007 128 32 2866 45 2 8 0 11/5/2007
641762 11/5/2007 128 144 2866 45 2 8 0 11/5/2007
641851 11/5/2007 1024 2 2866 45 2 8 0 11/5/2007




What I need are results that look like this:

640404 11/5/2007 128 144 2866 45 2 8 0 11/5/2007 1
640888 11/5/2007 128 32 2866 45 2 8 0 11/5/2007 1
640979 11/5/2007 32 2 2866 45 2 112 15 11/5/2007 1
640979 11/5/2007 32 2 2866 45 2 112 15 11/6/2007 2
640979 11/5/2007 32 2 2866 45 2 112 15 11/7/2007 3
641533 11/5/2007 128 32 2866 45 2 8 0 11/5/2007 1
641762 11/5/2007 128 144 2866 45 2 8 0 11/5/2007 1
641851 11/5/2007 1024 2 2866 45 2 8 0 11/5/2007 1


In the first column, 640979 appears three times.
How do I get that count in the last column of my results ?

Thanks so much for your help!!!

View 4 Replies View Related

Cannot Figure Query Out

May 2, 2008

I have a small database in which Employee's are linked to "Tags".   These tags can be red or yellow.  So,  I have 3 tables.
Tag_Colors :    tag_color_ID   ,    tag_colors
Employee_Table:  employeeID    ,      employee_name  
Tag_Table:      tagID,    tag_color_ID,   employeeID,   tag_notes
 
I need to be able to perform a query, where I can list employee's by the number of tags they have.
I don't even know how to get started on this  --     could anybody point me in the right direction?
After I can et a query working,  I think I can take it from there  -- and get it to display on a web page.
 
Thanks in advance. 
 
 
 
 
 
 
 
 

View 4 Replies View Related

Who's Smart Enough To Figure This Out?

Mar 9, 2004

Hello All,

I've been trying to get a range of values out of my SQL Server 2000 db without sucess. The field in question has the data type of char(8) and looks like this:

House_numbr_pub (leading spaces in front of each value)
140A
140
141
142
143
144
145
146
147
148
149
150
151
.
.
.
14500
.
.
.

Does anyone know how write a sql statement that will return 140-150, but excluding the ' 14500' and 100-1000. I tried the following where clause to return a range between 100-1000.

WHERE (cook.STREET_PUB LIKE 'lincoln%') AND (LEN(LTRIM(cook.HOUSE_NUMBR_PUB)) BETWEEN 3 AND 4) AND (
(LTRIM(cook.HOUSE_NUMBR_PUB) >= '100') and (LTRIM(cook.HOUSE_NUMBR_PUB) <= '1000') )

This where clause only return two records (100 and 1000). I want it to return 100-1000.

I also tried the following where clause:

WHERE LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'
OR
LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'

However, building this on the fly with .net will take some effort if someone is trying to search range 1-10000000.


Please Help,

James

View 3 Replies View Related

Can't Figure Out This Query

Feb 21, 2007

I have a table with multiple records.

Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.

So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

So the tables looks sort of lLike this:

ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3

I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

I can not figure this out. :eek:

Any help would be GREATLY appreciated.

View 3 Replies View Related

Figure Is Rounding Up

Mar 13, 2007

I have the following sp which is appending records into my table. However the values appended are being round up eg

SC_PrimaryPupilPrice is 1.5

but when it is inserted into the sql table it is 2

The field in the sql table is numeric.

CREATE PROCEDURE spSM_AddWeeksandMealPrices

@dteWeekEnding datetime

as

INSERT INTO tblSM_Meals
(ML_Id,
ML_WeekEnding,
ML_SchoolNumber,
ML_PupilMealPrice,
ML_AdultMealPrice,
ML_SpillagesMealPrice,
ML_AdultRechargeMealPrice,
ML_ReservedMealPrice)

select convert(varchar,@dteWeekEnding ,103) + '*' + cast(SC_SchoolNumber as varchar(10)) , convert(datetime,@dteWeekEnding ,106),
SC_SchoolNumber,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryAdultPrice
from tblSM_Schools
GO

View 4 Replies View Related

Can't Figure Out This Error!

Jun 8, 2007

Here is my code below. When I attempt to run the data flow task that calls this script, I get this error:



"Index was outside the bounds of the array."



I honestly do not know what the problem is here. There are definitely 6 columns in the file. In fact, even if comment out everything except the first line (myCol1), I still get the "Index was outside the bounds of the array." error.



Any ideas??? Need help.



Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim rowValues As String()

rowValues = Row.Line.Split(columnDelimiter) 'parse row by comma

If Row.Line Like "*OPENING BALANCE*" Then

Row.AccountNumber = Nothing 'set to null for conditional split

Row.OpeningBalance = CDec(rowValues(3)) 'get the 4th value



ElseIf Row.Line Like "*CLOSING BALANCE*" Then

Row.AccountNumber = Nothing 'set to null for conditional split

Row.ClosingBalance = CDec(rowValues(2)) 'get the 3rd value



Else

Row.myCol1 = CStr(rowValues(0)).Replace("""", String.Empty)

Row.myCol2 = CStr(rowValues(1)).Replace("""", String.Empty)

Row.myCol3 = CStr(rowValues(2)).Replace("""", String.Empty)

Row.myCol4 = CStr(rowValues(3)).Replace("""", String.Empty)

Row.myCol5 = CStr(rowValues(4)).Replace("""", String.Empty)

Row.myCol6 = CDec(rowValues(5))



End If

End Sub

View 10 Replies View Related

New On Asp.net 2.0 Trying To Figure Out How To Connect To A Database

Mar 22, 2007

Right now I'm just looking at websites how to create a database and putting it in the App_Data folder which is no problem.  What I want to do is Programmatically do it.  in the example code that I see it says:
Dim ConnString as string = ConfigurationManager.ConnectionStrings(connStringName).ConnectionString
or
Dim MyConnString as new sqlconnection = New SqlConnection(ConfigurationManager.ConnectionStrings(connStringName).ConnectionString)
now every time I put the connStringName in the parenthasis it tells me that its an error: Name connstringName is not declared.
I figure the connstringName is the name in the connectionStrings in the web.config.
 So I am doing something wrong here, I would appreciate any help.

View 1 Replies View Related

Can't Figure Out Error Message In SQL

Oct 16, 2007

Can someone please look at my stored procedure? I am trying to create the following stored procedure, but get the following errormessages:
Msg 102, Level 15, State 1, Procedure InsertWork, Line 3Incorrect syntax near '7'.Msg 102, Level 15, State 1, Procedure InsertWork, Line 25Incorrect syntax near '@7am8am'.
USE [Work]GO
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[InsertWork](  7am8am       nvarchar(500),  8am9am       nvarchar(500),  9am10am       nvarchar(500),  10am11am      nvarchar(500),                  11am12noon    nvarchar(500),                12Noon1pm     nvarchar(500),  1pm2pm       nvarchar(500),  2pm3pm       nvarchar(500),  3pm4pm        nvarchar(500),  4pm5pm       nvarchar(500),                  5pm6pm       nvarchar(500),  6pm7pm       nvarchar(500),  7pm8pm       nvarchar(500),  8pm9pm        nvarchar(500),  9pm10pm       nvarchar(500),                  10pm11pm      nvarchar(500),  Notes         nvarchar(500),         Date           nvarchar(15))AS BEGIN INSERT INTO WorkDay VALUES                 @7am8am,                 @8am9am,  @9am10am,   @10am11am,   @11am12Noon,  @12Noon1pm,  @1pm2pm,  @2pm3pm,  @3pm4pm,  @4pm5pm,  @5pm6pm,  @6pm7pm,  @7pm8pm,   @8pm9pm,  @9pm10pm,  @10pm11pm,  @Notes,  @DateEND
 

View 3 Replies View Related

I Need A Very Good SQL Genious To Help Me Figure Out How To Do This....

Sep 15, 2005

I have a simple table right now that has some rows listed like this:Table Name = TicketStatusTicketNumber      TicketType        Status         Time1                             Normal               In                 09/15/2005 10:50:213                             Normal               In                 09/11/2005 19:25:101                             Normal               Out              09/15/2005 11:45:103                             Normal               Out              09/11/2005 20:27:092                             Normal               In                 09/14/2005 17:25:101                             Normal               Pay              09/15/2005 11:15:152                             Normal               Out              09/14/2005 21:45:30What I want to do is select only 1 row per ticket number, and this row needs to be the row that has the LATEST time for that particular ticket number.  Then I want to sort the results by ticket number decending.  So for instance, the select I am looking for would bring me back ONLY the following rows in the following order: TicketNumber      TicketType        Status         Time3                             Normal               Out              09/11/2005 20:27:092                             Normal               Out              09/14/2005 21:45:301                             Normal               Out              09/15/2005 11:45:10My issue is I do not know how to go about selecting ONLY 1 row per ticket number, and the row I select has to be the row with the latest date for that particular ticket number.Can any SQL gurus provide me with some code in order to do this?  Thanks so much for the help guys!

View 1 Replies View Related

Invalid Column Name...can't Figure This Out

Feb 25, 2005

The following is an approximation of the code I'm trying:

SELECT 'dummy' as DummyField, FieldA, FieldB, FieldC,
FieldD, FieldE, FieldF, FieldG, FieldH, FieldI,
FieldJ, FieldK, FieldL, AVG(AMT) AS AMT,
AVG(QTY) AS QTY, FieldM

FROM GetMonthlyData('200501') as i

GROUP BY DummyField, FieldA, FieldB, FieldC,
FieldD, FieldE, FieldF, FieldG, FieldH, FieldI,
FieldJ, FieldK, FieldL, FieldM

It checks out OK syntactically but whenever I try to run it I get a message like the following:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DummyField'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DummyField'.

I've also tried not giving DummyField an alias and using the 'dummy' value in the GROUP BY clause but that won't even get by the CheckMark button:

Server: Msg 164, Level 15, State 1, Line 8
GROUP BY expressions must refer to column names that appear in the select list.

:confused: Advice?

View 8 Replies View Related

How To Join Data - Can't Figure This One Out

May 22, 2008

Hi -

I have three tables that I am trying to join in order to produce some report output. I have tried numerous ways, but they all don't give the results I need to provide.

Below is the table creation, some inserts for data and the output I am looking for.

CREATE TABLE [SalesAcct]
(
[EmpName] [varchar] (100),
[Acct_Num] [varchar](15),
[Acct_Name] [varchar] (50)

)


CREATE TABLE [Perf]
(
[Acct_Num] [varchar](15),
[PerfDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[Clicks] [int],
[Impressions] [int]
)



CREATE TABLE [Revenue]
(
[Acct_Num] [varchar](15),
[RevenueDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[ServiceLevel] [varchar],
[TransactionType] [varchar],
[GrossRev] [decimal](18, 2)
)

INSERT INTO SalesAcct (EmpName, Acct_Num, Acct_Name)
VALUES ('BobSmith', '100XYZ', 'My Account')

INSERT INTO Perf (Acct_NUm, PerfDate, Channel, Medium, Clicks, Impressions)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net', 240, 500)

INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net',500.50)
INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Cable',23.75)



What I need to provide to mgmt is two reports...one is rolled up by EmpName and AcctNum and Date, and the other is details like:

Suumary_Acct_By_Day:
EMPName | Acct_Num | Acct_Name | Date Rev $ | Clicks | Impressions
BobSmith| X0000005 | My Account | 2008-05-11 |524.25| 240500


Detail_Acct_By_Day:
EMPName | Acct_Num | Channel | Medium | Date | Rev $ | Clicks | Impressions
BobSmith | X0000005 | US | Net | 2008-05-11 | 500.50 |240 | 500
BobSmith | X0000005 | US | Cable | 2008-05-11 | 23.75 | 0 | 0

My latest query, which doesn't work is:
select S.EmpName, S.acct_num, R.RevenueDate as Date, coalesce(R.Channel, P.channel)as channel,
coalesce(R.medium, P.medium), GrossRev, coalesce(P.clicks, ''), coalesce(P.impressions, '')
FROM SalesAcct AS S
left outer join Revenue as R on S.acct_num = R.acct_num
left outer join Perf as P on S.acct_num = P.acct_num

Yields two rows...but the Medium duplicates and the Clicks and Impressions also get duplicated when they should be zero.

Any help would be appreciated.

Thank you,
Will

View 4 Replies View Related

Complicated Query? Cant Figure Out What I Need...

Jan 3, 2006

I have run into a problem, I have 2 fields in my database, both key fields:

Table 1
=====
Field X <key>
Field Y <key>

In field X, there are say about 3 records for each unique Field Y. I let my users query the data base like follows:

Enter the Codes you want: 1000 and 3000 and 8500

So I want to pick up records where there will be the above values for All Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there is even ONE of the X values not matching a record without a matching X value, leave it out.

i.e:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

When the query runs, I want to see the following records:

X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB

BUT NOT:

X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

because one of the X values was not matched (the last X value =9999 and not one of the requirements of the search)

So I guess I want something like this:

SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES (X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD

^^ Hope the above makes sense... but I am really stuck. The only other way I think I could do it is, copy all records that match all 3 X values into a temp table, and weed out any that are missing any one of the X values after they are copied but, I am running this on MYSQL 5.0 Clustered, and there is not enough room in memory for it probably... and query time has to remain under a second.

Anyhelp would be appreciated...

View 5 Replies View Related

Calculation For Accumulated Figure

Feb 27, 2008

I've created SQL statement to display data that include Project ID, WBS Element, DebitTotal, CreditTotal, Net (net figure is sum between DebitTotal and CreditTotal = CreditTotal - DebitTotal), FiscalPeriod and FiscalYear. To display data I need to key-in the timekey. If 200702 refer to data for February 2007.

The problem is, how to calculate accumulated figure for Net? I have to display YearToDate figure which is accumulated figure for every month for Net table.
e.g Net for February is 1000, March is 2000. YearToDate should be 3000 (1000+2000).
Another problem is because I'm using the timekey to display the data, table name is the same for every month.
How to accumulate the Net figure? Please help.

View 3 Replies View Related

Substring Function Can't Figure Out

Mar 5, 2008

I have a SQL query in Visual Studio (SSRS). I have a GL Account field that is formatted such as 100-400-123-1234. I wanted to use the substring function to pull out the second set of numbers which I can assign a location:

CASE WHEN substring(GlAccount,5,3)= '400' THEN 'Gainesville'
CASE WHEN substring(GlAccount,5,3)= '401' THEN 'Aledo'

I tried this and it comes back with syntax errors. Can anyone tell me how to approach this as I have a list of about 35 locations that I need to do like this. Thanks.

View 10 Replies View Related

Baffled! Can't Figure Out How To Do This Query. Is It Even Possible?

Jan 13, 2006

I have an "Issues" table for my technicians. An issue can be on "hold"or "assigned".I want to get a count for each tech with a column showing number ofissues on hold and a column for number of issues assigned. It wouldlook like this --Tech Num_Assigned Num_On_HoldFred 3 10Carol 6 7I can get each column separately, but I want both in the same answertable!Is that too much to ask??? :)

View 2 Replies View Related

Simple Query, But Can't Figure It Out

Jul 20, 2005

I'm sure this is simple -- I'm just spacing it today. Assume the following:freq fruit---- ----10 apple5 apple7 banana6 pear3 pear2 orange8 orange13 kiwiHow do I find the highest [freq] for each [fruit] all at the same time, e.g.:10 apple7 banana6 pear8 orange13 kiwiThanks in advance.Mark D.

View 3 Replies View Related

Need Help To Figure Out This SqlQueryNotificationService Error.

Mar 30, 2008

I found every several minutes, sometimes one or twice in an hour, I still get a dozen error like below logged in SQL Log. See the error is "You do not have permission to access the service". I found some articles on other errors, but nothing about this error. I want to get more information on this, and a way to trace what is the permission about and which user doesn't have the permission.


Source spid26s

Message
The query notification dialog on conversation handle '{E6FC299F-8BFE-DC11-A4E1-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service &apos;SqlQueryNotificationService-9ff8b39d-90a8-45bc-83a7-23837920774d&apos;.</Description></Error>'.



thanks

View 7 Replies View Related

Trying To Figure Out Deadlock In SQL 2005

Oct 11, 2007



I have not turned on DBcc TRACEON (1222, -1) yet because of this message in BOL:
Use DBCC TRACEON ( trace# [, ....n], -1 ) only while users or applications are not concurrently running statements on the system.

I am running the profiler with "Deadlock Graph" but I'm not sure how to use the information.


It would be nice if it would say "this sql statement blocked this sql statement". Any advise on where to start?

View 5 Replies View Related

How Do I Figure Out Where SSIS Logs Are?

May 4, 2006

Dear all,

I was wondering how to open .LOG files created for a SSIS execution package.

Thanks in advance,

View 3 Replies View Related

SQL Help - How To Figure Out Consecutive Workdays With Holidays

Jul 18, 2006

Hi everyone,
I'm hoping someone can help me with some sql statements.  I have a temp table that contains 30 dates that a student has missed in the last year.  I also have a holiday table of when training was not available.  I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table).  I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks in advance,  Jessica
 

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved