|
I'm trying to sum up a column twice, each with a separate condition
For example, if we have the following table:
Visitors
--------
MeetingID LocationID NumVisitors
1 1 3
1 1 5
1 3 7
2 1 9
2 5 3
2 5 4
4 3 10
I want to specify 2 Meeting IDs (lets say 1 & 2) and have the query return the following:
LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 9
3 7 0
5 0 7
I tried using a full outer join on the table with itself, but that accumulates everything (both ids) together.
Select MeetingID, Sum(Visitors.NumVisitors) as NumVisitors1, Sum(Visitors1.NumVisitors) NumVisitors1
from Visitors
full outer join ...
where Visitors.MeetingId = 1 or Visitors1.MeetingId = 2
Group By ...
I then tried using sub-queries - that results in dis-joined return set - half of the columns are populated, the other half are null, then it switches
Select Visitors.MeetingId, TotalNumVisitors = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 1),
TotalNumVisitors1 = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 2)
where ...
LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 NULL
1 NULL 9
3 7 NULL
5 NULL 7
Any ideas?
|
|
|
|
|
What database are you using?
Some have Pivot builtin.
Otherwise you can do it as a variation of this query:
Select LocationID
,Sum(Case when MeetingID = 1 then Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then Numvisitors else 0) as TotalNumVisitors2
From Visitors
Group By LocationID
|
|
|
|
|
It's actually a query used to populate an rdl report.
Would that work if the calculation is more complex? It's actually adding the rows together, then multiplying by a number from a different table. Something like this maybe:
Select LocationID, Groups.GroupSize
,Sum(Case when MeetingID = 1 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors2
From Visitors
join Groups on Visitors.SomeID = Groups.SomeID
Group By LocationID, GroupSize
I could probably work around this by adding some logic in the report, but it seems a bit cleaner to do my data handling in the query and have the report only display the data. And I figured there got to be an easy way to do that 
I'll give it a try later, but it should probably work right?
|
|
|
|
|
Probably.
But personally I'm reluctant to use SQL for presentation logics, you'll find that it isn't very flexible.
I would simply get the data you need:
Select LocationID
,MeetingID
,Sum(Numvisitors) as TotalNumVisitors
From Visitors
where MeetingID IN (1,2)
Group By LocationID,MeetingIDAnd then do the Pivot in Reporting Services where it's called "Matrix control". (Here I assume that your "rdl report" means you're using SSRS)
|
|
|
|
|
I Have complicated sql.
(there has many join sql.)
so i want to check executing time.
How can i check time?
Have a nice day.
hi
My english is a little.
anyway, nice to meet you~~
and give me your advice anytime~
|
|
|
|
|
SQL server management studio comes with SQL Query Analyzer[^] which can be used to analyse individual queries.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
print getdate()
execute yoursp
print getdate()
|
|
|
|
|
Hi .
I am working on a project that is about an audit procedure . it works for all of my tables except one. The one has 51 Fields and because the field has more than 25 field the power could not calculate the result of the Power Function of Sql server
CREATE FUNCTION [dbo].[GenColUpdated]
(@Col INT, @ColTotal INT)
RETURNS INT
AS
BEGIN
DECLARE
@ColByte INT,
@ColTotalByte INT,
@ColBit INT
-- Calculate Byte Positions
SET @ColTotalByte = 1 + ((@ColTotal-1) /8)
SET @ColByte = 1 + ((@Col-1)/8)
SET @ColBit = @col - ((@colByte-1) * 8)
-- gen Columns_Updated() value for given column position
RETURN
POWER(2, @colbit + ((@ColTotalByte-@ColByte) * 8)-1)
END
GO
I will Get this error for my table
Arithmetic overflow error for type int, value = 281474976710656.000000.
i have changed my datatype to int but again it has the same problem
------------------------------------------------------------
modified 2-Nov-11 2:25am.
|
|
|
|
|
So change your data type to a bigint in your variable declaration or use a float.
Also read the guidelines on how to ask a question!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks for your answer .
I have changed it myself again it has the problem .
Secondly , about your suggestion : your opinion is respectful for yourself not me
by the way thanks for your help i appreciate it
|
|
|
|
|
What I suggest is that you read the guidelines on under the sticky at the top of the page, this will tell you that your title is not very helpful. It is only an emergency to you, not to anyone else and it does not tell us anything about the problem you are asking for help with!
Show us the change, I am betting you only changed it for 1 of your variables, do it for all the vars in the formula.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I love u so Much . Thanks a million for your guides . At first i could not get what you mean . for That accept my apologies . I had changed the type of every one of my variables but i had problem only on the return type of the Function . It must have been BigInt too . Thank you so much You helped me a lot
Bye the way My Name is "Yousef" . It is same as "Josef"
thanks and have a nice day
|
|
|
|
|
I have done some searching, and I do understand how to do some of what I want to do. But I can't just do a bulk insert into the database I'm already using.
Sample Data
Real Estate Number,Owner,Street Number,Street,Street Type,Street Direction,Unit,City,Zip Code
004306-5000,MANWARREN JAMES P,10933,BRIDGES,RD,,,Jacksonville,32225-
013092-0164,WILSON DANTE J,8438,MC GIRTS VILLAGE,LN,,,Jacksonville,32225
013100-1035,LEWIS GARNET A,4435,JACKSON HOLE,CT,,,Jacksonville,32225
048496-0040,ALLEN JON R,2830,WICKWIRE,ST,,2,Jacksonville,32225-
112862-1000,CITY OF JACKSONVILLE,0,FT CAROLINE,RD,,,Jacksonville,32225
112863-0000,ONAS CORPORATION,0,FT CAROLINE,RD,,,Jacksonville,32225
Database structure
SELECT [PropertyID]
,[RealEstateNumber]
,[Name]
,[StreetNumber]
,[StreetName]
,[Type]
,[Direction]
,[Unit]
,[City]
,[ZipCode]
,[LastReviewed]
FROM [DuvalFL].[dbo].[PropertyLookupInformation]
PropertyID is a uniqueidentifier. It defaults to newid()
LastReviewed is a datetime.
I need to get the CSV file(File name changes each time) into a temp table. I don't have a problem with an actual table being made to do a temporary insert and then dropping it at the end of the procedure.
Truncate the Zip Code field to 5 characters.(some of them have a "-" after the first 5 digits)
If the Real Estate Number exists in the database then update the record. Otherwise Insert a new record.
I could do all this from my program, but the DB is faster than using
for or foreach statements to process each record.
|
|
|
|
|
I'm pretty sure you will not be able to use the table with propertyid as the target for bulkcopy. Column count and I think format must be exact for bulk copy to work.
I load everyting into a temp table designed specifically to receive the data, all fields are varchar and after loading I run a stored proc to do the transforms into the final table.
This article mat help CSV to Table to CSV [^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know the bulk insert won't work on the CSV itself. I know that the below code is incorrect to do much of what I want. Problem is I don't know how make it all work together.
The first part should work as is, I just need to pass the file name to the procedure.
Edited the first part here to actually work. Plus adding in all the normalization updates I know need to be there.
CREATE TABLE [dbo].[TempCsvImport](
[RealEstateNumber] [nvarchar](11) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[StreetNumber] [nvarchar](10) NOT NULL,
[StreetName] [nvarchar](50) NOT NULL,
[Type] [nvarchar](10) NOT NULL,
[Direction] [nvarchar](10) NULL,
[Unit] [nvarchar](20) NULL,
[City] [nvarchar](20) NOT NULL,
[ZipCode] [nvarchar](10) NOT NULL)
GO
SET QUOTED_IDENTIFIER OFF
DECLARE @fn varchar(8000)
SELECT @fn = 'C:\Users\owner\Documents\Visual Studio 2010\Projects\UPA Data Miner\UPA Data Miner\bin\Debug\634556001928942124.csv'
DECLARE @cmd varchar(8000)
SET @cmd = "BULK INSERT dbo.[TempCsvImport] FROM '" + @fn + "' WITH(CODEPAGE='RAW',FIELDTERMINATOR = ',', ROWTERMINATOR='\n')"
EXEC(@cmd)
GO
UPDATE [dbo].[TempCsvImport]
SET [ZipCode] = SUBSTRING(ZipCode,1,5)
WHERE LEN([ZipCode]) != 5
GO
UPDATE [dbo].TempCsvImport
SET RealEstateNumber = REPLACE(RealEstateNumber,"-","")
WHERE CHARINDEX("-",RealEstateNumber) != 0
The next part is where I have no clue how to make it all work. The '@' parameters all have information from a record in the TempCsvImport
SELECT * FROM [dbo].[PropertyLookupInformation]
WHERE [dbo].[PropertyLookupInformation].[RealEstateNumber] = @RealEstateNumber
UPDATE [dbo].[PropertyLookupInformation]
SET [Name] = @Name
,[StreetNumber] = @StreetNumber
,[StreetName] = @StreetName
,[Type] = @Type
,[Direction] = @Direction
,[Unit] = @Unit
,[City] = @City
,[ZipCode] = @ZipCode
,[LastReviewed] = CURRENT_TIMESTAMP
WHERE RealEstateNumber = @RealEstateNumber
INSERT INTO [dbo].[PropertyLookupInformation]
([PropertyID]
,[RealEstateNumber]
,[Name]
,[StreetNumber]
,[StreetName]
,[Type]
,[Direction]
,[Unit]
,[City]
,[ZipCode]
,[LastReviewed])
VALUES
( NEWID()
,@RealEstateNumber
,@Name
,@StreetNumber
,@StreetName
,@Type
,@Direction
,@Unit
,@City
,@ZipCode
,CURRENT_TIMESTAMP)
Finally
DROP TABLE [dbo].[TempCsvImport]
If I have to do this all from the program, that is fine. I'd just rather use the database cause it is a lot faster internally than I can do the processing externally.
modified 31-Oct-11 9:15am.
|
|
|
|
|
I never do this type of operation from within the database, too fragile with permissions and operations that are not part of a database operation. Like checking for the source file, moving it to archive and general IO work.
Break your process into 2 operations, does the bulk load work!
Now that you have the data in a table (change it to a temp table later).
You logic of using variables implies a cursor, something to avoid if possible. What is wrong with a normal update and insert query?
Update D Set D,Field = S.Field
--Select *
from DestTable D inner join SourceTable S on D.Key = S.Key
Insert DestTable
Select Field1....
From SourceTable
Where KeyField not in(select keyfield from desttable)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
All the code where I created the temp table and all works like a charm.
The update/insert phase is where I have a problem. It has to do with my limited knowledge of SQL.
Of course during all this I found out the data was being inserted wrong so I have to redo what I've already inserted which gives me a chance to fix the problem. Thank god it isn't live data.
But I finally decided to go with
SET ANSI_WARNINGS OFF
MERGE PropertyLookupInformation as targetDB
USING TempCsvImport as sourceDB
ON targetDB.RealEstateNumber = sourceDB.RealEstateNumber
WHEN MATCHED
THEN
UPDATE
SET Name = sourceDB.Name
,StreetNumber = sourceDB.StreetNumber
,StreetName = sourceDB.StreetName
,Type = sourceDB.Type
,Direction = sourceDB.Direction
,Unit = sourceDB.Unit
,City = sourceDB.City
,ZipCode = sourceDB.ZipCode
,LastReviewed = CURRENT_TIMESTAMP
WHEN NOT MATCHED BY TARGET
THEN
INSERT
(PropertyID
,RealEstateNumber
,Name
,StreetNumber
,StreetName
,Type
,Direction
,Unit
,City
,ZipCode
,LastReviewed)
VALUES
( NEWID()
,sourceDB.RealEstateNumber
,sourceDB.Name
,sourceDB.StreetNumber
,sourceDB.StreetName
,sourceDB.Type
,sourceDB.Direction
,sourceDB.Unit
,sourceDB.City
,sourceDB.ZipCode
,CURRENT_TIMESTAMP)
;
The only thing I'm not sure of doing it this way is performance issues.
Now just to get it put into a stored proc and we are set and ready to go.
|
|
|
|
|
Brownie points for using the MERGE functions, I dislike them only b/c I consider separate update/insert queries simpler to support. You may be right to suspect the performance but I have no helpful info on that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When I've used BCP in the past, I've created a special table to hold the raw data (all as varchar) and used a trigger to move (and convert) the data to the real destination. This technique also allows for adding foreign keys as necessary.
On the other hand, I only use BCP for occasional imports, if I have data I need to import frequently I write a console app or Windows Service to do it.
|
|
|
|
|
Hi,
anyone heard of and tried a mobile app as mysql client for Android?
Thanks,
Jassim
|
|
|
|
|
Hi,
I have sql server table containing dates,currency and rates.
For non working dates we dont get rates data, so I need a stored proc to fill these gaps.
for example: for Saturday and Sunday, rates data is not received, so for Sat and Sun data with rates of previous working days i.e. Friday should be entered. Same is the case with public holidays also.
Please provide me with some sample proc.
Any help is appreciated!
|
|
|
|
|
Devesh Sinha wrote: Please provide me with some sample proc.
Asking for the codez is just lazy.
You need to decide if you want linier interpolation or a copy forward methodology. Both are very simple comparing the friday and monday rates and creating the new records as required.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all! I would like to know how to grant sql server role to another role in a database. I have a role which I have named 'GeneralAdministration' who are headed by another role which I have also named 'HeadsOfAdministration'. Each role has its own members.
Now the problem I am having is that in one of the tasks of those in the 'GeneralAdministration' role, they need to select the member in the 'HeadsOfAdministration' role who gave the authorisation for that task. I want a way to get the list of the members in the 'HeadsOfAdministration' role to be displayed in a combobox so that the one who gave the authorisation can be selected. But I have realised that when members in the 'GeneralAdministration' role log in, the 'HeadsOfAdministration' role is not visible to them.
I get an error in my C# application that 'HeadsOfAdministration' is not a role. I would like to know if there is a way to make the 'HeadsOfAdministration' role visible to those in the 'GeneralAdministration' role so that the one who gave the authorisation can be selected. Thanks in advance.
|
|
|
|
|
I seem to remember that SQL Server has some type of an app in which I can create or open a DB file and view the contents, add tables, set keys, add/update/delete records, etc., like one can do in Access. But I don't see it anywhere. Is it in some other installation package?
|
|
|
|
|
You need to install the "SQL Server Management Studio". See here[^] for the Express version.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
