If you cannot create a Tally table, you can make of this code to create a CTE for Tally Table and then use the real code to generate the desird...

Sachin, this might help you:

DECLARE @DATE DATETIME

SET @DATE = '01-01-1985'

SELECT DATEPART(YY,DATEADD(M,N-1,@DATE)) [YEAR], DATENAME(MM,DATEADD(M,N-1,@DATE)) [MONTH]

FROM Tally

WHERE DATEPART(YY,DATEADD(M,N-1,@DATE)) <= YEAR(GETDATE())

To know how to create Tally table, refer the following link from...

Suhbro, try this:

SELECT DISTINCT TOP(20) PM.Partner_Name,PL1.Location as Location_From,PL2.Location as Location_To,RS.Route_Key,RS.LeadTime_Min,RS.LeadTime_Max,CN.Begin_Journey_Date AS Start_Date,

CN.End_Journey_Date AS End_Date,RLH.Container_Key,RLH.Container_Id,RLH.Journey_Arrival_LeadTime

FROM dbo.Partner_Master PM

INNER JOIN dbo.Containers_Ended_Journey CN ON

CN.Enterprise_Partner_Key = PM.Partner_Key

INNER JOIN dbo.Route_Summary RS ON

CN.Enterprise_Partner_Key = RS.Enterprise_Partner_Key

INNER JOIN...

Would this help you:

SELECT name, MIN(price) min_pice , MAX(price) max_pice, min(date) min_date, max(date) max_date

from table

group by name

order by name

As said by Bhuvnesh, please go thro the article he referred...

How many rows do u have you in your table?

Not a problem , changebluesky, you are welcome! 🙂

Hey there.. try this:

declare @mytable table

(

id int,

potype varchar(10)

)

insert into @mytable values (1,'AA')

insert into @mytable values (1,'BB')

insert into @mytable values (1,'CC')

SELECT p1.id,

...

Hi there, try this code:

SELECT p1.Recnumber,

( SELECT [Subject] + ','

FROM View_RecNumberSubject p2

...

This sort of problems are called as "Running Totals". All you need is to go thro one of the finest articles that describes what Running Totals are, how to tackle...

This will get u there!

SELECT * FROM

( SELECT * FROM <TABLE> ) PIVOT_TABLE

PIVOT

(SUM(AMOUNT) FROM PRODUCT IN ([1],[2],[3]))

Replace <Table> with your table name!

Tell us if that helped!!

A slight modification on WayneS' code is producing exact simliar test timings as mine

Modified WayneS' Code:

;WITH

/*

--Am commenting this section of the code which is taking that extra time

Tens...

Test results on 5 runs between mine and WayneS'

Mewayne

cpuelapsedcpu elapsed

1391387672376

2375389671693

3390402672673

4391388703690

5391386671672

Result387.6390.4677.8620.8

Here's my solution.

;WITH TALLY AS

(

SELECT 1 N

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

...

You're welcome!

Hi purushot,, try these:

SELECT

DATEADD(YY, -2 , GETDATE()) AS [2_YRS_BACK_FULLDATE] ,

(DATEPART(YY,GETDATE()) + 2) AS ADD_2YRS