October 16, 2008 at 6:01 pm
I have a Doctor table and child tales of Specialties and Offices.
I want to put the specialties inline, but repeat the whole Doctor record for each office record. So my output will look like this:
ID NAME OFFICE SPECIALTY1 SPECIALTY2
DOC1 Dr Jones 123 Pine Gastro Internal Med
DOC1 Dr Jones 432 Spruce Gastro Internal Med
DOC2 Dr Smith 227 Hickory Cardio
etc.
What would the JOIN be assuming that all 3 tables have an ID field?
Thanks,
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 1, 2008 at 10:17 am
This sounds like homework or an interview question. Please make an attempt to show that you understand some of this.
November 1, 2008 at 11:33 am
Well I did it, but with multiple selects (selected from the address file and did a fetch on each address for the other stuff (ie: Specialty1, Specialty2...), which I programmatically concatenated with a digit appended. I'm kind of an old guy, so I did it using procedural code. RBAR.
As far as I can tell, Select doesn't give you enough control over how many records appear in your ouput. You can to Top x, but if there are less than x records you get less records.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 1, 2008 at 4:30 pm
I am sure that there is a better way to do this, but you have given us so little information that we really cannot tell you anything specific. Could you please at least post the table definitions and some sample data? Thanks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 1, 2008 at 5:29 pm
This is an export to Excel as a .csv (I have it working, so this is purely academic)...
DOCTOR TABLE
DOCTORID, NAME
OFFICE TABLE
DOCTORID, ADDR, CITY, STATE, ZIP
SPECIALTY TABLE
DOCTORID, SPECIALTY, LASTCRED
I need to have a record for each address, with a CSV heading:
DoctorID, Address, City, State, Zip
DOC1,123 Pine, NY, NY, 12345
DOC1,224 Oak, NY, NY, 12345
But at the end of each record I need three Specialties:
Specialty1, Last Cred, Specialty2, Last Cred2, Specialty3, Last Cred3
Gastro, 1/1/2000, Internal Med, 2,2,2002,,
The commas on the end indicate that this particular doc has no third specialty (the columns need to line up)
The same Spec data needs to be on the end of the line of each address, like so:
DoctorID, Address, City, State, Zip, Specialty1, Last Cred1, Specialty2, Last Cred2, Specialty3, Last Cred3
DOC1,123 Pine, NY, NY, 12345,Gastro, 1/1/2000, Internal Med, 2,2,2002,,
DOC1,224 Oak, NY, NY, 12345,Gastro, 1/1/2000, Internal Med, 2,2,2002,,
DOC2...
Obviously my real table are way more complicated. But you see that I need to go DOWN with the addresses and REPEAT the Specialties which are FLATTENED, and have THREE records, even if there are only one or two in the table.
This wasn't too bad using procedural code. I thought this would be a snap in SQL IF ONLY I were experienced in queries. I'm not sure it is. I kind of think it isn't possible!
But, then again, I'm a newbe so I really don't know.
Thanks.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 1, 2008 at 8:24 pm
Here's something to start with:
WITH Spec as (Select *
, Row_Number() Over (Partition By DoctorID Order By LastCred) as Num
From Specialty
)
Select
D.DoctorId
, O.Addr, O.City, O.State, O.Zip
, Coalesce(S1.Specialty, '') as Specialty1
, Coalesce(S1.LastCred, '') as LastCred1
, Coalesce(S2.Specialty, '') as Specialty2
, Coalesce(S2.LastCred, '') as LastCred2
, Coalesce(S3.Specialty, '') as Specialty3
, Coalesce(S3.LastCred, '') as LastCred3
From Doctor D
Left Join Office O ON O.DoctorID = D.DoctorID
Left Join Spec as S1 on D.DoctorID = S1.DoctorID and Num = 1
Left Join Spec as S2 on D.DoctorID = S2.DoctorID and Num = 2
Left Join Spec as S3 on D.DoctorID = S3.DoctorID and Num = 3
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 1, 2008 at 8:47 pm
Very impressive!
Apparently I need to get to know Window Functions... I have another situation where this will apply.
"With"
"Over"
"Partition By"
"Coalesce"
"Window Functions"
Yeesh. This old dog has got a lot of new tricks to learn.
Well, thanks. I'll soak up window functions in BOL and try recoding my query set into a single (dense!) query.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 1, 2008 at 9:16 pm
Let's see if I get this...
WITH [I'm going to create a Temp table that will be available to my main query...]
Spec as (Select *, Row_Number() Over ["Over" says I'm going to partition the Temp table - most likely by a key value - into subgroups, or "data window"...]
(Partition By DoctorID Order By LastCred) as Num From Specialty []) [My Temp table is broken up into data windows by the DoctorID...]
Select
D.DoctorId
, O.Addr, O.City, O.State, O.Zip
, Coalesce(S1.Specialty, '') as Specialty1 ["Coalesce" provides a default value if the field is null...]
, Coalesce(S1.LastCred, '') as LastCred1
, Coalesce(S2.Specialty, '') as Specialty2
, Coalesce(S2.LastCred, '') as LastCred2
, Coalesce(S3.Specialty, '') as Specialty3
, Coalesce(S3.LastCred, '') as LastCred3
From Doctor D
Left Join Office O ON O.DoctorID = D.DoctorID
Left Join Spec as S1 on D.DoctorID = S1.DoctorID and Num = 1 [Pull the Temp table rec where the rownumber is 1 - the first record in the partition for that DoctorID]
Left Join Spec as S2 on D.DoctorID = S2.DoctorID and Num = 2
Left Join Spec as S3 on D.DoctorID = S3.DoctorID and Num = 3
I think I get it.
Thanks again. "Kinda not so intuitive" and "kinda dense" but functional...
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 1, 2008 at 9:33 pm
billross (11/1/2008)
..."kinda dense" but functional...
Heh. Funny, that's how my wife describes me to her mother. 😛
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 1, 2008 at 9:38 pm
Oh, by the way, I think that the specialty LastCred's will need to be cast-ed to varchar() before they are Coalesced, or else you might get datatype errors.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 1, 2008 at 9:59 pm
I need to create a report of bags ordered this year compared to last year, by vendor.
Since I need to create a report, I'm thinking I need a view, from which I can create a dataset, upon which I can drive my report/graph.
This is probably not the right approach, but this is my first stab at it, based on the knowledge of window functions I recently acquired...
WITH OneYearByVendor as (Select DatePart("Year",[Date Ordered], [Vendor ID], [Bags] Over(Partition By [Vendor ID], DatePart("Year",[Date Ordered] AS Yr)
FROM [Order Details])
Select
V.[Vendor ID], CY.Sum(Bags), LY.Sum(Bags)
From Vendors V,
Left Join OneYearByVendor CY on V.[Vendor ID] = CY.[Vendor ID] and Yr = DatePart("Year", Today())
Left Join OneYearByVendor LY on V.[Vendor ID] = LY.[Vendor ID] and Yr = (DatePart("Year", Today()) -1)
What would be a better approach?
Thanks,
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 1, 2008 at 10:25 pm
The OVER keyword has to be paired with a windowing function (such as ROW_NUMBER()) or an aggregate function (such as SUM()). so your CTE should be slightly changed:
WITH OneYearByVendor as (Select
DatePart("Year",[Date Ordered])
, [Vendor ID]
, Sum([Bags]) Over(Partition By [Vendor ID], DatePart("Year",[Date Ordered])) as Bags
, DatePart("Year",[Date Ordered]) AS Yr
FROM [Order Details])
Select
V.[Vendor ID], CY.Sum(Bags), LY.Sum(Bags)
From Vendors V
Left Join OneYearByVendor CY on V.[Vendor ID] = CY.[Vendor ID] and CY.Yr = DatePart("Year", Getdate())
Left Join OneYearByVendor LY on V.[Vendor ID] = LY.[Vendor ID] and LY.Yr = (DatePart("Year", GetDate()) -1)
Also, I included a number of syntactical corrections.
Thanks,[/quote]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 2, 2008 at 4:18 am
Kewl!
Two things...
If I sum the bags in my CTE, should I change it to this? (Note "[TOTAL_BAGS]")
WITH OneYearByVendor as (Select
DatePart("Year",[Date Ordered])
, [Vendor ID]
, Sum([Bags]) AS [TOTAL_BAGS] Over(Partition By [Vendor ID], DatePart("Year",[Date Ordered])) as
Bags
, DatePart("Year",[Date Ordered]) AS Yr
FROM [Order Details])
Select
V.[Vendor ID], CY.[TOTAL_BAGS], LY.[TOTAL_BAGS]
From Vendors V
Left Join OneYearByVendor CY on V.[Vendor ID] = CY.[Vendor ID] and CY.Yr = DatePart("Year",
Getdate())
Left Join OneYearByVendor LY on V.[Vendor ID] = LY.[Vendor ID] and LY.Yr = (DatePart("Year",
GetDate()) -1)
And do I need to do an ORDER BY [Vendor ID] and/or GROUP BY [Vendor ID] in order to get my partition/data window to group by Vendor ID?
Thanks,
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 2, 2008 at 12:00 pm
No, your "AS [TOTAL_BAGS]" does not go there, it would go out were i have "as Bags" as the end of that line. Which would just mean that you were renaming my "Bags" to your "TOTAL_BAGS". Which makes our queries virtually the same, excpet that you have corrected my mistake with ", CY.Sum(Bags), LY.Sum(Bags)..."
So more correct version of both would be:
WITH OneYearByVendor as (Select
DatePart("Year",[Date Ordered])
, [Vendor ID]
, Sum([Bags]) Over(Partition By [Vendor ID], DatePart("Year",[Date Ordered])) AS [TOTAL_BAGS]
, DatePart("Year",[Date Ordered]) AS Yr
FROM [Order Details])
Select
V.[Vendor ID], CY.[TOTAL_BAGS], LY.[TOTAL_BAGS]
From Vendors V
Left Join OneYearByVendor CY on V.[Vendor ID] = CY.[Vendor ID] and CY.Yr = DatePart("Year",Getdate())
Left Join OneYearByVendor LY on V.[Vendor ID] = LY.[Vendor ID] and LY.Yr = (DatePart("Year",GetDate()) -1)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2008 at 2:26 am
Ok, I worked out all of the syntax and this compiles and gives me the results I want:
WITH OneYearByVendor (Vendor,Yr,TotalBags)
AS (Select
[Vendor ID],
DatePart("Year",[Order Date]),
Sum([Bags])
Over(Partition By [Vendor ID], DatePart("Year",[Order Date])) as Bags
FROM [Orders])
Select
V.[Vendor ID], CY.TotalBags as [Bags Last Year], LY.TotalBags as [Bags This Year]
From Vendors V
Left Join OneYearByVendor CY on V.[Vendor ID] = CY.[Vendor] and CY.Yr = DatePart("Year", Getdate())
Left Join OneYearByVendor LY on V.[Vendor ID] = LY.[Vendor] and LY.Yr = (DatePart("Year", GetDate()) )
Many thanks!
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply