January 25, 2016 at 6:48 pm
Sean Lange (1/25/2016)
Ed Wagner (1/25/2016)
I know what you're dealing with now so I'd imagine it must look like a piece of cake to you. 😉What I am dealing with now is why I haven't been around as much and also why I still MUST come around some. It is truly awful. The upside is we keep promising release dates and then trying to force the development to fit into that fantasy. Needless to say we have moved back the release date multiple times and the next one is going to be missed too but the powers that be have yet to spill the beans to the top.
I wish I could tell you that we never, ever had the problem of delays when working with the predecessor of what you're using...but it would be nothing but lies. 😉 Sadly, they were completely normal. It was then that I learned how due dates make an interesting noise as they go whizzing by. I've never had more delays and late releases than at that point in my life. I grew to hate them with a passion.
January 26, 2016 at 8:44 am
How long does that query take? There are so many nonSARGable predicates in that view alone it is frightening!!! Then you have several more in your query.
I gave you the basic gist of how you could write this query. I can't write it for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2016 at 7:54 am
Sean Lange (1/25/2016)
SQLisAwE5OmE (1/25/2016)
INNER join is fine at the parent info....I am confused whats the exact syntax to use under group by clause.
Basically I need to find out if a student is registered to both music and dane school.
Parent 1 normally a father, parent 2 mother.
Your requirements are still a little fuzzy here. Do you want students that are registered in both schools or students in a family that have students in both schools. The difference is subtle but incredibly important.
With no ddl to work and vague business rules this is a bit like hunting for squids in a black hole while wearing a blind fold and covered in peanut butter with a pack of wolves closing in.
This would be one way to write the sql to get a family that has at least one student (maybe not the same student) in both schools.
SELECT DISTINCT
n.ID,
n.FULL_NAME,
n.BIRTH_DATE,
n.FULL_ADDRESS,
n.EMAIL,
mm.TITLE,
mm.MEETING,
mm.BEGIN_DATE,
o.TOTAL_PAYMENTS,
o.[STATUS],
mm.MEETING_TYPE,
o.ST_ID,
o.ORDER_DATE,
p.Parent1_FullName,
p.Parent1_Cell,
p.Parent2_FullName,
p.Parent2_Cell,
p.Family_Email,
arc.[Count],
mm.END_DATE,
om.MEETING,
n.PAID_THRU
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
INNER JOIN IMIS.dbo.Order_Lines ol ON om.ORDER_NUMBER = ol.ORDER_NUMBER
INNER JOIN IMIS.dbo.vw_csys_ActualRegistration_Count arc ON om.MEETING = arc.PRODUCT_MAJOR
WHERE mm.MEETING_TYPE = 'PADA'
AND ol.PRODUCT_CODE LIKE '%/MAIN'
AND EXISTS
(
select pa.ParentID --no clue what the primary key is for this so I am guessing
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
where pa.ParentID = p.ParentID
and mm.MEETING_TYPE in ('TSM', 'PADA')
group by pa.ParentID
having COUNT(distinct MEETING_TYPE) = 2
)
ORDER BY mm.MEETING
Hi Sean,
I tried your query, and I am getting the following error.
Msg 4104, Level 16, State 1, Line 43
The multi-part identifier "pa.ParentID" could not be bound.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'ParentID'.
Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "pa.ParentID" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "pa.ParentID" could not be bound.
Let me know if you need the create script for any other table.
Regards,
SQLisAwe5oMe.
February 4, 2016 at 8:22 am
SQLisAwE5OmE (2/4/2016)
Hi Sean,
I tried your query, and I am getting the following error.
Msg 4104, Level 16, State 1, Line 43
The multi-part identifier "pa.ParentID" could not be bound.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'ParentID'.
Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "pa.ParentID" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "pa.ParentID" could not be bound.
Let me know if you need the create script for any other table.
Did you see my comment?
select pa.ParentID --no clue what the primary key is for this so I am guessing
I have no idea what the key is. I am a bit scared that you can't convert that into your environment. I get the feeling you don't understand this code and are just trying it out. The important part of this is understanding what the code is doing. Once that happens the adjustment should be simple.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2016 at 8:40 am
Sean Lange (2/4/2016)
SQLisAwE5OmE (2/4/2016)
Hi Sean,
I tried your query, and I am getting the following error.
Msg 4104, Level 16, State 1, Line 43
The multi-part identifier "pa.ParentID" could not be bound.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'ParentID'.
Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "pa.ParentID" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "pa.ParentID" could not be bound.
Let me know if you need the create script for any other table.
Did you see my comment?
select pa.ParentID --no clue what the primary key is for this so I am guessing
I have no idea what the key is. I am a bit scared that you can't convert that into your environment. I get the feeling you don't understand this code and are just trying it out. The important part of this is understanding what the code is doing. Once that happens the adjustment should be simple.
I understand the first part of the query.....second part I am confused
There is no parent ID on Name table....
The way you differentiate Parent1, parent2 is from Member_Type column
Member_Type = MP, NM-MP = Parent1
Member_Type = MSP, NP-SP = Parent2
column ID is the primary key column.
--Here is the create script for Name table...
CREATE TABLE [dbo].[Name](
[ID] [varchar](10) NOT NULL,
[ORG_CODE] [varchar](5) NOT NULL,
[MEMBER_TYPE] [varchar](5) NOT NULL,
[CATEGORY] [varchar](5) NOT NULL,
[STATUS] [varchar](5) NOT NULL,
[MAJOR_KEY] [varchar](15) NOT NULL,
[CO_ID] [varchar](10) NOT NULL,
[LAST_FIRST] [varchar](70) NOT NULL,
[COMPANY_SORT] [varchar](30) NOT NULL,
[BT_ID] [varchar](10) NOT NULL,
[DUP_MATCH_KEY] [varchar](20) NOT NULL,
[FULL_NAME] [varchar](70) NOT NULL,
[TITLE] [varchar](80) NOT NULL,
[COMPANY] [varchar](80) NOT NULL,
[FULL_ADDRESS] [varchar](255) NOT NULL,
[PREFIX] [varchar](25) NOT NULL,
[FIRST_NAME] [varchar](20) NOT NULL,
[MIDDLE_NAME] [varchar](20) NOT NULL,
[LAST_NAME] [varchar](30) NOT NULL,
[SUFFIX] [varchar](10) NOT NULL,
[DESIGNATION] [varchar](20) NOT NULL,
[INFORMAL] [varchar](20) NOT NULL,
[WORK_PHONE] [varchar](25) NOT NULL,
[HOME_PHONE] [varchar](25) NOT NULL,
[FAX] [varchar](25) NOT NULL,
[TOLL_FREE] [varchar](25) NOT NULL,
[CITY] [varchar](40) NOT NULL,
[STATE_PROVINCE] [varchar](15) NOT NULL,
[ZIP] [varchar](10) NOT NULL,
[COUNTRY] [varchar](25) NOT NULL,
[MAIL_CODE] [varchar](5) NOT NULL,
[CRRT] [varchar](40) NOT NULL,
[BAR_CODE] [varchar](14) NOT NULL,
[COUNTY] [varchar](30) NOT NULL,
[MAIL_ADDRESS_NUM] [int] NOT NULL,
[BILL_ADDRESS_NUM] [int] NOT NULL,
[GENDER] [varchar](1) NOT NULL,
[BIRTH_DATE] [datetime] NULL,
[US_CONGRESS] [varchar](20) NOT NULL,
[STATE_SENATE] [varchar](20) NOT NULL,
[STATE_HOUSE] [varchar](20) NOT NULL,
[SIC_CODE] [varchar](10) NOT NULL,
[CHAPTER] [varchar](15) NOT NULL,
[FUNCTIONAL_TITLE] [varchar](50) NOT NULL,
[CONTACT_RANK] [int] NOT NULL,
[MEMBER_RECORD] [bit] NOT NULL,
[COMPANY_RECORD] [bit] NOT NULL,
[JOIN_DATE] [datetime] NULL,
[SOURCE_CODE] [varchar](40) NOT NULL,
[PAID_THRU] [datetime] NULL,
[MEMBER_STATUS] [varchar](5) NOT NULL,
[MEMBER_STATUS_DATE] [datetime] NULL,
[PREVIOUS_MT] [varchar](5) NOT NULL,
[MT_CHANGE_DATE] [datetime] NULL,
[CO_MEMBER_TYPE] [varchar](5) NOT NULL,
[EXCLUDE_MAIL] [bit] NOT NULL,
[EXCLUDE_DIRECTORY] [bit] NOT NULL,
[DATE_ADDED] [datetime] NULL,
[LAST_UPDATED] [datetime] NULL,
[UPDATED_BY] [varchar](60) NOT NULL,
[INTENT_TO_EDIT] [varchar](80) NOT NULL,
[ADDRESS_NUM_1] [int] NOT NULL,
[ADDRESS_NUM_2] [int] NOT NULL,
[ADDRESS_NUM_3] [int] NOT NULL,
[varchar](100) NOT NULL,
[WEBSITE] [varchar](255) NOT NULL,
[TIME_STAMP] [timestamp] NULL,
[SHIP_ADDRESS_NUM] [int] NOT NULL,
[DISPLAY_CURRENCY] [varchar](3) NOT NULL,
Regards,
SQLisAwe5oMe.
February 4, 2016 at 9:10 am
SQLisAwE5OmE (2/4/2016)
Sean Lange (2/4/2016)
SQLisAwE5OmE (2/4/2016)
Hi Sean,
I tried your query, and I am getting the following error.
Msg 4104, Level 16, State 1, Line 43
The multi-part identifier "pa.ParentID" could not be bound.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'ParentID'.
Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "pa.ParentID" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "pa.ParentID" could not be bound.
Let me know if you need the create script for any other table.
Did you see my comment?
select pa.ParentID --no clue what the primary key is for this so I am guessing
I have no idea what the key is. I am a bit scared that you can't convert that into your environment. I get the feeling you don't understand this code and are just trying it out. The important part of this is understanding what the code is doing. Once that happens the adjustment should be simple.
I understand the first part of the query.....second part I am confused
There is no parent ID on Name table....
The way you differentiate Parent1, parent2 is from Member_Type column
Member_Type = MP, NM-MP = Parent1
Member_Type = MSP, NP-SP = Parent2
column ID is the primary key column.
Right so change the code I took a shot in the dark with and replace ParentID with column(s) that are your primary key. I guess ID?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2016 at 11:10 am
Sean Lange (2/4/2016)
SQLisAwE5OmE (2/4/2016)
Sean Lange (2/4/2016)
SQLisAwE5OmE (2/4/2016)
Hi Sean,
I tried your query, and I am getting the following error.
Msg 4104, Level 16, State 1, Line 43
The multi-part identifier "pa.ParentID" could not be bound.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'ParentID'.
Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "pa.ParentID" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "pa.ParentID" could not be bound.
Let me know if you need the create script for any other table.
Did you see my comment?
select pa.ParentID --no clue what the primary key is for this so I am guessing
I have no idea what the key is. I am a bit scared that you can't convert that into your environment. I get the feeling you don't understand this code and are just trying it out. The important part of this is understanding what the code is doing. Once that happens the adjustment should be simple.
I understand the first part of the query.....second part I am confused
There is no parent ID on Name table....
The way you differentiate Parent1, parent2 is from Member_Type column
Member_Type = MP, NM-MP = Parent1
Member_Type = MSP, NP-SP = Parent2
column ID is the primary key column.
Right so change the code I took a shot in the dark with and replace ParentID with column(s) that are your primary key. I guess ID?
Hi Sean,
I change the code according to what you suggested, but this query only listing out students with registered to Dance school(Meeting type = PADA)
I was thinking, the results should be like, 2 records for each student, 1 for TSM and 1 for PADA.
My requirement is, I need to list all students that are registered to both music(TSM) and dacne(PADA) school.
Let me know if you see if you see anything wrong in the script.
SELECT DISTINCT
n.ID,
n.FULL_NAME,
n.BIRTH_DATE,
n.FULL_ADDRESS,
n.EMAIL,
mm.TITLE,
mm.MEETING,
mm.BEGIN_DATE,
o.TOTAL_PAYMENTS,
o.[STATUS],
mm.MEETING_TYPE,
o.ST_ID,
o.ORDER_DATE,
p.Parent1_FullName,
p.Parent1_Cell,
p.Parent2_FullName,
p.Parent2_Cell,
p.Family_Email,
arc.[Count],
mm.END_DATE,
om.MEETING,
n.PAID_THRU
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
INNER JOIN IMIS.dbo.Order_Lines ol ON om.ORDER_NUMBER = ol.ORDER_NUMBER
INNER JOIN IMIS.dbo.vw_csys_ActualRegistration_Count arc ON om.MEETING = arc.PRODUCT_MAJOR
WHERE mm.MEETING_TYPE = 'PADA'
AND ol.PRODUCT_CODE LIKE '%/MAIN'
AND EXISTS
(
select n.ID
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
where n.ID = p.id
and mm.MEETING_TYPE in ('TSM', 'PADA')
group by n.ID
having COUNT(distinct MEETING_TYPE) = 2
)
ORDER BY mm.MEETING
Regards,
SQLisAwe5oMe.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply