.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

SQL 2005 Query Assistance needed

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
 
Question: I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], ' ' AS [Debit], (CONVERT(varchar, SUM(pvp.totalfee))) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.CompanyI


View Complete Post


More Related Resource Links

SQL 2005 Query Assistance needed ASAP

  
I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 ($225.00 charge minus $65.00 adjustment). SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], 0.0 AS [Debit], SUM(pvp.totalfee) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.Compa

SQL Server 2005 Log Shipping and subsequent FULL backups that are needed

  
Afternoon,   I have a few Log Shipped DBs that are working great.   Currently they are set to fire off every 15 minutes 24/7.   My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.   If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?   Is there a special method to accomplish this?   As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?   I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.   Thanks

SQL Reporting Service 2005 - share schedule report performace T-SQL query?

  
Hi, I have SQL 2005 reporting services Shared Schedules and each schedule has its own subscribed report. I would like to have T-SQL 2005 to find out performance loading on each schedule. i.e. MySchedule_1 has 10 reports in it and AVEGARE report eaxecutiontime is like 3mins 5sec      MySchedule_2 has 7 reports in it and AVEGARE report eaxecutiontime is like 4mins 9sec Pls can I have T-SQL 2005 on ReportServer database to find out load on each schedules (and more drill-down to each report level for execution time)?

T-SQL 2005 query for Group BY and not GROUP in same query for SQL reporting service use purpose?

  
Hi, I have SQL 2005 table like bellow @OrderTable I want to display all row data and GROUP BY data as well for SQL Reporting Service Matrix purpose...   declare @OrderTable TABLE (OrderID varchar(10),OrderType varchar(20),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06','O1',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06','O2',105.48,'2010-06-12') INSERT INTO @OrderTable VALUES('P07','O3',555.00,'2010-06-09') INSERT INTO @OrderTable VALUES('P08','O1',10.22,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O1',55.66,'2010-03-17') INSERT INTO @OrderTable VALUES('P06','O1',45.44,'2010-03-17') INSERT INTO @OrderTable VALUES('P07','O3',477.81,'2010-03-18') INSERT INTO @OrderTable VALUES('P07','O3',78.85,'2010-03-18') INSERT INTO @OrderTable VALUES('P06','O1',78.08,'2010-04-09') INSERT INTO @OrderTable VALUES('P07','O2',899.90,'2010-04-22') INSERT INTO @OrderTable VALUES('P08','O3',25.33,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',859.01,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',7433.89,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O1',1005.41,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O2',455.20,'2010-06-09') INSERT INTO @OrderTable VALUES('P07','O3',85.30,'2010-06-12') INSERT INTO @OrderTable VALUES

T-SQL 2005 table query

  
Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

Sql Server 2005 ( remove header information from query output file "Job 'bustd_Daily_Query_Output_1

  
Dear All,I have scheduled job for querying output & asve it into particuler folder....but in the top of file i getting the message like "Job 'bustd_Daily_Query_Output_13July09' : Step 1, 'Query1' : Began Executing 2009-07-13 16:32:06"I want to remove this & want output in txt file format without above header....RegardsRavendra Cindia

SQL Query help needed

  
I made a change to my SQL tables but I am having trouble changing my query to acommodate that change.  This query works:   select members.playername, ratings.rating, MAX(ratings.DATE), ratings.type from roles left join ratings on roles.fk_member_id=ratings.member_ID_FKleft join members on roles.fk_member_id=members.pk_id where roles.fk_member_id in (SELECT ROLES.FK_MEMBER_ID FROM ROLES    WHERE ROLES.TYPE = 'Player'   And ratings.type='8'   AND FK_TEAMBAR_NUMBER = 109)group by playername, date, ratings.type, ratings.rating   The change is that now my application tracks historical data on a member's rating.  So now there are multiple records for each member.  I want my query to return a table of the entire team members and their most recent rating (most recent date).  The above query will pull all ratings for each team member.  I just need the most recent rating - therein lies my problem. Here are the table structures: RATINGS TABLE: RATING_ID          intMEMBER_ID_FK   intDATE                   smalldatetimeSET_ID_FK          intRATING         

How to query the Active directory using SQL Server 2005

  
Hi, I am trying to query Active server from Sql Server 2005 and getting error I used following script to create link server EXEC sp_addlinkedserver 'ADSI3', 'Active Directory Services 111', 'ADSDSOObject', 'adsdatasource' and then trying to connect using following statement SELECT   * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=local.mycompany.ca'' WHERE objectCategory=''User'' ') and getting following error An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=local.resound.ca' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". Please let me know if you know How to query the Active directory using SQL Server 2005 Thanks, Raj    Dreaming a world without any war in anywhere

SQL query help needed

  
The following query works: SELECT playername,        rating,        [DATE],        [TYPE] FROM   (SELECT members.playername,                ratings.rating,                ratings.DATE,                ratings.TYPE,                Row_number() OVER(PARTITION BY members.playername ORDER BY                ratings.DATE                       DESC )                rn         FROM   roles                LEFT JOIN ratings                  ON roles.fk_memb

employee attendance with in/out times (was "Query Help Needed...")

  

Dear Friends,


I am in problem & have to solve one query.
I have a one table with the empno, employee time in & time out, data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

I want to know that how much time every emp have not attend in the company per day(once he enter at morning and he come out in the end of the day)

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx in Advance...

SuraReddy, Emani


employee attendance with in/out times (was "Query Help Needed...")

  

Dear Friends,


I am in problem & have to solve one query.
I have a one table with the empno, employee time in & time out, data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

I want to know that how much time every emp have not attend in the company per day(once he enter at morning and he come out in the end of the day)

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx in Advance...

SuraReddy, Emani

 

 

The Table Like this


Create table emptime
(
 EMPCODE VARCHAR2(5),
 CARDNO VARCHAR2(5),
 EMP_CODE_NAME VARCHAR2(25),
 INOUTTIME TIMESTAMP(6),               
 IOGATENO VARCHAR2(10),               
 DOOR VARCHAR2(20),               
 IN_OUT VARCHAR2(10)               
 CARDDATE DTE
);      

2005 query runs slow in 2008

  

Hi,

I'm running some of our stored  procedures, written on 2005, on a test 2008 server

One of the stored procedures runs much slower on 2008, the execution plan is considerably different as well.

I've tried changing the compatability level, to no effect.

I'm wondering f there are any general actions I can take to mitigate this issue, or whether I'm just going to have to deal with these problems on a one by one basis.

thanks

 

 


Sean

Converting an Access pivot query to SQL 2005

  

Hi, I am new to table pivot and would like to know how to go about converting the following query in Access to SQL Server 2005:

 

TRANSFORM First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS FirstOfBUGDET_LINE_BURDENED_COST 
SELECT tbl_Proj_Budget.PROJ_ID,
 First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS [Total Of BUGDET_LINE_BURDENED_COST] 
FROM tbl_Proj_Budget 
GROUP BY tbl_Proj_Budget.PROJ_ID 
PIVOT tbl_Proj_Budget.RESOURCE_LIST_ALIAS;


1) what does the 1st line mean?

TRANSFORM First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS FirstOfBUGDET_LINE_BURDENED_COST

2) How would I conver this to TSQL ?

First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS [Total Of BUGDET_LINE_BURDENED_COST]

 

Thanks


Permissions needed to run DTS packages in SQLServer 2005

  

All,

I was asked to document the permissions to enumarate, modify and execute DTS packages which we have on our SQLServer 2005 production server(s).

I know that the user needs read-access to sysdtspackage to list the DTS package(s), the user also needs to be the Owner of the package or sysadmin inorder to modify the package.

Now, there are three ways of running a DTS package stored in the msdb

[1] Run the package from the DTS designer

[2] Run from the command line via DTSRun

[3] As a DTS Task in SSIS

I have a DTS package (ExtractWestCoast.dts) which I wanted to test for permissions. So, I created a login named DTSTest and gave it db_datareader role to the msdb database (Note that I also have a Windows Authentication based login - CorpDomain\grajee - into the sqlserver with sysadmin role). With this role given, I logged into SSMS using DTSTest and made sure I was able to see the list of DTSPackages. I did a sample run of ExtractWestCoast DTSpackage expecting it to fail. But to my surprise the DTS package executed successfully. Using my regular id (CorpDomain\grajee), I enabled the logging feature on the DTS package so that it creates entries in the sysdtslog msdb system table for every run to see under whom the package runs. Using the DTSTest login, I executed the DTS package and it executed successfully. I checked the

Refactor TSQL Query Help Needed

  

Hello,

I am looking at a lengthy query that needs refactoring and clean up. The overall jist of the query is this:

We have a customer with several items they have subscribed to.

We have a lookup table for these subscriptions with the number of days the subscription is good for.

We have another table that has a count of the days since the subscription began.

We basically then do a join and a big case (I mean really big) case statement with different logic for each subscription code that matches what the customer has subscribed to. In the end the sp returns the subscription code and a Y or N flag for if the subscription has expired.

I've looked at PIVOT and I can't see how it would help here. ROW_NUMBER isn't much use as there really aren't any sub queries. I'm racking my brain trying to figure out a way to simplify this monsterous case statement and coming up empty so I was hoping for some ideas from others. Redesign of tables and approach is an option as we are already moving alot of hard coding to table driven etc.

Thank You for your help and ideas.


Tracing of query in sql server 2005

  
hi friends,

Is it possible that while a query is running in sql server 2005 but cann't be possible to trace it in profiler or in activity monitor.


Thanks in Advance
Ravi

help with sql 2005 query+ if exists

  

Hi,

I need to write a query that returns history of a carton. I need to check if the carton's status had the status NEW any time. If yes, then I must return the history results from status NEW else return all the carton's history.

This is what i have so far without checking the NEW status

 

SELECT cartonstatus_name as Event ,cartonstatushistory_date as Date
FROM dbo.CartonStatusHistory
inner join Carton on Carton.carton_id=CartonStatusHistory.carton_id
inner join dbo.CartonStatus on CartonStatus.cartonstatus_id=CartonStatusHistory.cartonstatus_id
 WHERE CARTON_ID=@ItemId

 

 

 

 
CREATE TABLE [dbo].[Carton](
	[carton_id] [nvarchar](255) NOT NULL,
	[packlist_id] [nvarchar](30) NULL,
	[businessunit_id] [int] NOT NULL,
	[seasonyear_id] [int] NULL,
	[electronicinvoice_id] [int] NULL,
	[cartonstatus_id] [int] NULL,
	[cartonstatus_date] [smalldatetime] NULL,
	[StatusUser_id] [int] NULL,
 CONSTRAINT [PK_Carton] PRIMARY KEY CLUSTERED 
(
	[carton_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = O
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend