Please provide a solution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please provide a solution

Author

Topic

mailtojis
Starting Member

2 Posts

Posted - 2013-05-23 : 01:32:45

Hi,
pls help me to find a solution.

I have two tables ,named tblMaster and tblSTudent.
tblMaster contains only type and its name.

tbl_Studentuse this master table.(i attached table creating scripts last)

I need to select Sfrom_Date ,S_Cat from tbl_Student with these conditions.

1. tbl_student grouped by S_Cat
2.for tblMaster contains name hard code as INDIA has minimum from date from tblstudent(ie in tblstudent having type 1 has minimum from date in the group)
3.If no type exists with 1 in tblStudent for a group s_cat ,then return from _date as null

so result look like
s_cat Sfrom_Date

1001 2011-10-10
1003 NULL
1004 NULL
1005 2011-11-11

Table scripts :

--------------------------------------------------
USE [test]
GO
/****** Object: Table [dbo].[tblMaster] Script Date: 05/23/2013 11:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblMaster](
[id] [int] NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblMaster] ([id], [Name]) VALUES (1, N'INDIA')
INSERT [dbo].[tblMaster] ([id], [Name]) VALUES (2, N'UK')
INSERT [dbo].[tblMaster] ([id], [Name]) VALUES (3, N'Canada')
/****** Object: Table [dbo].[tbl_Student] Script Date: 05/23/2013 11:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Student](
[Sid] [int] NULL,
[Sname] [nvarchar](50) NULL,
[Stype] [nvarchar](50) NULL,
[Sfrom_Date] [date] NULL,
[S_toDate] [date] NULL,
[S_Cat] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbl_Student] ([Sid], [Sname], [Stype], [Sfrom_Date], [S_toDate], [S_Cat]) VALUES (101, N'jgt', N'1', CAST(0xD3340B00 AS Date), CAST(0x41360B00 AS Date), 1001)
INSERT [dbo].[tbl_Student] ([Sid], [Sname], [Stype], [Sfrom_Date], [S_toDate], [S_Cat]) VALUES (102, N'shinto', N'1', CAST(0x23360B00 AS Date), CAST(0x41360B00 AS Date), 1001)
INSERT [dbo].[tbl_Student] ([Sid], [Sname], [Stype], [Sfrom_Date], [S_toDate], [S_Cat]) VALUES (103, N'Sharan', N'2', CAST(0xD4340B00 AS Date), CAST(0xAE370B00 AS Date), 1003)
INSERT [dbo].[tbl_Student] ([Sid], [Sname], [Stype], [Sfrom_Date], [S_toDate], [S_Cat]) VALUES (104, N'KIRAN', N'3', CAST(0x893A0B00 AS Date), CAST(0xF73B0B00 AS Date), 1004)
INSERT [dbo].[tbl_Student] ([Sid], [Sname], [Stype], [Sfrom_Date], [S_toDate], [S_Cat]) VALUES (105, N'ARUN', N'1', CAST(0xF3340B00 AS Date), CAST(0x61360B00 AS Date), 1005)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 01:35:24

How are the tables related? As of now I cant see any common columns using which we can relate them. Without that there's no way we can merge information from them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 01:39:39

Anyways your current output can be got by below logic


SELECT [S_Cat],
MIN(CASE WHEN Stype = N'1' THEN [Sfrom_Date] END) AS  Sfrom_Date
FROM [dbo].[tbl_Student]
GROUP BY [S_Cat]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mailtojis
Starting Member

2 Posts

Posted - 2013-05-23 : 06:16:34

quote:
Originally posted by visakh16

Anyways your current output can be got by below logic

SELECT [S_Cat],
MIN(CASE WHEN Stype = N'1' THEN [Sfrom_Date] END) AS Sfrom_Date
FROM [dbo].[tbl_Student]
GROUP BY [S_Cat]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks for ur reply.But the query worked ,but little bit problem.

tblStudent stype used as a foreign key tblMaster ID field.

But i want to filter data of type name as 'India' in tblMaster.

Now it 1.

But it may be changed in future. So i need a generic form,for finding ID of having name 'INDIA' IN tblMaster.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 06:32:10

[code]
SELECT s.[S_Cat],
MIN(s.[Sfrom_Date]) AS Sfrom_Date
FROM [dbo].[tbl_Student] s
INNER JOIN [dbo].[tblMaster] m
ON m.ID = s.Stype
WHERE m.[Name] = N'INDIA'
GROUP BY s.[S_Cat]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources