DB Design :: Summing From Two Tables Then Performing Operation With 2 Sums

Jun 17, 2015

I am using sql server and I have a table called accnt with the fields ven1 and amnt1 and a table called acc1167 with fields ven, job#, and amnt. for this example these tables look like this

       accnt                          acc1167
    ven1     amnt1              ven     job#   amnt
    1167     100                1167     1      200     
    1152     50                  1167     2      300
    1167     110                1167     3      100
    1167     300                1167     4      200
    1252     1050              1167     5      200
    1167     210                1167     6      150
    1167     1150 
    1167     130 
    2113     800 
    1167     550
    1167     1200

I need to sum amnt1 for all the records in accnt with the ven1 of 1167, we will call this sumA. Then sum amnt in acc1167 for all records, we will call this sumB. next I need to divide sumB by sumA to get a ratio. finally I need to multiply each amnt value from acc1167 by the ratio and get a number that will then replace the acc1167 amnt value.

for example, sumA = 3750, sumB = 1150. taking these values, sumB/sumA = 0.307. I then replace every value in acc1167 amnt with 0.307*itself, so the final table should look like this:

          acc1167
    ven   job#    amnt
    1167   1      61.4
    1167   2      92.1
    1167   3      30.7
    1167   4      61.4
    1167   5      61.4
    1167   6      46.05

i have tried to use the sum function and and some insert, but i am very new to SQL and have never used sum before and don't know how to call from multiple tables, or how to store a ratio. Ive tried this:

    UPDATE     acc1167
    sum1 = sum amnt1 where ven1 = '1167'
    from accnt
    sum2 = sum amnt
    from accnt
    SET        amnt = sum2/sum1*amnt
    FROM       acc1167

View 2 Replies


ADVERTISEMENT

Performing String Operation

Mar 29, 2008

Hi,

I have got a column with the string "I too Love Sql Server". I am trying an output as shown below:

I want to divide this into 4 columns :

Column1 Column2 Column3 Column4
I too Love Sql Server I.t.L.S.S

The Column 4 contains the first letter of each word. If the string contains only 2 words, Eg: "Sql Server" then Column 2 will be empty and only Column 1,3 and 4 will be filled.

How do I do this?

View 2 Replies View Related

Connecting For Entire Session, Or Just When Performing An Operation?

Jul 23, 2005

Here's a simple question for you:Is it better to maintain a single open connection to the database for theentire duration of the users session, or to connect, perform an operationand then disconnect each time the user wants to do some work, within thatsession?If so, why?Thanks,Robin

View 3 Replies View Related

The Permissions Granted To User 'mydomainmyuser' Are Insufficient For Performing This Operation.

Mar 8, 2006

Hi,

I am trying to deploy a reporting services report to remote reorting services server , i did the following :



in project property : http://192.xxx.xxx.xxx/reportserver

(NB: my pc and server are on the same domain)

and i assigned a permission for my user 'mydomainmyuser' on the virtual folder of the reporting service, but i got this error :

The permissions granted to user 'mydomainmyuser' are insufficient for performing this operation.I

I know it is a permission pb but please any idea to solve that ??

Thanks,

Tarek Ghazali

SQL Server MVP

View 6 Replies View Related

Error RsAccessDenied : The Permissions Granted To User '' Are Insufficient For Performing This Operation.

Mar 20, 2007

I get an error message when deploying reports to the reportserver from microsoft visual studio.

error message : Error rsAccessDenied : The permissions granted to user '' are insufficient for performing this operation.

TargetServerURL : http://server.com/ReportServer$sql_2005

The Report server is configured to use a custom security extension. i can access the reportserver.

It looks like when i deploy the reports from VS. it does not pass any credentials to the report server. From the error message it looks like there is no username . How do i deploy reports to report server if we are using a custom security extension. How do i grant user rights to deploy report if we are using a custom security extension. Any idea. Thanks!



chi



View 4 Replies View Related

The Permissions Granted To User 'domainusername' Are Insufficient For Performing This Operation. (rsAccessDenied)

Jan 21, 2008



Hi,

I wonder if anyone can help me?

I have installed and configured sql server 2005 express edition with advanced services on an xp box, with the aim of using reporting services.

I have set up reporting services successfully using the report manager, with my web services id = netwrokservice.

I can access both the report manager and report url locally, i.e while using localhost anjd on the machine. The problem arises however when i try to access

the reports from another machine, as i get the message:

"The permissions granted to user 'domainusername' are insufficient for performing this operation. (rsAccessDenied)"

when going to for example:

"http://servername/ReportServer/Pages/ReportViewer.aspx?%2fReport1&rs:Command=Render"

I have looked at various sites (including microsoft) in an attempt to find a solution to this and most sites give exmaples of how to configure the full

version of reporting services which i do not have. The things that I have managed to gleam and try are:

- open up security on dir: C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportServer to everyone and allow all access
- added my own username as a new role assigment in the report manager page
- added my own username to the reportserveruser and reportingserviceswebservicesuser roles, as indicated by microft:

http://msdn2.microsoft.com/en-us/library/ms365166.aspx, who state that "Custom authentication extensions and custom role assignments are not supported. You

must map existing Windows domain user and group accounts to predefined role definitions."

Can anyone suggest where to look next as I cannot believe that this should be this difficult?

Many Thanks in advance

View 3 Replies View Related

The Permissions Granted To User 'Domainuser' Are Insufficient For Performing This Operation. (rsAccessDenied)

Apr 25, 2008

Hi, I've scoured the forums and tried just about every answer to this problem without any success. What I have is SQL Server 2005 installed on a single machine, and trying to deploy reports to the same machine for testing purposes. When I try to deploy the report I receive the permissions error. Here's what I've gotten to at this point:


I can get to the reports URL (http://machine/Reports)

I can login to Reporting Services via SQL Server Management Studio as the administrator. (Windows Authentication)

I have added my login to the Home Folder-->Permissions in SQL Server Management Studio, and all reports are set to inherit the home folder's permissions

In Reporting Service Configuration Manager the Windows Service Identity is set to Local System, and for web service identity - ASP.NET Service Account is set to NT AuthorityNetworkService; Report Server and Report Manager are set to DefaultAppPool.

In IIS I tried enabling Anonymous Authentication

Current Application pools are set to "Classic" for Managed Pipeline mode (I did this to actually be able to login to Reporting Services via SQL Server management studio; having this set to integrated gives me an error)
What gets me is that this is entirely self-contained on one machine, that I set up as an administrator. So why do I get a permissions error when I am the admin on this machine deploying to this machine?

View 17 Replies View Related

The Permissions Granted To User &&<MachineName&&>ASPNET' Are Insufficient For Performing This Operation.

Jul 25, 2007

Hi,



I wanted to deploy my Sql 2005 reports to my local machine and want it to get viewed by all the users through Asp.Net application.

User can directly view the reports by clicking on the direct link to report but when they try to view it using application having Report Viewer, running on iis it gives acess denied 401 error and can't view the reports

If I make my application to run on default port then it works fine

If I give rights to <particularMachineNameASPNET> then that user can view the report but if there are 100 users then will i Add 100 such entries???

I think I am making mistake in this case.

Any response will be appriciated.





-Thanks,

Digs

View 6 Replies View Related

The Permissions Granted To User 'DomainUser' Are Insufficient For Performing This Operation. (rsAccessDenied)

Apr 15, 2008

i finally got SSRS(SQL Server Reporting Services) to install properly and and it was working before i had to restart my PC
after restarting my PC i went back to the Domain/Reports$SQLExpress and the menus where Blank it just showed the help link and the border. so i opened the Domain/ReportServer$SQLExpress and it said


The permissions granted to user 'DOMAINOwner' are insufficient for performing this operation.(rsAccessDenied) Get Online Help
and there is no Login Box Popping up How do i get it to Login - Its set to use Windows Authentication what sould i doo please help ive had one problem after another with this SSRS

View 2 Replies View Related

Software For Performing Mass Design Changes

Jun 28, 2001

Hi,

I was wondering if anyone knows of any way, including third party tools, to replicate a design change on a table across many different databases. I have written an ASP script that allows me to copy multiple tables to multiple databases in one go but I need something that will allow me to replicate the design of a table by comparing source and destination tables. So far I have scripted most of it but I have no idea on which system table to get the identity information and it seems there must be an easier way!

Any help would be appreciated,

Seoras

View 1 Replies View Related

Two Sums From Different Tables

Jul 29, 2004

I have this query:

select Customers.CustomersID, Customers.name, sum(table1.amount), sum(table2.amount)
from Customers, table1, table2
where Customers.CustomerID = table1.CustomerID
and Customers.CustomersID = table2.CustomerID
group by Customers.CustomersID, Customers.name

I am trying to make a query to report the total amount of two different things from two different tables by customer. the problem is that the total amount from table1 doubles if there are two rows of that customer in table2. My guess is that I have to group the things in a different way, but I don't know how.

Any suggestions?

View 1 Replies View Related

An Error 1069 - )The Service Did Not Start Due To Logon Failure) Occurred While Performing This Service Operation ...

Oct 10, 2007

Hi All,

We seem to be being plagued by the error below by our SQL Server agent. This happens almost everytime we restart the server that has been running for a day or two.

Our SQL Server Agent uses a none expiring domain credential. I understand that this problem only happens when the profile being used by the SQL Servr Agent has changed (password change). What puzzles me is that the login is A ok and no changes has been made to it's password.

We always resolve this problem by changing the login used in the SQL Server Agent to local and after that, returning it back to it's original domain login. Unfortunately, we cant always do this everytime something goes wrong.

Can anyone please help us shed a light on this? We're using SQL2k with SP3a. Thanks!

Error:

An error 1069 - )The service did not start due to logon failure) occurred while performing this service operation on the SQLServerAgent service.


Regards,
Joseph

View 29 Replies View Related

Summing From Multiple Tables

Jul 20, 2005

We recently added a new database at the company. It has only onepurpose - to hold massive amounts a daily data generated by telephonecalls on a network.The amount of data was so large (several gigabytes a day) that the guywho set up the database creates a new table for it each day.His thinking was that if we only need to query one day's worth of datathen it would be a lot faster to query a table with one day's datathan having to query many days of data in one table.I see his reasoning. Any comments or alternatives to this schemewould be appreciated.Here's the question though...I'm writing a front end for this and waswondering if the most efficient way to query and sum data acrossmultiple tables (days) is in the form of the following statement.Suppose three days of worth of data are wanted:select sum(ET) from (select sum(vc_elapsed_time) AS ET fromswitch2030608 where init_cell_info_cell = 196 union all selectsum(vc_elapsed_time) as ET from switch2030609 whereinit_cell_info_cell = 196 union all select sum(vc_elapsed_time) as ETfrom switch2030610 where init_cell_info_cell = 196 ) tIn my front end, based on user input, I plan to keep extending thisstatement with more union alls. Is this the best way to implement thegoal of this query?-David

View 1 Replies View Related

Summing Across Reporting Tables

Apr 14, 2008

Hi, I'm converting an existing report to an RDLC report using VS2008 C#. I'm a relative newbie to creating RDLC files, and I'm having a problem with one part of the report. I have three Table controls on the report. Two show detail breakouts from different data sources and the third Table is supposed to show some grand total information by summing the totals of the first two tables. Since it appears that a table can only be bound to one datasource, I can't seem to figure out how to get a sum by peaking into the totals of the first two tables. How can I do this?

Thanks,

Ian

View 4 Replies View Related

Performing A Query Using Two Tables

Jul 20, 2005

Hi,Have a database that contains various tables.I need to run a query on two tables.Table A contains a column called TitlesTable B contains a column called Uni_TitlesIn Table B's column it contains multiple titles as shown belowseparated by semi-colons in each row.Table B Uni-Title Column__________________________Row 1: Landlord and tenant; Leases; Rent reviewsRow 2: Acquisitions; Advisers; Appointment; Contract termTable A - Titles Column_________________________Table A's column contains a only one of the words in Table B's columni.e. Landlord and tenantI would like to obtain a count of how many times the same word orphrases appears in the column of Table B.ThanksSteve

View 2 Replies View Related

Performing A Range Lookup Using Two Tables

May 16, 2006

Hello.

I have a car table, whose rows contain cars and their respective weight:

Ex: (1, 1000), (2,1100), (3, 900) etx.

I also have a car class table with the classes cars can fall into, based on a lower bound and an upper bound in which the car's weight must fit.

Ex: (Class1, 0, 999), (Class2, 1000, 1499), (Class3, 1500, 1999), etc.

I need to match each car to it's respective class. I've already search the database for post on this subject, but unfortunatelly my goal is yet to be reached. Can someone help?

Thanks in advance,

Hugo Oliveira

View 6 Replies View Related

Get Tables Involved In Operation

Jun 17, 2014

Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.

View 2 Replies View Related

SQL 2012 :: Current Operation Cancelled Because Another Operation In Transaction Failed

Nov 20, 2013

I'm using SQL Server 2012 Analysis services in Tabular mode and connected to Oracle Database and while importing, I'm getting below error after importing some rows.

OLE DB or ODBC error: Accessor is not a parameter accessor.. The current operation was cancelled because another operation in the transaction failed.

View 1 Replies View Related

Tables Design Help

May 13, 2007

I used to get flat files which i need to import into tables..
The flat files data which contains.....

How should i design the tables so that i can get the output in single row...

Table1ForRow1
--------------
col1,100
col2,1
col3,xx
col4,yy
col5,,,
col6,20030101

Table1ForRow2
-------------
col1,100
col2,2
col3,20030101

Table1ForRow3
------------
col1,100
col2,3
col3,01
col4,20030101

FlatFiledata:
------------
100,1,xx,yy,,20030101
100,2,20030101
100,3,01,20030101

I want the output:
100,1,xx,yy,20030101,2,3,01

View 1 Replies View Related

Design -- Should This Be Split Up Into A Few Tables?

Feb 5, 2005

I'm grappling with this design problem right now:

I have a table of users. Every user has an e-mail address and (hashed) password. Some of those users work for a company, and some of them do not. Of those who do not work for a company, some are salespeople who sell to one or more companies. Some users are simply administrators who don't work for a specific company. So here's what my users table looks like right now: "UserID, Email, Password, CompanyID (Nullable), IsAdmin"
And here's my companies table: "CompanyID, CompanyName, SalespersonID"

Of course, I could separate it out and make a Users table, an Employees table, and a Salespeople table. The way the relationship works out, though, I could use the same ID number for all three tables, and that indicates to me that perhaps they all belong in the same table. It seems silly, after all to have a Salespeople table whose only field is "UserID."

Two factors of the first design concern me: First is the fact that a salesperson could also have a company. I guess I could write a check constraint to prevent this, but doesn't having the companyID in the Users table violate a normalization rule? Maybe? The second is the fact that the Companies table relies upon Users, which in turn relies upon Companies. In OOP, this usually isn't a good thing, but I'm not sure whether it's cause for concern in a relational database.

Anyway, I really don't know what I should be doing with this design. Any suggestions?

Thanks in advance,
-Starwiz

View 1 Replies View Related

Need Some Help With Design Of Tables/views

Jul 23, 2005

I have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)

View 9 Replies View Related

Design Of Database Tables

Jul 20, 2005

Dear sirI have one SQL Server database.In this database more than 20 tables existsthat their structures is same.I have two way for design this tables:1.Create all tables separately(create more than 20 tables)2.Create one table and separate each groupby type(add column type to table and assign samevalue for each group)What is better solution?Please help me.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Sum Of Sums

Oct 19, 2005

Hello, any help is appreciated.Here is what I’m trying. I want a month to date total of bookingsbased on a fiscal month.I can isolate my records for the month and sum up the days so I windup with a list of days of the month with their bookings total forthat day. Like this10/1/05 - $100010/2/05 - $500010/3/05 - $2000And so on thru the month.But I want the sum of month 10.I have a line of code like “select sum(EXT_AMT) where month = “10””What I need is sum(sum(EXT_AMT).And that does not work.Any suggestions? I thought to throw the results into a table then sumthe table.Not sure how to do that either! I'm frustrated! Any help isappreciated.Thanks, Duane

View 2 Replies View Related

DB Design: One Table Or Multiple Tables?

Dec 28, 2005

I am developing a Content management system of sorts and I want it
to be pretty flexible. I have noticed that with web content (News,
Articles, Events, FAQs) that there is a lot of similarity between these
items. They all have the same basic fields:
TitleSynopsisBodyStartDateEndDate
(for News, Articles, FAQs start and end date can be used for content
expiration. For events, they are used for the actual event dates.)
But for some types of content, I will need fields specific to that
type. For instance, I want to have a few custom settings for a "Photo
Gallery" type, like, how many rows/columns of thumbnails to display per
page.

I feel like I have 3 options, but would really appreciate your advice. I created diagrams for the 3 options, located here:  http://nontalk.com/dbdesign/

View 2 Replies View Related

Design Question Regarding Junction Tables

Jan 18, 2008

I'm designing a database with 3 tables called Function, Test andScene.A Function has multiple Tests, but a Test has only one Function. Amany to many relationship exists between Test and Scene therefore Ineed a junction table between these two tables - giving 4 tables intotal. The Test table would store a foreign key, the primary key ofthe Function table.There is a problem with design though and that is that Functions andScenes are actually defined before the Test is defined. Therefore itshould be possible to create a Function and add to id its Scenes,before Tests have been defined. In other words, Scenes are as much apart of a Function as they are of Tests. Tests are in fact only ofrelavence to testers. Anyway, to satisfy this scenario, a Junction boxis also needed beween Function and Scene. This creates a loop betweenall tables.Is this a good approach? Any other suggestions or advice on thematter? Any advice regarding data integrity?Thanks,Barry

View 1 Replies View Related

A Player Team Tables Design?

Jul 20, 2005

i am a beginner of database design, could anyone please help me tofigure out how to make these two tables work.1) a "players" table, with columns "name", "age"2) a "teams" table, which can have one OR two player(s)a team also has a column "level", which may have values "A", "B",or "C"how do you build the "teams" table (the critical question is "do ineed to create two fields" for the maximum two possible players?")how do you use one query to display the information with the followingcolumns:"name", "age", "levelA", "levelB", "levelC" (the later three columnsare integer type, showing how many teams with coresponding level thisplayer is in).now suppose i don't have any access to sql server, i save the datainto xml, and load it into a dataset. how could you do the selectionwithin the dataset? or ahead of that, how do you specify the relationsbetween "players" and "teams".the following is the schema file i am trying to make (I still don'tknow if i need to specified the primary key... and how to buildrelation between them):<code><?xml version="1.0" ?><xs:schema id="AllTables" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="AllTables" msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Players"><xs:complexType><xs:sequence><xs:element name="PlayerID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Name" type="xs:string" minOccurs="0" /><xs:element name="Age" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element><xs:element name="Teams"><xs:complexType><xs:sequence><xs:element name="TeamID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Level" type="xs:string" minOccurs="0" /><xs:element name="Player1" type="xs:int" minOccurs="0" /><xs:element name="Player2" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema></code>

View 2 Replies View Related

Tables Design And Linking For A Job Portal

Feb 28, 2008



Hi,

Currently i am developing a job portal in ASP 2.0, SQL Server 2005 which involves Job Seeker registration, Searching of resumes, applying for job Posting, Employer Registration, Create Job Posting, Searching for Job Seeker etc.
The Job Seeker is allowed to upload a word document of size up to 500Kb which is stored in Table as varbinary.

Right now I have MemberShip/Roles in seperate database. The Job Portal Tables are in seperate Database. I was told to split the Tables so that Tables of JobSeeker are One database and Employer to another Database so that they speed up the performance.

I have several tables that bridge (thats either store id's of Job Seeker or Employer) like Job Postings applied, Saved Postings Job Seeker, Job Postings of the Employer, Job Posting (Applied ones) alert etc.

Can any give me how to create a good Database design (one or more) with excellent performation. Right now I have one Database for Job Portal related tables excluding membership. The mapping of key fields including the fields that are enabled for Text indexing are given below.

(JobSeekerTable - Stores Personal Details)
JobSeekerId (PK)
...............

(JobSeekerResumeTable - Stores Resume Details)
JobSeekerResumeId (PK)
JobSeekerId (FK)
Job Title (enabled Text Indexing)
........

(JobSeekerDocTable - Stores Resume Details)
JobSeekerDocId (PK)
JobSeekerId (FK)
Resume (as varbinary) (enabled Text Indexing)
Covering Letter (Text)
........

(JobSeekerPostingTable - Stores Job Postings Saved by the Job Seeker)
JobSeekerPostingId(PK)
JobSeekerId (FK)
JobPostingId (FK)
......

(JobSeekerAppliedTable - Stores Job Postings Applied by the Job Seeker)
JobSeekerAppliedId(PK)
JobSeekerId (FK)
JobPostingId (FK)
.....

(CompanyTable - Employer Details)
CompanyId(PK)
.....

(JobPostingTable - Stores the information of the Job Posting created by Employer)
JobPostingId(PK)
CompanyId(FK)
Job Title (enabled Text Indexing)
Job Desc(enabled Text Indexing)
.....

(JobPostingConTable - Stores the information of the Job Posting Location Details )
JobPostingConId(PK)
JobPostingId(FK)
.....

(CompResumeSaved - Job Seeker details saved by Employer)
CompResumeSaved(PK)
CompanyId(FK)
JobSeekerId(FK)
.....

Eventually more tables would be added. Can any one tell me how to speed up the performance (particulary search engine fo Employer for searching resumes & Jobseeker for searching job Postings.) I hope I have mentioned everything clearly.

Thanks,
Uma Ramiya








View 4 Replies View Related

Gets Sums On Inner Joins

Feb 21, 2008

Ok I have two tables

clients and expenses

enpense table columns: clientid, expensetype, expensevalue

clients.clientid and expenses.clientid relate the tow tables on an inner join

what i have is one client that will have multiple expense records in the expense table

cleint1, expensetype1, expensevalue1
cleint1, expensetype2, expensevalue2
cleint1, expensetype3, expensevalue3

what i need to output is client.*, sum(expensevalues), and also expensevalue1, expensevalue2, expensevalue3 as one output line

how do I get this?

View 3 Replies View Related

Getting 2 SUMs From The Same Table

Jul 23, 2005

Hi AllI'm really stuck on this one so would appreciate any help you can give.In essence, I have 1 SQL 2000 table with rows of data logging stockmovement. To differenciate between a stock sale and a stock receipt thetable has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equals areceipt.I've been asked to report on this data by suming the total qty used oninvoices and the total qty recvd for each stock item, but I can't figure outhow I sum the same rows twice in the one query.For example, my query is as follows:select st.stockid as 'STYLE',s.picture as 'COLOUR','' as 'IN FIRST IN LAST WEEK','' as 'THIS WEEK IN','' as 'TOTAL IN','' as 'OUT FIRST OUT LAST WEEK',SUM(st.quantity) as 'THIS WEEK OUT','' as 'TOTAL OUT','' as 'REMAINING','' as 'TOTAL DIGESTION %'from stocktransactions st, stock swhere st.stockid = s.stockid andst.transactiontype in (8,7) andst.transactiondate >= '2005-07-12 00:00:00' andst.transactiondate <= '2005-07-12 23:59:59'group by st.stockid,s.pictureorder by st.stockidApart from the 'THIS WEEK OUT' column SUMing all of the stock sales bytransactiontype 7,8, I also want the 'THIS WEEK IN' column to SUM all of thetransactions by transactiontype 3, so that I get the following results:STYLE COLOUR .... THIS WEEK IN .... THIS WEEK OUT .......IVP Red 12 23STP Blue 4 15etc etcMy problem is that I don't want to exclude a stock item if it hasn't got arow/value for the THIS WEEK IN and/or the THIS WEEK OUT. Am I asking toomuch of SQL?My table schemas are as follows:create table STOCKTRANSACTIONS(STOCKTRANSACTIONID T_STOCKTRANSACTIONSDOMAIN not nullidentity(1,1),TRANSACTIONTYPE smallint not null,TRANSACTIONDATE datetime null ,REFERENCE varchar(40) null ,Comment varchar(255) null ,STOCKID T_STOCKDOMAIN null ,DESCRIPTION varchar(255) null ,UNITOFSALE varchar(20) null ,WAREHOUSEID T_WAREHOUSESDOMAIN null ,PEOPLEID T_PEOPLEDOMAIN null ,AccountID T_AccountsDomain null ,AgentID T_AgentsDomain null ,PLRate float null ,CONTACTID T_CONTACTDETAILSDOMAIN null ,JOBID T_JOBSDOMAIN null ,QUANTITY float null ,CURRENCYID T_CURRENCIESDOMAIN null ,SELLINGPRICE float null ,DISCOUNTPERCENT float null ,COSTPRICE float null ,MINIMUMPRICE float null ,TILLID T_TILLSDOMAIN null ,UserID T_UsersDomain null ,ClockDate DateTime null ,TimeStamp TimeStamp ,constraint pk_stocktransactions primary key (STOCKTRANSACTIONID))gocreate table STOCK(STOCKID T_STOCKDOMAIN not null,NAME varchar(40) not null,PICTURE varchar(40) null ,WEIGHT float null ,VOLUME float null ,BARCODE smallint null ,NumberOfPriceBreaks SmallInt not null default 1,STOCKCATEGORYID T_STOCKCATEGORIESDOMAIN null ,SALESNOMINALID T_NOMINALACCOUNTSDOMAIN null ,PURCHASENOMINALID T_NOMINALACCOUNTSDOMAIN null ,SELLINGCOMMENT varchar(255) null ,INCLUDESELLINGCOMMENT TinyInt null ,DISPLAYSELLINGCOMMENT TinyInt null ,COSTCOMMENT varchar(255) null ,DISPLAYCOSTCOMMENT TinyInt null ,PRODUCTTRACKING smallint null ,ITEMTYPE smallint null ,VALUATIONPRICE float not null default0.00 ,INCLUDEINCUSTOMERSTURNOVER TinyInt null ,INCLUDEINAGENTSTURNOVER TinyInt null ,SUPERCEDED TinyInt null ,SUPERCEDEDBY T_STOCKDOMAIN null ,SUPPLIERID T_PEOPLEDOMAIN null ,SUPPLIERSTOCKID varchar(40) null ,SUPPLIERCOMMENT varchar(255) null ,NEXTSERIALNUMBER int null ,SERIALNUMBERLENGTH smallint null ,SERIALNUMBERPREFIX varchar(10) null ,SERIALNUMBERSUFFIX varchar(10) null ,SERIALNUMBERPREFIXLENGTH smallint null ,SERIALNUMBERSUFFIXLENGTH smallint null ,TIMESTAMP timestamp not null,constraint pk_stock primary key (STOCKID))goThanksRobbie

View 3 Replies View Related

How To Do Multiple Sums?

May 13, 2008

Hello All,

I'm trying to do a query to produce multiple sums based on how many rows are in a table.
Here's a sample of the table and data, what I want is to have a query to sum each company's total and display it.




create table #invoices


(
InvoiceNumber varchar(5),
--other info
CompanyCode int,
InvoiceAmount real
)


Insert Into #invoices values('A1000', 1, 1000)
Insert Into #invoices values('A1000', 2, 100)
Insert Into #invoices values('A1000', 3, 300)
Insert Into #invoices values('A1000', 1, 600)
Insert Into #invoices values('A1001', 2, 2000)
Insert Into #invoices values('A1001', 3, 1000)
Insert Into #invoices values('A1001', 1, 300)
Insert Into #invoices values('A1002', 2, 2500)
Insert Into #invoices values('A1002', 3, 2000

I was thinking of doing it something like this:

Select

Sum(case when CompanyCode=1 Then CompanyCode End) as TOTAL1,
Sum(case when CompanyCode=2 Then CompanyCode End) as TOTAL2,

Sum(case when CompanyCode=3 Then CompanyCode End) as TOTAL3
from
#invoices


But I would rather not have to hard code the company numbers in the query as they can be added or removed from the list. Ideally it would take the CompanyCode from the COMPANY table and SUM each companies totals and display it.
Any help on this would be greatly appreciated!
Thanks,

View 3 Replies View Related

Different Sums From Table

May 12, 2006

Could someone explain to me, how I can get sum from row which I have values in 2 colums and I want the realtime sum to third column. Fourth colum is for item.

Also can someone tell me how to sum these third colums where the item is same so I have real time values for the item sum.

Thanks!

AD

View 1 Replies View Related

Combine SUMs

May 5, 2006

How can I combine the 2 Sum amounts below. Basically teh 2 queries are exactly the same, just hitting 2 different tables (pdc and pdcdeleted) with the same structure:

SELECT SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT     c.name,
          c.customer,
          (SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
                    where c.feeSchedule = fd.code)
          AS FeeSchedule,
          m.branch,
         pd.desk,
        'PDC' AS Type,
       pd.Active,
        m.number,
        pd.Amount,
        CONVERT(money, 0) AS OverPaidAmt,
          pd.OnHold

FROM Master m (NOLOCK)
LEFT JOIN pdc pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer

WHERE     pd.Active = 1
          AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
          AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
          AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
          AND pd.Deposit IS NOT NULL    
          AND pd.OnHold IS NULL
          AND c.customer <> '9999999'
) as PQuery

 

SELECT SUM(PQuery2.Amount) as PDCs_IL_deleted
FROM
(SELECT     c.name,
          c.customer,
          (SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
                    where c.feeSchedule = fd.code)
          AS FeeSchedule,
          m.branch,
         pd.desk,
        'PDC' AS Type,
       pd.Active,
        m.number,
        pd.Amount,
        CONVERT(money, 0) AS OverPaidAmt,
          pd.OnHold

FROM Master m (NOLOCK)
LEFT JOIN pdcdeleted pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer

WHERE     pd.Active = 1
          AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
          AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
          AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
          AND pd.Deposit IS NOT NULL    
          AND pd.OnHold IS NULL
          AND c.customer <> '9999999'
) as PQuery2

View 1 Replies View Related

DB Design :: Inserting Data From 3 Tables Into 1 Table

Jun 10, 2015

I have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one statement or query.

View 3 Replies View Related







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