Give Results Even If Two Tables Used Don't Match
Aug 12, 2014
I have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).
If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.
What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.
Is there a way to get all 33 results while linking the two tables?
View 4 Replies
ADVERTISEMENT
Oct 15, 2007
Hello I have a problem with a query..
What I want:
I have a query that selects from different tables:
Code:
SELECT * FROM table1 as t1 ,table2 as t2 WHERE t1.t2_Id = t2.Id
But now I want all results from table1 even if it's not linked with table2, if that's the case i want to add something like:
Code:
CASE
WHEN 'notlinked' THEN 1
ELSE 0
END HighLight
that way I can show the rows that have to be manually linked
hope you understand my problem...
thanks!
View 1 Replies
View Related
Sep 1, 2004
I am running some query with the following where clause
where DateCreated BETWEEN '06/01/2004' AND '06/30/2004'
It gives the records where DateCreated is of 2004-07-01 21:48:02.377
the default language on the server is British English.
Could anybody please tell me the reason why records from july are also coming
View 1 Replies
View Related
May 14, 2015
We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
View 9 Replies
View Related
May 5, 2008
Dear Pals,
I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)
insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'
I need the output something similar as below
REG
Reg
Regsxysn
psReg
I have tried out with full text indexing but i could'nt get the required output.
Any suggestions would be appreciated.
Thanks in Advance.
View 1 Replies
View Related
May 5, 2008
Dear Pals,
I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)
insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'
I need the output something similar as below
REG
Reg
Regsxysn
psReg
I have tried out with full text indexing but i could'nt get the required output.
Any suggestions would be appreciated.
Thanks in Advance.
View 3 Replies
View Related
Mar 3, 2014
I work for a school district and new requirement we were just given for scoring some student scores.
Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.
View 9 Replies
View Related
Jul 1, 2015
is it possible to replicate this in SSRS I wonder??I have included the code of the fields used and a snapshot of some data, and also how the Pivot looks in Excel.
SELECT
TARNSubmissionID,
ISSBand,
BPTLevelAchieved,
FinancialYearOfDischargeOrDeath,
FinancialQuarterOfDischargeOrDeath,
FinancialMonthOfDischargeOrDeath,
CalendarMonthNameOfDischargeOrDeath,
[code]...
View 4 Replies
View Related
Mar 1, 2006
Hello All,
I have multiple SQL Server 2000 databases all identical in structure (Company1.mdf, Company2.mdf, Company3.mdf). I have a situation where while connected to Company1, i want to create a record in Table1 and have that same record automatically be created within Company2 and Company3.
If i was working with MSAccess, i could use linked tables where i would link Company2 and Company3 to Company1 Table1. This way if any changes are made, all three companies and working from the same record set.
My situation deals with mutiple tables that need to be in line with each other across mutliple databases.
Your suggestions are appreciated.
Thanks
View 9 Replies
View Related
Dec 28, 2007
Hi, hope someone can help.
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees
1 Dave
2 Peter
3 John
Site 1
Mon John
Tue Peter
Wed John
Thu John
Fri Peter
Site 2
Mon Dave
Tue Dave
Wed Dave
Thu Dave
Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
Thanks
View 4 Replies
View Related
Aug 29, 2007
i have two tables one with id and one with storyid which match. i need to check which record is at top of table two. and then pull top four from table one where the top story from table two (storyid) is not one of the top four in table one (id). is this possible? can anyone help?
View 8 Replies
View Related
Aug 12, 2015
Need to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies
View Related
Feb 19, 2007
Hello, Everyone
I had 2 tables contain data:
1) old_data: 601,195 records
firstname
lastname
address
city
state
zip
zip4
2) current_data: 410,185 records
firstname
lastname
address
city
state
zip
zip4
3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.
****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.
/*******
Delete current_data
From old_data Cross Join current_data
Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName
and old_data.Address = current_data.Address
********/
RV
View 8 Replies
View Related
Jul 6, 2015
I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.
Then I have a table usergroups with two columns (groupid, user) with example:
1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott
I want to find groupid 2 and 4 because they exactly match the content of @tbl.
View 8 Replies
View Related
Aug 5, 2015
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
View 4 Replies
View Related
Jan 18, 2007
Hi All,I need to link two tables using partial word match.How can I write a SQL statement to do so? (I am using MS-Access.)Table One: [Table Name: tblStreet] [Field Name: Street]123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTable Two: [Table Name: tblWord] [Field Name: Word]ABCCBSThe output should be:123 ABC Street124 ABC Street125 ABC Street10 CBS Street11 CBS Street12 CBS StreetCould this be done?Thank you in advanced.- Grasshopper -
View 2 Replies
View Related
Feb 12, 2015
I have two table People and Employee, both have firstname and lastname as fields
I want to display only the names that don't match on firstname and lastname
View 3 Replies
View Related
Aug 6, 2015
CREATE TABLE #NAME1(FULLNAME VARCHAR (100))
INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')
INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')
CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))
INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')
INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')
My task is to find matches between these two tables on "name."
View 1 Replies
View Related
May 4, 2015
There are two tables
TABLE 1 (NAME - Groupseats)
id session course groupcode sub1 sub2 sub3
1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
View 10 Replies
View Related
Jul 22, 2015
The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
View 9 Replies
View Related
Dec 30, 2004
Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
Is there anything like that out there?
View 4 Replies
View Related
Aug 13, 2013
Aim – Append the results from #tablecut on to table #Deletedupes
Table name #tablecut
Columns within #tablecut
Records = 186
Fdmsaccountno
Seller_Code
mcc_code
chain_chain_no
Dba_name
se_number
pca
post_code
rolling12
Segmentation
Table name# #Deletedupes
Columns within # Deletedupes
Records = 11195
Fdmsaccountno
Seller_Code
mcc_code
chain_chain_no
Dba_name
se_number
pca
post_code
rolling12
Segmentation
End result 11389 records
Put into a table called #Results
View 1 Replies
View Related
Dec 2, 2003
Hello!
I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?
View 2 Replies
View Related
Jan 28, 2015
I have two tables, one with data for one type of ID (call it key1) and a table where this ID (call it key2) is transformed to another. It is not one-to-one match with these types of ID and I want to check those key2 cases that have two or more key1 linked to it.
It is simple enough and for the easiest check I don't even need table1 to run it as table2 has both key1 and key2 variables.
However, not all doubles are of identical worth. Table1 (that has only key1) has a year variable. I am interested in doubles that have same year variable, ie. in table1 there are two key1 cases with the same year variable that are linked to one key2 case in table2.
So in essence in table1 I have key1, year and in table2 I have key1, key2 and I am interested in those key2-cases that have more than one key1 linked to it where years are the same.
SELECT query.key2
FROM (
SELECT DISTINCT a.key1, b.key2
FROM table1 AS a JOIN table2 AS b ON a.key1=b.key1 JOIN table1 AS c ON
a.key1=c.key1
WHERE a.year=c.year)
AS query
GROUP BY query.key2
HAVING COUNT(*)>1
I tried it joining table1 twice and fiddling around with various JOIN and WHERE clauses (the one on show being the simplest and most naive one) but the query still returns key2-doubles whose key1 cases are linked to different years. It is simple enough if you give a distinct year value in where clause (and drop second table1 join as unnecessary) but I don't want to go through all years manually one by one. I was thinking some kind of iterative loop that changes the value of the year in where clause could do the trick (and be heavy computationally) but I don't really know how to go around doing it, haven't done any loops in SQL ever.
View 0 Replies
View Related
Jul 2, 2014
I have a quick question for the SQL community about how to combine 3 tables to get the results needed...
The table names are :
setup_zipcode,
setup_category,
and listing
and inside setup_zipcode it has the columns:
zip_id , zip_code, zip_latitude and zip_longitude
and inside the setup_category it has the columns:
category_id, category_parent, category_path and category_name
and the final table for listing has the columns:
listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country
I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...
So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..
How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?
I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...
$cat is assigned in a query previously on the page...
SELECT *
FROM
setup_zipcode, setup_category, listing
WHERE
listing_category LIKE '%-$cat-%'
AND
listing_zip = zip_code
AND
category_id = '$cat'
ORDER BY listing_title ASC
When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..
I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..
If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...
View 1 Replies
View Related
Jan 18, 2006
This seems like a very simple question but i have never been able tofind an easy answer to it.I have a user table and i do a join with another table, we'll call theother table a results table.The results table has numerous rows with the userid foreign key.I want to make a query that will give me the number of rows in theresults table for each user where the result is some valueThe query is simple to make but will only show the users who have arecord in the results table the meet the where criteria, however i wantto display each user and show a record count of 0 when there are noresults in the results table that match the criteria.for example i have 2 tables.tblUsers_______________userid | username--------------------------1 | user12 | user2tblAnswers________________userid | answer----------------------------1 | 11 | 01 | 42 | 12 | 0if i run the query:select max(username), count(answer) from tblUsersleft outer join tblanswers on tblAnswers.userid = tblUSers.idwhere tblAnswers.answer = 4group by tblUsers.idi just getuser1 | 1i want to getuser1 | 1user2 | 0the only way ive found to do this is with a temp table and a curser tocreate all the users records and go back through an insert the answercount for each user. This approach seems very expensive and requires aquery that is 3 times larger than is needed for the same resultswithout including 0 count records. I know there must be a better way todo this.Any help is appreciated.
View 2 Replies
View Related
Oct 5, 2015
We have two tables.
Table1:
Servers | Numbers
------------
Server1 | 1
Server1 | 2
Server1 | 3
Server2 | 1
Server2 | 2
Server2 | 4
Server3 | 2
Server3 | 5
Server3 | 9
Server3 | 7
.....
Table2:
| Numbers
-----------
NULL | 1
NULL | 2
NULL | 3
I need to select Server1, Server2, Server3 and other servers that does not have correct value in Table2. Results should return server name and number that server does not have like:
Server2 | 3
Server3 | 1
Server3 | 3
Table1 is updated time to time, Table2 - static table. The best would be to avoid loop or cursor. Is that possible to get these results in one query?
View 4 Replies
View Related
Apr 17, 2008
Hi,
I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format:
Customer Name Address
----------------------------------------------------------
ABC Add1
ABC Add2
XYZ Add1
XYZ Add2
Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report).
Please give me a solution to this.
Shyam
View 4 Replies
View Related
Mar 13, 2014
below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01
SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,
SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013,
SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012,
SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010,
SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009
FROM
[code]...
I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
View 9 Replies
View Related
Feb 1, 2015
I have 3 Tables
TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]
A query returns duplicate results from TableB as well as TableC
TableB and TableC have nothing in common and should not interfere with each other.
with TwoTables as (
select
a.QuickSpec as QuickSpecId,
[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]
View 1 Replies
View Related
Dec 5, 2007
I have a query that joins two large partitioned tables and depending on the values in the where clause, I can get dramatically different performance results.
The first query completed in around 7s and has 47,000 logical reads.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (5339, 5341, 5342, 943842, 943866)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The second query takes 188s to complete and has 1.8m logical reads. The only difference between the two is the value of the monitor_ids in the where clause.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (152682, 5339, 5341, 5342, 268080)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The two tables have clustered indexes on monitor_id, testtime and site_id. Comparing the execution plan, I can see why there is such a difference in performance. The second query performs a clustered index seek on the monitor_object table starting at the lowest monitor_id, testtime & site_id through the highest monitor_id, testtime & site_id. The first query performs a clustered index seek where the monitor_id, testtime and site_id equals the same values from the monitor_raw table.
My question is, how can I force the second query to use the same execution plan as the first so that I can get better performance?
One possible workaround that I could use is to execute five individual queries, one for each monitor_id and then union the results together but this would require significant code changes to my stored procs.
Thanks,
Tim
View 5 Replies
View Related
Nov 16, 2014
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
View 9 Replies
View Related
Nov 8, 2006
I need help in writing a query.
The query should get top 10 items and their values from current year and the values for the same items from previous year table.
I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.
The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.
I am attaching some images below to give more information.
Image of results from my query.
Image of how the final output should look like.
The Store Procedure code is:
ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'
EXEC sp_executesql @SQuery
END
View 2 Replies
View Related
