Query help

[nelco]

I have a table with following columns:
Item, Unit_Price, List_price, Start_date, End_date, Int_ind.

In my query – I have to show Current month sale. (The difference between start and end date should be 15 or more days)
In another query – I have to show next month sale. (The difference between start and end date should be 15 or more days)


Scenario 1: ( Current Month Sale)
If start_date and end_date are in the same month (say May) then my query is:

SELECT [ITEM], [UNIT_PRICE], [LIST_PRICE], (round(List_price,1) - round(unit_price,1)) as savings, [START_DATE], [END_DATE], [INT_IND] FROM TBL where INT_IND = 'SP'
and DATEDIFF(Day, Start_Date, End_Date) >=15 and datepart(month,start_date) in (select DATEPART (month,getdate())) and datepart(year,start_date) in (select DATEPART (year,getdate())) order by start_date.

The above query works correctly.

Scenario 2: (Current Month sale)

If start date is 29th or 30th of previous month and end date is 15th of current month then what should be my query.

Scenario 3: (Next Month Sale)
If start date and end date are in next month, then my query would be like:

SELECT [ITEM], [UNIT_PRICE], [LIST_PRICE], (round(List_price,1) - round(unit_price,1)) as savings, [START_DATE], [END_DATE], [INT_IND]
FROM TBL where INT_IND = 'SP'
and DATEDIFF(Day, Start_Date, End_Date) >=15 and datepart(month,start_date) in (select DATEPART (month,getdate())+1) and datepart(year,start_date) in (select DATEPART (year,getdate())) order by start_date.

Scenario 4: (Next month Sale)
If start date is 29th or 30th of this month and end date is 15th of next month then what should be my query.

Scenario 5: (Next Month sale)

If start date is next month (say June 5th ) and end date is 3rd July then what should be my query.


I will appreciate, if I can get any help.

 

[hmckillop]

This query should give you everything in one resultset

SELECT [ITEM],  [UNIT_PRICE], [LIST_PRICE], (round(List_price,1) - round(unit_price,1)) as savings, [START_DATE], [END_DATE], [INT_IND], 
convert(varchar, Month(END_DATE)) + '/' + convert(varchar, YEAR(END_DATE)) as MonthYearOfSale
 FROM TBL where INT_IND = 'SP'
and DATEDIFF(Day, Start_Date, End_Date) >=15 
order by start_date.

Other than that, I am not 100% certain on what it is you are looking for.
The MonthYearOfSale will always be the END Dates month and year and as such dont need seperate queries for each condition.

BTW what happens if datediff isnt >15?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[nelco]

Thanks hmckillop for the quick reply. The query you sent will display all the records.

I need to show in the month of May - only May sales and not other months.

To show May sale - It will be easy if the start and end dates are in May. The problem comes when the start date is in late April ( 28th or 29th of April) and end date is in May ( say 15th). What should be my query.

Same way - I need to show next month sale in May and that will be June. It will be easy if the start and end dates are in June. The problem comes when the start date is in late May ( 28th or 29th of April) and end date is in June ( say 15th). What should be my query.

 

[hmckillop]

try

SELECT [ITEM],  [UNIT_PRICE], [LIST_PRICE], (round(List_price,1) - round(unit_price,1)) as savings, [START_DATE], [END_DATE], [INT_IND], 
convert(varchar, Month(END_DATE)) + '/' + convert(varchar, YEAR(END_DATE)) as MonthYearOfSale
 FROM TBL 
where INT_IND = 'SP'
and DATEDIFF(Day, Start_Date, End_Date) >=15 
AND convert(varchar, Month(END_DATE)) + '/' + convert(varchar, YEAR(END_DATE))  = convert(varchar, Month(GET_DATE())) + '/' + convert(varchar, YEAR(GET_DATE())) )) 
order by start_date.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[nelco]

This works good in most of the situation. There were minor syntax errors like getdate().

With your query:
If start date is 2009-05-16 00:00:00.000
and end date is 2009-06-01 23:59:00.000
Month/sale it shows is 6/2010 instead of 5/2010.

 

[nelco]

This works good in most of the situation. There were minor syntax errors like getdate().

With your query:
If start date is 2009-05-16 00:00:00.000
and end date is 2009-06-01 23:59:00.000
Month/sale it shows is 6/2009 instead of 5/2009

 

[hmckillop]

Sorry, I thought if any of the sale was in next month then it meant it was next months sale.

How many days from start of month are the cut off for the previous months sales?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[nelco]

Well the start date could be 3-4 days before the start of next month ( end date) - for next month sale
start date is 2009-05-29 00:00:00.000
and end date is 2009-06-16 00:00:00.000


and end date could be max 3-4 days after the end of previous month (start day) - for current month sale
(start date is 2009-05-16 00:00:00.000
and end date is 2009-06-01 23:59:00.000)


Thanks for all your help.