Post reply

How to achive this output

  • December 19, 2011 at 1:39 am

    #261926

    Consider i have two tables SourceCode and SC_Charts

    Let say, I have 2 Sourcing charts ChartA and ChartB for 2011. In ChartA, I have selected SC1, SC3 and SC4. In chartB, I have SC2,SC4,SC5

    Output of the view should be

    Table - SourceCode

    SourceCodeSRCValueDescYear

    SC13Pas0

    SC221GL0

    SC3101TRT#12011

    SC4102TRT#22011

    SC5103TRT#32011

    Table - SC_Charts

    YearSC chartSourceCode

    2011ChartASC1

    2011ChartASC3

    2011ChartASC4

    2011ChartBSC2

    2011chartBSC4

    2011ChartBSC5

    2012ChartXSC1

    2012ChartXSC2

    Output

    YearSourcing_Chart_IDSRC_IDSRC_ValueDescriptionIsSourceCodeExistInSourcingChart

    2011ChartASC13Pas1

    2011ChartASC221GL0

    2011ChartASC3101TRT#11

    2011ChartASC4102TRT#21

    2011ChartASC5103Pas0

    2011ChartBSC13Pas0

    2011ChartBSC221GL1

    2011ChartBSC3101TRT#10

    2011ChartBSC4102TRT#21

    2011ChartBSC5103TRT#31

    For a given Sourcing chart, if the 'valid source codes' is in the chart then return IsSourceCodeExistInSourcingChart = 1 else 0

    Please can anyone say which would be the best possible join can be used to get the task.

    Thanks & Regards
    Syed Sami Ur Rehman
    SQL-Server (Developer)
    Hyderabad
    Email-sami.sqldba@gmail.com

  • December 19, 2011 at 1:45 am

    #1422981

    Please don't cross post, it fragments replies and wastes time.

    Replies to http://www.sqlservercentral.com/Forums/FindPost1223667.aspx

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • December 19, 2011 at 3:21 am

    #1423012

    CREATE TABLE [dbo].[SourceCode](

    [SourceCode] [varchar](50) NOT NULL,

    [SRCvalue] [varchar](50) NULL,

    [Description] [varchar](50) NULL,

    [Years] [int] NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING ON

    GO

    INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC1', N'3', N'PAS', 0)

    INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC2', N'21', N'GL', 0)

    INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC3', N'101', N'TRT1', 2011)

    INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC4', N'102', N'TRT2', 2011)

    INSERT [dbo].[SourceCode] ([SourceCode], [SRCvalue], [Description], [Years]) VALUES (N'SC5', N'103', N'TRT3', 2011)

    CREATE TABLE [dbo].[Relation](

    [ChartID] [varchar](50) NULL,

    [SourceCode] [varchar](50) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING ON

    GO

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'1', N'SC1')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'1', N'SC3')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'1', N'SC4')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'2', N'SC2')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'2', N'SC4')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'2', N'SC5')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'3', N'SC1')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'3', N'SC2')

    INSERT [dbo].[Relation] ([ChartID], [SourceCode]) VALUES (N'3', N'SC3')

    CREATE TABLE [dbo].[Chart](

    [ChartID] [varchar](50) NULL,

    [Chart_Type] [varchar](50) NULL,

    [ChartName] [varchar](50) NULL,

    [Year] [int] NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING ON

    GO

    INSERT [dbo].[Chart] ([ChartID], [Chart_Type], [ChartName], [Year]) VALUES (N'1', N'SRC', N'CHART-A', 2011)

    INSERT [dbo].[Chart] ([ChartID], [Chart_Type], [ChartName], [Year]) VALUES (N'2', N'SRC', N'CHART-B', 2011)

    INSERT [dbo].[Chart] ([ChartID], [Chart_Type], [ChartName], [Year]) VALUES (N'3', N'SRC', N'CHART-C', 2011)

    Thanks & Regards
    Syed Sami Ur Rehman
    SQL-Server (Developer)
    Hyderabad
    Email-sami.sqldba@gmail.com

  • December 19, 2011 at 4:05 am

    #1423024

    Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1223667-392-1.aspx

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply