sql query doubt help me

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

salarypayment--paymentid,userid,month,year,salarypaid,chequeno,comment-contain record whose payment has done
tbl_users-userid,empname,emailid,address-conain record of all emp

i have 2 dropdown month,year
suppose user select jan 2012 from dropdown then
it show record in gridview of those
emp whose salary is pending and whose salary is already paid
i am not able to fire sql query anyone plz help me

i tried this

SQL

select u.UserID,u.FirstName+' '+u.LastName[name],st.SalaryPaid[SalaryPaid],dbo.ufn_MAFormatDate(st.PaymentDate)PaymentDate,st.ChequeNo[ChequeNo],st.Comments[Comments],pt.Desc_[PaymentType],st.PaymentID
  from tbl_Users u
 left outer join tbl_SalaryPaymentDetails st on st.UserID=u.UserID
 left outer  join TBL_PaymentType pt on pt.PaymentTypeID=st.PaymentType

 where u.UserID  in(select UserID from tbl_SalaryPaymentDetails where month =1 and year=113)

Posted 17-May-12 19:44pm

invisible@123

Updated 17-May-12 19:45pm

sravani.v

v2

Add a Solution

Comments

bhagirathimfs 18-May-12 1:55am

Can you please give the all table structures(All column with data type).
Your requirement is to show all the employees salary details???

invisible@123 18-May-12 2:00am

CREATE TABLE [dbo].[tbl_SalaryPaymentDetails](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EmpName] [nvarchar](100) NULL,
[month] [tinyint] NULL,
[year] [smallint] NULL,
[SalaryPaid] [decimal](12, 2) NULL,
[PaymentDate] [datetime] NULL,
[PaymentType] [int] NULL,
[ChequeNo] [nvarchar](50) NULL,
[Comments] [nvarchar](500) NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[LastModifiedOn] [datetime] NULL,
[LastModifiedBy] [int] NULL,
CONSTRAINT [SalaryPaymentDetails] PRIMARY KEY CLUSTERED
(
[PaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_SalaryPaymentDetails] ADD CONSTRAINT [DF_SalaryPaymentDetails_SalaryPaid] DEFAULT ((0)) FOR [SalaryPaid]
GO

ALTER TABLE [dbo].[tbl_SalaryPaymentDetails] ADD CONSTRAINT [DF_SalaryPaymentDetails_PaymentDate] DEFAULT (getutcdate()) FOR [PaymentDate]
GO

ALTER TABLE [dbo].[tbl_SalaryPaymentDetails] ADD CONSTRAINT [DF_SalaryPaymentDetails_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]
GO

CREATE TABLE [dbo].[tbl_Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Title] [tinyint] NULL,
[UserName] [nvarchar](200) NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[Password] [nvarchar](500) NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[LastModifiedOn] [datetime] NULL,
[LastModifiedBy] [int] NULL,
[MobileNo] [varchar](20) NULL,
[mDailCode] [varchar](10) NULL,
[EmailID] [varchar](200) NULL,
[Gender] [int] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[tbl_Users] ADD [MiddleName] [varchar](100) NULL
ALTER TABLE [dbo].[tbl_Users] ADD [Managerid] [int] NULL
ALTER TABLE [dbo].[tbl_Users] ADD CONSTRAINT [PK_tbl_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_Users] ADD CONSTRAINT [DF_tbl_Users_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]
GO

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bhagirathimfs · Accepted Answer · 2012-05-17T20:37:00

Solution 1

Try this

SQL

SELECT U.FIRSTNAME + '  ' + U.LASTNAME AS NAME,
       US.SALARYPAID AS SALARY,
       US.PAYMENTDATE AS DATE,
       US.COMMENTS AS COMMENTS ,
       US.PAYMENTTYPE AS PAYMENT_TYPE
  FROM TBL_USERS U
  LEFT OUTER JOIN TBL_SALARYPAYMENTDETAILS US
    ON US.USERID = U.USERID AND US.YEAR = 2012 ;

if you want to show for the specific month than add 'ADD US.MONTH = 2' at the end.

Posted 17-May-12 20:37pm

bhagirathimfs

Comments

invisible@123 18-May-12 2:40am

Thanks a lot Sir

Prasad_Kulkarni 18-May-12 2:43am

If the answer is useful then formally click on green button to 'Accept Solution'

bhagirathimfs 18-May-12 2:45am

WC :)