August 17, 2010 at 5:39 am
Hi
I am trying to get join two tables, the first contains the names of all deapartments in an organisation across the country the other is a select table picking up a specific product sold only in some departments.
I have created a query to look at the select the minimum and maximum values sold by peers of the deaprtment across the country but I still want to keep the departments that don't offer the services in the new view with peer minimums and maximums
August 17, 2010 at 5:45 am
Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 5:52 am
the first table looks like this :specialist view
select type, num, denom,round(cast(sum(num)as float)/cast(sum(denom) as float),3) rate ,tab1.quarter, tab1.storeid,tab2. department
from
(select type,sum(product) num, storeid,department
,quarter
from normalising_births_table2 tab1,dbo.Lookups_NHS_National_Trust_Sites a
where provider = Organisation_Code
and (product1= 'O757' or product2 = 'O757'or product3 = 'O757'or product4 = 'O757'or product5 = 'O757'or product6 = 'O757'or product7 = 'O757'or product8 = 'O757'or product9 = 'O757'or product10 = 'O757')
group by storeid,provider,quarter) tab1
LEFT JOIN
(select sum(product)denom,storeid,department
,quarter
from product_2 tab1,dbo.National a
where department
= Organisation_Code
and ((product1= 'O757' or product2 = 'O757'or product3 = 'O757'or product4 = 'O757'or product5 = 'O757'or product6 = 'O757'or product7 = 'O757')
OR (product1= 'O342' or product2 = 'O342'or product3 = 'O342'or product4 = 'O342'or product5 = 'O342'or product6 = 'O342'or product7 = 'O342'or product8 = 'O342'or product9 = 'O342'or product10 = 'O342'))productname
group by storeid, department
,quarter) tab2
August 17, 2010 at 5:56 am
Table definitions please, sample data and expected results.
Please read the article I linked to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 5:57 am
typenumdenomratequarterstoreiddepartment
xps131250.104Q3 0910chixt1
xps181040.173Q4 0910losst1
xps2150.133Q1 0910atlat1
xps12550.218Q2 0910nycnt1
xps19830.229Q4 0910RXHrt1
and that gives something like this
August 17, 2010 at 5:58 am
Hi
pls clarify what you mean by table definitions
ta
August 17, 2010 at 6:01 am
niyinks (8/17/2010)
pls clarify what you mean by table definitions
Please read the article I linked to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 6:02 am
storeidstore_nameIT_Cluster_coderegional_codeAddress_Line_1Address_Line_2Address_Line_3Address_Line_4Address_Line_5zipcodeOpen_DateShorthand
August 17, 2010 at 6:06 am
No idea about the clustering but my last post describes the national table
August 17, 2010 at 6:09 am
niyinks (8/17/2010)
No idea about the clustering but my last post describes the national table
I'm not sure if you know this but this forum is supported by people that are giving of their time so, when they ask for you to read an article and the submit information it is so that they can help you properly without them having to do all the work trying to piece together your environment from snippets of code or information that you provide. That is time consuming and not profitable for anyone.
So, take a few minutes, read the article and post the information that is being requested in the format described. I'm pretty sure that you will find all that you need on how to get that information after reading the article.
Thanks for your help and understanding.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 17, 2010 at 6:11 am
Hehe - after checking Gila's post it appears she posted the wrong link. Try this one;
http://www.sqlservercentral.com/articles/Best+Practices/61537/
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 17, 2010 at 6:14 am
niyinks (8/17/2010)
No idea about the clustering but my last post describes the national table
My apologies, copied the wrong link and didn't notice (what I get for working on presentation and forum posts at the same time)
Please read through the link that David posted (I have also updated my post)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 6:20 am
My apologies if it seems like I did not read the articles but I did read them but was out of my depth, while I have been learning the creation of queries and the syntax involved I havent gone as deep as the articles and would need more time to grasp ant tangible meaning from the articles. which was my I said I couldn't figure it out
August 17, 2010 at 6:33 am
Hi
I have read the new link and still am slightly out of my depth, please could you just give pointers on how to get the table into the desired structure so I camn make that available
August 17, 2010 at 6:39 am
Assuming you're using management studio...
In Object Explorer, right click the table, select script table -> Create -> To new query window. Now you've got the table's creation scripts.
Jeff's article covers how to get the data into a usable state.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply